On Jan 28, 2008 10:17 PM, Jeremy Harris <jgh@xxxxxxxxxxx> wrote: > Hi, > > We're starting to run autovacuum for the first time on a system > that's been running with nightly cron-driven vacuum for some time. > > Version: > PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070418 (Red Hat 4.1.2-10) > > We have one problematic table, which has a steady stream of entries > and a weekly mass-delete of ancient history. The "bloat" query from > Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns: > > schemaname | tablename | reltuples | relpages | otta | tbloat | wastedpages | wastedbytes | wastedsize | iname | ituples | ipages | iotta | ibloat | wastedipages | wastedibytes | wastedisize > ------------+------------+-----------+----------+------+--------+-------------+-------------+------------+-----------------------------+---------+--------+-------+--------+--------------+--------------+------------- > public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 | 1194262528 | 1139 MB | rcpt_audit_msg_audit_id_idx | 1300300 | 6798 | 3819 | 1.8 | 2979 | 24403968 | 23 MB > public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 | 1194262528 | 1139 MB | rcpt_audit_id_idx | 1300300 | 4727 | 3819 | 1.2 | 908 | 7438336 | 7264 kB > We have uncommented "autovacuum = on" in postgresql.conf and run > "service postgresql reload". pg_stat_all_tables shows 4 tables > as autoanalyzed at about that time; 3 of which were also > autovacuumed. The problem table is not included; no other autos > are logged there in the succeeding 24 hours. > Is other action needed to enable autovacuum? > > > The autovacuum tuning parameters are all at default settings. > We have > max_fsm_pages = 2000000 > max_fsm_relations = 100000 ... > Are there any other changes we should make to stop this table > getting so bloated? Is it possible that this table didn't see many updates, today? You could add an entry to pg_catalog.pg_autovacuum to customize the handling of your Favorite Table. http://www.postgresql.org/docs/8.2/static/catalog-pg-autovacuum.html You might lower the thresholds for that table... -- http://linuxfinances.info/info/linuxdistributions.html "The definition of insanity is doing the same thing over and over and expecting different results." -- assortedly attributed to Albert Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
