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

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 (view raw or flat)
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

pgsql-performance by date

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

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