Dne 23.12.2010 20:09, Robert Haas napsal(a): > 2010/12/23 Tomas Vondra <t...@fuzzy.cz>: >> Dne 20.12.2010 00:03, Tom Lane napsal(a): >>> I wrote: >>>> That is not the number of interest. The number of interest is that it's >>>> 8 bytes added onto a struct that currently contains 11 of 'em; in other >>>> words a 9% increase in the size of the stats file, and consequently >>>> about a 9% increase in the cost of updating it. >>> >>> Wups, sorry, I was looking at the wrong struct. It's actually an >>> addition of 1 doubleword to a struct of 21 of 'em, or about 5%. >>> That's still an awful lot in comparison to the prospective usefulness >>> of the information. >>> >>> regards, tom lane >>> >> >> OK, so here goes the simplified patch - it tracks one reset timestamp >> for a background writer and for each database. > > I think you forgot the attachment.
Yes, I did. Thanks! Tomas
>From da2154954a547c143cd0d130597411911f339c07 Mon Sep 17 00:00:00 2001 From: vampire <vamp...@rimmer.reddwarf> Date: Thu, 23 Dec 2010 18:40:48 +0100 Subject: [PATCH] Track timestamp of the last stats reset (for each database and a background writer). This is simplified version of the previous patch, that kept a timestamp for each database, table, index and function and for the background writer. This does not keep the timestamp for individual objects, but if stats for a table/index/function are reset, the timestamp for the whole database is updated. --- doc/src/sgml/monitoring.sgml | 18 ++++++++++++++++++ src/backend/catalog/system_views.sql | 6 ++++-- src/backend/postmaster/pgstat.c | 13 +++++++++++++ src/backend/utils/adt/pgstatfuncs.c | 26 ++++++++++++++++++++++++++ src/include/catalog/pg_proc.h | 4 ++++ src/include/pgstat.h | 5 +++++ 6 files changed, 70 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 5fd0213..99e091d 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -600,6 +600,15 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re </row> <row> + <entry><literal><function>pg_stat_get_db_stat_reset_time</function>(<type>oid</type>)</literal></entry> + <entry><type>timestamptz</type></entry> + <entry> + Time of the last reset of statistics for this database (as a result of executing + <function>pg_stat_reset</function> function) or any object within it (table or index). + </entry> + </row> + + <row> <entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry> <entry><type>bigint</type></entry> <entry> @@ -1062,6 +1071,15 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re <varname>bgwriter_lru_maxpages</varname> parameter </entry> </row> + + <row> + <entry><literal><function>pg_stat_get_bgwriter_stat_reset_time()</function></literal></entry> + <entry><type>timestamptz</type></entry> + <entry> + Time of the last reset of statistics for the background writer (as a result of executing + <function>pg_stat_reset_shared('bgwriter')</function>) + </entry> + </row> <row> <entry><literal><function>pg_stat_get_buf_written_backend()</function></literal></entry> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 346eaaf..da92438 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -502,7 +502,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_stat_reset_time(D.oid) AS stats_reset FROM pg_database D; CREATE VIEW pg_stat_user_functions AS @@ -538,7 +539,8 @@ CREATE VIEW pg_stat_bgwriter AS pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, - pg_stat_get_buf_alloc() AS buffers_alloc; + pg_stat_get_buf_alloc() AS buffers_alloc, + pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; CREATE VIEW pg_user_mappings AS SELECT diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 856daa7..66e0b69 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -3130,6 +3130,8 @@ pgstat_get_db_entry(Oid databaseid, bool create) 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); @@ -3408,6 +3410,12 @@ pgstat_read_statsfile(Oid onlydb, bool permanent) * load an existing statsfile. */ memset(&globalStats, 0, sizeof(globalStats)); + + /* + * Set the current timestamp (will be kept only in case we can't load an + * existing statsfile. + */ + globalStats.stat_reset_timestamp = GetCurrentTimestamp(); /* * Try to open the status file. If it doesn't exist, the backends simply @@ -4022,6 +4030,8 @@ pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int len) dbentry->n_tuples_deleted = 0; dbentry->last_autovac_time = 0; + dbentry->stat_reset_timestamp = GetCurrentTimestamp(); + memset(&hash_ctl, 0, sizeof(hash_ctl)); hash_ctl.keysize = sizeof(Oid); hash_ctl.entrysize = sizeof(PgStat_StatTabEntry); @@ -4053,6 +4063,7 @@ pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len) { /* Reset the global background writer statistics for the cluster. */ memset(&globalStats, 0, sizeof(globalStats)); + globalStats.stat_reset_timestamp = GetCurrentTimestamp(); } /* @@ -4077,6 +4088,8 @@ pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, int len) if (!dbentry) return; + /* Set the reset timestamp for the whole database */ + dbentry->stat_reset_timestamp = GetCurrentTimestamp(); /* Remove object if it exists, ignore it if not */ if (msg->m_resettype == RESET_TABLE) diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index adab948..5a1ea49 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -71,12 +71,14 @@ 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_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); extern Datum pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS); extern Datum pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS); extern Datum pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS); extern Datum pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS); extern Datum pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS); extern Datum pg_stat_get_buf_alloc(PG_FUNCTION_ARGS); @@ -1128,6 +1130,24 @@ pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS) PG_RETURN_INT64(result); } + +Datum +pg_stat_get_db_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) @@ -1160,6 +1180,12 @@ pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS) } Datum +pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS) +{ + PG_RETURN_TIMESTAMPTZ(pgstat_fetch_global()->stat_reset_timestamp); +} + +Datum pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS) { PG_RETURN_INT64(pgstat_fetch_global()->buf_written_backend); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 1e6e75f..14ba954 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -3109,6 +3109,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_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_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_ )); @@ -3119,6 +3121,8 @@ DATA(insert OID = 2772 ( pg_stat_get_bgwriter_buf_written_clean PGNSP PGUID 12 1 DESCR("statistics: number of buffers written by the bgwriter for cleaning dirty buffers"); DATA(insert OID = 2773 ( pg_stat_get_bgwriter_maxwritten_clean PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_maxwritten_clean _null_ _null_ _null_ )); DESCR("statistics: number of times the bgwriter stopped processing when it had written too many buffers while cleaning"); +DATA(insert OID = 3118 ( pg_stat_get_bgwriter_stat_reset_time PGNSP PGUID 12 1 0 0 f f f t f s 0 0 1184 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_stat_reset_time _null_ _null_ _null_ )); +DESCR("statistics: last reset for the bgwriter"); DATA(insert OID = 2775 ( pg_stat_get_buf_written_backend PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_buf_written_backend _null_ _null_ _null_ )); DESCR("statistics: number of buffers written by backends"); DATA(insert OID = 3063 ( pg_stat_get_buf_fsync_backend PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_buf_fsync_backend _null_ _null_ _null_ )); diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 42bf9c4..9f679b2 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -490,6 +490,8 @@ 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 @@ -565,6 +567,9 @@ typedef struct PgStat_GlobalStats PgStat_Counter buf_written_backend; PgStat_Counter buf_fsync_backend; PgStat_Counter buf_alloc; + + TimestampTz stat_reset_timestamp; + } PgStat_GlobalStats; -- 1.7.2.2
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers