While testing on 8.3, i see that upon postmaster restart , i am loosing
data from pg_stat_user_tables. as i understand in 8.3 only way to reset
is by calling |pg_stat_reset().
Sorry for the long post:
Here is my test case:
sample=# 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)
Created sample database
pg_bench on DB with scaling factor 100
*** Stats after loading the data:
sample=# SELECT * from pg_stat_user_tables where relname='accounts';
-[ RECORD 1 ]----+------------------------------
relid | 114694
schemaname | public
relname | accounts
seq_scan | 1
seq_tup_read | 10000000
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 10000000
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 10000000
n_dead_tup | 0
last_vacuum | 2008-08-06 16:29:23.153879-04
last_autovacuum |
last_analyze | 2008-08-06 16:29:23.153879-04
last_autoanalyze |
*** Running update to create dead tuples:
UPDATE accounts SET abalance = abalance +1;
****Stats after update:
sample=# SELECT * from pg_stat_user_tables where relname='accounts';
-[ RECORD 1 ]----+------------------------------
relid | 114694
schemaname | public
relname | accounts
seq_scan | 2
seq_tup_read | 20000000
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 10000000
n_tup_upd | 10000000
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 10000000
n_dead_tup | 10000000
last_vacuum | 2008-08-06 16:29:23.153879-04
last_autovacuum |
last_analyze | 2008-08-06 16:29:23.153879-04
last_autoanalyze |
looking at pg_stat_activity, it shows as expected AUTOVAC started
vacuuming the table.
*** Restart the DB:
sample=# SELECT * from pg_stat_user_tables where relname='accounts';
-[ RECORD 1 ]----+---------
relid | 114694
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 |
and now autovac process does not start, as start are reseted.
Is this expected behavior ?
Also later i tryed following:
sample=# ANALYZE accounts ;
ANALYZE
sample=# SELECT * from pg_stat_user_tables where relname='accounts';
-[ RECORD 1 ]----+---------
relid | 114694
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: when i did ANALYZE accounts ; stats did't get updated.
sample=# SELECT * from pg_stat_user_tables where relname='accounts';
-[ RECORD 1 ]----+---------
relid | 114694
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: Upon doing ANALYZE verbose accounts;
sample=# ANALYZE verbose accounts;
INFO: analyzing "public.accounts"
INFO: "accounts": scanned 3000 of 317461 pages, containing 94185 live
rows and 60165 dead rows; 3000 rows in sample, 9966688 estimated total rows
ANALYZE
sample=# SELECT * from pg_stat_user_tables where relname='accounts';
-[ RECORD 1 ]----+------------------------------
relid | 114694
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 | 9966688
n_dead_tup | 6366680
last_vacuum |
last_autovacuum |
last_analyze | 2008-08-06 16:47:13.404946-04
last_autoanalyze |
note: stats got updated
Thanks in advance.
--
Chirag Dave 416-673-4102
Database Administrator, Afilias Canada Corp.
cdave@ca.afilias.info
