Ernesto Quiñones<ernestoq@xxxxxxxxx> wrote: I understand the impulse to run autovacuum less frequently or less aggressively. When we first started running PostgreSQL the default configuration was very cautious. A lot of bloat would accumulate before it kicked in, at which point there was a noticeable performance hit, as it worked though a large number of dead pages. The first thing I did was to make it run less often, which only made things worse. The numbers we settled on through testing as optimal for us are very close to current default values (for recent major releases). Not only do queries run more quickly between autovacuum runs, because there is less dead space to wade through to get the current tuples, but the autovacuum runs just don't have the same degree of impact -- presumably because they find less to do. Some small, frequently updated tables when from having hundreds of pages down to one or two. > autovacuum_analyze_scale_factor 0,5 > autovacuum_analyze_threshold 50000 We use 0.10 + 10 in production. Defaults are now 0.10 + 50. That's the portion of the table plus a number of rows. Analyze just does a random sample from the table; it doesn't pass the whole table. > autovacuum_vacuum_scale_factor 0,5 > autovacuum_vacuum_threshold 50000 We use 0.20 + 10 in production. Defaults are now 0.20 + 50. Again, a proportion of the table (in this case what is expected to have become unusable dead space) plus a number of unusable dead tuples. > autovacuum_naptime 1h A one-page table could easily bloat to hundreds (or thousands) of pages within an hour. You will wonder where all your CPU time is going because it will constantly be scanning the same (cached) pages to find the one version of the row which matters. I recommend 1min. > vacuum_cost_delay 1s A vacuum run will never get much done at that rate. I recommend 10ms. > vacuum_cost_limit 200 We've boosted this to 600. Once you're in a "steady state", this is the setting you might want to adjust up or down as needed to make cleanup aggressive enough without putting a noticeable dent in performance while it is running. On 8.3 I believe you still need to worry about the fsm settings. Run your regular database vacuum with the VERBOSE option, and check what the last few lines say. If you don't have enough memory set aside to track free space, no vacuum regimen will prevent bloat. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
