really stupid question about checkpointing

From: Doug Hunley <doug(dot)hunley(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: really stupid question about checkpointing
Date: 2009-07-28 17:18:39
Message-ID: 7284e2210907281018r2cfa55fbl756a7375fbfeb913@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(dot)hunley(at)gmail(dot)com : http://douglasjhunley.com : Twitter: @hunleyd

Obsessively opposed to the typical.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Dave Youatt 2009-07-28 18:42:16 Re: hyperthreaded cpu still an issue in 8.4?
Previous Message Nikolas Everett 2009-07-28 14:25:45 Re: Will Postgres ever lock with read only queries?