pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedstats reset during pg_restore?
4+ messages / 2 participants
[nested] [flat]
* stats reset during pg_restore?
@ 2006-08-26 17:13 George Pavlov <gpavlov@mynewplace.com>
2006-08-27 02:45 ` Re: stats reset during pg_restore? Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 4+ messages in thread
From: George Pavlov @ 2006-08-26 17:13 UTC (permalink / raw)
To: pgsql-performance
This did not have any takers in pgsql-general. Maybe
performance-oriented folks can shed light? The basic question is if
there is a way to preserve stats during pg_restore?
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of George Pavlov
Sent: Monday, August 21, 2006 3:15 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] stats reset during pg_restore?
I would like to analyze server stats offline, so I attempt to pg_dump my
production database and then pg_restore it into another database. In the
process all stats seem to be reset (they are not completely zeroed). So
in production I have a table with the following stats (from
pg_stat_all_tables as an example):
relid | 25519576
relname | property_contact
seq_scan | 5612
seq_tup_read | 569971320
idx_scan | 4486454
idx_tup_fetch | 180100369
n_tup_ins | 39114
n_tup_upd | 17553
n_tup_del | 21877
After I restore the stats for the same table look like this:
relid | 104017313
relname | property_contact
seq_scan | 9
seq_tup_read | 992493
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 110277
n_tup_upd | 0
n_tup_del | 0
These look like stats for table accesses during the restore itself:
11027 is indeed the number of rows in the table, and 992493 / 110277 =
9, which happens to be the number of indexes and FK constraints on the
table.
I do have stats_reset_on_server_start = off on both servers.
Can someone share what exatly happens with stats upon restore? Also is
there anything one can do to keep them intact during a dump/restore?
Apologies if already discussed--I failed to find any references.
TIA,
George
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: stats reset during pg_restore?
2006-08-26 17:13 stats reset during pg_restore? George Pavlov <gpavlov@mynewplace.com>
@ 2006-08-27 02:45 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-08-27 14:24 ` Re: stats reset during pg_restore? George Pavlov <gpavlov@mynewplace.com>
0 siblings, 1 reply; 4+ messages in thread
From: Alvaro Herrera @ 2006-08-27 02:45 UTC (permalink / raw)
To: George Pavlov <gpavlov@mynewplace.com>; +Cc: pgsql-performance
George Pavlov wrote:
> This did not have any takers in pgsql-general. Maybe
> performance-oriented folks can shed light? The basic question is if
> there is a way to preserve stats during pg_restore?
No, there isn't.
> Can someone share what exatly happens with stats upon restore? Also is
> there anything one can do to keep them intact during a dump/restore?
These stats are not stored in tables, only in memory and saved to a
special file on disk to be able to preserve it across server stop/start.
But pg_dump does not make the slightest attempt to save it.
Also, you can't save it yourself -- while you could save the values it
returns on queries to the stats views, there is no way to feed those
saved values back to the system after a dump/restore.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: stats reset during pg_restore?
2006-08-26 17:13 stats reset during pg_restore? George Pavlov <gpavlov@mynewplace.com>
2006-08-27 02:45 ` Re: stats reset during pg_restore? Alvaro Herrera <alvherre@commandprompt.com>
@ 2006-08-27 14:24 ` George Pavlov <gpavlov@mynewplace.com>
2006-08-27 14:37 ` Re: stats reset during pg_restore? Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 4+ messages in thread
From: George Pavlov @ 2006-08-27 14:24 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-performance
> These stats are not stored in tables, only in memory and saved to a
> special file on disk to be able to preserve it across server
> stop/start.
> But pg_dump does not make the slightest attempt to save it.
>
> Also, you can't save it yourself -- while you could save the values it
> returns on queries to the stats views, there is no way to feed those
> saved values back to the system after a dump/restore.
Thanks! Sounds like I just need to query the stats tables and save the
output for oofline analysis before I do a dump.
Based on how it works it seems that a server crash might lose the
in-memory stats data as well? I imagine PITR does not take care of that
special file (where is it by, by the way?). I have not worked with
replication (yet), but I imagine replica databases will also be agnostic
of the master's stats?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: stats reset during pg_restore?
2006-08-26 17:13 stats reset during pg_restore? George Pavlov <gpavlov@mynewplace.com>
2006-08-27 02:45 ` Re: stats reset during pg_restore? Alvaro Herrera <alvherre@commandprompt.com>
2006-08-27 14:24 ` Re: stats reset during pg_restore? George Pavlov <gpavlov@mynewplace.com>
@ 2006-08-27 14:37 ` Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 4+ messages in thread
From: Alvaro Herrera @ 2006-08-27 14:37 UTC (permalink / raw)
To: George Pavlov <gpavlov@mynewplace.com>; +Cc: pgsql-performance
George Pavlov wrote:
> Based on how it works it seems that a server crash might lose the
> in-memory stats data as well?
Yeah, IIRC the postmaster removes the stat file after crash recovery.
It doesn't check the file for correctness.
> I imagine PITR does not take care of that special file (where is it
> by, by the way?). I have not worked with replication (yet), but I
> imagine replica databases will also be agnostic of the master's stats?
Neither PITR nor the replication systems I know about do anything about
the stats.
The file is $PGDATA/global/pgstat.stat
The code to read it, which is at the same time the documentation to its
format, is in src/backend/postmaster/pgstat.c, function
pgstat_read_statfile. It's quite simple. I think you could read it in
Perl if you wanted; and rewrite the file again after a restore (you'd
need to change the Oids in the table entries, etc).
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-08-27 14:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-26 17:13 stats reset during pg_restore? George Pavlov <gpavlov@mynewplace.com>
2006-08-27 02:45 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-08-27 14:24 ` George Pavlov <gpavlov@mynewplace.com>
2006-08-27 14:37 ` Alvaro Herrera <alvherre@commandprompt.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