Gael Le Mignot wrote:
The delay is the delay of the "sync" part of the checkpoints : 2010-10-21 16:39:15 CEST LOG: checkpoint complete: wrote 365 buffers (11.9%); 0 transaction log file(s) added, 0 removed, 3 recycled; write=0.403 s, sync=21.312 s, total=21.829 s Maybe there is something I misunderstood, but aren't the checkpoints supposed to run smoothly over the checkpoint_completion_target interval ?
Well, first off you have to get the checkpoints spaced out in time enough for that to work. Both checkpoint_segments and possibly checkpoint_timeout may also need to be increased in order for the checkpoint write spreading code to work. When I start seeing long sync times, I'll usually shoot for >64 segments and >10 minutes for the timeout to give that smoothing work some room to do what it's supposed to.
However, only the main checkpoint writes are spread over time. The hope is that by the time the sync phase starts, the operating system will have already written most of them out. Sometimes, particularly in servers with lots of RAM for caching writes, this doesn't happen. In that case, you can have gigabytes of data queued up at the beginning of the sync phase--which is not spread out at all.
We are currently working on a "spread sync" feature for PostgreSQL that makes this problem better on platforms/filesystems it's possible to improve behavior on (you can't do anything about this issue on ext3 for example). I'll be talking about that development at the PgWest conference in a two weeks: https://www.postgresqlconference.org/content/righting-your-writes and hope to submit a patch with a first version of this feature to the November development CommitFest, in hopes of it making it into version 9.1.
If you can't come up with any solution here and need help with your current version sooner than that, we've already backported this improvement all the way to V8.3; drop me an off-list note if you want to discuss consulting services in this area we have available. If you're lucky, just adjusting the segment and timeout values may be enough for you.
-- Greg Smith, 2ndQuadrant US greg@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance" http://www.2ndquadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
