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
