2010/2/25 Tory M Blue <tmblue@xxxxxxxxx>: > 2010/2/25 Devrim GÜNDÜZ <devrim@xxxxxxxxxx>: >> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
