Greg Smith schrieb:
You also didn't mention what disk controller you have, or how much write
cache it has (if any).
8.3.1, Controller is
http://www.infortrend.com/main/2_product/es_a08(12)f-g2422.asp with 2GB
cache (writeback was enabled).
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.
Perhaps the background writer takes too long to find the required number
of dirty pages among the 16GB shared buffers (currently), which should
be mostly clean. We could reduce the shared buffers to a more commonly
used amount (<= 2GB or so) but some of our most frequently used tables
are in the 8+ GB range and sequential scans are much faster with this
setting (for ~, ~* etc.).
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.
I was under the impression that wal_buffers should be kept at/above the
size of tyical transactions. We do have some large-ish ones that are
time-critical.
-mjy
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
