lossing pg_stat's data
От | Chirag Dave |
---|---|
Тема | lossing pg_stat's data |
Дата | |
Msg-id | 489A0F46.80604@ca.afilias.info обсуждение исходный текст |
Ответы |
Re: lossing pg_stat's data
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: