Determining last auto vacuum / analyze - Mailing list pgsql-general

Hi all,

According to http://heatware.net/databases/how-when-postgres-tables-auto-vacuum-analyze/ (August 2010), running:

select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

should tell me which tables have been auto vacuumed and auto analyzed. My schema has >300 tables, wheighs in at ~650
GiBon-disk and compresses to 70 GiB as a plain SQL dump. Running the above query + "WHERE last_autoanalyze is not null
orlast_autovacuum is not null" only returns 38 rows?! I see the autovacuum daemon work when polling pg_stat_activity.
Somethingdoesn't jive here... 

I have not touched the autovacuum settings at all, so everything is at the default values (see details at bottom).

The application is on-line analytics with long reporting queries. 3/hour I import new data. Once a day, I rollup the
rawvalues into summary tables and run reports on the summary values. One of the steps in each of the import and summary
scriptsis to ANALYZE the tables (not VACUUM, plain ANALYZE). 

I suspect autovacuum / autoanalyze doesn't kick in frequently enough. Would you say this is true? What would be
recommendedsettings given the above? First thing I'll do is set log_autovacuum_min_duration to 0 to see what's really
goingon. 

Thanks!
François



> SELECT version(); -- Stock Ubuntu 12.04 PostgreSQL installed from apt
PostgreSQL 9.1.8 on x86_64-iso-8859-1-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit

$ cat /etc/postgresql/9.1/main/postgresql.conf
data_directory = '/var/lib/postgresql/9.1/main'

listen_addresses = '*'
port = 5432
max_connections = 120

shared_buffers = 8GB
work_mem = 512MB

fsync = on
synchronous_commit = off
checkpoint_segments = 96
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
effective_cache_size = 12GB
maintenance_work_mem = 2GB

hot_standby = on
hot_standby_feedback = on

> SHOW all;autovacuum                      | on                       | Starts the autovacuum
subprocess.autovacuum_analyze_scale_factor| 0.1                      | Number of tuple inserts, updates or deletes
priorto analyze as a fraction of reltuples.autovacuum_analyze_threshold    | 50                       | Minimum number
oftuple inserts, updates or deletes prior to analyze.autovacuum_freeze_max_age       | 200000000                | Age
atwhich to autovacuum a table to prevent transaction ID wraparound.autovacuum_max_workers          | 3
     | Sets the maximum number of simultaneously running autovacuum worker processes.autovacuum_naptime              |
1min                    | Time to sleep between autovacuum runs.autovacuum_vacuum_cost_delay    | 20ms
  | Vacuum cost delay in milliseconds, for autovacuum.autovacuum_vacuum_cost_limit    | -1                       |
Vacuumcost amount available before napping, for autovacuum.autovacuum_vacuum_scale_factor  | 0.2                      |
Numberof tuple updates or deletes prior to vacuum as a fraction of reltuples.autovacuum_vacuum_threshold     | 50
               | Minimum number of tuple updates or deletes prior to vacuum.