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 GiB on-disk and compresses to 70 GiB as a plain SQL dump. Running the above query + "WHERE last_autoanalyze is not null or last_autovacuum is not null" only returns 38 rows?! I see the autovacuum daemon work when polling pg_stat_activity. Something doesn'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 raw values into summary tables and run reports on the summary values. One of the steps in each of the import and summary scripts is 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 recommended settings given the above? First thing I'll do is set log_autovacuum_min_duration to 0 to see what's really going on. Thanks! François > SELECT version(); -- Stock Ubuntu 12.04 PostgreSQL installed from apt PostgreSQL 9.1.8 on x86_64-unknown-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 prior to analyze as a fraction of reltuples. autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates or deletes prior to analyze. autovacuum_freeze_max_age | 200000000 | Age at which 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 | Vacuum cost amount available before napping, for autovacuum. autovacuum_vacuum_scale_factor | 0.2 | Number of 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.
<<attachment: smime.p7s>>
