Thread: How to identify whether the stats were reset?

Hello everyone,

we're collecting snapshots of the statistics (pg_stat, pg_statio etc.)
regularly so that we can analyze the performance, detect trends etc.

We need to identify whether the stats were reset between the snapshots,
because then the collected data are useless (and the scripts that do the
analysis are quite confused).

Thanks to pg_postmaster_start_time it's quite simple to find out whether
the postmaster was restarted (before 8.3 that may reset the stats if
stats_reset_on_server_start=true).

But is there a way to find out whether the statistics were reset using
pg_stat_reset_* functions? E.g. 'timestamp of the last reset' or
something like that?

Currently I'm comparing some of the cumulative values (e.g. n_commits)
and if the newer version is lower than the old one, it's considered as a
proof that the stats were reset.

But I don't like this solution and it does not detect resets of
statistics for a single table, function or a bgwriter :-(

But is there a better way?


regards
Tomas

Tomas Vondra <tv@fuzzy.cz> writes:
> We need to identify whether the stats were reset between the snapshots,
> because then the collected data are useless (and the scripts that do the
> analysis are quite confused).

The stats are never reset automatically.  If you have a DBA who randomly
resets them, fire him ;-)

            regards, tom lane

Dne 19.11.2010 05:56, Tom Lane napsal(a):
> Tomas Vondra <tv@fuzzy.cz> writes:
>> We need to identify whether the stats were reset between the snapshots,
>> because then the collected data are useless (and the scripts that do the
>> analysis are quite confused).
>
> The stats are never reset automatically.

That's true since 8.3 where you removed that feature ;-) but I'd like to
collect data from older versions too. Yes, I know the default value for
that setting used to be 'off' and it's simple to change it.

Anyway it's quite simple to deal with restarts of the whole db, so this
is not a problem.

> If you have a DBA who randomly resets them, fire him ;-)

Well, to do that you need two things. First you have to be a boss, so
that you have the power to fire him (and that's not my case). Second you
have to be able to identify that the stats were actually reset, and
that's what I can't do right now.

Anyway resets do happen, and I need to deal with them (as elegantly as I
can). I don't want to ignore them.

I can identify when all the stats were reset using pg_stat_reset() -
comparing n_commits seems like a quite reliable check. But what about
pg_stat_reset_single_table_counters?

regards
Tomas