Обсуждение: really stupid question about checkpointing

I understand that checkpointing is a necessary part of a pgsql
database, but I am also under the impression that you want to find a
balance between how frequently you checkpoint and how much a given
checkpoint has to do. It's all about balancing the disk I/O out to get
a consistent throughput and forstall the db 'stalling' while it writes
out large checkpoints. However, when I check out our production
system, I think we're checkpointing a little too frequently (I am
_not_ referring to the 'checkpointing too fast' message). An example:
Jul 26 04:40:05  checkpoint starting: time
Jul 26 04:40:35  checkpoint complete: wrote 150 buffers (0.1%); 0
transaction log file(s) added, 0 removed, 0 recycled; write=29.836 s,
Jul 26 04:40:35    sync=0.128 s, total=29.974 s
Jul 26 04:45:05  checkpoint starting: time
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,
Jul 26 04:45:48    sync=0.047 s, total=43.687 s
Jul 26 04:50:05  checkpoint starting: time
Jul 26 04:50:35  checkpoint complete: wrote 153 buffers (0.1%); 0
transaction log file(s) added, 0 removed, 0 recycled; write=30.418 s,
Jul 26 04:50:35    sync=0.148 s, total=30.577 s
Jul 26 04:55:05  checkpoint starting: time
Jul 26 04:55:26  checkpoint complete: wrote 108 buffers (0.0%); 0
transaction log file(s) added, 0 removed, 0 recycled; write=21.429 s,

While I see the number of buffers fluctuating decently, I note that
percentage only fluctuates from 0.0% to 0.4% for the duration of an
entire day. It seems to me that we might want to space the checkpoints
out a bit less frequently and get maybe 1 or 2% before we write things
out.

Is my understanding of all this accurate, or am I off base here? We're
running 8.3.7 (going to 8.4.x soon).  Checkpoint settings currently:
              name               |            start_setting
|            stop_setting             |        source

---------------------------------+-------------------------------------+-------------------------------------+----------------------
 checkpoint_segments             | 128
| 128                                 | configuration file
 checkpoint_warning              | 240
| 240                                 | configuration file

More than happy to provide additional info as requested. TIA!
--
Douglas J Hunley, RHCT
doug.hunley@gmail.com : http://douglasjhunley.com : Twitter: @hunleyd

Obsessively opposed to the typical.

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@gregsmith.com http://www.gregsmith.com Baltimore, MD