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.