Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit28cac71

Browse files
committed
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 nowwe 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 informationfor a predefined list of SLRUs, and also introduces a new system viewpg_stat_slru displaying the data.The list of built-in SLRUs is fixed, but additional SLRUs may be definedin extensions. Unfortunately, there's no suitable registry of SLRUs, sothis patch simply defines a fixed list of SLRUs with entries for thebuilt-in ones and one entry for all additional SLRUs. Extensions addingtheir own SLRU are fairly rare, so this seems acceptable.This patch only allows monitoring of SLRUs, not tuning. The SLRU sizesare still fixed (hard-coded in the code) and it's not entirely clearwhich of the SLRUs might need a GUC to tune size. In a way, allowing usto determine that is one of the goals of this patch.Bump catversion as the patch introduces new functions and system view.Author: Tomas VondraReviewed-by: Alvaro HerreraDiscussion:https://www.postgresql.org/message-id/flat/20200119143707.gyinppnigokesjok@development
1 parent17ca067 commit28cac71

File tree

9 files changed

+613
-1
lines changed

9 files changed

+613
-1
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -575,6 +575,13 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
575575
yet included in <structname>pg_stat_user_functions</structname>).</entry>
576576
</row>
577577

578+
<row>
579+
<entry><structname>pg_stat_slru</structname><indexterm><primary>pg_stat_slru</primary></indexterm></entry>
580+
<entry>One row per SLRU, showing statistics of operations. See
581+
<xref linkend="pg-stat-slru-view"/> for details.
582+
</entry>
583+
</row>
584+
578585
</tbody>
579586
</tgroup>
580587
</table>
@@ -3259,6 +3266,76 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
32593266
</tgroup>
32603267
</table>
32613268

3269+
<para>
3270+
The <structname>pg_stat_slru</structname> view will contain
3271+
one row for each tracked SLRU cache, showing statistics about access
3272+
to cached pages.
3273+
</para>
3274+
3275+
<table id="pg-stat-slru-view" xreflabel="pg_stat_slru">
3276+
<title><structname>pg_stat_slru</structname> View</title>
3277+
<tgroup cols="3">
3278+
<thead>
3279+
<row>
3280+
<entry>Column</entry>
3281+
<entry>Type</entry>
3282+
<entry>Description</entry>
3283+
</row>
3284+
</thead>
3285+
3286+
<tbody>
3287+
<row>
3288+
<entry><structfield>name</structfield></entry>
3289+
<entry><type>name</type></entry>
3290+
<entry>name of the SLRU</entry>
3291+
</row>
3292+
<row>
3293+
<entry><structfield>blks_zeroed</structfield></entry>
3294+
<entry><type>bigint</type></entry>
3295+
<entry>Number of blocks zeroed during initializations</entry>
3296+
</row>
3297+
<row>
3298+
<entry><structfield>blks_hit</structfield></entry>
3299+
<entry><type>biging</type></entry>
3300+
<entry>Number of times disk blocks were found already in the SLRU,
3301+
so that a read was not necessary (this only includes hits in the
3302+
SLRU, not the operating system's file system cache)
3303+
</entry>
3304+
</row>
3305+
<row>
3306+
<entry><structfield>blks_read</structfield></entry>
3307+
<entry><type>bigint</type></entry>
3308+
<entry>Number of disk blocks read for this SLRU</entry>
3309+
</row>
3310+
<row>
3311+
<entry><structfield>blks_written</structfield></entry>
3312+
<entry><type>bigint</type></entry>
3313+
<entry>Number of disk blocks written for this SLRU</entry>
3314+
</row>
3315+
<row>
3316+
<entry><structfield>blks_exists</structfield></entry>
3317+
<entry><type>bigint</type></entry>
3318+
<entry>Number of blocks checked for existence for this SLRU</entry>
3319+
</row>
3320+
<row>
3321+
<entry><structfield>flushes</structfield></entry>
3322+
<entry><type>bigint</type></entry>
3323+
<entry>Number of flushes of dirty data for this SLRU</entry>
3324+
</row>
3325+
<row>
3326+
<entry><structfield>truncates</structfield></entry>
3327+
<entry><type>bigint</type></entry>
3328+
<entry>Number of truncates for this SLRU</entry>
3329+
</row>
3330+
<row>
3331+
<entry><structfield>stats_reset</structfield></entry>
3332+
<entry><type>timestamp with time zone</type></entry>
3333+
<entry>Time at which these statistics were last reset</entry>
3334+
</row>
3335+
</tbody>
3336+
</tgroup>
3337+
</table>
3338+
32623339
<para>
32633340
The <structname>pg_stat_user_functions</structname> view will contain
32643341
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
33833460
function can be granted to others)
33843461
</entry>
33853462
</row>
3463+
3464+
<row>
3465+
<entry><literal><function>pg_stat_reset_slru</function>(text)</literal><indexterm><primary>pg_stat_reset_slru</primary></indexterm></entry>
3466+
<entry><type>void</type></entry>
3467+
<entry>
3468+
Reset statistics either for a single SLRU or all SLRUs in the cluster
3469+
to zero (requires superuser privileges by default, but EXECUTE for this
3470+
function can be granted to others).
3471+
Calling <literal>pg_stat_reset_slru(NULL)</literal> will zero all the
3472+
counters shown in the <structname>pg_stat_slru</structname> view for
3473+
all SLRU caches.
3474+
Calling <literal>pg_stat_reset_slru(name)</literal> with names from a
3475+
predefined list (<literal>async</literal>, <literal>clog</literal>,
3476+
<literal>commit_timestamp</literal>, <literal>multixact_offset</literal>,
3477+
<literal>multixact_member</literal>, <literal>oldserxid</literal>,
3478+
<literal>pg_xact</literal>, <literal>subtrans</literal> and
3479+
<literal>other</literal>) resets counters for only that entry.
3480+
Names not included in this list are treated as <literal>other</literal>.
3481+
</entry>
3482+
</row>
33863483
</tbody>
33873484
</tgroup>
33883485
</table>

‎src/backend/access/transam/slru.c

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -286,6 +286,9 @@ SimpleLruZeroPage(SlruCtl ctl, int pageno)
286286
/* Assume this page is now the latest active page */
287287
shared->latest_page_number=pageno;
288288

289+
/* update the stats counter of zeroed pages */
290+
pgstat_count_slru_page_zeroed(ctl);
291+
289292
returnslotno;
290293
}
291294

@@ -403,6 +406,10 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,
403406
}
404407
/* Otherwise, it's ready to use */
405408
SlruRecentlyUsed(shared,slotno);
409+
410+
/* update the stats counter of pages found in the SLRU */
411+
pgstat_count_slru_page_hit(ctl);
412+
406413
returnslotno;
407414
}
408415

@@ -444,6 +451,10 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,
444451
SlruReportIOError(ctl,pageno,xid);
445452

446453
SlruRecentlyUsed(shared,slotno);
454+
455+
/* update the stats counter of pages not found in SLRU */
456+
pgstat_count_slru_page_read(ctl);
457+
447458
returnslotno;
448459
}
449460
}
@@ -596,6 +607,9 @@ SimpleLruDoesPhysicalPageExist(SlruCtl ctl, int pageno)
596607
boolresult;
597608
off_tendpos;
598609

610+
/* update the stats counter of checked pages */
611+
pgstat_count_slru_page_exists(ctl);
612+
599613
SlruFileName(ctl,path,segno);
600614

601615
fd=OpenTransientFile(path,O_RDONLY |PG_BINARY);
@@ -730,6 +744,9 @@ SlruPhysicalWritePage(SlruCtl ctl, int pageno, int slotno, SlruFlush fdata)
730744
charpath[MAXPGPATH];
731745
intfd=-1;
732746

747+
/* update the stats counter of written pages */
748+
pgstat_count_slru_page_written(ctl);
749+
733750
/*
734751
* Honor the write-WAL-before-data rule, if appropriate, so that we do not
735752
* write out data before associated WAL records. This is the same action
@@ -1125,6 +1142,9 @@ SimpleLruFlush(SlruCtl ctl, bool allow_redirtied)
11251142
inti;
11261143
boolok;
11271144

1145+
/* update the stats counter of flushes */
1146+
pgstat_count_slru_flush(ctl);
1147+
11281148
/*
11291149
* Find and write dirty pages
11301150
*/
@@ -1186,6 +1206,9 @@ SimpleLruTruncate(SlruCtl ctl, int cutoffPage)
11861206
SlruSharedshared=ctl->shared;
11871207
intslotno;
11881208

1209+
/* update the stats counter of truncates */
1210+
pgstat_count_slru_truncate(ctl);
1211+
11891212
/*
11901213
* The cutoff point is the start of the segment containing cutoffPage.
11911214
*/

‎src/backend/catalog/system_views.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -793,6 +793,19 @@ CREATE VIEW pg_stat_replication AS
793793
JOIN pg_stat_get_wal_senders()AS WON (S.pid=W.pid)
794794
LEFT JOIN pg_authidAS UON (S.usesysid=U.oid);
795795

796+
CREATEVIEWpg_stat_slruAS
797+
SELECT
798+
s.name,
799+
s.blks_zeroed,
800+
s.blks_hit,
801+
s.blks_read,
802+
s.blks_written,
803+
s.blks_exists,
804+
s.flushes,
805+
s.truncates,
806+
s.stats_reset
807+
FROM pg_stat_get_slru() s;
808+
796809
CREATEVIEWpg_stat_wal_receiverAS
797810
SELECT
798811
s.pid,
@@ -1410,6 +1423,7 @@ REVOKE EXECUTE ON FUNCTION pg_promote(boolean, integer) FROM public;
14101423

14111424
REVOKE EXECUTEON FUNCTION pg_stat_reset()FROM public;
14121425
REVOKE EXECUTEON FUNCTION pg_stat_reset_shared(text)FROM public;
1426+
REVOKE EXECUTEON FUNCTION pg_stat_reset_slru(text)FROM public;
14131427
REVOKE EXECUTEON FUNCTION pg_stat_reset_single_table_counters(oid)FROM public;
14141428
REVOKE EXECUTEON FUNCTION pg_stat_reset_single_function_counters(oid)FROM public;
14151429

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp