Re: lowering impact of checkpoints

On Tue, 2007-09-25 at 11:58 +0200, hubert depesz lubaczewski wrote:
> hi,
> our system is handling between 600 and 2000 transactions per second. all
> of them are very small, very fast. typical query runs in under 1ms.
> yes - sometimes we get queries that take longer than then should get.
> simple check shows that we have a very visible pattern of
> every-5-minutes peak.
> in the minute that there is checkpoint - we get usually 15-20 times more
> queries "over 500 ms" than in other minutes.
>
> we are using 8.2.4 (upgrade will be soon), with these settings:
> # select name, setting, unit from pg_settings where name ~* 'bgwriter|wal|checkpoint';
> name | setting | unit
> -----------------------+-----------+--------
> bgwriter_all_maxpages | 5 |
> bgwriter_all_percent | 0.333 | [null]
> bgwriter_delay | 200 | ms
> bgwriter_lru_maxpages | 5 |
> bgwriter_lru_percent | 1 | [null]
> checkpoint_segments | 32 |
> checkpoint_timeout | 300 | s
> checkpoint_warning | 30 | s
> wal_buffers | 128 | 8kB
> wal_sync_method | fdatasync | [null]
> (10 rows)
>
> is there anything i can change to make it "smoother"?

Sounds like bgwriter is not flushing dirty pages quickly enough, so
there is still a lot of work to do at checkpoint time. You probably
need to tune it. This can be a tough thing to do properly though.
There are no magic values to suggest, as what will work is highly
dependent on your hardware and your applications pattern of use.

If possible, up the settings for bgwriter_all_percent a *little* and
perhaps bgwriter_all_maxpages and see if it helps. You can change
these with a reload. If you are doing this on a production system as
opposed to a test system, keep a close eye on what is going on, as it is
possible that you can make things worse.

I would start with something like 2% for bgwriter_all_maxpages and see
if that helps things out.

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.