pg_stat_reset() not resetting all statistics counters

Lists: pgsql-general
From: Rafael Martinez <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: pg_stat_reset() not resetting all statistics counters
Date: 2009-07-15 12:13:34
Message-ID: 4A5DC7EE.4060606@usit.uio.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/


From: Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>
To: Rafael Martinez <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: pg_stat_reset() not resetting all statistics counters
Date: 2009-07-15 12:25:12
Message-ID: 1247660712.5546.2.camel@bnicholson-desktop
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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.


From: Rafael Martinez <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: pg_stat_reset() not resetting all statistics counters
Date: 2009-07-15 12:25:23
Message-ID: 4A5DCAB3.6020808@usit.uio.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/


From: Rafael Martinez <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
To: Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: pg_stat_reset() not resetting all statistics counters
Date: 2009-07-15 12:39:38
Message-ID: 4A5DCE0A.1000807@usit.uio.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/