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

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 (view raw or flat)
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

pgsql-general by date

Next:From: Devrim GÜNDÜZDate: 2007-09-25 10:27:25
Subject: Re: PgpoolAdmin installation
Previous:From: Ashish KaralkarDate: 2007-09-25 09:23:40
Subject: Re: PgpoolAdmin installation

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