Skip to content

Navigation Menu

Sign in

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
Appearance settings

Commit f3fa313

Browse filesBrowse files
tglsfdctglsfdc
committed
Add pg_buffercache_usage_counts() to contrib/pg_buffercache.
It was pointed out that pg_buffercache_summary()'s report of the overall average usage count isn't that useful, and what would be more helpful in many cases is to report totals for each possible usage count. Add a new function to do it like that. Since pg_buffercache 1.4 is already new for v16, we don't need to create a new extension version; we'll just define this as part of 1.4. Nathan Bossart Discussion: https://postgr.es/m/20230130233040.GA2800702@nathanxps13
1 parent e056c55 commit f3fa313
Copy full SHA for f3fa313

File tree

5 files changed

+173
-4
lines changed
Filter options

5 files changed

+173
-4
lines changed

‎contrib/pg_buffercache/expected/pg_buffercache.out

Copy file name to clipboardExpand all lines: contrib/pg_buffercache/expected/pg_buffercache.out
+14Lines 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

Copy file name to clipboardExpand all lines: contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
+11Lines 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+
CREATE FUNCTION pg_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 ALL ON FUNCTION pg_buffercache_summary() FROM PUBLIC;
1726
GRANT EXECUTE ON FUNCTION pg_buffercache_summary() TO pg_monitor;
27+
REVOKE ALL ON FUNCTION pg_buffercache_usage_counts() FROM PUBLIC;
28+
GRANT EXECUTE ON FUNCTION pg_buffercache_usage_counts() TO pg_monitor;

‎contrib/pg_buffercache/pg_buffercache_pages.c

Copy file name to clipboardExpand all lines: contrib/pg_buffercache/pg_buffercache_pages.c
+43Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,7 @@
1818
#define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8
1919
#define NUM_BUFFERCACHE_PAGES_ELEM 9
2020
#define NUM_BUFFERCACHE_SUMMARY_ELEM 5
21+
#define NUM_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+
int usage_counts[BM_MAX_USAGE_COUNT + 1] = {0};
315+
int dirty[BM_MAX_USAGE_COUNT + 1] = {0};
316+
int pinned[BM_MAX_USAGE_COUNT + 1] = {0};
317+
Datum values[NUM_BUFFERCACHE_USAGE_COUNTS_ELEM];
318+
bool nulls[NUM_BUFFERCACHE_USAGE_COUNTS_ELEM] = {0};
319+
320+
InitMaterializedSRF(fcinfo, 0);
321+
322+
for (int i = 0; i < NBuffers; i++)
323+
{
324+
BufferDesc *bufHdr = GetBufferDescriptor(i);
325+
uint32 buf_state = pg_atomic_read_u32(&bufHdr->state);
326+
int usage_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 (int i = 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

Copy file name to clipboardExpand all lines: contrib/pg_buffercache/sql/pg_buffercache.sql
+4Lines 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+
SELECT count(*) > 0 FROM 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 (wrong int);
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
SELECT count(*) > 0 FROM pg_buffercache;
2427
SELECT buffers_used + buffers_unused > 0 FROM pg_buffercache_summary();
28+
SELECT count(*) > 0 FROM pg_buffercache_usage_counts();

‎doc/src/sgml/pgbuffercache.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/pgbuffercache.sgml
+101-4Lines 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-
Average usagecount of used shared buffers
253+
Average usage 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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.