Search Postgresql Archives

enabling autovacuum

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


The table description is:
id | integer | not null default nextval(('"rcpt_audit_id_seq"'::text)::regclass) | msg_audit_id | integer | | mailuser_id | integer | | username | text | | domain | text | | copies | integer | | end_msg_size | integer | | disp_type | integer | | disp_id | integer | | disp_action | text | | disposition | text | | hdrs | text | |

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?


Thanks,
  Jeremy

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux