Hi Matthew,
Yes, I do have one query that creates a temporary table and that query
runs reasonably often, so that's a good explanation for the catalog tables.
Your query works perfectly on 8.4 and the ratio of dead rows to live
rows is generally quite small so it looks like everything is working as
it should.
Thanks very much for the queries and the explanation. Perfect.
Dave.
On 15/11/10 19:57, Matthew Walden wrote:
Dave,
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?
On Mon, Nov 15, 2010 at 6:55 PM, Dave Jennings
<dave@xxxxxxxxxxxxxxxxxxxxx <mailto: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
<mailto:pgsql-general@xxxxxxxxxxxxxx>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
