Thread: stats reset during pg_restore?
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
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.
> 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?
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.