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

Re: [ADMIN] Postgres Stats after Crash Recovery

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: chirag(dot)dave(at)gmail(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [ADMIN] Postgres Stats after Crash Recovery
Date: 2008-09-25 18:50:39
Message-ID: 1222368639.4445.831.camel@ebony.2ndQuadrant (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-bugs
I confirm this as a bug. First ANALYZE after crash recovery leaves stats
showing as zeroes. Repeatable on CVS HEAD with ANALYZE and VACUUM
ANALYZE. 

Forwarding to bugs.


On Wed, 2008-09-24 at 15:29 -0400, Chirag Dave wrote:
> 
> 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

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


In response to

pgsql-admin by date

Next:From: Scott MarloweDate: 2008-09-25 19:04:34
Subject: Re: missing chunk number 0 for toast value
Previous:From: Plugge, Joe R.Date: 2008-09-25 18:39:04
Subject: Re: Hex representation

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2008-09-25 20:57:52
Subject: Re: BUG #4436: (E'\\' LIKE E'\\') => f
Previous:From: Tom LaneDate: 2008-09-25 17:22:20
Subject: Re: BUG #4437: Breaking referential integrity with a trigger

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