Обсуждение: How to speedup CHECKPOINTs?
Hello.
The quintessence of the problem explained in previous letters: CHECKPOIND command works quite slow, disk write activity is high just after it is executed, and diring the CHECKPOINT SELECT queries work many times slower than usual.
I experimended with bgwriter, but it affects the CHECKPOINT performance very little (possibly 2-3 times faster but no more).
What else could I tune?
And the general question - why SELECT queries slowdown during the CHECKPOINT? I thought that Postgres is a version-based database and read queries are never blocked...
The quintessence of the problem explained in previous letters: CHECKPOIND command works quite slow, disk write activity is high just after it is executed, and diring the CHECKPOINT SELECT queries work many times slower than usual.
I experimended with bgwriter, but it affects the CHECKPOINT performance very little (possibly 2-3 times faster but no more).
What else could I tune?
And the general question - why SELECT queries slowdown during the CHECKPOINT? I thought that Postgres is a version-based database and read queries are never blocked...
Dmitry Koterov wrote: > And the general question - why SELECT queries slowdown during the > CHECKPOINT? I thought that Postgres is a version-based database and read > queries are never blocked... Because the disk is busy.
On 3/28/07, Joseph S <jks@selectacast.net> wrote:
Is your pg_xlog on a separate disk?
Dmitry Koterov wrote:
> And the general question - why SELECT queries slowdown during the
> CHECKPOINT? I thought that Postgres is a version-based database and read
> queries are never blocked...
Because the disk is busy.
Is your pg_xlog on a separate disk?
No. Disk read activity is ALWAYS 0, the system has a lot of disk cache.
On 3/28/07, Joseph S <jks@selectacast.net > wrote:
Dmitry Koterov wrote:
> And the general question - why SELECT queries slowdown during the
> CHECKPOINT? I thought that Postgres is a version-based database and read
> queries are never blocked...
Because the disk is busy.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
No, but disks are about 120 MB/s speed, peak writes during CHECKPOINT are only 20-30 MB/s, and there is no disk read activity at all, so - no matter where the xlog resides.
But now seems bgwriter tuning gets some effect, CHECKPOINT is running faster (about 2-3 seconds instead of 10-15). It is still beats the performance, but less.
But now seems bgwriter tuning gets some effect, CHECKPOINT is running faster (about 2-3 seconds instead of 10-15). It is still beats the performance, but less.
On 3/29/07, CAJ CAJ <pguser@gmail.com> wrote:
On 3/28/07, Joseph S <jks@selectacast.net> wrote:Dmitry Koterov wrote:
> And the general question - why SELECT queries slowdown during the
> CHECKPOINT? I thought that Postgres is a version-based database and read
> queries are never blocked...
Because the disk is busy.
Is your pg_xlog on a separate disk?
On Thu, 2007-03-29 at 15:13, Dmitry Koterov wrote: > No, but disks are about 120 MB/s speed, peak writes during CHECKPOINT > are only 20-30 MB/s, and there is no disk read activity at all, so - > no matter where the xlog resides. > > But now seems bgwriter tuning gets some effect, CHECKPOINT is running > faster (about 2-3 seconds instead of 10-15). It is still beats the > performance, but less. Well, I assume bgwriter isn't the only thing writing to those disks. Which would mean that your bgwriter output is random access not sequential. Which would mean putting pg_xlog on another set of drives where it wasn't competing with other postgresql (and system) processes for drive access would speed things up. Or getting a good battery backed RAID controller. That's the first step to building a fast db server if you're using built in storage. but I could be wrong or have misunderheard what you were saying.