Skip site navigation (1) Skip section navigation (2)

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
Message-ID: 7177d0cd40b82409024e7c495e9d6992.squirrel@sq.gransy.com (view raw or flat)
Thread:
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
community).

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
pg_stat_get_function_last_stat_reset_time)

- 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
PgStat_StatDBEntry.stat_reset_timestamp
- 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
pgstat_recv_resetsinglecounter)
- 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
types)

- 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).

regards
Tomas

Attachment: stat-reset-timestamp.diff
Description: text/plain (13.6 KB)

Responses

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group