On Wed, 16 Apr 2008, Marinos Yannikos wrote:
to save some people a headache or two: I believe we just solved our performance problem in the following scenario:
I was about to ask your PostgreSQL version but since I see you mention wal_writer_delay it must be 8.3. Knowing your settings for shared_buffers and checkpoint_segments in particular would make this easier to understand.
You also didn't mention what disk controller you have, or how much write cache it has (if any).
This helped with our configuration: bgwriter_delay = 10000ms # 10-10000ms between rounds bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round
The default for bgwriter_delay is 200ms = 5 passes/second. You're increasing that to 10000ms means one pass every 10 seconds instead. That's almost turning the background writer off. If that's what improved your situation, you might as well as turn it off altogether by setting all the bgwriter_lru_maxpages parameters to be 0. The combination you describe here, running very infrequently but with lru_maxpages set to its maximum, is a bit odd.
Other options we have tried/used were shared_buffers between 200MB and 20GB, wal_buffers = 256MB, wal_writer_delay=5000ms ...
The useful range for wal_buffers tops at around 1MB, so no need to get extreme there. wal_writer_delay shouldn't matter here unless you turned on asyncronous commit.
-- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
