pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres Stats after Crash Recovery
2+ messages / 2 participants
[nested] [flat]
* Postgres Stats after Crash Recovery
@ 2008-09-24 19:29 Chirag Dave <cdave@ca.afilias.info>
2008-09-25 18:50 ` Re: [ADMIN] Postgres Stats after Crash Recovery Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 1 reply; 2+ messages in thread
From: Chirag Dave @ 2008-09-24 19:29 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [ADMIN] Postgres Stats after Crash Recovery
2008-09-24 19:29 Postgres Stats after Crash Recovery Chirag Dave <cdave@ca.afilias.info>
@ 2008-09-25 18:50 ` Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 0 replies; 2+ messages in thread
From: Simon Riggs @ 2008-09-25 18:50 UTC (permalink / raw)
To: chirag.dave@gmail.com; +Cc: pgsql-bugs@postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-09-25 18:50 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-09-24 19:29 Postgres Stats after Crash Recovery Chirag Dave <cdave@ca.afilias.info>
2008-09-25 18:50 ` Simon Riggs <simon@2ndQuadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox