lost statistics; analyze needs to execute twice

On Tue, Sep 1, 2009 at 00:02, Jaime
Casanova<jcasanov@systemguards.com.ec> wrote:

Hi,

pgsql 8.3.7 and 8.4.0

when i issue an "immediate shutdown" the statistics on all tables disappear...

That is by design. Whenever the server goes into crash recovery on
startup, it will clean out the statistics. Since the statistics data
is not kept crashsafe, there is no way to know if it's corrupt or not.

and when i try to recover them via an analyze; (on all tables on the
database) the result is nothing...
i have to exexute the analyze commands twice to compute the statistics

pg_stat_* are not directly affected by ANALYZE. They collect runtime
statistics about activity in the tables, ANALYZE collects statistics
about what's *in* the tables (primarily stored in pg_statistics, not
pg_stat_*).

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus@hagander.net> writes:

On Tue, Sep 1, 2009 at 00:02, Jaime
Casanova<jcasanov@systemguards.com.ec> wrote:

when i issue an "immediate shutdown" the statistics on all tables disappear...

That is by design. Whenever the server goes into crash recovery on
startup, it will clean out the statistics. Since the statistics data
is not kept crashsafe, there is no way to know if it's corrupt or not.

Yeah. I don't think we'll change that. "-m immediate" is not the
recommended way to stop the server; it's more like the big red button
that dumps Halon all over your equipment. You expect to have to clean
up afterwards.

and when i try to recover them via an analyze; (on all tables on the
database) the result is nothing...
i have to exexute the analyze commands twice to compute the statistics

pg_stat_* are not directly affected by ANALYZE. They collect runtime
statistics about activity in the tables,

Yeah, but ANALYZE does update the stats collector stats too. I looked
into what's actually happening here, and it's a bit interesting:

1. Stats collector tables are empty.

2. ANALYZE does its thing and sends a PgStat_MsgAnalyze message.

3. pgstat_recv_analyze *intentionally throws the data away*, on the
grounds that if it were interesting there would already be a stats
table entry for the table.

4. At completion of ANALYZE, the regular tabstat machinery sends
off a tabstat message for the table, because guess what, ANALYZE did a
scan of that table, and there are t_blocks_fetched counts to report.

5. pgstat_recv_tabstat happily creates a table entry. (The pg_statio
counts in it are nonzero, even though the pg_stat counts aren't.)

6. Now, if you repeat the cycle, the stats collector will accept
the second PgStat_MsgAnalyze message, because this time there's
a stats table entry.

This is a bit silly I guess --- we dropped the data but didn't actually
save any stats-table space.

I'm inclined to think that the don't-create-a-table-entry behavior in
pgstat_recv_vacuum and pgstat_recv_analyze should just be dropped.
I'm dubious that it ever worked as intended. To have it work right
you'd need to suppress vacuum/analyze physical I/O from the tabstats
counts, which doesn't seem like an amazingly good idea. Moreover,
autovacuum is unlikely to issue vacuum or analyze against a table
that hasn't already got a stats-table entry, so the filter doesn't
seem likely to buy much if it did work. There might have been some
value in the idea back when cron-driven database-wide VACUUM ANALYZE
was the standard maintenance mechanism, but that's not the recommended
thing anymore.

Comments?

regards, tom lane

On Wed, Sep 2, 2009 at 06:25, Tom Lane<tgl@sss.pgh.pa.us> wrote:

Magnus Hagander <magnus@hagander.net> writes:

and when i try to recover them via an analyze; (on all tables on the
database) the result is nothing...
i have to exexute the analyze commands twice to compute the statistics

pg_stat_* are not directly affected by ANALYZE. They collect runtime
statistics about activity in the tables,

Yeah, but ANALYZE does update the stats collector stats too.  I looked
into what's actually happening here, and it's a bit interesting:

1. Stats collector tables are empty.

2. ANALYZE does its thing and sends a PgStat_MsgAnalyze message.

3. pgstat_recv_analyze *intentionally throws the data away*, on the
grounds that if it were interesting there would already be a stats
table entry for the table.

4. At completion of ANALYZE, the regular tabstat machinery sends
off a tabstat message for the table, because guess what, ANALYZE did a
scan of that table, and there are t_blocks_fetched counts to report.

Ah, d'uh. That's the part I missed :-)

5. pgstat_recv_tabstat happily creates a table entry.  (The pg_statio
counts in it are nonzero, even though the pg_stat counts aren't.)

6. Now, if you repeat the cycle, the stats collector will accept
the second PgStat_MsgAnalyze message, because this time there's
a stats table entry.

This is a bit silly I guess --- we dropped the data but didn't actually
save any stats-table space.

I'm inclined to think that the don't-create-a-table-entry behavior in
pgstat_recv_vacuum and pgstat_recv_analyze should just be dropped.
I'm dubious that it ever worked as intended.  To have it work right
you'd need to suppress vacuum/analyze physical I/O from the tabstats
counts, which doesn't seem like an amazingly good idea.  Moreover,
autovacuum is unlikely to issue vacuum or analyze against a table
that hasn't already got a stats-table entry, so the filter doesn't
seem likely to buy much if it did work.  There might have been some
value in the idea back when cron-driven database-wide VACUUM ANALYZE
was the standard maintenance mechanism, but that's not the recommended
thing anymore.

Agreed. I doubt it had much value back then either, really, and
definitely even less so now.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Tom Lane wrote:

4. At completion of ANALYZE, the regular tabstat machinery sends
off a tabstat message for the table, because guess what, ANALYZE did a
scan of that table, and there are t_blocks_fetched counts to report.

5. pgstat_recv_tabstat happily creates a table entry. (The pg_statio
counts in it are nonzero, even though the pg_stat counts aren't.)

6. Now, if you repeat the cycle, the stats collector will accept
the second PgStat_MsgAnalyze message, because this time there's
a stats table entry.

This is a bit silly I guess --- we dropped the data but didn't actually
save any stats-table space.

I'm inclined to think that the don't-create-a-table-entry behavior in
pgstat_recv_vacuum and pgstat_recv_analyze should just be dropped.
I'm dubious that it ever worked as intended. To have it work right
you'd need to suppress vacuum/analyze physical I/O from the tabstats
counts, which doesn't seem like an amazingly good idea. Moreover,
autovacuum is unlikely to issue vacuum or analyze against a table
that hasn't already got a stats-table entry, so the filter doesn't
seem likely to buy much if it did work. There might have been some
value in the idea back when cron-driven database-wide VACUUM ANALYZE
was the standard maintenance mechanism, but that's not the recommended
thing anymore.

I think this business about supressing pgstat entries started because of
autovacuum. I wasn't too fond of the idea at the time. I wouldn't be
opposed to ripping it out either.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane wrote:

I'm inclined to think that the don't-create-a-table-entry behavior in
pgstat_recv_vacuum and pgstat_recv_analyze should just be dropped.

I think this business about supressing pgstat entries started because of
autovacuum. I wasn't too fond of the idea at the time. I wouldn't be
opposed to ripping it out either.

Done.

regards, tom lane