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

Commitf3fa313

Browse files
committed
Add pg_buffercache_usage_counts() to contrib/pg_buffercache.
It was pointed out that pg_buffercache_summary()'s report ofthe overall average usage count isn't that useful, and whatwould be more helpful in many cases is to report totals foreach possible usage count. Add a new function to do it likethat. Since pg_buffercache 1.4 is already new for v16,we don't need to create a new extension version; we'll justdefine this as part of 1.4.Nathan BossartDiscussion:https://postgr.es/m/20230130233040.GA2800702@nathanxps13
1 parente056c55 commitf3fa313

File tree

5 files changed

+173
-4
lines changed

5 files changed

+173
-4
lines changed

‎contrib/pg_buffercache/expected/pg_buffercache.out

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,12 @@ from pg_buffercache_summary();
1717
t | t | t
1818
(1 row)
1919

20+
SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
21+
?column?
22+
----------
23+
t
24+
(1 row)
25+
2026
-- Check that the functions / views can't be accessed by default. To avoid
2127
-- having to create a dedicated user, use the pg_database_owner pseudo-role.
2228
SET ROLE pg_database_owner;
@@ -26,6 +32,8 @@ SELECT * FROM pg_buffercache_pages() AS p (wrong int);
2632
ERROR: permission denied for function pg_buffercache_pages
2733
SELECT * FROM pg_buffercache_summary();
2834
ERROR: permission denied for function pg_buffercache_summary
35+
SELECT * FROM pg_buffercache_usage_counts();
36+
ERROR: permission denied for function pg_buffercache_usage_counts
2937
RESET role;
3038
-- Check that pg_monitor is allowed to query view / function
3139
SET ROLE pg_monitor;
@@ -41,3 +49,9 @@ SELECT buffers_used + buffers_unused > 0 FROM pg_buffercache_summary();
4149
t
4250
(1 row)
4351

52+
SELECT count(*) > 0 FROM pg_buffercache_usage_counts();
53+
?column?
54+
----------
55+
t
56+
(1 row)
57+

‎contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,17 @@ CREATE FUNCTION pg_buffercache_summary(
1212
AS'MODULE_PATHNAME','pg_buffercache_summary'
1313
LANGUAGE C PARALLEL SAFE;
1414

15+
CREATEFUNCTIONpg_buffercache_usage_counts(
16+
OUT usage_count int4,
17+
OUT buffers int4,
18+
OUT dirty int4,
19+
OUT pinned int4)
20+
RETURNS SETOF record
21+
AS'MODULE_PATHNAME','pg_buffercache_usage_counts'
22+
LANGUAGE C PARALLEL SAFE;
23+
1524
-- Don't want these to be available to public.
1625
REVOKE ALLON FUNCTION pg_buffercache_summary()FROM PUBLIC;
1726
GRANT EXECUTEON FUNCTION pg_buffercache_summary() TO pg_monitor;
27+
REVOKE ALLON FUNCTION pg_buffercache_usage_counts()FROM PUBLIC;
28+
GRANT EXECUTEON FUNCTION pg_buffercache_usage_counts() TO pg_monitor;

‎contrib/pg_buffercache/pg_buffercache_pages.c

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,7 @@
1818
#defineNUM_BUFFERCACHE_PAGES_MIN_ELEM8
1919
#defineNUM_BUFFERCACHE_PAGES_ELEM9
2020
#defineNUM_BUFFERCACHE_SUMMARY_ELEM 5
21+
#defineNUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4
2122

2223
PG_MODULE_MAGIC;
2324

@@ -61,6 +62,7 @@ typedef struct
6162
*/
6263
PG_FUNCTION_INFO_V1(pg_buffercache_pages);
6364
PG_FUNCTION_INFO_V1(pg_buffercache_summary);
65+
PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts);
6466

6567
Datum
6668
pg_buffercache_pages(PG_FUNCTION_ARGS)
@@ -304,3 +306,44 @@ pg_buffercache_summary(PG_FUNCTION_ARGS)
304306

305307
PG_RETURN_DATUM(result);
306308
}
309+
310+
Datum
311+
pg_buffercache_usage_counts(PG_FUNCTION_ARGS)
312+
{
313+
ReturnSetInfo*rsinfo= (ReturnSetInfo*)fcinfo->resultinfo;
314+
intusage_counts[BM_MAX_USAGE_COUNT+1]= {0};
315+
intdirty[BM_MAX_USAGE_COUNT+1]= {0};
316+
intpinned[BM_MAX_USAGE_COUNT+1]= {0};
317+
Datumvalues[NUM_BUFFERCACHE_USAGE_COUNTS_ELEM];
318+
boolnulls[NUM_BUFFERCACHE_USAGE_COUNTS_ELEM]= {0};
319+
320+
InitMaterializedSRF(fcinfo,0);
321+
322+
for (inti=0;i<NBuffers;i++)
323+
{
324+
BufferDesc*bufHdr=GetBufferDescriptor(i);
325+
uint32buf_state=pg_atomic_read_u32(&bufHdr->state);
326+
intusage_count;
327+
328+
usage_count=BUF_STATE_GET_USAGECOUNT(buf_state);
329+
usage_counts[usage_count]++;
330+
331+
if (buf_state&BM_DIRTY)
332+
dirty[usage_count]++;
333+
334+
if (BUF_STATE_GET_REFCOUNT(buf_state)>0)
335+
pinned[usage_count]++;
336+
}
337+
338+
for (inti=0;i<BM_MAX_USAGE_COUNT+1;i++)
339+
{
340+
values[0]=Int32GetDatum(i);
341+
values[1]=Int32GetDatum(usage_counts[i]);
342+
values[2]=Int32GetDatum(dirty[i]);
343+
values[3]=Int32GetDatum(pinned[i]);
344+
345+
tuplestore_putvalues(rsinfo->setResult,rsinfo->setDesc,values,nulls);
346+
}
347+
348+
return (Datum)0;
349+
}

‎contrib/pg_buffercache/sql/pg_buffercache.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,15 +10,19 @@ select buffers_used + buffers_unused > 0,
1010
buffers_pinned<= buffers_used
1111
from pg_buffercache_summary();
1212

13+
SELECTcount(*)>0FROM pg_buffercache_usage_counts()WHERE buffers>=0;
14+
1315
-- Check that the functions / views can't be accessed by default. To avoid
1416
-- having to create a dedicated user, use the pg_database_owner pseudo-role.
1517
SET ROLE pg_database_owner;
1618
SELECT*FROM pg_buffercache;
1719
SELECT*FROM pg_buffercache_pages()AS p (wrongint);
1820
SELECT*FROM pg_buffercache_summary();
21+
SELECT*FROM pg_buffercache_usage_counts();
1922
RESET role;
2023

2124
-- Check that pg_monitor is allowed to query view / function
2225
SET ROLE pg_monitor;
2326
SELECTcount(*)>0FROM pg_buffercache;
2427
SELECT buffers_used+ buffers_unused>0FROM pg_buffercache_summary();
28+
SELECTcount(*)>0FROM pg_buffercache_usage_counts();

‎doc/src/sgml/pgbuffercache.sgml

Lines changed: 101 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -22,9 +22,10 @@
2222
</indexterm>
2323

2424
<para>
25-
The module provides the <function>pg_buffercache_pages()</function>
26-
function, wrapped in the <structname>pg_buffercache</structname> view, and
27-
the <function>pg_buffercache_summary()</function> function.
25+
This module provides the <function>pg_buffercache_pages()</function>
26+
function (wrapped in the <structname>pg_buffercache</structname> view),
27+
the <function>pg_buffercache_summary()</function> function, and the
28+
<function>pg_buffercache_usage_counts()</function> function.
2829
</para>
2930

3031
<para>
@@ -39,6 +40,12 @@
3940
row summarizing the state of the shared buffer cache.
4041
</para>
4142

43+
<para>
44+
The <function>pg_buffercache_usage_counts()</function> function returns a set
45+
of records, each row describing the number of buffers with a given usage
46+
count.
47+
</para>
48+
4249
<para>
4350
By default, use is restricted to superusers and roles with privileges of the
4451
<literal>pg_monitor</literal> role. Access may be granted to others
@@ -243,7 +250,7 @@
243250
<structfield>usagecount_avg</structfield> <type>float8</type>
244251
</para>
245252
<para>
246-
Averageusagecount of used shared buffers
253+
Averageusage count of used shared buffers
247254
</para></entry>
248255
</row>
249256
</tbody>
@@ -266,6 +273,84 @@
266273
</para>
267274
</sect2>
268275

276+
<sect2>
277+
<title>The <function>pg_buffercache_usage_counts()</function> Function</title>
278+
279+
<para>
280+
The definitions of the columns exposed by the function are shown in
281+
<xref linkend="pgbuffercache_usage_counts-columns"/>.
282+
</para>
283+
284+
<table id="pgbuffercache_usage_counts-columns">
285+
<title><function>pg_buffercache_usage_counts()</function> Output Columns</title>
286+
<tgroup cols="1">
287+
<thead>
288+
<row>
289+
<entry role="catalog_table_entry"><para role="column_definition">
290+
Column Type
291+
</para>
292+
<para>
293+
Description
294+
</para></entry>
295+
</row>
296+
</thead>
297+
298+
<tbody>
299+
<row>
300+
<entry role="catalog_table_entry"><para role="column_definition">
301+
<structfield>usage_count</structfield> <type>int4</type>
302+
</para>
303+
<para>
304+
A possible buffer usage count
305+
</para></entry>
306+
</row>
307+
308+
<row>
309+
<entry role="catalog_table_entry"><para role="column_definition">
310+
<structfield>buffers</structfield> <type>int4</type>
311+
</para>
312+
<para>
313+
Number of buffers with the usage count
314+
</para></entry>
315+
</row>
316+
317+
<row>
318+
<entry role="catalog_table_entry"><para role="column_definition">
319+
<structfield>dirty</structfield> <type>int4</type>
320+
</para>
321+
<para>
322+
Number of dirty buffers with the usage count
323+
</para></entry>
324+
</row>
325+
326+
<row>
327+
<entry role="catalog_table_entry"><para role="column_definition">
328+
<structfield>pinned</structfield> <type>int4</type>
329+
</para>
330+
<para>
331+
Number of pinned buffers with the usage count
332+
</para></entry>
333+
</row>
334+
</tbody>
335+
</tgroup>
336+
</table>
337+
338+
<para>
339+
The <function>pg_buffercache_usage_counts()</function> function returns a
340+
set of rows summarizing the states of all shared buffers, aggregated over
341+
the possible usage count values. Similar and more detailed information is
342+
provided by the <structname>pg_buffercache</structname> view, but
343+
<function>pg_buffercache_usage_counts()</function> is significantly cheaper.
344+
</para>
345+
346+
<para>
347+
Like the <structname>pg_buffercache</structname> view,
348+
<function>pg_buffercache_usage_counts()</function> does not acquire buffer
349+
manager locks. Therefore concurrent activity can lead to minor inaccuracies
350+
in the result.
351+
</para>
352+
</sect2>
353+
269354
<sect2 id="pgbuffercache-sample-output">
270355
<title>Sample Output</title>
271356

@@ -300,6 +385,18 @@ regression=# SELECT * FROM pg_buffercache_summary();
300385
--------------+----------------+---------------+----------------+----------------
301386
248 | 2096904 | 39 | 0 | 3.141129
302387
(1 row)
388+
389+
390+
regression=# SELECT * FROM pg_buffercache_usage_counts();
391+
usage_count | buffers | dirty | pinned
392+
-------------+---------+-------+--------
393+
0 | 14650 | 0 | 0
394+
1 | 1436 | 671 | 0
395+
2 | 102 | 88 | 0
396+
3 | 23 | 21 | 0
397+
4 | 9 | 7 | 0
398+
5 | 164 | 106 | 0
399+
(6 rows)
303400
</screen>
304401
</sect2>
305402

0 commit comments

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