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

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

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: tv(at)fuzzy(dot)cz
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: keeping a timestamp of the last stats reset (for a db, table and function)
Date: 2010-12-11 21:26:13
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers

you have to respect pg coding style:

a) not too long lines
b) not C++ line comments



2010/12/11  <tv(at)fuzzy(dot)cz>:
> 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
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:

In response to


pgsql-hackers by date

Next:From: David E. WheelerDate: 2010-12-11 21:30:54
Subject: Re: Extensions, patch v16
Previous:From: Pavel StehuleDate: 2010-12-11 21:22:36
Subject: Re: proposal: auxiliary functions for record type

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