From 4b203d499c610160e9867e6add2366780429344c Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Mon, 24 Nov 2025 14:29:15 +0900 Subject: pg_buffercache: Add pg_buffercache_os_pages ba2a3c2302f has added a way to check if a buffer is spread across multiple pages with some NUMA information, via a new view pg_buffercache_numa that depends on pg_buffercache_numa_pages(), a SQL function. These can only be queried when support for libnuma exists, generating an error if not. However, it can be useful to know how shared buffers and OS pages map when NUMA is not supported or not available. This commit expands the capabilities around pg_buffercache_numa: - pg_buffercache_numa_pages() is refactored as an internal function, able to optionally process NUMA. Its SQL definition prior to this commit is still around to ensure backward-compatibility with v1.6. - A SQL function called pg_buffercache_os_pages() is added, able to work with or without NUMA. - The view pg_buffercache_numa is redefined to use pg_buffercache_os_pages(). - A new view is added, called pg_buffercache_os_pages. This ignores NUMA for its result processing, for a better efficiency. The implementation is done so as there is no code duplication between the NUMA and non-NUMA views/functions, relying on one internal function that does the job for all of them. The module is bumped to v1.7. Author: Bertrand Drouvot Reviewed-by: Mircea Cadariu Reviewed-by: Michael Paquier Discussion: https://postgr.es/m/Z/fFA2heH6lpSLlt@ip-10-97-1-34.eu-west-3.compute.internal --- doc/src/sgml/pgbuffercache.sgml | 108 +++++++++++++++++++++++++++++++++++++--- 1 file changed, 100 insertions(+), 8 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index 99ad2e68785..91bbedff343 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -46,8 +46,9 @@ This module provides the pg_buffercache_pages() function (wrapped in the pg_buffercache view), the - pg_buffercache_numa_pages() function (wrapped in the - pg_buffercache_numa view), the + pg_buffercache_os_pages() function (wrapped in the + pg_buffercache_os_pages and + pg_buffercache_numa views), the pg_buffercache_summary() function, the pg_buffercache_usage_counts() function, the pg_buffercache_evict() function, the @@ -63,12 +64,16 @@ - The pg_buffercache_numa_pages() function provides - NUMA node mappings for shared buffer entries. This - information is not part of pg_buffercache_pages() - itself, as it is much slower to retrieve. - The pg_buffercache_numa view wraps the function for - convenient use. + The pg_buffercache_os_pages() function provides OS + pages mappings for shared buffer entries. When its argument is + true, it also provides NUMA node + mappings for shared buffer entries (this information is not part of + pg_buffercache_pages() itself, as it is much + slower to retrieve). + The pg_buffercache_os_pages and + pg_buffercache_numa views wrap the function for + convenient use, with its argument set to false and + true respectively. @@ -242,6 +247,53 @@ + + The <structname>pg_buffercache_os_pages</structname> View + + + The definitions of the columns exposed by the view are shown in + . + + + + <structname>pg_buffercache_os_pages</structname> Columns + + + + + Column Type + + + Description + + + + + + + + bufferid integer + + + ID, in the range 1..shared_buffers + + + + + + os_page_num bigint + + + Number of OS memory page for this buffer + + + + + +
+ + + The <structname>pg_buffercache_numa</structname> View @@ -558,6 +610,46 @@ regression=# SELECT n.nspname, c.relname, count(*) AS buffers public | spgist_text_tbl | 182 (10 rows) +regression=# SELECT pages_per_buffer, COUNT(*) as buffer_count + FROM ( + SELECT bufferid, COUNT(*) as pages_per_buffer + FROM pg_buffercache_os_pages + GROUP BY bufferid + ) + GROUP BY pages_per_buffer + ORDER BY pages_per_buffer; + + pages_per_buffer | buffer_count +------------------+-------------- + 1 | 261120 + 2 | 1024 +(2 rows) + +regression=# SELECT n.nspname, c.relname, count(*) AS buffers_on_multiple_pages + FROM pg_buffercache b JOIN pg_class c + ON b.relfilenode = pg_relation_filenode(c.oid) AND + b.reldatabase IN (0, (SELECT oid FROM pg_database + WHERE datname = current_database())) + JOIN pg_namespace n ON n.oid = c.relnamespace + JOIN (SELECT bufferid FROM pg_buffercache_os_pages + GROUP BY bufferid HAVING count(*) > 1) m on m.bufferid = b.bufferid + GROUP BY n.nspname, c.relname + ORDER BY 3 DESC + LIMIT 10; + + nspname | relname | buffers_on_multiple_pages +------------+------------------------------+--------------------------- + public | delete_test_table | 3 + public | gin_test_idx | 2 + pg_catalog | pg_depend | 2 + public | quad_poly_tbl | 2 + pg_catalog | pg_depend_reference_index | 1 + pg_catalog | pg_index_indexrelid_index | 1 + pg_catalog | pg_constraint_contypid_index | 1 + pg_catalog | pg_statistic | 1 + pg_catalog | pg_depend_depender_index | 1 + pg_catalog | pg_operator | 1 +(10 rows) regression=# SELECT * FROM pg_buffercache_summary(); buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg -- cgit v1.2.3