Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 990F864FC1E for ; Wed, 24 Sep 2008 16:30:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 90327-10 for ; Wed, 24 Sep 2008 16:30:03 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.229]) by postgresql.org (Postfix) with ESMTP id 683D564FC4A for ; Wed, 24 Sep 2008 16:30:02 -0300 (ADT) Received: by rv-out-0506.google.com with SMTP id b25so53507rvf.43 for ; Wed, 24 Sep 2008 12:30:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:reply-to :sender:to:subject:mime-version:content-type:x-google-sender-auth; bh=QIc7Cy/vITyehM3m5d1cUgFC9E5w9Z5CyX9blcXkiRk=; b=UH7hoYk7S15JJc7BoZRQj/nWpaGNfA9WDQlVgQA2CQRseKjuxqSpz/xCxB2BZDG5ZU SJOnfxM39Dx25V4bXZYsi5sHW/idAHYkEQNlkj/e2dG6/bCL6fvVawRQ86Ayj5k4lKkj WzE5tv8zpNop8acsueYHEVnihQskuCVmAg/f8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:reply-to:sender:to:subject:mime-version :content-type:x-google-sender-auth; b=gYDtM2Fn7ofrelz4dF/ApTha4ylhFy8rsTNqILl72km61HOnKmrYKbt70V4wH5G1aG KaBfe63yEQklTlTXO0T9aDTCWsKGJ4UEZjTYFe65Iw3z1u+78iUeY36d73hQu3zKvwPK 2/wqyMF7b7q7JROrr0/GHP6tFG6Uk2gWUEroA= Received: by 10.141.107.13 with SMTP id j13mr3666129rvm.276.1222284600263; Wed, 24 Sep 2008 12:30:00 -0700 (PDT) Received: by 10.141.189.17 with HTTP; Wed, 24 Sep 2008 12:29:59 -0700 (PDT) Message-ID: Date: Wed, 24 Sep 2008 15:29:59 -0400 From: "Chirag Dave" Reply-To: chirag.dave@gmail.com To: pgsql-admin@postgresql.org Subject: Postgres Stats after Crash Recovery MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_88328_14103594.1222284600011" X-Google-Sender-Auth: e63bd828c5a9bf9c X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200809/289 X-Sequence-Number: 30382 ------=_Part_88328_14103594.1222284600011 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_88328_14103594.1222284600011 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

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







------=_Part_88328_14103594.1222284600011--