Does your application use temporary tables? This may explain high autovacuum activity in the catalog tables.
With regards to your tables, I wrote this very script to give me an indication of the amount of required activity on the user tables. I deliberately keep this one simple and it doesn't include the associated TOAST rows but you get the idea (it works on 9.0 you may need to test on 8).
select relid,
schemaname,
relname,
n_live_tup as live_rows,
n_dead_tup as dead_rows,
round(cast(n_dead_tup as decimal)/cast(n_live_tup as decimal),3) fraction_changed,
to_char(greatest(last_
autovacuum,last_vacuum),'DD/MM/YYYY HH24:MI:SS') last_vacuum,
to_char(greatest(last_autoanalyze,last_analyze),'DD/MM/YYYY HH24:MI:SS') last_analyze
from pg_stat_all_tables a
where schemaname not in ('pg_catalog','pg_toast','information_schema');
select name,
setting
from pg_settings
where name in ('autovacuum_vacuum_threshold','autovacuum_vacuum_scale_factor','autovacuum_analyze_scale_factor','autovacuum_analyze_threshold');
If your tables are particularly large then the number of dead rows may not qualify autovacuum_vacuum_scale_factor. What is the ratio between live and dead rows?
to_char(greatest(last_autoanalyze,last_analyze),'DD/MM/YYYY HH24:MI:SS') last_analyze
from pg_stat_all_tables a
where schemaname not in ('pg_catalog','pg_toast','information_schema');
select name,
setting
from pg_settings
where name in ('autovacuum_vacuum_threshold','autovacuum_vacuum_scale_factor','autovacuum_analyze_scale_factor','autovacuum_analyze_threshold');
If your tables are particularly large then the number of dead rows may not qualify autovacuum_vacuum_scale_factor. What is the ratio between live and dead rows?
On Mon, Nov 15, 2010 at 6:55 PM, Dave Jennings <dave@xxxxxxxxxxxxxxxxxxxxx> wrote:
Hi there,
I'm wondering if I'm seeing the appropriate amount of vacuuming in my 8.4 database.
I have a database with about twenty tables ranging from small, mostly static, tables to tables with tens or hundreds of thousands of rows and a fair number of inserts and updates (but very few deletes).
I have auto_vacuum with the default values (except "log_autovacuum_min_duration=0") but when I check the logs I see lots of vacuuming of a few pg_catalog tables - every 30 mins or so - but very few vacuums of my public tables. Just two occurences in the last week.
According to pg_stat_all_tables only two tables in the public schema have ever been auto-vacuumed.
Is this expected?
Thanks,
Dave.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
