Обсуждение: lost statistics; analyze needs to execute twice
Hi,
pgsql 8.3.7 and 8.4.0
when i issue an "immediate shutdown" the statistics on all tables disappear=
...
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
jd=3D# select relname, n_live_tup, n_dead_tup from pg_stat_user_tables
where relname =3D 'bpprovee';
relname | n_live_tup | n_dead_tup
----------+------------+------------
bpprovee | 111 | 0
(1 row)
jd=3D# select version();
version
---------------------------------------------------------------------------=
-------------------------
PostgreSQL 8.4.0 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(Debian 4.3.2-1.1) 4.3.2, 64-bit
(1 row)
jd=3D# \q
postgres@casanova1:/usr/local/pgsql/8.4$ bin/pg_ctl -m immediate -D
$PWD/data stop
waiting for server to shut down.... done
server stopped
postgres@casanova1:/usr/local/pgsql/8.4$ bin/pg_ctl -D $PWD/data start
server starting
postgres@casanova1:/usr/local/pgsql/8.4$ bin/psql
psql (8.4.0)
Type "help" for help.
jd=3D# select relname, n_live_tup, n_dead_tup from pg_stat_user_tables
where relname =3D 'bpprovee';
relname | n_live_tup | n_dead_tup
----------+------------+------------
bpprovee | 0 | 0
(1 row)
jd=3D# analyze;
ANALYZE
jd=3D# select relname, n_live_tup, n_dead_tup from pg_stat_user_tables
where relname =3D 'bpprovee';
relname | n_live_tup | n_dead_tup
----------+------------+------------
bpprovee | 0 | 0
(1 row)
jd=3D# analyze;
ANALYZE
jd=3D# select relname, n_live_tup, n_dead_tup from pg_stat_user_tables
where relname =3D 'bpprovee';
relname | n_live_tup | n_dead_tup
----------+------------+------------
bpprovee | 111 | 0
(1 row)
--=20
Atentamente,
Jaime Casanova
Soporte y capacitaci=C3=B3n de PostgreSQL
Asesor=C3=ADa y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
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. =A0I 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. =A0(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. =A0To 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. =A0Moreover, > 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. =A0There 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. --=20 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