really stupid question about checkpointing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx : http://douglasjhunley.com : Twitter: @hunleyd

Obsessively opposed to the typical.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux