[resending because I accidentally failed to include the list] Kiriakos Tsourapas wrote: > I am taking your suggestions one step at a time. > > I changed my configuration to a much more aggressive autovacuum > policy (0.5% for analyzing and 1% for autovacuum). > > autovacuum_naptime = 1min > autovacuum_vacuum_threshold = 50 > #autovacuum_analyze_threshold = 50 > autovacuum_vacuum_scale_factor = 0.01 > autovacuum_analyze_scale_factor = 0.005 > > I had tables with 180.000 record and another with 2M records, so > the default values of 0.2 for autovacuum would mean that 18.000 and > 200K records would have to change respectively, delaying the vacuum > for many days. I am concerned that your initial email said that you had this setting: autovacuum_naptime = 28800 This is much too high for most purposes; small, frequently-modified tables won't be kept in good shape with this setting. Perhaps you're not having that problem at the moment, but it's risky to assume that you don't and never will. When autovacuum wakes up and there is nothing to do it should go back to sleep very quickly. Don't expect too much from just making autovacuum run more often until you have eliminated existing bloat (autovacuum generally just limits further growth of bloat) and updated to the latest 8.4 minor release. The following bugs fixes are among many you are living without until you upgrade: - Prevent show_session_authorization() from crashing within autovacuum processes (Tom Lane) - Fix persistent slowdown of autovacuum workers when multiple workers remain active for a long time (Tom Lane) The effective vacuum_cost_limit for an autovacuum worker could drop to nearly zero if it processed enough tables, causing it to run extremely slowly. - Fix VACUUM so that it always updates pg_class.reltuples/relpages (Tom Lane) This fixes some scenarios where autovacuum could make increasingly poor decisions about when to vacuum tables. - Fix btree index corruption from insertions concurrent with vacuuming (Tom Lane) An index page split caused by an insertion could sometimes cause a concurrently-running VACUUM to miss removing index entries that it should remove. After the corresponding table rows are removed, the dangling index entries would cause errors (such as "could not read block N in file ...") or worse, silently wrong query results after unrelated rows are re-inserted at the now-free table locations. This bug has been present since release 8.2, but occurs so infrequently that it was not diagnosed until now. If you have reason to suspect that it has happened in your database, reindexing the affected index will fix things. - Ensure autovacuum worker processes perform stack depth checking properly (Heikki Linnakangas) Previously, infinite recursion in a function invoked by auto-ANALYZE could crash worker processes. - Only allow autovacuum to be auto-canceled by a directly blocked process (Tom Lane) The original coding could allow inconsistent behavior in some cases; in particular, an autovacuum could get canceled after less than deadlock_timeout grace period. - Improve logging of autovacuum cancels (Robert Haas) -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
