Skip site navigation (1) Skip section navigation (2)

Postgres Stats after Crash Recovery

From: "Chirag Dave" <cdave(at)ca(dot)afilias(dot)info>
To: pgsql-admin(at)postgresql(dot)org
Subject: Postgres Stats after Crash Recovery
Date: 2008-09-24 19:29:59
Message-ID: f1d4f3170809241229t50465cddx2f8f44c2507df935@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-bugs
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

Responses

pgsql-admin by date

Next:From: Milen A. RadevDate: 2008-09-24 19:32:46
Subject: Re: Hex representation
Previous:From: Carol WalterDate: 2008-09-24 19:20:03
Subject: Hex representation

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-09-24 20:00:54
Subject: Re: BUG #4436: (E'\\' LIKE E'\\') => f
Previous:From: Tom LaneDate: 2008-09-24 17:34:14
Subject: Re: Postgres won't start and doesn't generate any messages.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group