Expected frequency of auto_vacuum activity

Started by Dave Jenningsover 15 years ago4 messagesgeneral

dave@ambientindustries.com

over 15 years ago

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.

Matthew Walden

matthew.walden@gmail.com

over 15 years ago

In reply to: Dave Jennings (#1)

Re: Expected frequency of auto_vacuum activity

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@ambientindustries.com>wrote:

Show quoted text

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Matthew Walden

matthew.walden.list@gmail.com

over 15 years ago

In reply to: Dave Jennings (#1)

Re: Expected frequency of auto_vacuum activity

Dave,

Does your application use temporary tables? This may explain high
autovacuum activity in the catalog tables.

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@ambientindustries.com>wrote:

Show quoted text

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Dave Jennings

dave@ambientindustries.com

over 15 years ago

In reply to: Matthew Walden (#3)

Re: Expected frequency of auto_vacuum activity

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.

Show quoted text

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@ambientindustries.com <mailto:dave@ambientindustries.com>> 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@postgresql.org
<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general