Re: enabling autovacuum

On Jan 28, 2008 10:17 PM, Jeremy Harris <jgh(at)wizmail(dot)org> 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