Background writer configuration - Mailing list pgsql-performance

We were seeing clusters of query timeouts with our web site, which were
corrected by adjusting the configuration of the background writer.  I'm
posting just to provide information which others might find useful -- I
don't have any problem I'm trying to solve in this regard.

The web site gets 1 to 2 million hits per day, with about the same
number of select queries run to provide data for the web pages.  The
load is distributed across multiple databases.  (We have four, but the
load is easily handled by any two of them, and we often take one or two
out of web use for maintenance or special statistical runs.)  Each
database gets the same stream of modification requests -- about 2.7
million database transactions per day.  Each transaction can contain
multiple inserts, updates, or deletes.  The peak times for both the web
requests and the data modifications are in the afternoon on business
days.  Most web queries run under a timeout limit of 20 seconds.

During peak times, we would see clusters of timeouts (where queries
exceeded the 20 second limit) on very simple queries which normally run
in a few milliseconds.  The pattern suggested that checkpoints were at
fault.  I boosted the settings for the background writer from the
defaults to the values below, and we saw a dramatic reduction in these
timeouts.  We also happened to have one machine which had been out of
the replication mix which was in "catch up" mode, processing the
transaction stream as fast as the database could handle it, without any
web load.  We saw the transaction application rate go up by a factor of
four when I applied these changes:

bgwriter_lru_percent = 2.0
bgwriter_lru_maxpages = 250
bgwriter_all_percent = 1.0
bgwriter_all_maxpages = 250

This was with shared_buffers = 20000, so that last value was
effectively limited to 200 by the percentage.

I then did some calculations, based on the sustained write speed of our
drive array (as measured by copying big files to it), and we tried
this:

bgwriter_lru_percent = 20.0
bgwriter_lru_maxpages = 200
bgwriter_all_percent = 10.0
bgwriter_all_maxpages = 600

This almost totally eliminated the clusters of timeouts, and caused the
transaction application rate to increase by a factor of eight over the
already-improved speed.  (That is, we were running 30 to 35 times as
many transactions per minute into the database, compared to the default
background writer configuration.)  I'm going to let these settings
settle in for a week or two before we try adjusting them further (to see
if we can eliminate those last few timeouts of this type).

I guess my point is that people shouldn't be shy about boosting these
numbers by a couple orders of magnitude from the default values.  It may
also be worth considering whether the defaults should be something more
aggressive.

-Kevin