Thread: pg_stat_reset() not resetting all statistics counters
Hello Should not the execution of pg_stat_reset() reset *all* statistics counters everywhere in the database? We would like to use the numbers in pg_stat_database together with pg_postmaster_start_time() to find out the average values per second of some of the columns in this view. But when we execute pg_stat_reset() in our databases, not all values reported by pg_stat_database get updated. tup_returned, tup_fetched, tup_inserted, tup_updated and tup_deleted still have the old values after running pg_stat_reset(). Am I missing anything? Example: postgreSQL 8.3.7 running in a x86_64 GNU/Linux server. Data from pg_stat_database for the database 'rtprod' *before* running pg_stat_reset(): -[ RECORD 5 ]-+-------------- datid | 16777 datname | rtprod numbackends | 40 xact_commit | 1800706565 xact_rollback | 98722 blks_read | 1384382954 blks_hit | 70569876640 tup_returned | 1742826466651 tup_fetched | 67404470469 tup_inserted | 54681321 tup_updated | 11495258 tup_deleted | 18167792 Data from pg_stat_database for the database 'rtprod' *after* running pg_stat_reset(): -[ RECORD 5 ]-+-------------- datid | 16777 datname | rtprod numbackends | 40 xact_commit | 10000 xact_rollback | 2 blks_read | 60 blks_hit | 264238 tup_returned | 1742832161264 tup_fetched | 67407042466 tup_inserted | 54681617 tup_updated | 11495340 tup_deleted | 18167979 Any ideas? regards, -- Rafael Martinez, <r.m.guerrero@usit.uio.no> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/
Rafael Martinez wrote: > But when we execute pg_stat_reset() in our databases, not all values > reported by pg_stat_database get updated. tup_returned, tup_fetched, > tup_inserted, tup_updated and tup_deleted still have the old values > after running pg_stat_reset(). > > Am I missing anything? > I suppose that http://archives.postgresql.org/pgsql-hackers/2009-04/msg00520.php explains why this happens :( -- Rafael Martinez, <r.m.guerrero@usit.uio.no> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/
Brad Nicholson wrote: > On Wed, 2009-07-15 at 14:13 +0200, Rafael Martinez wrote: >> Hello >> >> Should not the execution of pg_stat_reset() reset *all* statistics >> counters everywhere in the database? > > It only resets the stats for the current database, not the cluster wide > stats - pg_database is cluster wide. > Jepp, thanks, but this explanation is an 'internal' explanation. A *normal* user sees a row per database when running pg_stat_database and that all the columns have values per database. It looks wrong and it is not logic if half of the columns for a database returned by pg_stat_database get a new value and the other half still have old values after running pg_stat_reset(). Don't you think? Specially when values associated to inserts, updates, deleted, etc in pg_stat_user_* get a new value after running pg_stat_reset(). In my humble opinion this not a consistent behavior. regards, -- Rafael Martinez, <r.m.guerrero@usit.uio.no> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/
On Wed, 2009-07-15 at 14:13 +0200, Rafael Martinez wrote: > Hello > > Should not the execution of pg_stat_reset() reset *all* statistics > counters everywhere in the database? It only resets the stats for the current database, not the cluster wide stats - pg_database is cluster wide. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.