From: | Jeremy Harris <jgh(at)wizmail(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | enabling autovacuum |
Date: | 2008-01-28 22:17:27 |
Message-ID: | 479E5477.5020506@wizmail.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Colson | 2008-01-28 22:18:43 | using = in a function |
Previous Message | Dann Corbit | 2008-01-28 22:11:21 | Re: Table has duplicate keys, what did I do |