keeping a timestamp of the last stats reset (for a db, table and function)

From: tv(at)fuzzy(dot)cz
To: pgsql-hackers(at)postgresql(dot)org
Subject: keeping a timestamp of the last stats reset (for a db, table and function)
Date: 2010-12-11 18:58:53
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi everyone,

I just wrote my first patch, and I need to know whether I missed something
or not. I haven't used C for a really long time, so sickbags on standby,
and if you notice something really stupid don't hesitate to call me an
asshole (according to Simon Phipps that proves we are a healthy open

So what the patch does (or should do)? It tracks when were the stats for a
given object (database, table or function) reset for the last time. This
is useful when you do snapshots of the stats for analysis - when comparing
two snapshots, you have to know whether the stats were reset (in that case
the analysis usually yields random noise and automatic tools get confused
by this).

Tom Lane already recommended a workaround - firing the DBA who randomly
resets statistics, but that's not a good solution I think. First, you have
to be superior to the DBA to be able to fire him ;-) Second, everyone
makes a mistake from time to time. Third, when there are functions to
reset stats, it's nice to provide such info as it makes life much easier.

And there are cases when you don't reset the stats explicitly but the data
are actually gone - e.g. when after a restore or upgrade (OK, this is
solvable using pg_postmaster_start_time).

In short, I think it's a useful feature (I need it and I think there are
others). And I think it's not disruptive.

So what the patch actually does:

- extends PgStat_StatDBEntry, PgStat_StatTableEntry and
PgStat_StatFuncEntry with a new field (stat_reset_timestamp)

- adds functions to read current value from these fields
(pg_stat_get_db_last_stat_reset_time, pg_stat_get_last_stat_reset_time and

- extends the system views with calls to these functions
(pg_stat_database, pg_stat_user_functions and pg_stat_all_tables)

The values are set like this:

- when a database is created, current timestamp is stored in
- by default all tables/functions inherit this timestamp
- when stats for a given table / function are reset, current timestamp is
stored in the stat_reset_timestamp (this happens in
- when stats for the whole database are reset, everything starts from
scratch (this happens in pgstat_recv_resetcounter)

What I'm not sure about:

- I really am not sure about the changes made in pg_proc.h. I'm not sure
how to assign OIDs to the new functions (I've simply chosen values that
are were not used in this file), and I'm not sure about the other columns
(I've copied and modified another function with the same parameter/return

- I'm not sure if there are any other ways how the stat entries can be
created. I've found two ways - directly (when asked for the stats e.g.
from pgstat_get_tab_entry), and indirectly (when processing stats from a
backend e.g. in pgstat_recv_tabstat).


Attachment Content-Type Size
stat-reset-timestamp.diff text/plain 13.6 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Jeff Janes 2010-12-11 19:09:07 Re: Anyone for SSDs?
Previous Message Tom Lane 2010-12-11 18:38:28 Re: create tablespace fails silently, or succeeds improperly