Re: Expected frequency of auto_vacuum activity - Mailing list pgsql-general

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@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
>
>