Dear All,
Thanks for all your help so far. This page was particularly helpful:
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
(does the advice for 8.3 apply unchanged to 8.4?)
I'm still hitting issues with this though: sync is taking 7-10 seconds
and I need to get it down to nearer 3.
We're running a lightly-loaded system which has a realtime response
requirement of 0.5 seconds all the time (with a few seconds permissible
occasionally, but never exceeding 10).
So far, I've set checkpoint_segments to 128, timeout to 10min, and
completion_target to 0.8. This helps, but not as much as I'd hoped.
But I haven't touched any of the other WAL or BG Writer settings.
Where should I look next?
Should I be looking at the BG Writer settings,
or should I look at the Linux VM configuration?
(eg changing /proc/sys/vm/dirty_background_ratio from 5 to 1)
Or would it be most useful to try to move the WAL to a different disk?
Latest messages:
# tail -f /var/log/postgresql/postgresql-8.4-main.log | grep check
2009-12-08 09:12:00 GMT LOG: checkpoint starting: time
2009-12-08 09:20:09 GMT LOG: checkpoint complete: wrote 51151 buffers
(8.9%); 0 transaction log file(s) added, 0 removed, 23 recycled;
write=479.669 s, sync=9.852 s, total=489.553 s
2009-12-08 09:22:00 GMT LOG: checkpoint starting: time
2009-12-08 09:30:07 GMT LOG: checkpoint complete: wrote 45772 buffers
(7.9%); 0 transaction log file(s) added, 0 removed, 24 recycled;
write=479.706 s, sync=7.337 s, total=487.120 s
2009-12-08 09:32:00 GMT LOG: checkpoint starting: time
2009-12-08 09:40:09 GMT LOG: checkpoint complete: wrote 47043 buffers
(8.2%); 0 transaction log file(s) added, 0 removed, 22 recycled;
write=479.744 s, sync=9.300 s, total=489.122 s
2009-12-08 09:42:00 GMT LOG: checkpoint starting: time
2009-12-08 09:50:07 GMT LOG: checkpoint complete: wrote 48210 buffers
(8.4%); 0 transaction log file(s) added, 0 removed, 23 recycled;
write=479.689 s, sync=7.707 s, total=487.416 s
Thanks a lot,
Richard
