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

Re: really stupid question about checkpointing

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Doug Hunley <doug(dot)hunley(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: really stupid question about checkpointing
Date: 2009-07-29 02:08:52
Message-ID: alpine.GSO.2.01.0907282201070.25458@westnet.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, 28 Jul 2009, Doug Hunley wrote:

> Jul 26 04:45:48  checkpoint complete: wrote 219 buffers (0.1%); 0
> transaction log file(s) added, 0 removed, 0 recycled; write=43.634 s,

Each buffer is 8KB here.  So this one, the largest of the checkpoints you 
showed in your sample, is writing out 1.71MB spread out over 43 seconds. 
It hardly seems worthwhile to further spread out I/O when the total amount 
of it is so small.

> checkpoint_segments             | 128

This is on the high side, and given the low buffer write statistics you're 
seeing I'd bet that it's checkpoint_timeout you'd need to increase in 
order spread out checkpoints further.  You might get a useful improvement 
increasing checkpoint_timeout a bit, checkpoint_segments you already have 
set to an extremely high value--one that is normally only justified if you 
have a lot more write activity than your logs suggest.

If I were you, I'd cut checkpoint_segments in half, double 
checkpoint_timeout, and check back again on the logs in a day.  That 
should reduce the amount of disk space wasted by the pg_log overhead while 
netting you better performance, because right now you're probably only 
having timed checkpoints rather than segment based ones.  If you look at 
pg_stat_bgwriter you'll probably find that checkpoints_req is close to 0 
while checkpoints_timed is not.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

pgsql-performance by date

Next:From: Stefan KaltenbrunnerDate: 2009-07-29 04:31:09
Subject: Re: hyperthreaded cpu still an issue in 8.4?
Previous:From: Scott MarloweDate: 2009-07-28 23:23:29
Subject: Re: hyperthreaded cpu still an issue in 8.4?

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