Re: bgwriter, checkpoints, curious (seeing delays) - Mailing list pgsql-performance

2010/2/25 Tory M Blue <tmblue@gmail.com>:
> 2010/2/25 Devrim GÜNDÜZ <devrim@gunduz.org>:
>> On Thu, 2010-02-25 at 22:12 -0800, Tory M Blue wrote:
>>> shared_buffers = 1500MB
>>
>> Some people tend to increase this to 2.2GB(32-bit) or 4-6 GB (64 bit),
>> if needed. Please note that more shared_buffers will lead to more
>> pressure on bgwriter, but it also has lots of benefits, too.
>>
>>> work_mem = 100MB
>>
>> This is too much. Since you have 300 connections, you will probably swap
>> because of this setting, since each connection may use this much
>> work_mem. The rule of the thumb is to set this to a lower general value
>> (say, 1-2 MB), and set it per-query when needed.
>>
>>> checkpoint_segments = 100
>>> checkpoint_warning = 3600s
>>
>> What about checkpoint_timeout? Please note that even if
>> checkpoint_segments = 100, if timeout value is low (say 5 mins),
>> PostgreSQL will probably checkpoint every checkpoint_timeout minutes
>> (unless PostgreSQL creates $checkpoint_segments xlogs before
>> checkpoint_timeout value). Depending on your workload, this may not be
>> intended, and it may cause spikes -- which will lead to the issues you
>> complain.
>>
>> I'll stop here, and suggest you read this:
>>
>> http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
>>
>> for details about this subject. As noted there, if you are running 8.3+,
>> pg_stat_bgwriter will help you to tune checkpoint & bgwriter settings.
>>
>> -HTH.
>
> Checkpoint_timeout is the default and that looks like 5 mins (300
> seconds). And is obviously why I have such a discrepancy between time
> reached and requested.
>
> Thank you sir, that's actually the page that I've spent much of my
> time on this eve :)  I'll continue to read and check my configuration
> settings.
>
> Tory
Also since I set the log on today I have some log information
regarding the checkpoints
2010-02-25 22:08:13 PST    LOG:  checkpoint starting: time
2010-02-25 22:10:41 PST    LOG:  checkpoint complete: wrote 44503
buffers (23.2%); 0 transaction log file(s) added, 0 removed, 20
recycled; write=148.539 s, sync=0.000 s, total=148.540 s
2010-02-25 22:13:13 PST    LOG:  checkpoint starting: time
2010-02-25 22:15:37 PST    LOG:  checkpoint complete: wrote 38091
buffers (19.8%); 0 transaction log file(s) added, 0 removed, 20
recycled; write=144.713 s, sync=0.000 s, total=144.714 s
2010-02-25 22:18:13 PST    LOG:  checkpoint starting: time
2010-02-25 22:20:42 PST    LOG:  checkpoint complete: wrote 38613
buffers (20.1%); 0 transaction log file(s) added, 0 removed, 19
recycled; write=149.870 s, sync=0.000 s, total=149.871 s
2010-02-25 22:23:13 PST    LOG:  checkpoint starting: time
2010-02-25 22:25:42 PST    LOG:  checkpoint complete: wrote 39009
buffers (20.3%); 0 transaction log file(s) added, 0 removed, 19
recycled; write=149.876 s, sync=0.000 s, total=149.877 s
2010-02-25 22:28:13 PST    LOG:  checkpoint starting: time
2010-02-25 22:30:43 PST    LOG:  checkpoint complete: wrote 30847
buffers (16.1%); 0 transaction log file(s) added, 0 removed, 19
recycled; write=150.000 s, sync=0.000 s, total=150.001 s
2010-02-25 22:33:13 PST    LOG:  checkpoint starting: time
2010-02-25 22:35:43 PST    LOG:  checkpoint complete: wrote 11917
buffers (6.2%); 0 transaction log file(s) added, 0 removed, 14
recycled; write=150.064 s, sync=0.000 s, total=150.065 s
2010-02-25 22:38:13 PST    LOG:  checkpoint starting: time
2010-02-25 22:40:43 PST    LOG:  checkpoint complete: wrote 10869
buffers (5.7%); 0 transaction log file(s) added, 0 removed, 5
recycled; write=149.995 s, sync=0.000 s, total=149.996 s
2010-02-25 22:43:13 PST    LOG:  checkpoint starting: time
2010-02-25 22:45:41 PST    LOG:  checkpoint complete: wrote 31424
buffers (16.4%); 0 transaction log file(s) added, 0 removed, 4
recycled; write=148.597 s, sync=0.000 s, total=148.598 s
2010-02-25 22:48:13 PST    LOG:  checkpoint starting: time
2010-02-25 22:50:42 PST    LOG:  checkpoint complete: wrote 33895
buffers (17.7%); 0 transaction log file(s) added, 0 removed, 17
recycled; write=149.825 s, sync=0.000 s, total=149.826 s
2010-02-25 22:53:13 PST    LOG:  checkpoint starting: time
2010-02-25 22:53:17 PST postgres postgres [local] LOG:  unexpected EOF
on client connection
2010-02-25 22:55:43 PST    LOG:  checkpoint complete: wrote 34155
buffers (17.8%); 0 transaction log file(s) added, 0 removed, 15
recycled; write=150.045 s, sync=0.000 s, total=150.046 s
2010-02-25 22:58:13 PST    LOG:  checkpoint starting: time
2010-02-25 23:00:41 PST    LOG:  checkpoint complete: wrote 33873
buffers (17.6%); 0 transaction log file(s) added, 0 removed, 15
recycled; write=148.223 s, sync=0.000 s, total=148.224 s
2010-02-25 23:03:13 PST    LOG:  checkpoint starting: time