lowering impact of checkpoints

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: lowering impact of checkpoints
Date: 2007-09-25 09:58:33
Message-ID: 20070925095833.GA20085@depesz.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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"?

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Devrim GÜNDÜZ 2007-09-25 10:27:25 Re: PgpoolAdmin installation
Previous Message Ashish Karalkar 2007-09-25 09:23:40 Re: PgpoolAdmin installation