Skip site navigation (1) Skip section navigation (2)

Re: lowering impact of checkpoints

From: Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>
To: depesz(at)depesz(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: lowering impact of checkpoints
Date: 2007-09-25 13:40:01
Message-ID: 1190727601.15101.7.camel@bnicholson-desktop (view raw, whole thread or download thread mbox)
Lists: pgsql-general
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.

In response to

pgsql-general by date

Next:From: Alvaro HerreraDate: 2007-09-25 13:53:06
Subject: Re: rules and command status question
Previous:From: Josh HarrisonDate: 2007-09-25 13:19:54
Subject: rules and command status question

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group