enabling autovacuum
| От | Jeremy Harris | 
|---|---|
| Тема | enabling autovacuum | 
| Дата | |
| Msg-id | 479E5477.5020506@wizmail.org обсуждение исходный текст  | 
		
| Ответы | 
                	
            		Re: enabling autovacuum
            		
            		 [OT] Re: enabling autovacuum Re: enabling autovacuum  | 
		
| Список | 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
		
	В списке pgsql-general по дате отправления: