From 28cac71bd368788d1ab22f048eef211641fb1283 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Thu, 2 Apr 2020 02:11:38 +0200 Subject: Collect statistics about SLRU caches There's a number of SLRU caches used to access important data like clog, commit timestamps, multixact, asynchronous notifications, etc. Until now we had no easy way to monitor these shared caches, compute hit ratios, number of reads/writes etc. This commit extends the statistics collector to track this information for a predefined list of SLRUs, and also introduces a new system view pg_stat_slru displaying the data. The list of built-in SLRUs is fixed, but additional SLRUs may be defined in extensions. Unfortunately, there's no suitable registry of SLRUs, so this patch simply defines a fixed list of SLRUs with entries for the built-in ones and one entry for all additional SLRUs. Extensions adding their own SLRU are fairly rare, so this seems acceptable. This patch only allows monitoring of SLRUs, not tuning. The SLRU sizes are still fixed (hard-coded in the code) and it's not entirely clear which of the SLRUs might need a GUC to tune size. In a way, allowing us to determine that is one of the goals of this patch. Bump catversion as the patch introduces new functions and system view. Author: Tomas Vondra Reviewed-by: Alvaro Herrera Discussion: https://www.postgresql.org/message-id/flat/20200119143707.gyinppnigokesjok@development --- doc/src/sgml/monitoring.sgml | 97 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 97 insertions(+) (limited to 'doc/src') diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 220b8164c35..28ceb04d331 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -575,6 +575,13 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser yet included in pg_stat_user_functions). + + pg_stat_slrupg_stat_slru + One row per SLRU, showing statistics of operations. See + for details. + + + @@ -3259,6 +3266,76 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i + + The pg_stat_slru view will contain + one row for each tracked SLRU cache, showing statistics about access + to cached pages. + + + + <structname>pg_stat_slru</structname> View + + + + Column + Type + Description + + + + + + name + name + name of the SLRU + + + blks_zeroed + bigint + Number of blocks zeroed during initializations + + + blks_hit + biging + Number of times disk blocks were found already in the SLRU, + so that a read was not necessary (this only includes hits in the + SLRU, not the operating system's file system cache) + + + + blks_read + bigint + Number of disk blocks read for this SLRU + + + blks_written + bigint + Number of disk blocks written for this SLRU + + + blks_exists + bigint + Number of blocks checked for existence for this SLRU + + + flushes + bigint + Number of flushes of dirty data for this SLRU + + + truncates + bigint + Number of truncates for this SLRU + + + stats_reset + timestamp with time zone + Time at which these statistics were last reset + + + +
+ The pg_stat_user_functions view will contain one row for each tracked function, showing statistics about executions of @@ -3383,6 +3460,26 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i function can be granted to others) + + + pg_stat_reset_slru(text)pg_stat_reset_slru + void + + Reset statistics either for a single SLRU or all SLRUs in the cluster + to zero (requires superuser privileges by default, but EXECUTE for this + function can be granted to others). + Calling pg_stat_reset_slru(NULL) will zero all the + counters shown in the pg_stat_slru view for + all SLRU caches. + Calling pg_stat_reset_slru(name) with names from a + predefined list (async, clog, + commit_timestamp, multixact_offset, + multixact_member, oldserxid, + pg_xact, subtrans and + other) resets counters for only that entry. + Names not included in this list are treated as other. + + -- cgit v1.2.3