Thread: 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...

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.

No. Disk read activity is ALWAYS 0, the system has a lot of disk cache.

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.

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.