Re: enabling autovacuum

On Jan 28, 2008 10:17 PM, Jeremy Harris <jgh@wizmail.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