Testing AutoVac on 8.3 , i came across the problem of loosing stats data, which was discussed in my last post
http://archives.postgresql.org/pgsql-general/2008-08/msg00198.php that problem was recognized that doing hard stop, server will throw away the stats while going through crash recovery. Problem i see is after crash recovery , we have to manually analyze database in order for autovac to work but it not working as expected. Here is test case: foo=# SELECT version(); version ------------------------------------------------------------------------------------------------ PostgreSQL 8.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 (Ubuntu 4.1.2-0ubuntu4) (1 row) foo=# SELECT * from pg_stat_user_tables where relname='accounts'; -[ RECORD 1 ]----+------------------------------ relid | 57350 schemaname | public relname | accounts seq_scan | 1 seq_tup_read | 1000000 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 1000000 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1000000 n_dead_tup | 0 last_vacuum | 2008-09-24 15:04:35.384012-04 last_autovacuum | last_analyze | 2008-09-24 15:04:35.384012-04 last_autoanalyze | Next i will stop DB immediate and expect to loose stats as normal behavior. pg_ctl -m immediate -D /opt/rg/foo/pgsql83 stop waiting for server to shut down...LOG: received immediate shutdown request done server stopped After stating the DB, as expected: foo=# SELECT * from pg_stat_user_tables where relname='accounts'; -[ RECORD 1 ]----+--------- relid | 57350 schemaname | public relname | accounts seq_scan | 0 seq_tup_read | 0 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 0 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | Next step is to manually analyse to collects the stats again: foo=# ANALYZE ; ANALYZE foo=# SELECT * from pg_stat_user_tables where relname='accounts'; -[ RECORD 1 ]----+--------- relid | 57350 schemaname | public relname | accounts seq_scan | 0 seq_tup_read | 0 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 0 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | note: After ANALYSE, ststs were not updated. Running ANALYSE, second time seems to does the trick. foo=# ANALYZE ; ANALYZE foo=# SELECT * from pg_stat_user_tables where relname='accounts'; -[ RECORD 1 ]----+------------------------------ relid | 57350 schemaname | public relname | accounts seq_scan | 0 seq_tup_read | 0 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 0 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1000062 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | 2008-09-24 15:13:13.423424-04 last_autoanalyze | So question i have is, is this normal operation,why we need to analyze twice to updates the stats ? if table/tables are candidate for vacuuming after crash recovery will never get auto-vac unless you do 'ANALYZE' twice. Thanks in advance, Chirag Dave DBA Afilias
