Re: Clearing global statistics

Itagaki Takahiro wrote:
> To be honest, I have a plan to add performance statistics counters to
> postgres. It is not bgwriter's counters, but cluster-level. I'd like
> to use your infrastructure in my work, too :)
>

Attached patch provides just that. It still works basically the same as
my earlier version, except you pass it a name of what you want to reset,
and if you don't give it the only valid one right now ('bgwriter') it
rejects it (for now):

gsmith=# select checkpoints_req,buffers_alloc from pg_stat_bgwriter;
checkpoints_req | buffers_alloc
-----------------+---------------
4 | 129
(1 row)

gsmith=# select pg_stat_reset_shared('bgwriter');
pg_stat_reset_shared
----------------------

(1 row)

gsmith=# select checkpoints_req,buffers_alloc from pg_stat_bgwriter;
checkpoints_req | buffers_alloc
-----------------+---------------
0 | 7
(1 row)

gsmith=# select pg_stat_reset_shared('rubbish');
ERROR: Unrecognized reset target

I turn the input text into an enum choice as part of composing the
message to the stats collector. If you wanted to add some other shared
cluster-wide reset capabilities into there you could re-use most of this
infrastructure. Just add an extra enum value, map the text into that
enum, and write the actual handler that does the reset work. Should be
able to reuse the same new message type and external UI I implemented
for this specific clearing feature.

I didn't see any interaction to be concerned about here with Magnus's
suggestion he wanted to target stats reset on objects such as a single
table at some point.

The main coding choice I wasn't really sure about is how I flag the
error case where you pass bad in. I do that validation and throw
ERRCODE_SYNTAX_ERROR before composing the message to the stats
collector. Didn't know if I should create a whole new error code just
for this specific case or if reusing another error code was more
appropriate. Also, I didn't actually have the collector process itself
validate the data at all, it just quietly ignores bad messages on the
presumption everything is already being checked during message creation.
That seems consistent with the other code here--the other message
handlers only seem to throw errors when something really terrible
happens, not when they just don't find something useful to do.

--
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 1f70fd4..b630bc8 100644
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
*************** postgres: <replaceable>user</> <replacea
*** 918,923 ****
--- 918,934 ----
         (requires superuser privileges)
        </entry>
       </row>
+
+      <row>
+       <entry><literal><function>pg_stat_reset_shared</function>()</literal></entry>
+       <entry><type>text</type></entry>
+       <entry>
+        Reset some of the shared statistics counters for the database cluster to
+        zero (requires superuser privileges).  Calling
+        <literal>pg_stat_reset_shared('bgwriter')</> will zero all the values shown by
+        <structname>pg_stat_bgwriter</>.
+       </entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 4fd2abf..7335a50 100644
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
*************** static void pgstat_recv_tabstat(PgStat_M
*** 270,275 ****
--- 270,276 ----
  static void pgstat_recv_tabpurge(PgStat_MsgTabpurge *msg, int len);
  static void pgstat_recv_dropdb(PgStat_MsgDropdb *msg, int len);
  static void pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int len);
+ static void pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len);
  static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len);
  static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
  static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
*************** pgstat_reset_counters(void)
*** 1153,1158 ****
--- 1154,1190 ----
      pgstat_send(&msg, sizeof(msg));
  }

+ /* ----------
+  * pgstat_reset_shared_counters() -
+  *
+  *    Tell the statistics collector to reset cluster-wide shared counters.
+  * ----------
+  */
+ void
+ pgstat_reset_shared_counters(const char *target)
+ {
+     PgStat_MsgResetsharedcounter msg;
+
+     if (pgStatSock < 0)
+         return;
+
+     if (!superuser())
+         ereport(ERROR,
+                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                  errmsg("must be superuser to reset statistics counters")));
+
+     if (strcmp(target, "bgwriter") == 0)
+         msg.m_resettarget = RESET_BGWRITER;
+     else
+         {
+         ereport(ERROR,
+                 (errcode(ERRCODE_SYNTAX_ERROR),
+                  errmsg("Unrecognized reset target")));
+         }
+
+     pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RESETSHAREDCOUNTER);
+     pgstat_send(&msg, sizeof(msg));
+ }

  /* ----------
   * pgstat_report_autovac() -
*************** PgstatCollectorMain(int argc, char *argv
*** 2915,2920 ****
--- 2947,2958 ----
                                               len);
                      break;

+                 case PGSTAT_MTYPE_RESETSHAREDCOUNTER:
+                     pgstat_recv_resetsharedcounter(
+                                              (PgStat_MsgResetsharedcounter *) &msg,
+                                              len);
+                     break;
+
                  case PGSTAT_MTYPE_AUTOVAC_START:
                      pgstat_recv_autovac((PgStat_MsgAutovacStart *) &msg, len);
                      break;
*************** pgstat_recv_resetcounter(PgStat_MsgReset
*** 3869,3874 ****
--- 3907,3930 ----
  }

  /* ----------
+  * pgstat_recv_resetshared() -
+  *
+  *    Reset some shared statistics of the cluster.
+  * ----------
+  */
+ static void
+ pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
+ {
+     if (msg->m_resettarget==RESET_BGWRITER)
+         {
+         /* Reset the global background writer statistics for the cluster. */
+         memset(&globalStats, 0, sizeof(globalStats));
+         }
+     /* Presumably the sender of this message validated the target, don't
+        complain here if it's not valid */
+ }
+
+ /* ----------
   * pgstat_recv_autovac() -
   *
   *    Process an autovacuum signalling message.
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index ab741ca..e5bc95a 100644
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
*************** extern Datum pg_stat_get_buf_alloc(PG_FU
*** 78,83 ****
--- 78,84 ----

  extern Datum pg_stat_clear_snapshot(PG_FUNCTION_ARGS);
  extern Datum pg_stat_reset(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_reset_shared(PG_FUNCTION_ARGS);

  /* Global bgwriter statistics, from bgwriter.c */
  extern PgStat_MsgBgWriter bgwriterStats;
*************** pg_stat_reset(PG_FUNCTION_ARGS)
*** 1108,1110 ****
--- 1109,1123 ----

      PG_RETURN_VOID();
  }
+
+ /* Reset some shared cluster-wide counters */
+ Datum
+ pg_stat_reset_shared(PG_FUNCTION_ARGS)
+ {
+     char    *target;
+     target = TextDatumGetCString(PG_GETARG_DATUM(0));
+
+     pgstat_reset_shared_counters(target);
+
+     PG_RETURN_VOID();
+ }
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index a8efed9..87072fc 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 2230 (  pg_stat_clear_
*** 3071,3076 ****
--- 3071,3078 ----
  DESCR("statistics: discard current transaction's statistics snapshot");
  DATA(insert OID = 2274 (  pg_stat_reset                    PGNSP PGUID 12 1 0 0 f f f f f v 0 0 2278 "" _null_ _null_
_null__null_    pg_stat_reset _null_ _null_ _null_ )); 
  DESCR("statistics: reset collected statistics for current database");
+ DATA(insert OID = 3775 (  pg_stat_reset_shared            PGNSP PGUID 12 1 0 0 f f f f f v 1 0 2278 "25" _null_
_null__null_ _null_    pg_stat_reset_shared _null_ _null_ _null_ )); 
+ DESCR("statistics: reset collected statistics shared across the cluster");

  DATA(insert OID = 1946 (  encode                        PGNSP PGUID 12 1 0 0 f f f t f i 2 0 25 "17 25" _null_ _null_
_null__null_ binary_encode _null_ _null_ _null_ )); 
  DESCR("convert bytea value into some ascii-only text string");
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 3cc334c..c3de665 100644
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
*************** typedef enum StatMsgType
*** 38,43 ****
--- 38,44 ----
      PGSTAT_MTYPE_TABPURGE,
      PGSTAT_MTYPE_DROPDB,
      PGSTAT_MTYPE_RESETCOUNTER,
+     PGSTAT_MTYPE_RESETSHAREDCOUNTER,
      PGSTAT_MTYPE_AUTOVAC_START,
      PGSTAT_MTYPE_VACUUM,
      PGSTAT_MTYPE_ANALYZE,
*************** typedef struct PgStat_TableCounts
*** 93,98 ****
--- 94,105 ----
      PgStat_Counter t_blocks_hit;
  } PgStat_TableCounts;

+ /* Possible targets for resetting cluster-wide shared values */
+ typedef enum PgStat_Shared_Reset_Target
+ {
+     RESET_BGWRITER
+ } PgStat_Shared_Reset_Target;
+

  /* ------------------------------------------------------------
   * Structures kept in backend local memory while accumulating counts
*************** typedef struct PgStat_MsgResetcounter
*** 260,265 ****
--- 267,282 ----
      Oid            m_databaseid;
  } PgStat_MsgResetcounter;

+ /* ----------
+  * PgStat_MsgResetsharedcounter    Sent by the backend to tell the collector
+  *                                to reset a shared counter
+  * ----------
+  */
+ typedef struct PgStat_MsgResetsharedcounter
+ {
+     PgStat_MsgHdr m_hdr;
+     PgStat_Shared_Reset_Target m_resettarget;
+ } PgStat_MsgResetsharedcounter;

  /* ----------
   * PgStat_MsgAutovacStart        Sent by the autovacuum daemon to signal
*************** typedef union PgStat_Msg
*** 414,419 ****
--- 431,437 ----
      PgStat_MsgTabpurge msg_tabpurge;
      PgStat_MsgDropdb msg_dropdb;
      PgStat_MsgResetcounter msg_resetcounter;
+     PgStat_MsgResetsharedcounter msg_resetsharedcounter;
      PgStat_MsgAutovacStart msg_autovacuum;
      PgStat_MsgVacuum msg_vacuum;
      PgStat_MsgAnalyze msg_analyze;
*************** extern void pgstat_drop_database(Oid dat
*** 635,640 ****
--- 653,659 ----

  extern void pgstat_clear_snapshot(void);
  extern void pgstat_reset_counters(void);
+ extern void pgstat_reset_shared_counters(const char *);

  extern void pgstat_report_autovac(Oid dboid);
  extern void pgstat_report_vacuum(Oid tableoid, bool shared, bool adopt_counts,