Re: lost statistics; analyze needs to execute twice

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Tue, Sep 1, 2009 at 00:02, Jaime
> Casanova<jcasanov(at)systemguards(dot)com(dot)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