Can I Determine if AutoVacuum Does Anything? - Mailing list pgsql-performance

From David Crane
Subject Can I Determine if AutoVacuum Does Anything?
Date
Msg-id 41ED0E73B2268F4D9E4081FAB5ED05FD0351BB0E@midas.utopiasystems.net
Whole thread Raw
Responses Re: Can I Determine if AutoVacuum Does Anything?  (Alvaro Herrera <alvherre@commandprompt.com>)
List pgsql-performance

We’ve had our PostgreSQL 8.1.4 installation configured to autovacuum since January, but I suspect it might not be doing anything.  Perhaps I can determine what happens through the log files?  Is there a summary of which “when to log” settings in postgresql.conf should be set to get at least table-level messages about yes/no decisions?  The only message I see now is very terse, indicating that autovacuum does run:

 

    LOG:  autovacuum: processing database "dc_prod"

 

I suspect there’s a problem because there appears to be 78% overhead in the database size, whereas I would expect 10-15% based on what I’ve read.  This is not good for some Seq Scan operations on large tables (the root problem I’m starting to tackle).  Notes:

 

  [+] Last week I restored a production backup into my

      development sandbox with a “psql -f”, then ran a

      “vacuumdb -a z” on it. After that, I noticed that the

      size of the production database is 78% larger than

      development, using “select pg_database_size('dc_prod')”

      in pgAdmin3.  Prod is 5.9GB, but my Dev is 3.3GB.

 

  [+] The worst table has about 2.7x overhead, according to

      "select relpages/reltuples from pg_class" queries.

 

Here are the relevant postgresql.conf settings in production.  I can’t speak to their suitability, but I think they should reclaim some unused space for reuse.

 

    #stats_start_collector = on

    #stats_block_level = off

    stats_row_level = on

    #stats_reset_on_server_start = off

 

    autovacuum = on

    autovacuum_naptime = 360

    autovacuum_vacuum_threshold = 1000

    autovacuum_analyze_threshold = 500

    autovacuum_vacuum_scale_factor = 0.04

    autovacuum_analyze_scale_factor = 0.02

    autovacuum_vacuum_cost_delay = 10

    autovacuum_vacuum_cost_limit = -1

 

I was suspicious that the stat_row_level might not work because stat_block_level is off.  But I see pg_stat_user_tables.n_tup_ins, pg_stat_user_tables.n_tup_upd and pg_stat_user_tables.n_tup_del are all increasing (slowly but surely).

 

Thanks,

David Crane

http://www.donorschoose.org

Teachers Ask. You Choose. Students Learn.

pgsql-performance by date:

Previous
From: "Guillaume Smet"
Date:
Subject: Re: [HACKERS] Estimation problem with a LIKE clause containing a /
Next
From: Alvaro Herrera
Date:
Subject: Re: Can I Determine if AutoVacuum Does Anything?