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
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index 346eaaf..0ee59b1 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -310,6 +310,7 @@ CREATE VIEW pg_stat_all_tables AS
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
+ pg_stat_get_last_stat_reset_time(C.oid) as last_stat_reset,
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
@@ -502,7 +503,8 @@ CREATE VIEW pg_stat_database AS
pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched,
pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
- pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted
+ pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
+ pg_stat_get_db_last_stat_reset_time(D.oid) AS last_stat_reset
FROM pg_database D;
CREATE VIEW pg_stat_user_functions AS
@@ -512,7 +514,8 @@ CREATE VIEW pg_stat_user_functions AS
P.proname AS funcname,
pg_stat_get_function_calls(P.oid) AS calls,
pg_stat_get_function_time(P.oid) / 1000 AS total_time,
- pg_stat_get_function_self_time(P.oid) / 1000 AS self_time
+ pg_stat_get_function_self_time(P.oid) / 1000 AS self_time,
+ pg_stat_get_function_last_stat_reset_time(P.oid) AS last_stat_reset
FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
WHERE P.prolang != 12 -- fast check to eliminate built-in functions
AND pg_stat_get_function_calls(P.oid) IS NOT NULL;
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index c3c136a..f0b3453 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -249,6 +249,8 @@ static void pgstat_sighup_handler(SIGNAL_ARGS);
static PgStat_StatDBEntry *pgstat_get_db_entry(Oid databaseid, bool create);
static PgStat_StatTabEntry *pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry,
Oid tableoid, bool create);
+static PgStat_StatFuncEntry *pgstat_get_func_entry(PgStat_StatDBEntry *dbentry,
+ Oid funcoid, bool create);
static void pgstat_write_statsfile(bool permanent);
static HTAB *pgstat_read_statsfile(Oid onlydb, bool permanent);
static void backend_read_statsfile(void);
@@ -3129,7 +3131,9 @@ pgstat_get_db_entry(Oid databaseid, bool create)
result->n_tuples_updated = 0;
result->n_tuples_deleted = 0;
result->last_autovac_time = 0;
-
+
+ result->stat_reset_timestamp = GetCurrentTimestamp();
+
memset(&hash_ctl, 0, sizeof(hash_ctl));
hash_ctl.keysize = sizeof(Oid);
hash_ctl.entrysize = sizeof(PgStat_StatTabEntry);
@@ -3196,11 +3200,46 @@ pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid
tableoid, bool create)
result->autovac_vacuum_count = 0;
result->analyze_count = 0;
result->autovac_analyze_count = 0;
+
+ result->stat_reset_timestamp = dbentry->stat_reset_timestamp;
/* inherit stat time reset from dbentry */
+
}
return result;
}
+/*
+ * Lookup the hash table entry for the specified function. If no hash
+ * table entry exists, initialize it, if the create parameter is true.
+ * Else, return NULL.
+ */
+static PgStat_StatFuncEntry *
+pgstat_get_func_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create)
+{
+ PgStat_StatFuncEntry *result;
+ bool found;
+ HASHACTION action = (create ? HASH_ENTER : HASH_FIND);
+
+ /* Lookup or create the hash table entry for this table */
+ result = (PgStat_StatFuncEntry *) hash_search(dbentry->functions,
+
&tableoid,
+
action, &found);
+
+ if (!create && !found)
+ return NULL;
+
+ /* If not found, initialize the new one. */
+ if (!found)
+ {
+ result->f_numcalls = 0;
+ result->f_time = 0;
+ result->f_time_self = 0;
+
+ result->stat_reset_timestamp = dbentry->stat_reset_timestamp;
/* inherit stat time reset from dbentry */
+ }
+
+ return result;
+}
/* ----------
* pgstat_write_statsfile() -
@@ -3867,6 +3906,8 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
tabentry->autovac_vacuum_timestamp = 0;
tabentry->analyze_timestamp = 0;
tabentry->autovac_analyze_timestamp = 0;
+
+ tabentry->stat_reset_timestamp =
dbentry->stat_reset_timestamp;
}
else
{
@@ -4010,6 +4051,8 @@ pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int
len)
dbentry->n_xact_rollback = 0;
dbentry->n_blocks_fetched = 0;
dbentry->n_blocks_hit = 0;
+
+ dbentry->stat_reset_timestamp = GetCurrentTimestamp();
memset(&hash_ctl, 0, sizeof(hash_ctl));
hash_ctl.keysize = sizeof(Oid);
@@ -4060,6 +4103,8 @@ static void
pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, int len)
{
PgStat_StatDBEntry *dbentry;
+ PgStat_StatTabEntry *tabentry;
+ PgStat_StatFuncEntry *funcentry;
dbentry = pgstat_get_db_entry(msg->m_databaseid, false);
@@ -4069,9 +4114,25 @@
pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, int len)
/* Remove object if it exists, ignore it if not */
if (msg->m_resettype == RESET_TABLE)
+ {
(void) hash_search(dbentry->tables, (void *)
&(msg->m_objectid), HASH_REMOVE, NULL);
+
+ // need to create it, so that I can set current timestamp
(otherwise it would be inherited
+ // from the database entry)
+ tabentry = pgstat_get_tab_entry(dbentry, msg->m_objectid, true);
+ tabentry->stat_reset_timestamp = GetCurrentTimestamp();
+
+ }
else if (msg->m_resettype == RESET_FUNCTION)
+ {
(void) hash_search(dbentry->functions, (void *)
&(msg->m_objectid), HASH_REMOVE, NULL);
+
+ // need to create it, so that I can set current timestamp
(otherwise it would be inherited
+ // from the database entry)
+ funcentry = pgstat_get_func_entry(dbentry, msg->m_objectid,
true);
+ funcentry->stat_reset_timestamp = GetCurrentTimestamp();
+
+ }
}
/* ----------
@@ -4227,6 +4288,9 @@ pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len)
funcentry->f_numcalls = funcmsg->f_numcalls;
funcentry->f_time = funcmsg->f_time;
funcentry->f_time_self = funcmsg->f_time_self;
+
+ funcentry->stat_reset_timestamp =
dbentry->stat_reset_timestamp;
+
}
else
{
diff --git a/src/backend/utils/adt/pgstatfuncs.c
b/src/backend/utils/adt/pgstatfuncs.c
index adab948..2b4665c 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -38,6 +38,7 @@ extern Datum pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_last_stat_reset_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_vacuum_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_autovacuum_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_analyze_count(PG_FUNCTION_ARGS);
@@ -46,6 +47,7 @@ extern Datum pg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_function_calls(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_function_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_function_self_time(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_function_last_stat_reset_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_backend_idset(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_activity(PG_FUNCTION_ARGS);
@@ -71,6 +73,7 @@ extern Datum pg_stat_get_db_tuples_fetched(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_last_stat_reset_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
@@ -352,6 +355,24 @@ pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS)
}
Datum
+pg_stat_get_last_stat_reset_time(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ TimestampTz result;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ result = 0;
+ else
+ result = tabentry->stat_reset_timestamp;
+
+ if (result == 0)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_TIMESTAMPTZ(result);
+}
+
+Datum
pg_stat_get_vacuum_count(PG_FUNCTION_ARGS)
{
Oid relid = PG_GETARG_OID(0);
@@ -445,6 +466,24 @@ pg_stat_get_function_self_time(PG_FUNCTION_ARGS)
}
Datum
+pg_stat_get_function_last_stat_reset_time(PG_FUNCTION_ARGS)
+{
+ Oid funcid = PG_GETARG_OID(0);
+ TimestampTz result;
+ PgStat_StatFuncEntry *funcentry;
+
+ if ((funcentry = pgstat_fetch_stat_funcentry(funcid)) == NULL)
+ result = 0;
+ else
+ result = funcentry->stat_reset_timestamp;
+
+ if (result == 0)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_TIMESTAMPTZ(result);
+}
+
+Datum
pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
{
FuncCallContext *funcctx;
@@ -1130,6 +1169,24 @@ pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS)
}
Datum
+pg_stat_get_db_last_stat_reset_time(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ TimestampTz result;
+ PgStat_StatDBEntry *dbentry;
+
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = dbentry->stat_reset_timestamp;
+
+ if (result == 0)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_TIMESTAMPTZ(result);
+}
+
+Datum
pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
{
PG_RETURN_INT64(pgstat_fetch_global()->timed_checkpoints);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index feae22e..53b21bd 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3055,6 +3055,8 @@ DATA(insert OID = 2783 ( pg_stat_get_last_analyze_time
PGNSP PGUID 12 1 0 0 f f
DESCR("statistics: last manual analyze time for a table");
DATA(insert OID = 2784 ( pg_stat_get_last_autoanalyze_time PGNSP PGUID 12 1 0
0 f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_
pg_stat_get_last_autoanalyze_time _null_ _null_ _null_ ));
DESCR("statistics: last auto analyze time for a table");
+DATA(insert OID = 3115 ( pg_stat_get_last_stat_reset_time PGNSP PGUID 12 1 0
0 f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_
pg_stat_get_last_stat_reset_time _null_ _null_ _null_ ));
+DESCR("statistics: last reset for a table");
DATA(insert OID = 3054 ( pg_stat_get_vacuum_count PGNSP PGUID 12 1 0 0 f f f t
f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_vacuum_count _null_
_null_ _null_ ));
DESCR("statistics: number of manual vacuums for a table");
DATA(insert OID = 3055 ( pg_stat_get_autovacuum_count PGNSP PGUID 12 1 0 0 f f
f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_autovacuum_count
_null_ _null_ _null_ ));
@@ -3109,6 +3111,8 @@ DATA(insert OID = 2761 ( pg_stat_get_db_tuples_updated
PGNSP PGUID 12 1 0 0 f f
DESCR("statistics: tuples updated in database");
DATA(insert OID = 2762 ( pg_stat_get_db_tuples_deleted PGNSP PGUID 12 1 0 0 f
f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_tuples_deleted
_null_ _null_ _null_ ));
DESCR("statistics: tuples deleted in database");
+DATA(insert OID = 3116 ( pg_stat_get_db_last_stat_reset_time PGNSP PGUID 12 1
0 0 f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_
pg_stat_get_db_last_stat_reset_time _null_ _null_ _null_ ));
+DESCR("statistics: last reset for a database");
DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12
1 0 0 f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_
pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ ));
DESCR("statistics: number of timed checkpoints started by the bgwriter");
DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP
PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_
pg_stat_get_bgwriter_requested_checkpoints _null_ _null_ _null_ ));
@@ -3132,6 +3136,8 @@ DATA(insert OID = 2979 ( pg_stat_get_function_time
PGNSP PGUID 12 1 0 0 f f f
DESCR("statistics: execution time of function");
DATA(insert OID = 2980 ( pg_stat_get_function_self_time PGNSP PGUID 12
1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_
pg_stat_get_function_self_time _null_ _null_ _null_ ));
DESCR("statistics: self execution time of function");
+DATA(insert OID = 3117 ( pg_stat_get_function_last_stat_reset_time PGNSP
PGUID 12 1 0 0 f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_
pg_stat_get_function_last_stat_reset_time _null_ _null_ _null_ ));
+DESCR("statistics: last reset for a function");
DATA(insert OID = 3037 ( pg_stat_get_xact_numscans
PGNSP PGUID 12 1 0 0 f f f t f v 1 0 20 "26" _null_ _null_ _null_ _null_
pg_stat_get_xact_numscans _null_ _null_ _null_ ));
DESCR("statistics: number of scans done for table/index in current
transaction");
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 42bf9c4..b514f4d 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -490,6 +490,7 @@ typedef struct PgStat_StatDBEntry
PgStat_Counter n_tuples_updated;
PgStat_Counter n_tuples_deleted;
TimestampTz last_autovac_time;
+ TimestampTz stat_reset_timestamp;
/*
* tables and functions must be last in the struct, because we don't
write
@@ -533,6 +534,9 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter analyze_count;
TimestampTz autovac_analyze_timestamp; /* autovacuum initiated
*/
PgStat_Counter autovac_analyze_count;
+
+ TimestampTz stat_reset_timestamp;
+
} PgStat_StatTabEntry;
@@ -548,6 +552,9 @@ typedef struct PgStat_StatFuncEntry
PgStat_Counter f_time; /* times in microseconds */
PgStat_Counter f_time_self;
+
+ TimestampTz stat_reset_timestamp;
+
} PgStat_StatFuncEntry;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
