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

Commit2589434

Browse files
committed
pg_buffercache: Add pg_buffercache_summary()
Using pg_buffercache_summary() is significantly cheaper than queryingpg_buffercache and summarizing in SQL.Author: Melih Mutlu <m.melihmutlu@gmail.com>Reviewed-by: Andres Freund <andres@anarazel.de>Reviewed-by: Aleksander Alekseev <aleksander@timescale.com>Reviewed-by: Zhang Mingli <zmlpostgres@gmail.com>Discussion:https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com
1 parent7f8d9ce commit2589434

File tree

8 files changed

+250
-5
lines changed

8 files changed

+250
-5
lines changed

‎contrib/pg_buffercache/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,8 @@ OBJS = \
77

88
EXTENSION = pg_buffercache
99
DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql\
10-
pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql
10+
pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql\
11+
pg_buffercache--1.3--1.4.sql
1112
PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time"
1213

1314
REGRESS = pg_buffercache

‎contrib/pg_buffercache/expected/pg_buffercache.out

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,3 +8,36 @@ from pg_buffercache;
88
t
99
(1 row)
1010

11+
select buffers_used + buffers_unused > 0,
12+
buffers_dirty <= buffers_used,
13+
buffers_pinned <= buffers_used
14+
from pg_buffercache_summary();
15+
?column? | ?column? | ?column?
16+
----------+----------+----------
17+
t | t | t
18+
(1 row)
19+
20+
-- Check that the functions / views can't be accessed by default. To avoid
21+
-- having to create a dedicated user, use the pg_database_owner pseudo-role.
22+
SET ROLE pg_database_owner;
23+
SELECT * FROM pg_buffercache;
24+
ERROR: permission denied for view pg_buffercache
25+
SELECT * FROM pg_buffercache_pages() AS p (wrong int);
26+
ERROR: permission denied for function pg_buffercache_pages
27+
SELECT * FROM pg_buffercache_summary();
28+
ERROR: permission denied for function pg_buffercache_summary
29+
RESET role;
30+
-- Check that pg_monitor is allowed to query view / function
31+
SET ROLE pg_monitor;
32+
SELECT count(*) > 0 FROM pg_buffercache;
33+
?column?
34+
----------
35+
t
36+
(1 row)
37+
38+
SELECT buffers_used + buffers_unused > 0 FROM pg_buffercache_summary();
39+
?column?
40+
----------
41+
t
42+
(1 row)
43+

‎contrib/pg_buffercache/meson.build

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@ install_data(
1919
'pg_buffercache--1.1--1.2.sql',
2020
'pg_buffercache--1.2--1.3.sql',
2121
'pg_buffercache--1.2.sql',
22+
'pg_buffercache--1.3--1.4.sql',
2223
'pg_buffercache.control',
2324
kwargs: contrib_data_args,
2425
)
Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
/* contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION pg_buffercache UPDATE TO '1.4'" to load this file. \quit
5+
6+
CREATEFUNCTIONpg_buffercache_summary(
7+
OUT buffers_used int4,
8+
OUT buffers_unused int4,
9+
OUT buffers_dirty int4,
10+
OUT buffers_pinned int4,
11+
OUT usagecount_avg float8)
12+
AS'MODULE_PATHNAME','pg_buffercache_summary'
13+
LANGUAGE C PARALLEL SAFE;
14+
15+
-- Don't want these to be available to public.
16+
REVOKE ALLON FUNCTION pg_buffercache_summary()FROM PUBLIC;
17+
GRANT EXECUTEON FUNCTION pg_buffercache_summary() TO pg_monitor;
Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pg_buffercache extension
22
comment = 'examine the shared buffer cache'
3-
default_version = '1.3'
3+
default_version = '1.4'
44
module_pathname = '$libdir/pg_buffercache'
55
relocatable = true

‎contrib/pg_buffercache/pg_buffercache_pages.c

Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,7 @@
1717

1818
#defineNUM_BUFFERCACHE_PAGES_MIN_ELEM8
1919
#defineNUM_BUFFERCACHE_PAGES_ELEM9
20+
#defineNUM_BUFFERCACHE_SUMMARY_ELEM 5
2021

2122
PG_MODULE_MAGIC;
2223

@@ -59,6 +60,7 @@ typedef struct
5960
* relation node/tablespace/database/blocknum and dirty indicator.
6061
*/
6162
PG_FUNCTION_INFO_V1(pg_buffercache_pages);
63+
PG_FUNCTION_INFO_V1(pg_buffercache_summary);
6264

6365
Datum
6466
pg_buffercache_pages(PG_FUNCTION_ARGS)
@@ -237,3 +239,68 @@ pg_buffercache_pages(PG_FUNCTION_ARGS)
237239
else
238240
SRF_RETURN_DONE(funcctx);
239241
}
242+
243+
Datum
244+
pg_buffercache_summary(PG_FUNCTION_ARGS)
245+
{
246+
Datumresult;
247+
TupleDesctupledesc;
248+
HeapTupletuple;
249+
Datumvalues[NUM_BUFFERCACHE_SUMMARY_ELEM];
250+
boolnulls[NUM_BUFFERCACHE_SUMMARY_ELEM];
251+
252+
int32buffers_used=0;
253+
int32buffers_unused=0;
254+
int32buffers_dirty=0;
255+
int32buffers_pinned=0;
256+
int64usagecount_total=0;
257+
258+
if (get_call_result_type(fcinfo,NULL,&tupledesc)!=TYPEFUNC_COMPOSITE)
259+
elog(ERROR,"return type must be a row type");
260+
261+
for (inti=0;i<NBuffers;i++)
262+
{
263+
BufferDesc*bufHdr;
264+
uint32buf_state;
265+
266+
/*
267+
* This function summarizes the state of all headers. Locking the
268+
* buffer headers wouldn't provide an improved result as the state of
269+
* the buffer can still change after we release the lock and it'd
270+
* noticeably increase the cost of the function.
271+
*/
272+
bufHdr=GetBufferDescriptor(i);
273+
buf_state=pg_atomic_read_u32(&bufHdr->state);
274+
275+
if (buf_state&BM_VALID)
276+
{
277+
buffers_used++;
278+
usagecount_total+=BUF_STATE_GET_USAGECOUNT(buf_state);
279+
280+
if (buf_state&BM_DIRTY)
281+
buffers_dirty++;
282+
}
283+
else
284+
buffers_unused++;
285+
286+
if (BUF_STATE_GET_REFCOUNT(buf_state)>0)
287+
buffers_pinned++;
288+
}
289+
290+
memset(nulls,0,sizeof(nulls));
291+
values[0]=Int32GetDatum(buffers_used);
292+
values[1]=Int32GetDatum(buffers_unused);
293+
values[2]=Int32GetDatum(buffers_dirty);
294+
values[3]=Int32GetDatum(buffers_pinned);
295+
296+
if (buffers_used!=0)
297+
values[4]=Float8GetDatum((double)usagecount_total /buffers_used);
298+
else
299+
nulls[4]= true;
300+
301+
/* Build and return the tuple. */
302+
tuple=heap_form_tuple(tupledesc,values,nulls);
303+
result=HeapTupleGetDatum(tuple);
304+
305+
PG_RETURN_DATUM(result);
306+
}

‎contrib/pg_buffercache/sql/pg_buffercache.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4,3 +4,21 @@ select count(*) = (select setting::bigint
44
from pg_settings
55
where name='shared_buffers')
66
from pg_buffercache;
7+
8+
select buffers_used+ buffers_unused>0,
9+
buffers_dirty<= buffers_used,
10+
buffers_pinned<= buffers_used
11+
from pg_buffercache_summary();
12+
13+
-- Check that the functions / views can't be accessed by default. To avoid
14+
-- having to create a dedicated user, use the pg_database_owner pseudo-role.
15+
SET ROLE pg_database_owner;
16+
SELECT*FROM pg_buffercache;
17+
SELECT*FROM pg_buffercache_pages()AS p (wrongint);
18+
SELECT*FROM pg_buffercache_summary();
19+
RESET role;
20+
21+
-- Check that pg_monitor is allowed to query view / function
22+
SET ROLE pg_monitor;
23+
SELECTcount(*)>0FROM pg_buffercache;
24+
SELECT buffers_used+ buffers_unused>0FROM pg_buffercache_summary();

‎doc/src/sgml/pgbuffercache.sgml

Lines changed: 111 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -16,13 +16,28 @@
1616
<primary>pg_buffercache_pages</primary>
1717
</indexterm>
1818

19+
<indexterm>
20+
<primary>pg_buffercache_summary</primary>
21+
</indexterm>
22+
23+
<para>
24+
The module provides the <function>pg_buffercache_pages()</function>
25+
function, wrapped in the <structname>pg_buffercache</structname> view, and
26+
the <function>pg_buffercache_summary()</function> function.
27+
</para>
28+
1929
<para>
20-
Themodule provides a C function<function>pg_buffercache_pages</function>
21-
that returns a setofrecords, plus a view
22-
<structname>pg_buffercache</structname>that wraps the function for
30+
The <function>pg_buffercache_pages()</function> function returns a set of
31+
records, each row describing the stateofone shared buffer entry. The
32+
<structname>pg_buffercache</structname>view wraps the function for
2333
convenient use.
2434
</para>
2535

36+
<para>
37+
The <function>pg_buffercache_summary()</function> function returns a single
38+
row summarizing the state of the shared buffer cache.
39+
</para>
40+
2641
<para>
2742
By default, use is restricted to superusers and roles with privileges of the
2843
<literal>pg_monitor</literal> role. Access may be granted to others
@@ -164,6 +179,92 @@
164179
</para>
165180
</sect2>
166181

182+
<sect2>
183+
<title>The <function>pg_buffercache_summary()</function> Function</title>
184+
185+
<para>
186+
The definitions of the columns exposed by the function are shown in <xref linkend="pgbuffercache_summary-columns"/>.
187+
</para>
188+
189+
<table id="pgbuffercache_summary-columns">
190+
<title><function>pg_buffercache_summary()</function> Output Columns</title>
191+
<tgroup cols="1">
192+
<thead>
193+
<row>
194+
<entry role="catalog_table_entry"><para role="column_definition">
195+
Column Type
196+
</para>
197+
<para>
198+
Description
199+
</para></entry>
200+
</row>
201+
</thead>
202+
203+
<tbody>
204+
<row>
205+
<entry role="catalog_table_entry"><para role="column_definition">
206+
<structfield>buffers_used</structfield> <type>int4</type>
207+
</para>
208+
<para>
209+
Number of unused shared buffers
210+
</para></entry>
211+
</row>
212+
213+
<row>
214+
<entry role="catalog_table_entry"><para role="column_definition">
215+
<structfield>buffers_unused</structfield> <type>int4</type>
216+
</para>
217+
<para>
218+
Number of unused shared buffers
219+
</para></entry>
220+
</row>
221+
222+
<row>
223+
<entry role="catalog_table_entry"><para role="column_definition">
224+
<structfield>buffers_dirty</structfield> <type>int4</type>
225+
</para>
226+
<para>
227+
Number of dirty shared buffers
228+
</para></entry>
229+
</row>
230+
231+
<row>
232+
<entry role="catalog_table_entry"><para role="column_definition">
233+
<structfield>buffers_pinned</structfield> <type>int4</type>
234+
</para>
235+
<para>
236+
Number of pinned shared buffers
237+
</para></entry>
238+
</row>
239+
240+
<row>
241+
<entry role="catalog_table_entry"><para role="column_definition">
242+
<structfield>usagecount_avg</structfield> <type>float8</type>
243+
</para>
244+
<para>
245+
Average usagecount of used shared buffers
246+
</para></entry>
247+
</row>
248+
</tbody>
249+
</tgroup>
250+
</table>
251+
252+
<para>
253+
The <function>pg_buffercache_summary()</function> function returns a
254+
single row summarizing the state of all shared buffers. Similar and more
255+
detailed information is provided by the
256+
<structname>pg_buffercache</structname> view, but
257+
<function>pg_buffercache_summary()</function> is significantly cheaper.
258+
</para>
259+
260+
<para>
261+
Like the <structname>pg_buffercache</structname> view,
262+
<function>pg_buffercache_summary()</function> does not acquire buffer
263+
manager locks. Therefore concurrent activity can lead to minor inaccuracies
264+
in the result.
265+
</para>
266+
</sect2>
267+
167268
<sect2>
168269
<title>Sample Output</title>
169270

@@ -191,6 +292,13 @@ regression=# SELECT n.nspname, c.relname, count(*) AS buffers
191292
public | gin_test_tbl | 188
192293
public | spgist_text_tbl | 182
193294
(10 rows)
295+
296+
297+
regression=# SELECT * FROM pg_buffercache_summary();
298+
buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
299+
--------------+----------------+---------------+----------------+----------------
300+
248 | 2096904 | 39 | 0 | 3.141129
301+
(1 row)
194302
</screen>
195303
</sect2>
196304

0 commit comments

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