public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v2] explain: show JIT details in non-text format, even if zero 18+ messages / 6 participants [nested] [flat]
* [PATCH v2] explain: show JIT details in non-text format, even if zero @ 2020-10-17 19:10 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 18+ messages in thread From: Justin Pryzby @ 2020-10-17 19:10 UTC (permalink / raw) --- src/backend/commands/explain.c | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 43f9b01e83..89caa76801 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -838,8 +838,9 @@ ExplainPrintJIT(ExplainState *es, int jit_flags, JitInstrumentation *ji) { instr_time total_time; - /* don't print information if no JITing happened */ - if (!ji || ji->created_functions == 0) + /* don't print information if JITing wasn't done at planning time */ + if (!ji || (ji->created_functions == 0 && + es->format == EXPLAIN_FORMAT_TEXT)) return; /* calculate total time */ -- 2.17.0 --9jxsPFA5p3P2qPhR-- ^ permalink raw reply [nested|flat] 18+ messages in thread
* [PATCH v1] explain: show JIT details in non-text format, even if zero @ 2020-10-17 19:10 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 18+ messages in thread From: Justin Pryzby @ 2020-10-17 19:10 UTC (permalink / raw) --- src/backend/commands/explain.c | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 41317f1837..7345971507 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -839,7 +839,8 @@ ExplainPrintJIT(ExplainState *es, int jit_flags, JitInstrumentation *ji) instr_time total_time; /* don't print information if no JITing happened */ - if (!ji || ji->created_functions == 0) + if (!ji || (ji->created_functions == 0 && + es->format == EXPLAIN_FORMAT_TEXT)) return; /* calculate total time */ -- 2.17.0 --veXX9dWIonWZEC6h-- ^ permalink raw reply [nested|flat] 18+ messages in thread
* Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2024-12-25 12:57 Nazir Bilal Yavuz <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Nazir Bilal Yavuz @ 2024-12-25 12:57 UTC (permalink / raw) To: PostgreSQL Hackers <[email protected]>; +Cc: Andres Freund <[email protected]> Hi, Andres off-list mentioned that: 1- It is time consuming to evict all shared buffers one by one using the pg_buffercache_evict() function. 2- It would be good to have a function to mark buffers as dirty to test different scenarios. So, this patchset extends pg_buffercache with 3 functions: 1- pg_buffercache_evict_all(): This is very similar to the already existing pg_buffercache_evict() function. The difference is pg_buffercache_evict_all() does not take an argument. Instead it just loops over the shared buffers and tries to evict all of them. It returns the number of buffers evicted and flushed. 2- pg_buffercache_mark_dirty(): This function takes a buffer id as an argument and tries to mark this buffer as dirty. Returns true on success. This returns false if the buffer is already dirty. Do you think this makes sense or do you prefer it to return true if the buffer is already dirty? 3- pg_buffercache_mark_dirty_all(): This is very similar to the pg_buffercache_mark_dirty() function. The difference is pg_buffercache_mark_dirty_all() does not take an argument. Instead it just loops over the shared buffers and tries to mark all of them as dirty. It returns the number of buffers marked as dirty. I tested these functions with 16GB shared buffers. pg_buffercache_evict() -> 790ms pg_buffercache_evict_all() -> 270ms pg_buffercache_mark_dirty() -> 550ms pg_buffercache_mark_dirty_all() -> 70ms Any feedback would be appreciated. -- Regards, Nazir Bilal Yavuz Microsoft Attachments: [text/x-patch] v1-0001-Add-pg_buffercache_evict_all-function-for-testing.patch (8.7K, ../../CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw@mail.gmail.com/2-v1-0001-Add-pg_buffercache_evict_all-function-for-testing.patch) download | inline diff: From 813e5ec0da4c65970b4b1ce2ec2918e4652da9ab Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Fri, 20 Dec 2024 14:06:47 +0300 Subject: [PATCH v1 1/2] Add pg_buffercache_evict_all() function for testing This new function provides a mechanism to evict all shared buffers at once. It is designed to address the inefficiency of repeatedly calling pg_buffercache_evict() for each individual buffer, which can be time-consuming when dealing with large shared buffer pools (e.g., ~790ms vs. ~270ms for 16GB of shared buffers). It is intended for developer testing and debugging purposes and is available to superusers only. --- src/include/storage/bufmgr.h | 2 +- src/backend/storage/buffer/bufmgr.c | 5 +- doc/src/sgml/pgbuffercache.sgml | 22 ++++++++- contrib/pg_buffercache/Makefile | 3 +- contrib/pg_buffercache/meson.build | 1 + .../pg_buffercache--1.5--1.6.sql | 7 +++ contrib/pg_buffercache/pg_buffercache.control | 2 +- contrib/pg_buffercache/pg_buffercache_pages.c | 47 ++++++++++++++++++- 8 files changed, 82 insertions(+), 7 deletions(-) create mode 100644 contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index eb0fba4230b..7f4eeca4afd 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -297,7 +297,7 @@ extern bool BgBufferSync(struct WritebackContext *wb_context); extern void LimitAdditionalPins(uint32 *additional_pins); extern void LimitAdditionalLocalPins(uint32 *additional_pins); -extern bool EvictUnpinnedBuffer(Buffer buf); +extern bool EvictUnpinnedBuffer(Buffer buf, bool *flushed); /* in buf_init.c */ extern void BufferManagerShmemInit(void); diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 2622221809c..d2a93cf7cc0 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -6098,12 +6098,14 @@ ResOwnerPrintBufferPin(Datum res) * or if the buffer becomes dirty again while we're trying to write it out. */ bool -EvictUnpinnedBuffer(Buffer buf) +EvictUnpinnedBuffer(Buffer buf, bool *flushed) { BufferDesc *desc; uint32 buf_state; bool result; + *flushed = false; + /* Make sure we can pin the buffer. */ ResourceOwnerEnlarge(CurrentResourceOwner); ReservePrivateRefCountEntry(); @@ -6134,6 +6136,7 @@ EvictUnpinnedBuffer(Buffer buf) LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_SHARED); FlushBuffer(desc, NULL, IOOBJECT_RELATION, IOCONTEXT_NORMAL); LWLockRelease(BufferDescriptorGetContentLock(desc)); + *flushed = true; } /* This will return false if it becomes dirty or someone else pins it. */ diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index 4b90eefc0b0..df4d90a650a 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -31,8 +31,9 @@ This module provides the <function>pg_buffercache_pages()</function> function (wrapped in the <structname>pg_buffercache</structname> view), the <function>pg_buffercache_summary()</function> function, the - <function>pg_buffercache_usage_counts()</function> function and - the <function>pg_buffercache_evict()</function> function. + <function>pg_buffercache_usage_counts()</function> function, the + <function>pg_buffercache_evict()</function> and the + <function>pg_buffercache_evict_all()</function> function. </para> <para> @@ -65,6 +66,12 @@ function is restricted to superusers only. </para> + <para> + The <function>pg_buffercache_evict_all()</function> function tries to evict + all buffers in the buffer pool. It returns how many buffers are + evicted and flushed. Use of this function is restricted to superusers only. + </para> + <sect2 id="pgbuffercache-pg-buffercache"> <title>The <structname>pg_buffercache</structname> View</title> @@ -378,6 +385,17 @@ </para> </sect2> + <sect2 id="pgbuffercache-pg-buffercache-evict-all"> + <title>The <structname>pg_buffercache_evict_all</structname> Function</title> + <para> + The <function>pg_buffercache_evict_all()</function> function is very similar + to <function>pg_buffercache_evict()</function> function. The difference is, + the <function>pg_buffercache_evict_all()</function> does not take argument; + instead it tries to evict all buffers in the buffer pool. The function is + intended for developer testing only. + </para> + </sect2> + <sect2 id="pgbuffercache-sample-output"> <title>Sample Output</title> diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile index eae65ead9e5..2a33602537e 100644 --- a/contrib/pg_buffercache/Makefile +++ b/contrib/pg_buffercache/Makefile @@ -8,7 +8,8 @@ OBJS = \ EXTENSION = pg_buffercache DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \ pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \ - pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql + pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql \ + pg_buffercache--1.5--1.6.sql PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time" REGRESS = pg_buffercache diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build index 1ca3452918b..1ff57aa22e9 100644 --- a/contrib/pg_buffercache/meson.build +++ b/contrib/pg_buffercache/meson.build @@ -23,6 +23,7 @@ install_data( 'pg_buffercache--1.2.sql', 'pg_buffercache--1.3--1.4.sql', 'pg_buffercache--1.4--1.5.sql', + 'pg_buffercache--1.5--1.6.sql', 'pg_buffercache.control', kwargs: contrib_data_args, ) diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql new file mode 100644 index 00000000000..c08b799be94 --- /dev/null +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -0,0 +1,7 @@ +\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.6'" to load this file. \quit + +CREATE FUNCTION pg_buffercache_evict_all( + OUT buffers_evicted int4, + OUT buffers_flushed int4) +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all' +LANGUAGE C PARALLEL SAFE VOLATILE; diff --git a/contrib/pg_buffercache/pg_buffercache.control b/contrib/pg_buffercache/pg_buffercache.control index 5ee875f77dd..b030ba3a6fa 100644 --- a/contrib/pg_buffercache/pg_buffercache.control +++ b/contrib/pg_buffercache/pg_buffercache.control @@ -1,5 +1,5 @@ # pg_buffercache extension comment = 'examine the shared buffer cache' -default_version = '1.5' +default_version = '1.6' module_pathname = '$libdir/pg_buffercache' relocatable = true diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index 3ae0a018e10..ea7c0e6934c 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -19,6 +19,7 @@ #define NUM_BUFFERCACHE_PAGES_ELEM 9 #define NUM_BUFFERCACHE_SUMMARY_ELEM 5 #define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4 +#define NUM_BUFFERCACHE_EVICT_ALL_ELEM 2 PG_MODULE_MAGIC; @@ -64,6 +65,7 @@ PG_FUNCTION_INFO_V1(pg_buffercache_pages); PG_FUNCTION_INFO_V1(pg_buffercache_summary); PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts); PG_FUNCTION_INFO_V1(pg_buffercache_evict); +PG_FUNCTION_INFO_V1(pg_buffercache_evict_all); Datum pg_buffercache_pages(PG_FUNCTION_ARGS) @@ -356,6 +358,7 @@ Datum pg_buffercache_evict(PG_FUNCTION_ARGS) { Buffer buf = PG_GETARG_INT32(0); + bool flushed; if (!superuser()) ereport(ERROR, @@ -365,5 +368,47 @@ pg_buffercache_evict(PG_FUNCTION_ARGS) if (buf < 1 || buf > NBuffers) elog(ERROR, "bad buffer ID: %d", buf); - PG_RETURN_BOOL(EvictUnpinnedBuffer(buf)); + PG_RETURN_BOOL(EvictUnpinnedBuffer(buf, &flushed)); +} + +/* + * Try to evict all shared buffers. + */ +Datum +pg_buffercache_evict_all(PG_FUNCTION_ARGS) +{ + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_ALL_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_ALL_ELEM] = {0}; + + int32 buffers_evicted = 0; + int32 buffers_flushed = 0; + bool flushed; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_evict_all function"))); + + for (int buf = 1; buf < NBuffers; buf++) + { + if (EvictUnpinnedBuffer(buf, &flushed)) + buffers_evicted++; + if (flushed) + buffers_flushed++; + } + + values[0] = Int32GetDatum(buffers_evicted); + values[1] = Int32GetDatum(buffers_flushed); + + /* Build and return the tuple. */ + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); } -- 2.45.2 [text/x-patch] v1-0002-Add-pg_buffercache_mark_dirty-_all-functions-for-.patch (8.7K, ../../CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw@mail.gmail.com/3-v1-0002-Add-pg_buffercache_mark_dirty-_all-functions-for-.patch) download | inline diff: From 19b0a56e72525ee1c9754fb06ab5f31d41e43ca7 Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Wed, 25 Dec 2024 15:46:10 +0300 Subject: [PATCH v1 2/2] Add pg_buffercache_mark_dirty[_all]() functions for testing This commit introduces two new functions for marking shared buffers as dirty: pg_buffercache_mark_dirty(): Marks a specific shared buffer as dirty. pg_buffercache_mark_dirty_all(): Marks all shared buffers as dirty in a single operation. The pg_buffercache_mark_dirty_all() function provides an efficient way to dirty the entire buffer pool (e.g., ~550ms vs. ~70ms for 16GB of shared buffers), complementing pg_buffercache_mark_dirty() for more granular control. These functions are intended for developer testing and debugging scenarios, enabling users to simulate various buffer pool states and test write-back behavior. Both functions are superuser-only. --- src/include/storage/bufmgr.h | 1 + src/backend/storage/buffer/bufmgr.c | 62 +++++++++++++++++++ doc/src/sgml/pgbuffercache.sgml | 40 +++++++++++- .../pg_buffercache--1.5--1.6.sql | 10 +++ contrib/pg_buffercache/pg_buffercache_pages.c | 43 +++++++++++++ 5 files changed, 154 insertions(+), 2 deletions(-) diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index 7f4eeca4afd..ac077402dd9 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -298,6 +298,7 @@ extern void LimitAdditionalPins(uint32 *additional_pins); extern void LimitAdditionalLocalPins(uint32 *additional_pins); extern bool EvictUnpinnedBuffer(Buffer buf, bool *flushed); +extern bool MarkUnpinnedBufferDirty(Buffer buf); /* in buf_init.c */ extern void BufferManagerShmemInit(void); diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index d2a93cf7cc0..7ab8bcaa228 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -6146,3 +6146,65 @@ EvictUnpinnedBuffer(Buffer buf, bool *flushed) return result; } + +/* + * MarkUnpinnedBufferDirty + * + * This function is intended for testing/development use only! + * + * To succeed, the buffer must not be pinned on entry, so if the caller had a + * particular block in mind, it might already have been replaced by some other + * block by the time this function runs. It's also unpinned on return, so the + * buffer might be occupied and flushed by the time control is returned. This + * inherent raciness without other interlocking makes the function unsuitable + * for non-testing usage. + * + * Returns true if the buffer was not dirty and it has now been marked as + * dirty. Returns false if it wasn't valid, if it couldn't be marked as dirty + * due to a pin, or if the buffer was already dirty. + */ +bool +MarkUnpinnedBufferDirty(Buffer buf) +{ + BufferDesc *desc; + uint32 buf_state; + bool result = false; + + Assert(!BufferIsLocal(buf)); + + /* Make sure we can pin the buffer. */ + ResourceOwnerEnlarge(CurrentResourceOwner); + ReservePrivateRefCountEntry(); + + desc = GetBufferDescriptor(buf - 1); + + /* Lock the header and check if it's valid. */ + buf_state = LockBufHdr(desc); + if ((buf_state & BM_VALID) == 0) + { + UnlockBufHdr(desc, buf_state); + return false; + } + + /* Check that it's not pinned already. */ + if (BUF_STATE_GET_REFCOUNT(buf_state) > 0) + { + UnlockBufHdr(desc, buf_state); + return false; + } + + PinBuffer_Locked(desc); /* releases spinlock */ + + /* If it was not already dirty, mark it as dirty. */ + if (!(buf_state & BM_DIRTY)) + { + LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_EXCLUSIVE); + MarkBufferDirty(buf); + LWLockRelease(BufferDescriptorGetContentLock(desc)); + result = true; + } + + UnpinBuffer(desc); + + return result; +} diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index df4d90a650a..a1c5036a383 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -32,8 +32,10 @@ function (wrapped in the <structname>pg_buffercache</structname> view), the <function>pg_buffercache_summary()</function> function, the <function>pg_buffercache_usage_counts()</function> function, the - <function>pg_buffercache_evict()</function> and the - <function>pg_buffercache_evict_all()</function> function. + <function>pg_buffercache_evict()</function> the + <function>pg_buffercache_evict_all()</function>, the + <function>pg_buffercache_mark_dirty()</function> and the + <function>pg_buffercache_mark_dirty_all()</function> function. </para> <para> @@ -72,6 +74,18 @@ evicted and flushed. Use of this function is restricted to superusers only. </para> + <para> + The <function>pg_buffercache_mark_dirty()</function> function allows a block + to be marked as dirty from the buffer pool given a buffer identifier. Use of + this function is restricted to superusers only. + </para> + + <para> + The <function>pg_buffercache_mark_dirty_all()</function> function tries to + mark all buffers dirty in the buffer pool. It returns how many buffers are + dirtied. Use of this function is restricted to superusers only. + </para> + <sect2 id="pgbuffercache-pg-buffercache"> <title>The <structname>pg_buffercache</structname> View</title> @@ -396,6 +410,28 @@ </para> </sect2> + <sect2 id="pgbuffercache-pg-buffercache-mark-dirty"> + <title>The <structname>pg_buffercache_mark_dirty</structname> Function</title> + <para> + The <function>pg_buffercache_mark_dirty()</function> function takes a buffer + identifier, as shown in the <structfield>bufferid</structfield> column of + the <structname>pg_buffercache</structname> view. It returns true on success, + and false if the buffer wasn't valid or if it couldn't be marked as dirty + because it was pinned. The function is intended for developer testing only. + </para> + </sect2> + + <sect2 id="pgbuffercache-pg-buffercache-mark-dirty-all"> + <title>The <structname>pg_buffercache_mark_dirty_all</structname> Function</title> + <para> + The <function>pg_buffercache_mark_dirty_all()</function> function is very similar + to <function>pg_buffercache_mark_dirty()</function> function. The difference is, + the <function>pg_buffercache_mark_dirty_all()</function> does not take argument; + instead it tries to mark all buffers dirty in the buffer pool. The function is + intended for developer testing only. + </para> + </sect2> + <sect2 id="pgbuffercache-sample-output"> <title>Sample Output</title> diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql index c08b799be94..e725fef1247 100644 --- a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -5,3 +5,13 @@ CREATE FUNCTION pg_buffercache_evict_all( OUT buffers_flushed int4) AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all' LANGUAGE C PARALLEL SAFE VOLATILE; + +CREATE FUNCTION pg_buffercache_mark_dirty(IN int) +RETURNS bool +AS 'MODULE_PATHNAME', 'pg_buffercache_mark_dirty' +LANGUAGE C PARALLEL SAFE VOLATILE STRICT; + +CREATE FUNCTION pg_buffercache_mark_dirty_all() +RETURNS INT4 +AS 'MODULE_PATHNAME', 'pg_buffercache_mark_dirty_all' +LANGUAGE C PARALLEL SAFE VOLATILE; diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index ea7c0e6934c..1a0992467b2 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -66,6 +66,8 @@ PG_FUNCTION_INFO_V1(pg_buffercache_summary); PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts); PG_FUNCTION_INFO_V1(pg_buffercache_evict); PG_FUNCTION_INFO_V1(pg_buffercache_evict_all); +PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty); +PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty_all); Datum pg_buffercache_pages(PG_FUNCTION_ARGS) @@ -412,3 +414,44 @@ pg_buffercache_evict_all(PG_FUNCTION_ARGS) PG_RETURN_DATUM(result); } + +/* + * Try to mark dirty a shared buffer. + */ +Datum +pg_buffercache_mark_dirty(PG_FUNCTION_ARGS) +{ + Buffer buf = PG_GETARG_INT32(0); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_mark_dirty function"))); + + if (buf < 1 || buf > NBuffers) + elog(ERROR, "bad buffer ID: %d", buf); + + PG_RETURN_BOOL(MarkUnpinnedBufferDirty(buf)); +} + +/* + * Try to mark dirty all shared buffers. + */ +Datum +pg_buffercache_mark_dirty_all(PG_FUNCTION_ARGS) +{ + int32 buffers_dirtied = 0; + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_mark_dirty_all function"))); + + for (int buf = 1; buf < NBuffers; buf++) + { + if (MarkUnpinnedBufferDirty(buf)) + buffers_dirtied++; + } + + PG_RETURN_INT32(buffers_dirtied); +} -- 2.45.2 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-02-17 16:59 Andres Freund <[email protected]> parent: Nazir Bilal Yavuz <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Andres Freund @ 2025-02-17 16:59 UTC (permalink / raw) To: Nazir Bilal Yavuz <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> Hi, On 2024-12-25 15:57:34 +0300, Nazir Bilal Yavuz wrote: > 1- It is time consuming to evict all shared buffers one by one using > the pg_buffercache_evict() function. This is really useful for benchmarking. When testing IO heavy workloads it turns out to be a lot lower noise to measure with shared buffers already "touched" before taking actual measurements. The first time a buffer is used the kernel needs to actually initialize the memory for the buffer, which can take a substantial amount of time. Which obviously can hide many performance differences. And it adds a lot of noise. > 2- It would be good to have a function to mark buffers as dirty to > test different scenarios. This is important to be able to measure checkpointer performance. Otherwise one has to load data from scratch. That's bad for three reasons: 1) After re-loading the data from scratch the data can be laid-out differently on-disk, which can have substantial performance impacts. By re-dirtying data one instead can measure the performance effects of a change with the same on-disk layaout. 2) It takes a lot of time to reload data from scratch. 3) The first write to data has substantially different performance characteristics than later writes, but often isn't the most relevant factor for real-world performance test. > So, this patchset extends pg_buffercache with 3 functions: > > 1- pg_buffercache_evict_all(): This is very similar to the already > existing pg_buffercache_evict() function. The difference is > pg_buffercache_evict_all() does not take an argument. Instead it just > loops over the shared buffers and tries to evict all of them. It > returns the number of buffers evicted and flushed. I do think that'd be rather useful. Perhaps it's also worth having a version that just evicts a specific relation? > 2- pg_buffercache_mark_dirty(): This function takes a buffer id as an > argument and tries to mark this buffer as dirty. Returns true on > success. This returns false if the buffer is already dirty. Do you > think this makes sense or do you prefer it to return true if the > buffer is already dirty? I don't really have feelings about that ;) > From 813e5ec0da4c65970b4b1ce2ec2918e4652da9ab Mon Sep 17 00:00:00 2001 > From: Nazir Bilal Yavuz <[email protected]> > Date: Fri, 20 Dec 2024 14:06:47 +0300 > Subject: [PATCH v1 1/2] Add pg_buffercache_evict_all() function for testing > > This new function provides a mechanism to evict all shared buffers at > once. It is designed to address the inefficiency of repeatedly calling > pg_buffercache_evict() for each individual buffer, which can be > time-consuming when dealing with large shared buffer pools > (e.g., ~790ms vs. ~270ms for 16GB of shared buffers). > */ > bool > -EvictUnpinnedBuffer(Buffer buf) > +EvictUnpinnedBuffer(Buffer buf, bool *flushed) > { > BufferDesc *desc; > uint32 buf_state; > bool result; > > + *flushed = false; > + > /* Make sure we can pin the buffer. */ > ResourceOwnerEnlarge(CurrentResourceOwner); > ReservePrivateRefCountEntry(); > @@ -6134,6 +6136,7 @@ EvictUnpinnedBuffer(Buffer buf) > LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_SHARED); > FlushBuffer(desc, NULL, IOOBJECT_RELATION, IOCONTEXT_NORMAL); > LWLockRelease(BufferDescriptorGetContentLock(desc)); > + *flushed = true; > } > > /* This will return false if it becomes dirty or someone else pins > it. */ I don't think *flushed is necessarily accurate this way, as it might detect that it doesn't need to flush the data (via StartBufferIO() returning false). > + */ > +Datum > +pg_buffercache_evict_all(PG_FUNCTION_ARGS) > +{ > + Datum result; > + TupleDesc tupledesc; > + HeapTuple tuple; > + Datum values[NUM_BUFFERCACHE_EVICT_ALL_ELEM]; > + bool nulls[NUM_BUFFERCACHE_EVICT_ALL_ELEM] = {0}; > + > + int32 buffers_evicted = 0; > + int32 buffers_flushed = 0; > + bool flushed; > + > + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) > + elog(ERROR, "return type must be a row type"); > + > + if (!superuser()) > + ereport(ERROR, > + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), > + errmsg("must be superuser to use pg_buffercache_evict_all function"))); > + > + for (int buf = 1; buf < NBuffers; buf++) > + { > + if (EvictUnpinnedBuffer(buf, &flushed)) > + buffers_evicted++; > + if (flushed) > + buffers_flushed++; I'd probably add a pre-check for the buffer not being in use. We don't need an external function call, with an unconditional LockBufHdr() inside, for that case. > +/* > + * MarkUnpinnedBufferDirty > + * > + * This function is intended for testing/development use only! > + * > + * To succeed, the buffer must not be pinned on entry, so if the caller had a > + * particular block in mind, it might already have been replaced by some other > + * block by the time this function runs. It's also unpinned on return, so the > + * buffer might be occupied and flushed by the time control is returned. This > + * inherent raciness without other interlocking makes the function unsuitable > + * for non-testing usage. > + * > + * Returns true if the buffer was not dirty and it has now been marked as > + * dirty. Returns false if it wasn't valid, if it couldn't be marked as dirty > + * due to a pin, or if the buffer was already dirty. > + */ Hm. One potentially problematic thing with this is that it can pin and dirty a buffer even while its relation is locked exclusively. Which i think some code doesn't expect. Not sure if there's a way around that :( Greetings, Andres Freund ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-03-12 16:15 Nazir Bilal Yavuz <[email protected]> parent: Andres Freund <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Nazir Bilal Yavuz @ 2025-03-12 16:15 UTC (permalink / raw) To: Andres Freund <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> Hi, On Mon, 17 Feb 2025 at 19:59, Andres Freund <[email protected]> wrote: > > Hi, Thanks for the review! And sorry for the late reply. > On 2024-12-25 15:57:34 +0300, Nazir Bilal Yavuz wrote: > > So, this patchset extends pg_buffercache with 3 functions: > > > > 1- pg_buffercache_evict_all(): This is very similar to the already > > existing pg_buffercache_evict() function. The difference is > > pg_buffercache_evict_all() does not take an argument. Instead it just > > loops over the shared buffers and tries to evict all of them. It > > returns the number of buffers evicted and flushed. > > I do think that'd be rather useful. Perhaps it's also worth having a version > that just evicts a specific relation? That makes sense. I will work on this. > > 2- pg_buffercache_mark_dirty(): This function takes a buffer id as an > > argument and tries to mark this buffer as dirty. Returns true on > > success. This returns false if the buffer is already dirty. Do you > > think this makes sense or do you prefer it to return true if the > > buffer is already dirty? > > I don't really have feelings about that ;) I feel the same. I just wanted to have a symmetry between dirty and evict functions. If you think this would be useless, I can remove it. > > From 813e5ec0da4c65970b4b1ce2ec2918e4652da9ab Mon Sep 17 00:00:00 2001 > > From: Nazir Bilal Yavuz <[email protected]> > > Date: Fri, 20 Dec 2024 14:06:47 +0300 > > Subject: [PATCH v1 1/2] Add pg_buffercache_evict_all() function for testing > > > > This new function provides a mechanism to evict all shared buffers at > > once. It is designed to address the inefficiency of repeatedly calling > > pg_buffercache_evict() for each individual buffer, which can be > > time-consuming when dealing with large shared buffer pools > > (e.g., ~790ms vs. ~270ms for 16GB of shared buffers). > > > */ > > bool > > -EvictUnpinnedBuffer(Buffer buf) > > +EvictUnpinnedBuffer(Buffer buf, bool *flushed) > > { > > BufferDesc *desc; > > uint32 buf_state; > > bool result; > > > > + *flushed = false; > > + > > /* Make sure we can pin the buffer. */ > > ResourceOwnerEnlarge(CurrentResourceOwner); > > ReservePrivateRefCountEntry(); > > @@ -6134,6 +6136,7 @@ EvictUnpinnedBuffer(Buffer buf) > > LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_SHARED); > > FlushBuffer(desc, NULL, IOOBJECT_RELATION, IOCONTEXT_NORMAL); > > LWLockRelease(BufferDescriptorGetContentLock(desc)); > > + *flushed = true; > > } > > > > /* This will return false if it becomes dirty or someone else pins > > it. */ > > I don't think *flushed is necessarily accurate this way, as it might detect > that it doesn't need to flush the data (via StartBufferIO() returning false). You are right. It seems there is no way to get that information without editing the FlushBuffer(), right? And I think editing FlushBuffer() is not worth it. So, I can either remove it or explain in the docs that #buffers_flushed may not be completely accurate. > > + */ > > +Datum > > +pg_buffercache_evict_all(PG_FUNCTION_ARGS) > > +{ > > + Datum result; > > + TupleDesc tupledesc; > > + HeapTuple tuple; > > + Datum values[NUM_BUFFERCACHE_EVICT_ALL_ELEM]; > > + bool nulls[NUM_BUFFERCACHE_EVICT_ALL_ELEM] = {0}; > > + > > + int32 buffers_evicted = 0; > > + int32 buffers_flushed = 0; > > + bool flushed; > > + > > + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) > > + elog(ERROR, "return type must be a row type"); > > + > > + if (!superuser()) > > + ereport(ERROR, > > + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), > > + errmsg("must be superuser to use pg_buffercache_evict_all function"))); > > + > > + for (int buf = 1; buf < NBuffers; buf++) > > + { > > + if (EvictUnpinnedBuffer(buf, &flushed)) > > + buffers_evicted++; > > + if (flushed) > > + buffers_flushed++; > > I'd probably add a pre-check for the buffer not being in use. We don't need an > external function call, with an unconditional LockBufHdr() inside, for that > case. I did not understand why we would need this. Does not EvictUnpinnedBuffer() already check that the buffer is not in use? > > +/* > > + * MarkUnpinnedBufferDirty > > + * > > + * This function is intended for testing/development use only! > > + * > > + * To succeed, the buffer must not be pinned on entry, so if the caller had a > > + * particular block in mind, it might already have been replaced by some other > > + * block by the time this function runs. It's also unpinned on return, so the > > + * buffer might be occupied and flushed by the time control is returned. This > > + * inherent raciness without other interlocking makes the function unsuitable > > + * for non-testing usage. > > + * > > + * Returns true if the buffer was not dirty and it has now been marked as > > + * dirty. Returns false if it wasn't valid, if it couldn't be marked as dirty > > + * due to a pin, or if the buffer was already dirty. > > + */ > > Hm. One potentially problematic thing with this is that it can pin and dirty a > buffer even while its relation is locked exclusively. Which i think some code > doesn't expect. Not sure if there's a way around that :( I see, I did not think about that. Since this function can be used only by superusers, that problem might not be a big issue. What do you think? -- Regards, Nazir Bilal Yavuz Microsoft ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-03-14 13:25 Nazir Bilal Yavuz <[email protected]> parent: Nazir Bilal Yavuz <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Nazir Bilal Yavuz @ 2025-03-14 13:25 UTC (permalink / raw) To: Andres Freund <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> Hi, Here is the v2. Changes prior to v1 are: - pg_buffercache_evict_relation() function is introduced. Which takes a relation as an argument and evicts all shared buffers in the relation. - It is mentioned in the docs that the #buffers_flushed in the pg_buffercache_evict_relation() and pg_buffercache_evict_all() functions do not necessarily mean these buffers are flushed by these functions. Remaining questions from the v1: On Wed, 12 Mar 2025 at 19:15, Nazir Bilal Yavuz <[email protected]> wrote: > On Mon, 17 Feb 2025 at 19:59, Andres Freund <[email protected]> wrote: > > On 2024-12-25 15:57:34 +0300, Nazir Bilal Yavuz wrote: > > > + */ > > > +Datum > > > +pg_buffercache_evict_all(PG_FUNCTION_ARGS) > > > +{ > > > + Datum result; > > > + TupleDesc tupledesc; > > > + HeapTuple tuple; > > > + Datum values[NUM_BUFFERCACHE_EVICT_ALL_ELEM]; > > > + bool nulls[NUM_BUFFERCACHE_EVICT_ALL_ELEM] = {0}; > > > + > > > + int32 buffers_evicted = 0; > > > + int32 buffers_flushed = 0; > > > + bool flushed; > > > + > > > + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) > > > + elog(ERROR, "return type must be a row type"); > > > + > > > + if (!superuser()) > > > + ereport(ERROR, > > > + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), > > > + errmsg("must be superuser to use pg_buffercache_evict_all function"))); > > > + > > > + for (int buf = 1; buf < NBuffers; buf++) > > > + { > > > + if (EvictUnpinnedBuffer(buf, &flushed)) > > > + buffers_evicted++; > > > + if (flushed) > > > + buffers_flushed++; > > > > I'd probably add a pre-check for the buffer not being in use. We don't need an > > external function call, with an unconditional LockBufHdr() inside, for that > > case. > > I did not understand why we would need this. Does not > EvictUnpinnedBuffer() already check that the buffer is not in use? > > > +/* > > > + * MarkUnpinnedBufferDirty > > > + * > > > + * This function is intended for testing/development use only! > > > + * > > > + * To succeed, the buffer must not be pinned on entry, so if the caller had a > > > + * particular block in mind, it might already have been replaced by some other > > > + * block by the time this function runs. It's also unpinned on return, so the > > > + * buffer might be occupied and flushed by the time control is returned. This > > > + * inherent raciness without other interlocking makes the function unsuitable > > > + * for non-testing usage. > > > + * > > > + * Returns true if the buffer was not dirty and it has now been marked as > > > + * dirty. Returns false if it wasn't valid, if it couldn't be marked as dirty > > > + * due to a pin, or if the buffer was already dirty. > > > + */ > > > > Hm. One potentially problematic thing with this is that it can pin and dirty a > > buffer even while its relation is locked exclusively. Which i think some code > > doesn't expect. Not sure if there's a way around that :( > > I see, I did not think about that. Since this function can be used > only by superusers, that problem might not be a big issue. What do you > think? -- Regards, Nazir Bilal Yavuz Microsoft Attachments: [text/x-patch] v2-0001-Add-pg_buffercache_evict_-relation-all-functions-.patch (13.1K, ../../CAN55FZ2mNTVK81jqsy2NXg9k_ex_LrdU6JmL1wvsW9-GPy9XuA@mail.gmail.com/2-v2-0001-Add-pg_buffercache_evict_-relation-all-functions-.patch) download | inline diff: From 2090816c1a2d97b257f136ea7ab36c1c86dc1e2a Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Fri, 20 Dec 2024 14:06:47 +0300 Subject: [PATCH v2 1/2] Add pg_buffercache_evict_[relation | all]() functions for testing pg_buffercache_evict_relation(): Evicts all shared buffers in a relation at once. pg_buffercache_evict_all(): Evicts all shared buffers at once. Both functions provide mechanism to evict multiple shared buffers at once. They are designed to address the inefficiency of repeatedly calling pg_buffercache_evict() for each individual buffer, which can be time-consuming when dealing with large shared buffer pools. (e.g., ~790ms vs. ~270ms for 16GB of shared buffers). These functions are intended for developer testing and debugging purposes and are available to superusers only. --- src/include/storage/bufmgr.h | 2 +- src/backend/storage/buffer/bufmgr.c | 13 +- doc/src/sgml/pgbuffercache.sgml | 44 +++++- contrib/pg_buffercache/Makefile | 3 +- contrib/pg_buffercache/meson.build | 1 + .../pg_buffercache--1.5--1.6.sql | 15 ++ contrib/pg_buffercache/pg_buffercache.control | 2 +- contrib/pg_buffercache/pg_buffercache_pages.c | 131 +++++++++++++++++- 8 files changed, 201 insertions(+), 10 deletions(-) create mode 100644 contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index 79a89f87fcc..16c92383969 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -297,7 +297,7 @@ extern uint32 GetAdditionalLocalPinLimit(void); extern void LimitAdditionalPins(uint32 *additional_pins); extern void LimitAdditionalLocalPins(uint32 *additional_pins); -extern bool EvictUnpinnedBuffer(Buffer buf); +extern bool EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed); /* in buf_init.c */ extern void BufferManagerShmemInit(void); diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 8243f4b2445..53bbdd92e53 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -6056,12 +6056,13 @@ ResOwnerPrintBufferPin(Datum res) * or if the buffer becomes dirty again while we're trying to write it out. */ bool -EvictUnpinnedBuffer(Buffer buf) +EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed) { BufferDesc *desc; - uint32 buf_state; bool result; + *flushed = false; + /* Make sure we can pin the buffer. */ ResourceOwnerEnlarge(CurrentResourceOwner); ReservePrivateRefCountEntry(); @@ -6069,8 +6070,11 @@ EvictUnpinnedBuffer(Buffer buf) Assert(!BufferIsLocal(buf)); desc = GetBufferDescriptor(buf - 1); - /* Lock the header and check if it's valid. */ - buf_state = LockBufHdr(desc); + /* Lock the header if it is not already locked. */ + if (!buf_state) + buf_state = LockBufHdr(desc); + + /* Check if it's valid. */ if ((buf_state & BM_VALID) == 0) { UnlockBufHdr(desc, buf_state); @@ -6092,6 +6096,7 @@ EvictUnpinnedBuffer(Buffer buf) LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_SHARED); FlushBuffer(desc, NULL, IOOBJECT_RELATION, IOCONTEXT_NORMAL); LWLockRelease(BufferDescriptorGetContentLock(desc)); + *flushed = true; } /* This will return false if it becomes dirty or someone else pins it. */ diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index 802a5112d77..83950ca5cce 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -31,8 +31,10 @@ This module provides the <function>pg_buffercache_pages()</function> function (wrapped in the <structname>pg_buffercache</structname> view), the <function>pg_buffercache_summary()</function> function, the - <function>pg_buffercache_usage_counts()</function> function and - the <function>pg_buffercache_evict()</function> function. + <function>pg_buffercache_usage_counts()</function> function, the + <function>pg_buffercache_evict()</function>, the + <function>pg_buffercache_evict_relation()</function>, and the + <function>pg_buffercache_evict_all()</function> function. </para> <para> @@ -65,6 +67,22 @@ function is restricted to superusers only. </para> + <para> + The <function>pg_buffercache_evict_relation()</function> function allows all + buffers in the relation to be evicted from the buffer pool given a relation + identifier. It returns how many buffers are evicted and flushed. Flushed buffers + do not have to be flushed by this function call, they might be flushed by + something else. Use of this function is restricted to superusers only. + </para> + + <para> + The <function>pg_buffercache_evict_all()</function> function allows all + buffers to be evicted in the buffer pool. It returns how many buffers are + evicted and flushed. Flushed buffers do not have to be flushed by this + function call, they might be flushed by something else. Use of this function + is restricted to superusers only. + </para> + <sect2 id="pgbuffercache-pg-buffercache"> <title>The <structname>pg_buffercache</structname> View</title> @@ -378,6 +396,28 @@ </para> </sect2> + <sect2 id="pgbuffercache-pg-buffercache-evict-relation"> + <title>The <structname>pg_buffercache_evict_relation</structname> Function</title> + <para> + The <function>pg_buffercache_evict_relation()</function> function is very similar + to <function>pg_buffercache_evict()</function> function. The difference is that + <function>pg_buffercache_evict_relation()</function> takes a relation + identifier instead of buffer identifier. Then, it tries to evict all + buffers in that relation. The function is intended for developer testing only. + </para> + </sect2> + + <sect2 id="pgbuffercache-pg-buffercache-evict-all"> + <title>The <structname>pg_buffercache_evict_all</structname> Function</title> + <para> + The <function>pg_buffercache_evict_all()</function> function is very similar + to <function>pg_buffercache_evict()</function> function. The difference is, + the <function>pg_buffercache_evict_all()</function> does not take argument; + instead it tries to evict all buffers in the buffer pool. The function is + intended for developer testing only. + </para> + </sect2> + <sect2 id="pgbuffercache-sample-output"> <title>Sample Output</title> diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile index eae65ead9e5..2a33602537e 100644 --- a/contrib/pg_buffercache/Makefile +++ b/contrib/pg_buffercache/Makefile @@ -8,7 +8,8 @@ OBJS = \ EXTENSION = pg_buffercache DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \ pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \ - pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql + pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql \ + pg_buffercache--1.5--1.6.sql PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time" REGRESS = pg_buffercache diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build index 12d1fe48717..9b2e9393410 100644 --- a/contrib/pg_buffercache/meson.build +++ b/contrib/pg_buffercache/meson.build @@ -23,6 +23,7 @@ install_data( 'pg_buffercache--1.2.sql', 'pg_buffercache--1.3--1.4.sql', 'pg_buffercache--1.4--1.5.sql', + 'pg_buffercache--1.5--1.6.sql', 'pg_buffercache.control', kwargs: contrib_data_args, ) diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql new file mode 100644 index 00000000000..3a02d56a89f --- /dev/null +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -0,0 +1,15 @@ +\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.6'" to load this file. \quit + +CREATE FUNCTION pg_buffercache_evict_relation( + IN regclass, + IN fork text default 'main', + OUT buffers_evicted int4, + OUT buffers_flushed int4) +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_relation' +LANGUAGE C PARALLEL SAFE VOLATILE; + +CREATE FUNCTION pg_buffercache_evict_all( + OUT buffers_evicted int4, + OUT buffers_flushed int4) +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all' +LANGUAGE C PARALLEL SAFE VOLATILE; diff --git a/contrib/pg_buffercache/pg_buffercache.control b/contrib/pg_buffercache/pg_buffercache.control index 5ee875f77dd..b030ba3a6fa 100644 --- a/contrib/pg_buffercache/pg_buffercache.control +++ b/contrib/pg_buffercache/pg_buffercache.control @@ -1,5 +1,5 @@ # pg_buffercache extension comment = 'examine the shared buffer cache' -default_version = '1.5' +default_version = '1.6' module_pathname = '$libdir/pg_buffercache' relocatable = true diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index 3ae0a018e10..9932a19735a 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -9,16 +9,21 @@ #include "postgres.h" #include "access/htup_details.h" +#include "access/relation.h" #include "catalog/pg_type.h" #include "funcapi.h" #include "storage/buf_internals.h" #include "storage/bufmgr.h" +#include "utils/builtins.h" +#include "utils/rel.h" #define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8 #define NUM_BUFFERCACHE_PAGES_ELEM 9 #define NUM_BUFFERCACHE_SUMMARY_ELEM 5 #define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4 +#define NUM_BUFFERCACHE_EVICT_RELATION_ELEM 2 +#define NUM_BUFFERCACHE_EVICT_ALL_ELEM 2 PG_MODULE_MAGIC; @@ -64,6 +69,8 @@ PG_FUNCTION_INFO_V1(pg_buffercache_pages); PG_FUNCTION_INFO_V1(pg_buffercache_summary); PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts); PG_FUNCTION_INFO_V1(pg_buffercache_evict); +PG_FUNCTION_INFO_V1(pg_buffercache_evict_relation); +PG_FUNCTION_INFO_V1(pg_buffercache_evict_all); Datum pg_buffercache_pages(PG_FUNCTION_ARGS) @@ -356,6 +363,7 @@ Datum pg_buffercache_evict(PG_FUNCTION_ARGS) { Buffer buf = PG_GETARG_INT32(0); + bool flushed; if (!superuser()) ereport(ERROR, @@ -365,5 +373,126 @@ pg_buffercache_evict(PG_FUNCTION_ARGS) if (buf < 1 || buf > NBuffers) elog(ERROR, "bad buffer ID: %d", buf); - PG_RETURN_BOOL(EvictUnpinnedBuffer(buf)); + PG_RETURN_BOOL(EvictUnpinnedBuffer(buf, 0, &flushed)); +} + +/* + * Try to evict specified relation. + */ +Datum +pg_buffercache_evict_relation(PG_FUNCTION_ARGS) +{ + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_RELATION_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_RELATION_ELEM] = {0}; + + Oid relOid; + Relation rel; + int32 buffers_evicted = 0; + int32 buffers_flushed = 0; + bool flushed; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_evict function"))); + + if (PG_ARGISNULL(0)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("relation cannot be null"))); + + + relOid = PG_GETARG_OID(0); + + /* Open relation. */ + rel = relation_open(relOid, AccessShareLock); + + if (RelationUsesLocalBuffers(rel)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("relation uses local buffers," + "pg_buffercache_evict_relation function is intended to" + "used for shared buffers only"))); + + for (int buf = 1; buf < NBuffers; buf++) + { + uint32 buf_state; + BufferDesc *bufHdr = GetBufferDescriptor(buf - 1); + + /* + * No need to call UnlockBufHdr() if BufTagMatchesRelFileLocator() + * returns true, EvictUnpinnedBuffer() will take care of it. + */ + buf_state = LockBufHdr(bufHdr); + if (BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator)) + { + if (EvictUnpinnedBuffer(buf, buf_state, &flushed)) + buffers_evicted++; + if (flushed) + buffers_flushed++; + } + else + UnlockBufHdr(bufHdr, buf_state); + } + + /* Close relation, release lock. */ + relation_close(rel, AccessShareLock); + + values[0] = Int32GetDatum(buffers_evicted); + values[1] = Int32GetDatum(buffers_flushed); + + /* Build and return the tuple. */ + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); +} + + +/* + * Try to evict all shared buffers. + */ +Datum +pg_buffercache_evict_all(PG_FUNCTION_ARGS) +{ + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_ALL_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_ALL_ELEM] = {0}; + + int32 buffers_evicted = 0; + int32 buffers_flushed = 0; + bool flushed; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_evict_all function"))); + + for (int buf = 1; buf < NBuffers; buf++) + { + if (EvictUnpinnedBuffer(buf, 0, &flushed)) + buffers_evicted++; + if (flushed) + buffers_flushed++; + } + + values[0] = Int32GetDatum(buffers_evicted); + values[1] = Int32GetDatum(buffers_flushed); + + /* Build and return the tuple. */ + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); } -- 2.47.2 [text/x-patch] v2-0002-Add-pg_buffercache_mark_dirty-_all-functions-for-.patch (8.7K, ../../CAN55FZ2mNTVK81jqsy2NXg9k_ex_LrdU6JmL1wvsW9-GPy9XuA@mail.gmail.com/3-v2-0002-Add-pg_buffercache_mark_dirty-_all-functions-for-.patch) download | inline diff: From 56ef07473155d3e5f568dab710501bd92f8718ff Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Wed, 25 Dec 2024 15:46:10 +0300 Subject: [PATCH v2 2/2] Add pg_buffercache_mark_dirty[_all]() functions for testing This commit introduces two new functions for marking shared buffers as dirty: pg_buffercache_mark_dirty(): Marks a specific shared buffer as dirty. pg_buffercache_mark_dirty_all(): Marks all shared buffers as dirty in a single operation. The pg_buffercache_mark_dirty_all() function provides an efficient way to dirty the entire buffer pool (e.g., ~550ms vs. ~70ms for 16GB of shared buffers), complementing pg_buffercache_mark_dirty() for more granular control. These functions are intended for developer testing and debugging scenarios, enabling users to simulate various buffer pool states and test write-back behavior. Both functions are superuser-only. --- src/include/storage/bufmgr.h | 1 + src/backend/storage/buffer/bufmgr.c | 62 +++++++++++++++++++ doc/src/sgml/pgbuffercache.sgml | 40 +++++++++++- .../pg_buffercache--1.5--1.6.sql | 10 +++ contrib/pg_buffercache/pg_buffercache_pages.c | 43 +++++++++++++ 5 files changed, 154 insertions(+), 2 deletions(-) diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index 16c92383969..fbd16440e55 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -298,6 +298,7 @@ extern void LimitAdditionalPins(uint32 *additional_pins); extern void LimitAdditionalLocalPins(uint32 *additional_pins); extern bool EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed); +extern bool MarkUnpinnedBufferDirty(Buffer buf); /* in buf_init.c */ extern void BufferManagerShmemInit(void); diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 53bbdd92e53..e3c84a5342c 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -6106,3 +6106,65 @@ EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed) return result; } + +/* + * MarkUnpinnedBufferDirty + * + * This function is intended for testing/development use only! + * + * To succeed, the buffer must not be pinned on entry, so if the caller had a + * particular block in mind, it might already have been replaced by some other + * block by the time this function runs. It's also unpinned on return, so the + * buffer might be occupied and flushed by the time control is returned. This + * inherent raciness without other interlocking makes the function unsuitable + * for non-testing usage. + * + * Returns true if the buffer was not dirty and it has now been marked as + * dirty. Returns false if it wasn't valid, if it couldn't be marked as dirty + * due to a pin, or if the buffer was already dirty. + */ +bool +MarkUnpinnedBufferDirty(Buffer buf) +{ + BufferDesc *desc; + uint32 buf_state; + bool result = false; + + Assert(!BufferIsLocal(buf)); + + /* Make sure we can pin the buffer. */ + ResourceOwnerEnlarge(CurrentResourceOwner); + ReservePrivateRefCountEntry(); + + desc = GetBufferDescriptor(buf - 1); + + /* Lock the header and check if it's valid. */ + buf_state = LockBufHdr(desc); + if ((buf_state & BM_VALID) == 0) + { + UnlockBufHdr(desc, buf_state); + return false; + } + + /* Check that it's not pinned already. */ + if (BUF_STATE_GET_REFCOUNT(buf_state) > 0) + { + UnlockBufHdr(desc, buf_state); + return false; + } + + PinBuffer_Locked(desc); /* releases spinlock */ + + /* If it was not already dirty, mark it as dirty. */ + if (!(buf_state & BM_DIRTY)) + { + LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_EXCLUSIVE); + MarkBufferDirty(buf); + LWLockRelease(BufferDescriptorGetContentLock(desc)); + result = true; + } + + UnpinBuffer(desc); + + return result; +} diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index 83950ca5cce..b0c19d56283 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -33,8 +33,10 @@ the <function>pg_buffercache_summary()</function> function, the <function>pg_buffercache_usage_counts()</function> function, the <function>pg_buffercache_evict()</function>, the - <function>pg_buffercache_evict_relation()</function>, and the - <function>pg_buffercache_evict_all()</function> function. + <function>pg_buffercache_evict_relation()</function>, the + <function>pg_buffercache_evict_all()</function>, the + <function>pg_buffercache_mark_dirty()</function> and the + <function>pg_buffercache_mark_dirty_all()</function> function. </para> <para> @@ -83,6 +85,18 @@ is restricted to superusers only. </para> + <para> + The <function>pg_buffercache_mark_dirty()</function> function allows a block + to be marked as dirty from the buffer pool given a buffer identifier. Use of + this function is restricted to superusers only. + </para> + + <para> + The <function>pg_buffercache_mark_dirty_all()</function> function tries to + mark all buffers dirty in the buffer pool. It returns how many buffers are + dirtied. Use of this function is restricted to superusers only. + </para> + <sect2 id="pgbuffercache-pg-buffercache"> <title>The <structname>pg_buffercache</structname> View</title> @@ -418,6 +432,28 @@ </para> </sect2> + <sect2 id="pgbuffercache-pg-buffercache-mark-dirty"> + <title>The <structname>pg_buffercache_mark_dirty</structname> Function</title> + <para> + The <function>pg_buffercache_mark_dirty()</function> function takes a buffer + identifier, as shown in the <structfield>bufferid</structfield> column of + the <structname>pg_buffercache</structname> view. It returns true on success, + and false if the buffer wasn't valid or if it couldn't be marked as dirty + because it was pinned. The function is intended for developer testing only. + </para> + </sect2> + + <sect2 id="pgbuffercache-pg-buffercache-mark-dirty-all"> + <title>The <structname>pg_buffercache_mark_dirty_all</structname> Function</title> + <para> + The <function>pg_buffercache_mark_dirty_all()</function> function is very similar + to <function>pg_buffercache_mark_dirty()</function> function. The difference is, + the <function>pg_buffercache_mark_dirty_all()</function> does not take argument; + instead it tries to mark all buffers dirty in the buffer pool. The function is + intended for developer testing only. + </para> + </sect2> + <sect2 id="pgbuffercache-sample-output"> <title>Sample Output</title> diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql index 3a02d56a89f..9aff57a8729 100644 --- a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -13,3 +13,13 @@ CREATE FUNCTION pg_buffercache_evict_all( OUT buffers_flushed int4) AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all' LANGUAGE C PARALLEL SAFE VOLATILE; + +CREATE FUNCTION pg_buffercache_mark_dirty(IN int) +RETURNS bool +AS 'MODULE_PATHNAME', 'pg_buffercache_mark_dirty' +LANGUAGE C PARALLEL SAFE VOLATILE STRICT; + +CREATE FUNCTION pg_buffercache_mark_dirty_all() +RETURNS INT4 +AS 'MODULE_PATHNAME', 'pg_buffercache_mark_dirty_all' +LANGUAGE C PARALLEL SAFE VOLATILE; diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index 9932a19735a..d20dced2077 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -71,6 +71,8 @@ PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts); PG_FUNCTION_INFO_V1(pg_buffercache_evict); PG_FUNCTION_INFO_V1(pg_buffercache_evict_relation); PG_FUNCTION_INFO_V1(pg_buffercache_evict_all); +PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty); +PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty_all); Datum pg_buffercache_pages(PG_FUNCTION_ARGS) @@ -496,3 +498,44 @@ pg_buffercache_evict_all(PG_FUNCTION_ARGS) PG_RETURN_DATUM(result); } + +/* + * Try to mark dirty a shared buffer. + */ +Datum +pg_buffercache_mark_dirty(PG_FUNCTION_ARGS) +{ + Buffer buf = PG_GETARG_INT32(0); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_mark_dirty function"))); + + if (buf < 1 || buf > NBuffers) + elog(ERROR, "bad buffer ID: %d", buf); + + PG_RETURN_BOOL(MarkUnpinnedBufferDirty(buf)); +} + +/* + * Try to mark dirty all shared buffers. + */ +Datum +pg_buffercache_mark_dirty_all(PG_FUNCTION_ARGS) +{ + int32 buffers_dirtied = 0; + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_mark_dirty_all function"))); + + for (int buf = 1; buf < NBuffers; buf++) + { + if (MarkUnpinnedBufferDirty(buf)) + buffers_dirtied++; + } + + PG_RETURN_INT32(buffers_dirtied); +} -- 2.47.2 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-03-18 22:02 Aidar Imamov <[email protected]> parent: Nazir Bilal Yavuz <[email protected]> 0 siblings, 3 replies; 18+ messages in thread From: Aidar Imamov @ 2025-03-18 22:02 UTC (permalink / raw) To: Nazir Bilal Yavuz <[email protected]>; +Cc: Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected] Hi! This is my first time trying out as a patch reviewer, and I don't claim to be an expert. I am very interested in the topic of buffer cache and would like to learn more about it. I have reviewed the patches after they were edited and I would like to share my thoughts on some points. pg_buffercache_evict_all(): > for (int buf = 1; buf < NBuffers; buf++) Mb it would be more correct to use <= NBuffers? I also did not fully understand what A. Freund was referring to. However, we cannot avoid blocking the buffer header, as we need to ensure that the buffer is not being pinned by anyone else. Perhaps it would be beneficial to call LockBufHdr() outside and check if BUT_STATE_GET_REFCOUNT == 0 before calling EvictUnpinnedBuffer(). This would help to prevent unnecessary calls to EvictUnpinnedBuffer() itself, ResourceOwnerEnlarge() and ReservePrivateRefCountEntry(). pg_buffercache_mark_dirty_all(): > for (int buf = 1; buf < NBuffers; buf++) Mb it would be more correct to use <= NBuffers? pg_buffercache_evict_relation(): > errmsg("must be superuser to use pg_buffercache_evict function") '_relation' postfix got lost here > /* Open relation. */ > rel = relation_open(relOid, AccessShareLock); If I understand correctly, this function is meant to replicate the behavior of VACUUM FULL, but only for dirty relation pages. In this case, wouldn't it be necessary to obtain an exclusive access lock? > for (int buf = 1; buf < NBuffers; buf++) Mb it would be more correct to use <= NBuffers? > /* > * No need to call UnlockBufHdr() if BufTagMatchesRelFileLocator() > * returns true, EvictUnpinnedBuffer() will take care of it. > */ > buf_state = LockBufHdr(bufHdr); > if (BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator)) > { > if (EvictUnpinnedBuffer(buf, buf_state, &flushed)) > buffers_evicted++; > if (flushed) > buffers_flushed++; > } If you have previously acquired the exclusive access lock to the relation, you may want to consider replacing the order of the BufTagMatchesRelFileLocator() and LockBufHdr() calls for improved efficiency (as mentioned in the comment on the BufTagMatchesRelFileLocator() call in the DropRelationBuffers() function in bufmgr.c). And it maybe useful also to add BUT_STATE_GET_REFCOUNT == 0 precheck before calling EvictUnpinnedBuffer()? regards, Aidar Imamov ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-03-20 22:25 Joseph Koshakow <[email protected]> parent: Aidar Imamov <[email protected]> 2 siblings, 1 reply; 18+ messages in thread From: Joseph Koshakow @ 2025-03-20 22:25 UTC (permalink / raw) To: Aidar Imamov <[email protected]>; +Cc: Nazir Bilal Yavuz <[email protected]>; Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]> Hi I am working with Aidar to give a review and I am also a beginner reviewer. > From 813e5ec0da4c65970b4b1ce2ec2918e4652da9ab Mon Sep 17 00:00:00 2001 > From: Nazir Bilal Yavuz <byavuz81(at)gmail(dot)com> > Date: Fri, 20 Dec 2024 14:06:47 +0300 > Subject: [PATCH v1 1/2] Add pg_buffercache_evict_all() function for testing > */ > bool > -EvictUnpinnedBuffer(Buffer buf) > +EvictUnpinnedBuffer(Buffer buf, bool *flushed) I think you should update the comment above this function to include details on the new `flushed` argument. > diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml > index 802a5112d77..83950ca5cce 100644 > --- a/doc/src/sgml/pgbuffercache.sgml > +++ b/doc/src/sgml/pgbuffercache.sgml > @@ -31,8 +31,10 @@ > This module provides the <function>pg_buffercache_pages()</function> > function (wrapped in the <structname>pg_buffercache</structname> view), > the <function>pg_buffercache_summary()</function> function, the > - <function>pg_buffercache_usage_counts()</function> function and > - the <function>pg_buffercache_evict()</function> function. > + <function>pg_buffercache_usage_counts()</function> function, the > + <function>pg_buffercache_evict()</function>, the > + <function>pg_buffercache_evict_relation()</function>, and the > + <function>pg_buffercache_evict_all()</function> function. All the other functions have indexterm tags above this, should we add indexterms for the new functions? > + <sect2 id="pgbuffercache-pg-buffercache-evict-relation"> > + <title>The <structname>pg_buffercache_evict_relation</structname> Function</title> > + <para> > + The <function>pg_buffercache_evict_relation()</function> function is very similar > + to <function>pg_buffercache_evict()</function> function. The difference is that > + <function>pg_buffercache_evict_relation()</function> takes a relation > + identifier instead of buffer identifier. Then, it tries to evict all > + buffers in that relation. The function is intended for developer testing only. > + </para> > + </sect2> > + > + <sect2 id="pgbuffercache-pg-buffercache-evict-all"> > + <title>The <structname>pg_buffercache_evict_all</structname> Function</title> > + <para> > + The <function>pg_buffercache_evict_all()</function> function is very similar > + to <function>pg_buffercache_evict()</function> function. The difference is, > + the <function>pg_buffercache_evict_all()</function> does not take argument; > + instead it tries to evict all buffers in the buffer pool. The function is > + intended for developer testing only. > + </para> > + </sect2> The other difference is that these new functions have an additional output argument to indicate if the buffer was flushed which we probably want to mention here. Also I think it's more gramatically correct to say "the <fn-name> function" or just "<fn-name>". A couple of times you say "<fn-name> function" or "the <fn-name>". Also I think the third to last sentence should end with "... does not take **an** argument" or "... does not take argument**s**". > +CREATE FUNCTION pg_buffercache_evict_relation( > + IN regclass, > + IN fork text default 'main', > + OUT buffers_evicted int4, > + OUT buffers_flushed int4) > +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_relation' > +LANGUAGE C PARALLEL SAFE VOLATILE; > + > +CREATE FUNCTION pg_buffercache_evict_all( > + OUT buffers_evicted int4, > + OUT buffers_flushed int4) > +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all' > +LANGUAGE C PARALLEL SAFE VOLATILE; Does it make sense to also update pg_buffercache_evict to also return a bool if the buffer was flushed? Or is that too much of a breaking change? > + /* Lock the header and check if it's valid. */ > + buf_state = LockBufHdr(desc); > + if ((buf_state & BM_VALID) == 0) > + { > + UnlockBufHdr(desc, buf_state); > + return false; > + } EvictUnpinnedBuffer first checks if the buffer is locked before attempting to lock it. Do we need a similar check here? /* Lock the header if it is not already locked. */ if (!buf_state) buf_state = LockBufHdr(desc); >> I don't think *flushed is necessarily accurate this way, as it might detect >> that it doesn't need to flush the data (via StartBufferIO() returning false). > > You are right. It seems there is no way to get that information > without editing the FlushBuffer(), right? And I think editing > FlushBuffer() is not worth it. So, I can either remove it or explain > in the docs that #buffers_flushed may not be completely accurate. There's already a lot of caveats on EvictUnpinnedBuffer that it might have unexpected results when run concurrently, so I think it's fine to add one more. Thanks, Joe Koshakow ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-03-24 12:39 Nazir Bilal Yavuz <[email protected]> parent: Aidar Imamov <[email protected]> 2 siblings, 0 replies; 18+ messages in thread From: Nazir Bilal Yavuz @ 2025-03-24 12:39 UTC (permalink / raw) To: Aidar Imamov <[email protected]>; +Cc: Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected] Hi, On Wed, 19 Mar 2025 at 01:02, Aidar Imamov <[email protected]> wrote: > > Hi! > > This is my first time trying out as a patch reviewer, and I don't claim > to be > an expert. I am very interested in the topic of buffer cache and would > like to > learn more about it. Thank you so much for the review! > pg_buffercache_evict_all(): > > for (int buf = 1; buf < NBuffers; buf++) > Mb it would be more correct to use <= NBuffers? Yes, done. > I also did not fully understand what A. Freund was referring to. > However, we > cannot avoid blocking the buffer header, as we need to ensure that the > buffer > is not being pinned by anyone else. Perhaps it would be beneficial to > call > LockBufHdr() outside and check if BUT_STATE_GET_REFCOUNT == 0 before > calling > EvictUnpinnedBuffer(). This would help to prevent unnecessary calls to > EvictUnpinnedBuffer() itself, ResourceOwnerEnlarge() and > ReservePrivateRefCountEntry(). I had an off-list talk with Andres and learned that ResourceOwnerEnlarge() and ReservePrivateRefCountEntry() need to be called before acquiring the buffer header lock. I introduced the EvictUnpinnedBuffersFromSharedRelation() function for that [1]. > pg_buffercache_mark_dirty_all(): > > for (int buf = 1; buf < NBuffers; buf++) > Mb it would be more correct to use <= NBuffers? Done. > pg_buffercache_evict_relation(): > > errmsg("must be superuser to use pg_buffercache_evict function") > '_relation' postfix got lost here Done. > > /* Open relation. */ > > rel = relation_open(relOid, AccessShareLock); > If I understand correctly, this function is meant to replicate the > behavior of > VACUUM FULL, but only for dirty relation pages. In this case, wouldn't > it be > necessary to obtain an exclusive access lock? I think VACUUM FULL does more things but I agree with you, obtaining an exclusive access lock is the correct way. > > for (int buf = 1; buf < NBuffers; buf++) > Mb it would be more correct to use <= NBuffers? Done. > > > /* > > * No need to call UnlockBufHdr() if BufTagMatchesRelFileLocator() > > * returns true, EvictUnpinnedBuffer() will take care of it. > > */ > > buf_state = LockBufHdr(bufHdr); > > if (BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator)) > > { > > if (EvictUnpinnedBuffer(buf, buf_state, &flushed)) > > buffers_evicted++; > > if (flushed) > > buffers_flushed++; > > } > > If you have previously acquired the exclusive access lock to the > relation, > you may want to consider replacing the order of the > BufTagMatchesRelFileLocator() > and LockBufHdr() calls for improved efficiency (as mentioned in the > comment on > the BufTagMatchesRelFileLocator() call in the DropRelationBuffers() > function in > bufmgr.c). I think you are right, we are basically copying FlushRelationBuffers() to the contrib/pg_buffer_cache. Done. > And it maybe useful also to add BUT_STATE_GET_REFCOUNT == 0 precheck > before calling > EvictUnpinnedBuffer()? I think we do not need to do this. I see EvictUnpinnedBuffer() as a central place that checks all conditions. If we add this pre-check then we should not do the same check in the EvictUnpinnedBuffer(), creating this logic would add unnecessary complexity to EvictUnpinnedBuffer(). I addressed these reviews in v3. I will share the patches in my next reply. -- Regards, Nazir Bilal Yavuz Microsoft ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-03-24 12:39 Nazir Bilal Yavuz <[email protected]> parent: Joseph Koshakow <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Nazir Bilal Yavuz @ 2025-03-24 12:39 UTC (permalink / raw) To: Joseph Koshakow <[email protected]>; +Cc: Aidar Imamov <[email protected]>; Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]> Hi, On Fri, 21 Mar 2025 at 01:25, Joseph Koshakow <[email protected]> wrote: > > Hi I am working with Aidar to give a review and I am also a beginner > reviewer. Thank you so much for the review! > > From 813e5ec0da4c65970b4b1ce2ec2918e4652da9ab Mon Sep 17 00:00:00 2001 > > From: Nazir Bilal Yavuz <byavuz81(at)gmail(dot)com> > > Date: Fri, 20 Dec 2024 14:06:47 +0300 > > Subject: [PATCH v1 1/2] Add pg_buffercache_evict_all() function for testing > > */ > > bool > > -EvictUnpinnedBuffer(Buffer buf) > > +EvictUnpinnedBuffer(Buffer buf, bool *flushed) > > I think you should update the comment above this function to include > details on the new `flushed` argument. Yes, done. > > diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml > > index 802a5112d77..83950ca5cce 100644 > > --- a/doc/src/sgml/pgbuffercache.sgml > > +++ b/doc/src/sgml/pgbuffercache.sgml > > @@ -31,8 +31,10 @@ > > This module provides the <function>pg_buffercache_pages()</function> > > function (wrapped in the <structname>pg_buffercache</structname> view), > > the <function>pg_buffercache_summary()</function> function, the > > - <function>pg_buffercache_usage_counts()</function> function and > > - the <function>pg_buffercache_evict()</function> function. > > + <function>pg_buffercache_usage_counts()</function> function, the > > + <function>pg_buffercache_evict()</function>, the > > + <function>pg_buffercache_evict_relation()</function>, and the > > + <function>pg_buffercache_evict_all()</function> function. > > All the other functions have indexterm tags above this, should we add > indexterms for the new functions? I think so, done. > > + <sect2 id="pgbuffercache-pg-buffercache-evict-relation"> > > + <title>The <structname>pg_buffercache_evict_relation</structname> Function</title> > > + <para> > > + The <function>pg_buffercache_evict_relation()</function> function is very similar > > + to <function>pg_buffercache_evict()</function> function. The difference is that > > + <function>pg_buffercache_evict_relation()</function> takes a relation > > + identifier instead of buffer identifier. Then, it tries to evict all > > + buffers in that relation. The function is intended for developer testing only. > > + </para> > > + </sect2> > > + > > + <sect2 id="pgbuffercache-pg-buffercache-evict-all"> > > + <title>The <structname>pg_buffercache_evict_all</structname> Function</title> > > + <para> > > + The <function>pg_buffercache_evict_all()</function> function is very similar > > + to <function>pg_buffercache_evict()</function> function. The difference is, > > + the <function>pg_buffercache_evict_all()</function> does not take argument; > > + instead it tries to evict all buffers in the buffer pool. The function is > > + intended for developer testing only. > > + </para> > > + </sect2> > > The other difference is that these new functions have an additional > output argument to indicate if the buffer was flushed which we probably > want to mention here. You are right, done. > Also I think it's more gramatically correct to say "the <fn-name> > function" or just "<fn-name>". A couple of times you say "<fn-name> > function" or "the <fn-name>". I choose "the <fn-name> function", done. > Also I think the third to last sentence should end with "... does not > take **an** argument" or "... does not take argument**s**". I agree, done. > > +CREATE FUNCTION pg_buffercache_evict_relation( > > + IN regclass, > > + IN fork text default 'main', > > + OUT buffers_evicted int4, > > + OUT buffers_flushed int4) > > +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_relation' > > +LANGUAGE C PARALLEL SAFE VOLATILE; > > + > > +CREATE FUNCTION pg_buffercache_evict_all( > > + OUT buffers_evicted int4, > > + OUT buffers_flushed int4) > > +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all' > > +LANGUAGE C PARALLEL SAFE VOLATILE; > > Does it make sense to also update pg_buffercache_evict to also return > a bool if the buffer was flushed? Or is that too much of a breaking > change? I think it makes sense but I did that change in another patch (0002) as this may need more discussion. > > + /* Lock the header and check if it's valid. */ > > + buf_state = LockBufHdr(desc); > > + if ((buf_state & BM_VALID) == 0) > > + { > > + UnlockBufHdr(desc, buf_state); > > + return false; > > + } > > EvictUnpinnedBuffer first checks if the buffer is locked before > attempting to lock it. Do we need a similar check here? I do not think so, for now we do not call MarkUnpinnedBufferDirty() while the buffer header is locked. > /* Lock the header if it is not already locked. */ > if (!buf_state) > buf_state = LockBufHdr(desc); > > >> I don't think *flushed is necessarily accurate this way, as it might detect > >> that it doesn't need to flush the data (via StartBufferIO() returning false). > > > > You are right. It seems there is no way to get that information > > without editing the FlushBuffer(), right? And I think editing > > FlushBuffer() is not worth it. So, I can either remove it or explain > > in the docs that #buffers_flushed may not be completely accurate. > > There's already a lot of caveats on EvictUnpinnedBuffer that it might > have unexpected results when run concurrently, so I think it's fine to > add one more. I agree. v3 is attached, I addressed both you and Aidar's reviews in the v3. -- Regards, Nazir Bilal Yavuz Microsoft Attachments: [text/x-patch] v3-0001-Add-pg_buffercache_evict_-relation-all-functions-.patch (15.8K, ../../CAN55FZ06WCeFgQPgftiFyycGRnsuHPXumrZgyxQS30GJGT7Tbg@mail.gmail.com/2-v3-0001-Add-pg_buffercache_evict_-relation-all-functions-.patch) download | inline diff: From 522d285af9e2294efe02f67c964e264f35e855c4 Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Mon, 24 Mar 2025 13:52:04 +0300 Subject: [PATCH v3 1/3] Add pg_buffercache_evict_[relation | all]() functions for testing pg_buffercache_evict_relation(): Evicts all shared buffers in a relation at once. pg_buffercache_evict_all(): Evicts all shared buffers at once. Both functions provide mechanism to evict multiple shared buffers at once. They are designed to address the inefficiency of repeatedly calling pg_buffercache_evict() for each individual buffer, which can be time-consuming when dealing with large shared buffer pools. (e.g., ~790ms vs. ~270ms for 16GB of shared buffers). These functions are intended for developer testing and debugging purposes and are available to superusers only. Author: Nazir Bilal Yavuz <[email protected]> Reviewed-by: Andres Freund <[email protected]> Reviewed-by: Aidar Imamov <[email protected]> Reviewed-by: Joseph Koshakow <[email protected]> Discussion: https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com --- src/include/storage/bufmgr.h | 3 +- src/backend/storage/buffer/bufmgr.c | 83 ++++++++++++-- doc/src/sgml/pgbuffercache.sgml | 56 ++++++++- contrib/pg_buffercache/Makefile | 3 +- contrib/pg_buffercache/meson.build | 1 + .../pg_buffercache--1.5--1.6.sql | 16 +++ contrib/pg_buffercache/pg_buffercache.control | 2 +- contrib/pg_buffercache/pg_buffercache_pages.c | 108 ++++++++++++++++++ 8 files changed, 260 insertions(+), 12 deletions(-) create mode 100644 contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index 538b890a51d..e068319b736 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -298,7 +298,8 @@ extern uint32 GetAdditionalLocalPinLimit(void); extern void LimitAdditionalPins(uint32 *additional_pins); extern void LimitAdditionalLocalPins(uint32 *additional_pins); -extern bool EvictUnpinnedBuffer(Buffer buf); +extern bool EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed); +extern void EvictUnpinnedBuffersFromSharedRelation(Relation rel, int32 *buffers_evicted, int32 *buffers_flushed); /* in buf_init.c */ extern void BufferManagerShmemInit(void); diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 323382dcfa8..74cb1ef1b9f 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -6078,26 +6078,40 @@ ResOwnerPrintBufferPin(Datum res) * even by the same block. This inherent raciness without other interlocking * makes the function unsuitable for non-testing usage. * + * flushed is set to true if the buffer is flushed but this does not + * necessarily mean that the buffer is flushed by us, it might be flushed by + * someone else. + * * Returns true if the buffer was valid and it has now been made invalid. * Returns false if it wasn't valid, if it couldn't be evicted due to a pin, * or if the buffer becomes dirty again while we're trying to write it out. */ bool -EvictUnpinnedBuffer(Buffer buf) +EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed) { BufferDesc *desc; - uint32 buf_state; bool result; - /* Make sure we can pin the buffer. */ - ResourceOwnerEnlarge(CurrentResourceOwner); - ReservePrivateRefCountEntry(); + *flushed = false; Assert(!BufferIsLocal(buf)); desc = GetBufferDescriptor(buf - 1); - /* Lock the header and check if it's valid. */ - buf_state = LockBufHdr(desc); + /* + * If the buffer is already locked, we assume that preparations to pinning + * buffer are already done. + */ + if (!buf_state) + { + /* Make sure we can pin the buffer. */ + ResourceOwnerEnlarge(CurrentResourceOwner); + ReservePrivateRefCountEntry(); + + /* Lock the header if it is not already locked. */ + buf_state = LockBufHdr(desc); + } + + /* Check if it's valid. */ if ((buf_state & BM_VALID) == 0) { UnlockBufHdr(desc, buf_state); @@ -6119,6 +6133,7 @@ EvictUnpinnedBuffer(Buffer buf) LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_SHARED); FlushBuffer(desc, NULL, IOOBJECT_RELATION, IOCONTEXT_NORMAL); LWLockRelease(BufferDescriptorGetContentLock(desc)); + *flushed = true; } /* This will return false if it becomes dirty or someone else pins it. */ @@ -6128,3 +6143,57 @@ EvictUnpinnedBuffer(Buffer buf) return result; } + +/* + * Try to evict all shared buffers in the relation by calling + * EvictUnpinnedBuffer() for all the shared buffers in the relation. + * + * We need this helper function because of the following reason. + * ReservePrivateRefCountEntry() needs to be called before acquiring the + * buffer header lock but ReservePrivateRefCountEntry() is static and it would + * be better to have it as static. Hence, it can't be called from outside of + * this file. This helper function is created to bypass that problem. + * + * buffers_evicted and buffers_flushed is set the total number of buffers + * evicted and flushed respectively. + * + * If the relation uses local buffers retun false, otherwise return true. + */ +void +EvictUnpinnedBuffersFromSharedRelation(Relation rel, int32 *buffers_evicted, int32 *buffers_flushed) +{ + bool flushed; + + *buffers_evicted = *buffers_flushed = 0; + + Assert(!RelationUsesLocalBuffers(rel)); + + for (int buf = 1; buf <= NBuffers; buf++) + { + uint32 buf_state = 0; + BufferDesc *bufHdr = GetBufferDescriptor(buf - 1); + + /* An unlocked precheck should be safe and saves some cycles. */ + if (!BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator)) + continue; + + /* Make sure we can pin the buffer. */ + ResourceOwnerEnlarge(CurrentResourceOwner); + ReservePrivateRefCountEntry(); + + /* + * No need to call UnlockBufHdr() if BufTagMatchesRelFileLocator() + * returns true, EvictUnpinnedBuffer() will take care of it. + */ + buf_state = LockBufHdr(bufHdr); + if (BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator)) + { + if (EvictUnpinnedBuffer(buf, buf_state, &flushed)) + (*buffers_evicted)++; + if (flushed) + (*buffers_flushed)++; + } + else + UnlockBufHdr(bufHdr, buf_state); + } +} diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index 802a5112d77..d99aa979410 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -27,12 +27,22 @@ <primary>pg_buffercache_evict</primary> </indexterm> + <indexterm> + <primary>pg_buffercache_evict_relation</primary> + </indexterm> + + <indexterm> + <primary>pg_buffercache_evict_all</primary> + </indexterm> + <para> This module provides the <function>pg_buffercache_pages()</function> function (wrapped in the <structname>pg_buffercache</structname> view), the <function>pg_buffercache_summary()</function> function, the - <function>pg_buffercache_usage_counts()</function> function and - the <function>pg_buffercache_evict()</function> function. + <function>pg_buffercache_usage_counts()</function> function, the + <function>pg_buffercache_evict()</function>, the + <function>pg_buffercache_evict_relation()</function> function and the + <function>pg_buffercache_evict_all()</function> function. </para> <para> @@ -65,6 +75,18 @@ function is restricted to superusers only. </para> + <para> + The <function>pg_buffercache_evict_relation()</function> function allows all + shared buffers in the relation to be evicted from the buffer pool given a + relation identifier. Use of this function is restricted to superusers only. + </para> + + <para> + The <function>pg_buffercache_evict_all()</function> function allows all + shared buffers to be evicted in the buffer pool. Use of this function is + restricted to superusers only. + </para> + <sect2 id="pgbuffercache-pg-buffercache"> <title>The <structname>pg_buffercache</structname> View</title> @@ -378,6 +400,36 @@ </para> </sect2> + <sect2 id="pgbuffercache-pg-buffercache-evict-relation"> + <title>The <structname>pg_buffercache_evict_relation</structname> Function</title> + <para> + The <function>pg_buffercache_evict_relation()</function> function is very + similar to the <function>pg_buffercache_evict()</function> function. The + difference is that the <function>pg_buffercache_evict_relation()</function> + takes a relation identifier instead of buffer identifier. Then, it tries + to evict all buffers in that relation. It returns the number of evicted and + flushed buffers. Flushed buffers aren't necessarily flushed by us, they + might be flushed by someone else. The result is immediately out of date + upon return, as the buffer might become valid again at any time due to + concurrent activity. The function is intended for developer testing only. + </para> + </sect2> + + <sect2 id="pgbuffercache-pg-buffercache-evict-all"> + <title>The <structname>pg_buffercache_evict_all</structname> Function</title> + <para> + The <function>pg_buffercache_evict_all()</function> function is very + similar to the <function>pg_buffercache_evict()</function> function. The + difference is, the <function>pg_buffercache_evict_all()</function> function + does not take an argument; instead it tries to evict all buffers in the + buffer pool. It returns the number of evicted and flushed buffers. + Flushed buffers aren't necessarily flushed by us, they might be flushed by + someone else. The result is immediately out of date upon return, as the + buffer might become valid again at any time due to concurrent activity. + The function is intended for developer testing only. + </para> + </sect2> + <sect2 id="pgbuffercache-sample-output"> <title>Sample Output</title> diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile index eae65ead9e5..2a33602537e 100644 --- a/contrib/pg_buffercache/Makefile +++ b/contrib/pg_buffercache/Makefile @@ -8,7 +8,8 @@ OBJS = \ EXTENSION = pg_buffercache DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \ pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \ - pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql + pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql \ + pg_buffercache--1.5--1.6.sql PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time" REGRESS = pg_buffercache diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build index 12d1fe48717..9b2e9393410 100644 --- a/contrib/pg_buffercache/meson.build +++ b/contrib/pg_buffercache/meson.build @@ -23,6 +23,7 @@ install_data( 'pg_buffercache--1.2.sql', 'pg_buffercache--1.3--1.4.sql', 'pg_buffercache--1.4--1.5.sql', + 'pg_buffercache--1.5--1.6.sql', 'pg_buffercache.control', kwargs: contrib_data_args, ) diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql new file mode 100644 index 00000000000..2494a0a19b1 --- /dev/null +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -0,0 +1,16 @@ +\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.6'" to load this file. \quit + +CREATE FUNCTION pg_buffercache_evict_relation( + IN regclass, + OUT buffers_evicted int4, + OUT buffers_flushed int4) +RETURNS record +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_relation' +LANGUAGE C PARALLEL SAFE VOLATILE STRICT; + +CREATE FUNCTION pg_buffercache_evict_all( + OUT buffers_evicted int4, + OUT buffers_flushed int4) +RETURNS record +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all' +LANGUAGE C PARALLEL SAFE VOLATILE; diff --git a/contrib/pg_buffercache/pg_buffercache.control b/contrib/pg_buffercache/pg_buffercache.control index 5ee875f77dd..b030ba3a6fa 100644 --- a/contrib/pg_buffercache/pg_buffercache.control +++ b/contrib/pg_buffercache/pg_buffercache.control @@ -1,5 +1,5 @@ # pg_buffercache extension comment = 'examine the shared buffer cache' -default_version = '1.5' +default_version = '1.6' module_pathname = '$libdir/pg_buffercache' relocatable = true diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index 3ae0a018e10..7d3bb50b942 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -9,16 +9,21 @@ #include "postgres.h" #include "access/htup_details.h" +#include "access/relation.h" #include "catalog/pg_type.h" #include "funcapi.h" #include "storage/buf_internals.h" #include "storage/bufmgr.h" +#include "utils/builtins.h" +#include "utils/rel.h" #define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8 #define NUM_BUFFERCACHE_PAGES_ELEM 9 #define NUM_BUFFERCACHE_SUMMARY_ELEM 5 #define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4 +#define NUM_BUFFERCACHE_EVICT_RELATION_ELEM 2 +#define NUM_BUFFERCACHE_EVICT_ALL_ELEM 2 PG_MODULE_MAGIC; @@ -64,6 +69,8 @@ PG_FUNCTION_INFO_V1(pg_buffercache_pages); PG_FUNCTION_INFO_V1(pg_buffercache_summary); PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts); PG_FUNCTION_INFO_V1(pg_buffercache_evict); +PG_FUNCTION_INFO_V1(pg_buffercache_evict_relation); +PG_FUNCTION_INFO_V1(pg_buffercache_evict_all); Datum pg_buffercache_pages(PG_FUNCTION_ARGS) @@ -367,3 +374,104 @@ pg_buffercache_evict(PG_FUNCTION_ARGS) PG_RETURN_BOOL(EvictUnpinnedBuffer(buf)); } + +/* + * Try to evict specified relation. + */ +Datum +pg_buffercache_evict_relation(PG_FUNCTION_ARGS) +{ + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_RELATION_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_RELATION_ELEM] = {0}; + + Oid relOid; + Relation rel; + int32 buffers_evicted = 0; + int32 buffers_flushed = 0; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_evict_relation function"))); + + if (PG_ARGISNULL(0)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("relation cannot be null"))); + + + relOid = PG_GETARG_OID(0); + + /* Open relation. */ + rel = relation_open(relOid, AccessExclusiveLock); + + if (RelationUsesLocalBuffers(rel)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("relation uses local buffers," + "pg_buffercache_evict_relation function is intended to" + "used for shared buffers only"))); + + EvictUnpinnedBuffersFromSharedRelation(rel, &buffers_evicted, &buffers_flushed); + + /* Close relation, release lock. */ + relation_close(rel, AccessExclusiveLock); + + values[0] = Int32GetDatum(buffers_evicted); + values[1] = Int32GetDatum(buffers_flushed); + + /* Build and return the tuple. */ + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); +} + + +/* + * Try to evict all shared buffers. + */ +Datum +pg_buffercache_evict_all(PG_FUNCTION_ARGS) +{ + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_ALL_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_ALL_ELEM] = {0}; + + int32 buffers_evicted = 0; + int32 buffers_flushed = 0; + bool flushed; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_evict_all function"))); + + for (int buf = 1; buf <= NBuffers; buf++) + { + if (EvictUnpinnedBuffer(buf, 0, &flushed)) + buffers_evicted++; + if (flushed) + buffers_flushed++; + } + + values[0] = Int32GetDatum(buffers_evicted); + values[1] = Int32GetDatum(buffers_flushed); + + /* Build and return the tuple. */ + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); +} -- 2.47.2 [text/x-patch] v3-0002-Return-buffer-flushed-information-in-pg_buffercac.patch (4.5K, ../../CAN55FZ06WCeFgQPgftiFyycGRnsuHPXumrZgyxQS30GJGT7Tbg@mail.gmail.com/3-v3-0002-Return-buffer-flushed-information-in-pg_buffercac.patch) download | inline diff: From 683dc0d8e27f99ee0897fdbd33f58a1caf56bb80 Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Mon, 24 Mar 2025 13:53:26 +0300 Subject: [PATCH v3 2/3] Return buffer flushed information in pg_buffercache_evict function Prior commit added ability to get buffer flushed information from EvictUnpinnedBuffer() function. Show this information in pg_buffercache_evict() function too. Author: Nazir Bilal Yavuz <[email protected]> Suggested-by: Joseph Koshakow <[email protected]> Discussion: https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com --- doc/src/sgml/pgbuffercache.sgml | 15 ++++++++------ .../pg_buffercache--1.5--1.6.sql | 9 +++++++++ contrib/pg_buffercache/pg_buffercache_pages.c | 20 ++++++++++++++++++- 3 files changed, 37 insertions(+), 7 deletions(-) diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index d99aa979410..681c74251d4 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -391,12 +391,15 @@ <para> The <function>pg_buffercache_evict()</function> function takes a buffer identifier, as shown in the <structfield>bufferid</structfield> column of - the <structname>pg_buffercache</structname> view. It returns true on success, - and false if the buffer wasn't valid, if it couldn't be evicted because it - was pinned, or if it became dirty again after an attempt to write it out. - The result is immediately out of date upon return, as the buffer might - become valid again at any time due to concurrent activity. The function is - intended for developer testing only. + the <structname>pg_buffercache</structname> view. It returns the + information about whether the buffer is evicted and flushed. evicted + column is true on success, and false if the buffer wasn't valid, if it + couldn't be evicted because it was pinned, or if it became dirty again + after an attempt to write it out. flushed column is true if the buffer is + flushed. This does not necessarily mean that buffer is flushed by us, it + might be flushed by someone else. The result is immediately out of date + upon return, as the buffer might become valid again at any time due to + concurrent activity. The function is intended for developer testing only. </para> </sect2> diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql index 2494a0a19b1..15881f5b8fe 100644 --- a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -1,5 +1,14 @@ \echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.6'" to load this file. \quit +DROP FUNCTION pg_buffercache_evict(integer); +CREATE OR REPLACE FUNCTION pg_buffercache_evict( + IN int, + OUT evicted boolean, + OUT flushed boolean) +RETURNS record +AS 'MODULE_PATHNAME', 'pg_buffercache_evict' +LANGUAGE C PARALLEL SAFE VOLATILE STRICT; + CREATE FUNCTION pg_buffercache_evict_relation( IN regclass, OUT buffers_evicted int4, diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index 7d3bb50b942..77a80401525 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -22,6 +22,7 @@ #define NUM_BUFFERCACHE_PAGES_ELEM 9 #define NUM_BUFFERCACHE_SUMMARY_ELEM 5 #define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4 +#define NUM_BUFFERCACHE_EVICT_ELEM 2 #define NUM_BUFFERCACHE_EVICT_RELATION_ELEM 2 #define NUM_BUFFERCACHE_EVICT_ALL_ELEM 2 @@ -362,7 +363,17 @@ pg_buffercache_usage_counts(PG_FUNCTION_ARGS) Datum pg_buffercache_evict(PG_FUNCTION_ARGS) { + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_ELEM] = {0}; + Buffer buf = PG_GETARG_INT32(0); + bool flushed; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); if (!superuser()) ereport(ERROR, @@ -372,7 +383,14 @@ pg_buffercache_evict(PG_FUNCTION_ARGS) if (buf < 1 || buf > NBuffers) elog(ERROR, "bad buffer ID: %d", buf); - PG_RETURN_BOOL(EvictUnpinnedBuffer(buf)); + values[0] = BoolGetDatum(EvictUnpinnedBuffer(buf, 0, &flushed)); + values[1] = BoolGetDatum(flushed); + + /* Build and return the tuple. */ + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); } /* -- 2.47.2 [text/x-patch] v3-0003-Add-pg_buffercache_mark_dirty-_all-functions-for-.patch (9.8K, ../../CAN55FZ06WCeFgQPgftiFyycGRnsuHPXumrZgyxQS30GJGT7Tbg@mail.gmail.com/4-v3-0003-Add-pg_buffercache_mark_dirty-_all-functions-for-.patch) download | inline diff: From 4c436cd2d474c8013cfcd85f999b98ece096dbd7 Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Wed, 25 Dec 2024 15:46:10 +0300 Subject: [PATCH v3 3/3] Add pg_buffercache_mark_dirty[_all]() functions for testing This commit introduces two new functions for marking shared buffers as dirty: pg_buffercache_mark_dirty(): Marks a specific shared buffer as dirty. pg_buffercache_mark_dirty_all(): Marks all shared buffers as dirty in a single operation. The pg_buffercache_mark_dirty_all() function provides an efficient way to dirty the entire buffer pool (e.g., ~550ms vs. ~70ms for 16GB of shared buffers), complementing pg_buffercache_mark_dirty() for more granular control. These functions are intended for developer testing and debugging scenarios, enabling users to simulate various buffer pool states and test write-back behavior. Both functions are superuser-only. Author: Nazir Bilal Yavuz <[email protected]> Reviewed-by: Andres Freund <[email protected]> Reviewed-by: Aidar Imamov <[email protected]> Reviewed-by: Joseph Koshakow <[email protected]> Discussion: https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com --- src/include/storage/bufmgr.h | 1 + src/backend/storage/buffer/bufmgr.c | 62 +++++++++++++++++++ doc/src/sgml/pgbuffercache.sgml | 54 +++++++++++++++- .../pg_buffercache--1.5--1.6.sql | 10 +++ contrib/pg_buffercache/pg_buffercache_pages.c | 43 +++++++++++++ 5 files changed, 167 insertions(+), 3 deletions(-) diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index e068319b736..ce046712405 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -300,6 +300,7 @@ extern void LimitAdditionalLocalPins(uint32 *additional_pins); extern bool EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed); extern void EvictUnpinnedBuffersFromSharedRelation(Relation rel, int32 *buffers_evicted, int32 *buffers_flushed); +extern bool MarkUnpinnedBufferDirty(Buffer buf); /* in buf_init.c */ extern void BufferManagerShmemInit(void); diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 74cb1ef1b9f..52525953669 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -6197,3 +6197,65 @@ EvictUnpinnedBuffersFromSharedRelation(Relation rel, int32 *buffers_evicted, int UnlockBufHdr(bufHdr, buf_state); } } + +/* + * MarkUnpinnedBufferDirty + * + * This function is intended for testing/development use only! + * + * To succeed, the buffer must not be pinned on entry, so if the caller had a + * particular block in mind, it might already have been replaced by some other + * block by the time this function runs. It's also unpinned on return, so the + * buffer might be occupied and flushed by the time control is returned. This + * inherent raciness without other interlocking makes the function unsuitable + * for non-testing usage. + * + * Returns true if the buffer was not dirty and it has now been marked as + * dirty. Returns false if it wasn't valid, if it couldn't be marked as dirty + * due to a pin, or if the buffer was already dirty. + */ +bool +MarkUnpinnedBufferDirty(Buffer buf) +{ + BufferDesc *desc; + uint32 buf_state; + bool result = false; + + Assert(!BufferIsLocal(buf)); + + /* Make sure we can pin the buffer. */ + ResourceOwnerEnlarge(CurrentResourceOwner); + ReservePrivateRefCountEntry(); + + desc = GetBufferDescriptor(buf - 1); + + /* Lock the header and check if it's valid. */ + buf_state = LockBufHdr(desc); + if ((buf_state & BM_VALID) == 0) + { + UnlockBufHdr(desc, buf_state); + return false; + } + + /* Check that it's not pinned already. */ + if (BUF_STATE_GET_REFCOUNT(buf_state) > 0) + { + UnlockBufHdr(desc, buf_state); + return false; + } + + PinBuffer_Locked(desc); /* releases spinlock */ + + /* If it was not already dirty, mark it as dirty. */ + if (!(buf_state & BM_DIRTY)) + { + LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_EXCLUSIVE); + MarkBufferDirty(buf); + LWLockRelease(BufferDescriptorGetContentLock(desc)); + result = true; + } + + UnpinBuffer(desc); + + return result; +} diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index 681c74251d4..baf64e07b4e 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -35,14 +35,24 @@ <primary>pg_buffercache_evict_all</primary> </indexterm> + <indexterm> + <primary>pg_buffercache_mark_dirty</primary> + </indexterm> + + <indexterm> + <primary>pg_buffercache_mark_dirty_all</primary> + </indexterm> + <para> This module provides the <function>pg_buffercache_pages()</function> function (wrapped in the <structname>pg_buffercache</structname> view), the <function>pg_buffercache_summary()</function> function, the <function>pg_buffercache_usage_counts()</function> function, the - <function>pg_buffercache_evict()</function>, the - <function>pg_buffercache_evict_relation()</function> function and the - <function>pg_buffercache_evict_all()</function> function. + <function>pg_buffercache_evict()</function> function, the + <function>pg_buffercache_evict_relation()</function> function, the + <function>pg_buffercache_evict_all()</function> function, the + <function>pg_buffercache_mark_dirty()</function> function and the + <function>pg_buffercache_mark_dirty_all()</function> function. </para> <para> @@ -87,6 +97,18 @@ restricted to superusers only. </para> + <para> + The <function>pg_buffercache_mark_dirty()</function> function allows a block + to be marked as dirty from the buffer pool given a buffer identifier. Use of + this function is restricted to superusers only. + </para> + + <para> + The <function>pg_buffercache_mark_dirty_all()</function> function tries to + mark all buffers dirty in the buffer pool. Use of this function is + restricted to superusers only. + </para> + <sect2 id="pgbuffercache-pg-buffercache"> <title>The <structname>pg_buffercache</structname> View</title> @@ -433,6 +455,32 @@ </para> </sect2> + <sect2 id="pgbuffercache-pg-buffercache-mark-dirty"> + <title>The <structname>pg_buffercache_mark_dirty</structname> Function</title> + <para> + The <function>pg_buffercache_mark_dirty()</function> function takes a + buffer identifier, as shown in the <structfield>bufferid</structfield> + column of the <structname>pg_buffercache</structname> view. It returns + true on success, and false if the buffer wasn't valid or if it couldn't be + marked as dirty because it was pinned. The result is immediately out of + date upon return, as the buffer might become valid again at any time due to + concurrent activity. The function is intended for developer testing only. + </para> + </sect2> + + <sect2 id="pgbuffercache-pg-buffercache-mark-dirty-all"> + <title>The <structname>pg_buffercache_mark_dirty_all</structname> Function</title> + <para> + The <function>pg_buffercache_mark_dirty_all()</function> function is very + similar to the <function>pg_buffercache_mark_dirty()</function> function. + The difference is, the <function>pg_buffercache_mark_dirty_all()</function> + function does not take an argument; instead it tries to mark all buffers + dirty in the buffer pool. The result is immediately out of date upon + return, as the buffer might become valid again at any time due to + concurrent activity. The function is intended for developer testing only. + </para> + </sect2> + <sect2 id="pgbuffercache-sample-output"> <title>Sample Output</title> diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql index 15881f5b8fe..9e7c3fb6a5f 100644 --- a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -23,3 +23,13 @@ CREATE FUNCTION pg_buffercache_evict_all( RETURNS record AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all' LANGUAGE C PARALLEL SAFE VOLATILE; + +CREATE FUNCTION pg_buffercache_mark_dirty(IN int) +RETURNS bool +AS 'MODULE_PATHNAME', 'pg_buffercache_mark_dirty' +LANGUAGE C PARALLEL SAFE VOLATILE STRICT; + +CREATE FUNCTION pg_buffercache_mark_dirty_all() +RETURNS INT4 +AS 'MODULE_PATHNAME', 'pg_buffercache_mark_dirty_all' +LANGUAGE C PARALLEL SAFE VOLATILE; diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index 77a80401525..a4d82c14ff6 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -72,6 +72,8 @@ PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts); PG_FUNCTION_INFO_V1(pg_buffercache_evict); PG_FUNCTION_INFO_V1(pg_buffercache_evict_relation); PG_FUNCTION_INFO_V1(pg_buffercache_evict_all); +PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty); +PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty_all); Datum pg_buffercache_pages(PG_FUNCTION_ARGS) @@ -493,3 +495,44 @@ pg_buffercache_evict_all(PG_FUNCTION_ARGS) PG_RETURN_DATUM(result); } + +/* + * Try to mark dirty a shared buffer. + */ +Datum +pg_buffercache_mark_dirty(PG_FUNCTION_ARGS) +{ + Buffer buf = PG_GETARG_INT32(0); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_mark_dirty function"))); + + if (buf < 1 || buf > NBuffers) + elog(ERROR, "bad buffer ID: %d", buf); + + PG_RETURN_BOOL(MarkUnpinnedBufferDirty(buf)); +} + +/* + * Try to mark dirty all shared buffers. + */ +Datum +pg_buffercache_mark_dirty_all(PG_FUNCTION_ARGS) +{ + int32 buffers_dirtied = 0; + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_mark_dirty_all function"))); + + for (int buf = 1; buf <= NBuffers; buf++) + { + if (MarkUnpinnedBufferDirty(buf)) + buffers_dirtied++; + } + + PG_RETURN_INT32(buffers_dirtied); +} -- 2.47.2 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-03-24 12:55 Nazir Bilal Yavuz <[email protected]> parent: Nazir Bilal Yavuz <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Nazir Bilal Yavuz @ 2025-03-24 12:55 UTC (permalink / raw) To: Joseph Koshakow <[email protected]>; +Cc: Aidar Imamov <[email protected]>; Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]> Hi, It seems that Aidar's email ended up as another thread [1]. I am copy-pasting mail and answer here to keep the discussion in this thread. On Sun, 23 Mar 2025 at 22:16, Aidar Imamov <[email protected]> wrote: > > I agree with most of what Joseph said. However, I would like to add some > comments. > > At the moment, the "flushed" flag essentially indicates whether the > buffer > was dirty at the time of eviction and it does not guarantee that it has > been > written to disk. Therefore, it would be better to rename the > buffers_flushed > column in the output of pg_buffer_cache_evict_all() and > pg_buffercache_evict_relation() functions to dirty_buffers mb? This > would > allow us to avoid the confusion that arises from the fact that not all > dirty > buffers could have actually been written to disk. In addition, this > would > remove the "flushed" parameter from the EvictUnpinnedBuffer() function. > Because if we explicitly call LockBufHdr() outside of > EvictUnpinnedBuffer(), > we can already know in advance whether the buffer is dirty or not. > > The same applies to the suggestion to retrieve "flushed" count from the > pg_buffercache_evict() call. We cannot say this for certain, but we can > determine whether the buffer was dirty. I think flushed means 'passing the buffer contents to the kernel' in the Postgres context (as it is explained in the FlushBuffer()). We know that flush has happened, we just do not know if the buffer is flushed by us or someone else. [1] https://postgr.es/m/flat/76a550315baef9d7424b70144f1c6a2d%40postgrespro.ru -- Regards, Nazir Bilal Yavuz Microsoft ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-03-27 22:53 Aidar Imamov <[email protected]> parent: Nazir Bilal Yavuz <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Aidar Imamov @ 2025-03-27 22:53 UTC (permalink / raw) To: Nazir Bilal Yavuz <[email protected]>; +Cc: Joseph Koshakow <[email protected]>; Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]> Hi! I've been looking at the patches v3 and there are a few things I want to talk about. EvictUnpinnedBuffer(): I think we should change the paragraph with "flushed" of the comment to something more like this: "If the buffer was dirty at the time of the call it will be flushed by calling FlushBuffer() and if 'flushed' is not NULL '*flushed' will be set to true." I also think it'd be a good idea to add null-checks for "flushed" before dereferencing it: > *flushed = false; > *flushed = true; If the (!buf_state) clause is entered then we assume that the header is not locked. Maybe edit the comment: "Lock the header if it is not already locked." -> "Lock the header"? > if (!buf_state) > { > /* Make sure we can pin the buffer. */ > ResourceOwnerEnlarge(CurrentResourceOwner); > ReservePrivateRefCountEntry(); > > /* Lock the header if it is not already locked. */ > buf_state = LockBufHdr(desc); > } EvictUnpinnedBuffersFromSharedRelation(): Maybe it will be more accurate to name the function as EvictRelUnpinnedBuffers()? I think the comment will seem more correct in the following manner: "Try to evict all the shared buffers containing provided relation's pages. This function is intended for testing/development use only! Before calling this function, it is important to acquire AccessExclusiveLock on the specified relation to avoid replacing the current block of this relation with another one during execution. If not null, buffers_evicted and buffers_flushed are set to the total number of buffers evicted and flushed respectively." I also think it'd be a good idea to add null-checks for "buffers_evicted" and "buffers_flushed" before dereferencing them: > *buffers_evicted = *buffers_flushed = 0; I think we don't need to check this clause again if AccessExclusiveLock was acquired before function call. Don't we? > if (BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator)) > { > if (EvictUnpinnedBuffer(buf, buf_state, &flushed)) > (*buffers_evicted)++; > if (flushed) > (*buffers_flushed)++; > } MarkUnpinnedBufferDirty(): I think the comment will seem more correct in the following manner: "Try to mark provided shared buffer as dirty. This function is intended for testing/development use only! Same as EvictUnpinnedBuffer() but with MarkBufferDirty() call inside. Returns true if the buffer was already dirty or it has successfully been marked as dirty." And also I think the function should return true at the case when the buffer was already dirty. What do you think? pg_buffercache_evict_relation(): "pg_buffercache_evict_relation function is intended to" - 'be' is missed here. pg_buffercache_mark_dirty(): Maybe edit the comment to: "Try to mark a shared buffer as dirty."? Maybe edit the elog text to: "bad shared buffer ID" - just to clarify the case when provided buffer number is negative (local buffer number). pg_buffercache_mark_dirty_all(): Maybe also edit the comment to: "Try to mark all the shared buffers as dirty."? bufmgr.h: I think it might be a good idea to follow the Postgres formatting style and move the function's arguments to the next line if they exceed 80 characters. regards, Aidar Imamov ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-03-28 10:37 Nazir Bilal Yavuz <[email protected]> parent: Aidar Imamov <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Nazir Bilal Yavuz @ 2025-03-28 10:37 UTC (permalink / raw) To: Aidar Imamov <[email protected]>; +Cc: Joseph Koshakow <[email protected]>; Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]> Hi, On Fri, 28 Mar 2025 at 01:53, Aidar Imamov <[email protected]> wrote: > > Hi! > > I've been looking at the patches v3 and there are a few things I want to > talk > about. Thank you for looking into this! > EvictUnpinnedBuffer(): > I think we should change the paragraph with "flushed" of the comment to > something more like this: "If the buffer was dirty at the time of the > call it > will be flushed by calling FlushBuffer() and if 'flushed' is not NULL > '*flushed' > will be set to true." This is correct if the buffer header lock is acquired before calling the EvictUnpinnedBuffer(). Otherwise, the buffer might be flushed after calling the EvictUnpinnedBuffer() and before acquiring the buffer header lock. I slightly edited this comment and also added a comment for the buf_state variable: * buf_state is used to understand if the buffer header lock is acquired * before calling this function. If it has non-zero value, it is assumed that * buffer header lock is acquired before calling this function. This is * helpful for evicting buffers in the relation as the buffer header lock * needs to be taken before calling this function in this case. * * *flushed is set to true if the buffer was dirty and has been flushed. * However, this does not necessarily mean that we flushed the buffer, it * could have been flushed by someone else. > I also think it'd be a good idea to add null-checks for "flushed" before > dereferencing it: > > *flushed = false; > > *flushed = true; Assert check is added. > If the (!buf_state) clause is entered then we assume that the header is > not locked. > Maybe edit the comment: "Lock the header if it is not already locked." > -> "Lock the header"? > > if (!buf_state) > > { > > /* Make sure we can pin the buffer. */ > > ResourceOwnerEnlarge(CurrentResourceOwner); > > ReservePrivateRefCountEntry(); > > > > /* Lock the header if it is not already locked. */ > > buf_state = LockBufHdr(desc); > > } I think this is better, done. > EvictUnpinnedBuffersFromSharedRelation(): > Maybe it will be more accurate to name the function as > EvictRelUnpinnedBuffers()? I liked that, done. > I think the comment will seem more correct in the following manner: > "Try to evict all the shared buffers containing provided relation's > pages. > > This function is intended for testing/development use only! > > Before calling this function, it is important to acquire > AccessExclusiveLock on > the specified relation to avoid replacing the current block of this > relation with > another one during execution. > > If not null, buffers_evicted and buffers_flushed are set to the total > number of > buffers evicted and flushed respectively." I added all comments except the not null part, I also preserved the explanation of why we need this function. > I also think it'd be a good idea to add null-checks for > "buffers_evicted" and > "buffers_flushed" before dereferencing them: > > *buffers_evicted = *buffers_flushed = 0; Assert check is added. > I think we don't need to check this clause again if AccessExclusiveLock > was acquired > before function call. Don't we? > > if (BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator)) > > { > > if (EvictUnpinnedBuffer(buf, buf_state, &flushed)) > > (*buffers_evicted)++; > > if (flushed) > > (*buffers_flushed)++; > > } I think we need it. Copy-pasting a comment from the DropRelationBuffers(): * We can make this a tad faster by prechecking the buffer tag before * we attempt to lock the buffer; this saves a lot of lock * acquisitions in typical cases. It should be safe because the * caller must have AccessExclusiveLock on the relation, or some other * reason to be certain that no one is loading new pages of the rel * into the buffer pool. (Otherwise we might well miss such pages * entirely.) Therefore, while the tag might be changing while we * look at it, it can't be changing *to* a value we care about, only * *away* from such a value. So false negatives are impossible, and * false positives are safe because we'll recheck after getting the * buffer lock. > MarkUnpinnedBufferDirty(): > I think the comment will seem more correct in the following manner: > "Try to mark provided shared buffer as dirty. > > This function is intended for testing/development use only! > > Same as EvictUnpinnedBuffer() but with MarkBufferDirty() call inside. > > Returns true if the buffer was already dirty or it has successfully been > marked as > dirty." Done. > And also I think the function should return true at the case when the > buffer was > already dirty. What do you think? I asked the same question in the first email and I still could not decide but I will be applying this as it was not decided before. > pg_buffercache_evict_relation(): > "pg_buffercache_evict_relation function is intended to" - 'be' is missed > here. Done. > pg_buffercache_mark_dirty(): > Maybe edit the comment to: "Try to mark a shared buffer as dirty."? Done. > Maybe edit the elog text to: "bad shared buffer ID" - just to clarify > the case when > provided buffer number is negative (local buffer number). Although I think your suggestion makes sense, I did not change this since it is already implemented like that in the pg_buffercache_evict(). > pg_buffercache_mark_dirty_all(): > Maybe also edit the comment to: "Try to mark all the shared buffers as > dirty."? Done. > bufmgr.h: > I think it might be a good idea to follow the Postgres formatting style > and move the > function's arguments to the next line if they exceed 80 characters. I thought that pgindent already does this, done. v4 is attached. -- Regards, Nazir Bilal Yavuz Microsoft Attachments: [application/octet-stream] v4-0001-Add-pg_buffercache_evict_-relation-all-functions-.patch (16.3K, ../../CAN55FZ24CoEijAwkWgEiyMzYQ6PqsWdaKxffN0mQy6DxTNv6Tg@mail.gmail.com/2-v4-0001-Add-pg_buffercache_evict_-relation-all-functions-.patch) download | inline diff: From 656443b51ec4c2d38db4c7264568363b90db773e Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Mon, 24 Mar 2025 13:52:04 +0300 Subject: [PATCH v4 1/3] Add pg_buffercache_evict_[relation | all]() functions for testing pg_buffercache_evict_relation(): Evicts all shared buffers in a relation at once. pg_buffercache_evict_all(): Evicts all shared buffers at once. Both functions provide mechanism to evict multiple shared buffers at once. They are designed to address the inefficiency of repeatedly calling pg_buffercache_evict() for each individual buffer, which can be time-consuming when dealing with large shared buffer pools. (e.g., ~790ms vs. ~270ms for 16GB of shared buffers). These functions are intended for developer testing and debugging purposes and are available to superusers only. Author: Nazir Bilal Yavuz <[email protected]> Reviewed-by: Andres Freund <[email protected]> Reviewed-by: Aidar Imamov <[email protected]> Reviewed-by: Joseph Koshakow <[email protected]> Discussion: https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com --- contrib/pg_buffercache/Makefile | 3 +- contrib/pg_buffercache/meson.build | 1 + .../pg_buffercache--1.5--1.6.sql | 16 +++ contrib/pg_buffercache/pg_buffercache.control | 2 +- contrib/pg_buffercache/pg_buffercache_pages.c | 108 ++++++++++++++++++ doc/src/sgml/pgbuffercache.sgml | 56 ++++++++- src/backend/storage/buffer/bufmgr.c | 94 +++++++++++++-- src/include/storage/bufmgr.h | 4 +- 8 files changed, 272 insertions(+), 12 deletions(-) create mode 100644 contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile index eae65ead9e5..2a33602537e 100644 --- a/contrib/pg_buffercache/Makefile +++ b/contrib/pg_buffercache/Makefile @@ -8,7 +8,8 @@ OBJS = \ EXTENSION = pg_buffercache DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \ pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \ - pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql + pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql \ + pg_buffercache--1.5--1.6.sql PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time" REGRESS = pg_buffercache diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build index 12d1fe48717..9b2e9393410 100644 --- a/contrib/pg_buffercache/meson.build +++ b/contrib/pg_buffercache/meson.build @@ -23,6 +23,7 @@ install_data( 'pg_buffercache--1.2.sql', 'pg_buffercache--1.3--1.4.sql', 'pg_buffercache--1.4--1.5.sql', + 'pg_buffercache--1.5--1.6.sql', 'pg_buffercache.control', kwargs: contrib_data_args, ) diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql new file mode 100644 index 00000000000..2494a0a19b1 --- /dev/null +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -0,0 +1,16 @@ +\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.6'" to load this file. \quit + +CREATE FUNCTION pg_buffercache_evict_relation( + IN regclass, + OUT buffers_evicted int4, + OUT buffers_flushed int4) +RETURNS record +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_relation' +LANGUAGE C PARALLEL SAFE VOLATILE STRICT; + +CREATE FUNCTION pg_buffercache_evict_all( + OUT buffers_evicted int4, + OUT buffers_flushed int4) +RETURNS record +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all' +LANGUAGE C PARALLEL SAFE VOLATILE; diff --git a/contrib/pg_buffercache/pg_buffercache.control b/contrib/pg_buffercache/pg_buffercache.control index 5ee875f77dd..b030ba3a6fa 100644 --- a/contrib/pg_buffercache/pg_buffercache.control +++ b/contrib/pg_buffercache/pg_buffercache.control @@ -1,5 +1,5 @@ # pg_buffercache extension comment = 'examine the shared buffer cache' -default_version = '1.5' +default_version = '1.6' module_pathname = '$libdir/pg_buffercache' relocatable = true diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index 62602af1775..6314ba653bc 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -9,16 +9,21 @@ #include "postgres.h" #include "access/htup_details.h" +#include "access/relation.h" #include "catalog/pg_type.h" #include "funcapi.h" #include "storage/buf_internals.h" #include "storage/bufmgr.h" +#include "utils/builtins.h" +#include "utils/rel.h" #define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8 #define NUM_BUFFERCACHE_PAGES_ELEM 9 #define NUM_BUFFERCACHE_SUMMARY_ELEM 5 #define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4 +#define NUM_BUFFERCACHE_EVICT_RELATION_ELEM 2 +#define NUM_BUFFERCACHE_EVICT_ALL_ELEM 2 PG_MODULE_MAGIC_EXT( .name = "pg_buffercache", @@ -67,6 +72,8 @@ PG_FUNCTION_INFO_V1(pg_buffercache_pages); PG_FUNCTION_INFO_V1(pg_buffercache_summary); PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts); PG_FUNCTION_INFO_V1(pg_buffercache_evict); +PG_FUNCTION_INFO_V1(pg_buffercache_evict_relation); +PG_FUNCTION_INFO_V1(pg_buffercache_evict_all); Datum pg_buffercache_pages(PG_FUNCTION_ARGS) @@ -370,3 +377,104 @@ pg_buffercache_evict(PG_FUNCTION_ARGS) PG_RETURN_BOOL(EvictUnpinnedBuffer(buf)); } + +/* + * Try to evict specified relation. + */ +Datum +pg_buffercache_evict_relation(PG_FUNCTION_ARGS) +{ + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_RELATION_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_RELATION_ELEM] = {0}; + + Oid relOid; + Relation rel; + int32 buffers_evicted = 0; + int32 buffers_flushed = 0; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_evict_relation function"))); + + if (PG_ARGISNULL(0)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("relation cannot be null"))); + + + relOid = PG_GETARG_OID(0); + + /* Open relation. */ + rel = relation_open(relOid, AccessExclusiveLock); + + if (RelationUsesLocalBuffers(rel)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("relation uses local buffers," + "pg_buffercache_evict_relation function is intended to" + "be used for shared buffers only"))); + + EvictRelUnpinnedBuffers(rel, &buffers_evicted, &buffers_flushed); + + /* Close relation, release lock. */ + relation_close(rel, AccessExclusiveLock); + + values[0] = Int32GetDatum(buffers_evicted); + values[1] = Int32GetDatum(buffers_flushed); + + /* Build and return the tuple. */ + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); +} + + +/* + * Try to evict all shared buffers. + */ +Datum +pg_buffercache_evict_all(PG_FUNCTION_ARGS) +{ + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_ALL_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_ALL_ELEM] = {0}; + + int32 buffers_evicted = 0; + int32 buffers_flushed = 0; + bool flushed; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_evict_all function"))); + + for (int buf = 1; buf <= NBuffers; buf++) + { + if (EvictUnpinnedBuffer(buf, 0, &flushed)) + buffers_evicted++; + if (flushed) + buffers_flushed++; + } + + values[0] = Int32GetDatum(buffers_evicted); + values[1] = Int32GetDatum(buffers_flushed); + + /* Build and return the tuple. */ + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); +} diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index 802a5112d77..d99aa979410 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -27,12 +27,22 @@ <primary>pg_buffercache_evict</primary> </indexterm> + <indexterm> + <primary>pg_buffercache_evict_relation</primary> + </indexterm> + + <indexterm> + <primary>pg_buffercache_evict_all</primary> + </indexterm> + <para> This module provides the <function>pg_buffercache_pages()</function> function (wrapped in the <structname>pg_buffercache</structname> view), the <function>pg_buffercache_summary()</function> function, the - <function>pg_buffercache_usage_counts()</function> function and - the <function>pg_buffercache_evict()</function> function. + <function>pg_buffercache_usage_counts()</function> function, the + <function>pg_buffercache_evict()</function>, the + <function>pg_buffercache_evict_relation()</function> function and the + <function>pg_buffercache_evict_all()</function> function. </para> <para> @@ -65,6 +75,18 @@ function is restricted to superusers only. </para> + <para> + The <function>pg_buffercache_evict_relation()</function> function allows all + shared buffers in the relation to be evicted from the buffer pool given a + relation identifier. Use of this function is restricted to superusers only. + </para> + + <para> + The <function>pg_buffercache_evict_all()</function> function allows all + shared buffers to be evicted in the buffer pool. Use of this function is + restricted to superusers only. + </para> + <sect2 id="pgbuffercache-pg-buffercache"> <title>The <structname>pg_buffercache</structname> View</title> @@ -378,6 +400,36 @@ </para> </sect2> + <sect2 id="pgbuffercache-pg-buffercache-evict-relation"> + <title>The <structname>pg_buffercache_evict_relation</structname> Function</title> + <para> + The <function>pg_buffercache_evict_relation()</function> function is very + similar to the <function>pg_buffercache_evict()</function> function. The + difference is that the <function>pg_buffercache_evict_relation()</function> + takes a relation identifier instead of buffer identifier. Then, it tries + to evict all buffers in that relation. It returns the number of evicted and + flushed buffers. Flushed buffers aren't necessarily flushed by us, they + might be flushed by someone else. The result is immediately out of date + upon return, as the buffer might become valid again at any time due to + concurrent activity. The function is intended for developer testing only. + </para> + </sect2> + + <sect2 id="pgbuffercache-pg-buffercache-evict-all"> + <title>The <structname>pg_buffercache_evict_all</structname> Function</title> + <para> + The <function>pg_buffercache_evict_all()</function> function is very + similar to the <function>pg_buffercache_evict()</function> function. The + difference is, the <function>pg_buffercache_evict_all()</function> function + does not take an argument; instead it tries to evict all buffers in the + buffer pool. It returns the number of evicted and flushed buffers. + Flushed buffers aren't necessarily flushed by us, they might be flushed by + someone else. The result is immediately out of date upon return, as the + buffer might become valid again at any time due to concurrent activity. + The function is intended for developer testing only. + </para> + </sect2> + <sect2 id="pgbuffercache-sample-output"> <title>Sample Output</title> diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 323382dcfa8..6105c6d2d73 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -6078,26 +6078,47 @@ ResOwnerPrintBufferPin(Datum res) * even by the same block. This inherent raciness without other interlocking * makes the function unsuitable for non-testing usage. * + * buf_state is used to understand if the buffer header lock is acquired + * before calling this function. If it has non-zero value, it is assumed that + * buffer header lock is acquired before calling this function. This is + * helpful for evicting buffers in the relation as the buffer header lock + * needs to be taken before calling this function in this case. + * + * *flushed is set to true if the buffer was dirty and has been flushed. + * However, this does not necessarily mean that we flushed the buffer, it + * could have been flushed by someone else. + * * Returns true if the buffer was valid and it has now been made invalid. * Returns false if it wasn't valid, if it couldn't be evicted due to a pin, * or if the buffer becomes dirty again while we're trying to write it out. */ bool -EvictUnpinnedBuffer(Buffer buf) +EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed) { BufferDesc *desc; - uint32 buf_state; bool result; - /* Make sure we can pin the buffer. */ - ResourceOwnerEnlarge(CurrentResourceOwner); - ReservePrivateRefCountEntry(); + Assert(flushed); + *flushed = false; Assert(!BufferIsLocal(buf)); desc = GetBufferDescriptor(buf - 1); - /* Lock the header and check if it's valid. */ - buf_state = LockBufHdr(desc); + /* + * If the buffer is already locked, we assume that preparations to pinning + * buffer are already done. + */ + if (!buf_state) + { + /* Make sure we can pin the buffer. */ + ResourceOwnerEnlarge(CurrentResourceOwner); + ReservePrivateRefCountEntry(); + + /* Lock the header */ + buf_state = LockBufHdr(desc); + } + + /* Check if it's valid. */ if ((buf_state & BM_VALID) == 0) { UnlockBufHdr(desc, buf_state); @@ -6119,6 +6140,7 @@ EvictUnpinnedBuffer(Buffer buf) LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_SHARED); FlushBuffer(desc, NULL, IOOBJECT_RELATION, IOCONTEXT_NORMAL); LWLockRelease(BufferDescriptorGetContentLock(desc)); + *flushed = true; } /* This will return false if it becomes dirty or someone else pins it. */ @@ -6128,3 +6150,61 @@ EvictUnpinnedBuffer(Buffer buf) return result; } + +/* + * Try to evict all the shared buffers containing provided relation's pages. + * + * This function is intended for testing/development use only! + * + * We need this helper function because of the following reason. + * ReservePrivateRefCountEntry() needs to be called before acquiring the + * buffer header lock but ReservePrivateRefCountEntry() is static and it would + * be better to have it as static. Hence, it can't be called from outside of + * this file. This helper function is created to bypass that problem. + * + * Before calling this function, it is important to acquire + * AccessExclusiveLock on the specified relation to avoid replacing the + * current block of this relation with another one during execution. + + * buffers_evicted and buffers_flushed are set the total number of buffers + * evicted and flushed respectively. + */ +void +EvictRelUnpinnedBuffers(Relation rel, int32 *buffers_evicted, int32 *buffers_flushed) +{ + bool flushed; + + Assert(buffers_evicted && buffers_flushed); + *buffers_evicted = *buffers_flushed = 0; + + Assert(!RelationUsesLocalBuffers(rel)); + + for (int buf = 1; buf <= NBuffers; buf++) + { + uint32 buf_state = 0; + BufferDesc *bufHdr = GetBufferDescriptor(buf - 1); + + /* An unlocked precheck should be safe and saves some cycles. */ + if (!BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator)) + continue; + + /* Make sure we can pin the buffer. */ + ResourceOwnerEnlarge(CurrentResourceOwner); + ReservePrivateRefCountEntry(); + + /* + * No need to call UnlockBufHdr() if BufTagMatchesRelFileLocator() + * returns true, EvictUnpinnedBuffer() will take care of it. + */ + buf_state = LockBufHdr(bufHdr); + if (BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator)) + { + if (EvictUnpinnedBuffer(buf, buf_state, &flushed)) + (*buffers_evicted)++; + if (flushed) + (*buffers_flushed)++; + } + else + UnlockBufHdr(bufHdr, buf_state); + } +} diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index 538b890a51d..952c26c01c1 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -298,7 +298,9 @@ extern uint32 GetAdditionalLocalPinLimit(void); extern void LimitAdditionalPins(uint32 *additional_pins); extern void LimitAdditionalLocalPins(uint32 *additional_pins); -extern bool EvictUnpinnedBuffer(Buffer buf); +extern bool EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed); +extern void EvictRelUnpinnedBuffers(Relation rel, int32 *buffers_evicted, + int32 *buffers_flushed); /* in buf_init.c */ extern void BufferManagerShmemInit(void); -- 2.43.0 [application/octet-stream] v4-0002-Return-buffer-flushed-information-in-pg_buffercac.patch (4.5K, ../../CAN55FZ24CoEijAwkWgEiyMzYQ6PqsWdaKxffN0mQy6DxTNv6Tg@mail.gmail.com/3-v4-0002-Return-buffer-flushed-information-in-pg_buffercac.patch) download | inline diff: From 53cb1188ac035a3504a2a9ac2f037087c07cca43 Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Mon, 24 Mar 2025 13:53:26 +0300 Subject: [PATCH v4 2/3] Return buffer flushed information in pg_buffercache_evict function Prior commit added ability to get buffer flushed information from EvictUnpinnedBuffer() function. Show this information in pg_buffercache_evict() function too. Author: Nazir Bilal Yavuz <[email protected]> Suggested-by: Joseph Koshakow <[email protected]> Discussion: https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com --- .../pg_buffercache--1.5--1.6.sql | 9 +++++++++ contrib/pg_buffercache/pg_buffercache_pages.c | 20 ++++++++++++++++++- doc/src/sgml/pgbuffercache.sgml | 15 ++++++++------ 3 files changed, 37 insertions(+), 7 deletions(-) diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql index 2494a0a19b1..15881f5b8fe 100644 --- a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -1,5 +1,14 @@ \echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.6'" to load this file. \quit +DROP FUNCTION pg_buffercache_evict(integer); +CREATE OR REPLACE FUNCTION pg_buffercache_evict( + IN int, + OUT evicted boolean, + OUT flushed boolean) +RETURNS record +AS 'MODULE_PATHNAME', 'pg_buffercache_evict' +LANGUAGE C PARALLEL SAFE VOLATILE STRICT; + CREATE FUNCTION pg_buffercache_evict_relation( IN regclass, OUT buffers_evicted int4, diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index 6314ba653bc..a76625307a7 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -22,6 +22,7 @@ #define NUM_BUFFERCACHE_PAGES_ELEM 9 #define NUM_BUFFERCACHE_SUMMARY_ELEM 5 #define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4 +#define NUM_BUFFERCACHE_EVICT_ELEM 2 #define NUM_BUFFERCACHE_EVICT_RELATION_ELEM 2 #define NUM_BUFFERCACHE_EVICT_ALL_ELEM 2 @@ -365,7 +366,17 @@ pg_buffercache_usage_counts(PG_FUNCTION_ARGS) Datum pg_buffercache_evict(PG_FUNCTION_ARGS) { + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_ELEM] = {0}; + Buffer buf = PG_GETARG_INT32(0); + bool flushed; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); if (!superuser()) ereport(ERROR, @@ -375,7 +386,14 @@ pg_buffercache_evict(PG_FUNCTION_ARGS) if (buf < 1 || buf > NBuffers) elog(ERROR, "bad buffer ID: %d", buf); - PG_RETURN_BOOL(EvictUnpinnedBuffer(buf)); + values[0] = BoolGetDatum(EvictUnpinnedBuffer(buf, 0, &flushed)); + values[1] = BoolGetDatum(flushed); + + /* Build and return the tuple. */ + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); } /* diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index d99aa979410..681c74251d4 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -391,12 +391,15 @@ <para> The <function>pg_buffercache_evict()</function> function takes a buffer identifier, as shown in the <structfield>bufferid</structfield> column of - the <structname>pg_buffercache</structname> view. It returns true on success, - and false if the buffer wasn't valid, if it couldn't be evicted because it - was pinned, or if it became dirty again after an attempt to write it out. - The result is immediately out of date upon return, as the buffer might - become valid again at any time due to concurrent activity. The function is - intended for developer testing only. + the <structname>pg_buffercache</structname> view. It returns the + information about whether the buffer is evicted and flushed. evicted + column is true on success, and false if the buffer wasn't valid, if it + couldn't be evicted because it was pinned, or if it became dirty again + after an attempt to write it out. flushed column is true if the buffer is + flushed. This does not necessarily mean that buffer is flushed by us, it + might be flushed by someone else. The result is immediately out of date + upon return, as the buffer might become valid again at any time due to + concurrent activity. The function is intended for developer testing only. </para> </sect2> -- 2.43.0 [application/octet-stream] v4-0003-Add-pg_buffercache_mark_dirty-_all-functions-for-.patch (9.4K, ../../CAN55FZ24CoEijAwkWgEiyMzYQ6PqsWdaKxffN0mQy6DxTNv6Tg@mail.gmail.com/4-v4-0003-Add-pg_buffercache_mark_dirty-_all-functions-for-.patch) download | inline diff: From 22f90970ddd79ec9dd48b1ab3083e17e3d18f0cb Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Wed, 25 Dec 2024 15:46:10 +0300 Subject: [PATCH v4 3/3] Add pg_buffercache_mark_dirty[_all]() functions for testing This commit introduces two new functions for marking shared buffers as dirty: pg_buffercache_mark_dirty(): Marks a specific shared buffer as dirty. pg_buffercache_mark_dirty_all(): Marks all shared buffers as dirty in a single operation. The pg_buffercache_mark_dirty_all() function provides an efficient way to dirty the entire buffer pool (e.g., ~550ms vs. ~70ms for 16GB of shared buffers), complementing pg_buffercache_mark_dirty() for more granular control. These functions are intended for developer testing and debugging scenarios, enabling users to simulate various buffer pool states and test write-back behavior. Both functions are superuser-only. Author: Nazir Bilal Yavuz <[email protected]> Reviewed-by: Andres Freund <[email protected]> Reviewed-by: Aidar Imamov <[email protected]> Reviewed-by: Joseph Koshakow <[email protected]> Discussion: https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com --- .../pg_buffercache--1.5--1.6.sql | 10 ++++ contrib/pg_buffercache/pg_buffercache_pages.c | 43 +++++++++++++++ doc/src/sgml/pgbuffercache.sgml | 54 +++++++++++++++++-- src/backend/storage/buffer/bufmgr.c | 54 +++++++++++++++++++ src/include/storage/bufmgr.h | 1 + 5 files changed, 159 insertions(+), 3 deletions(-) diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql index 15881f5b8fe..9e7c3fb6a5f 100644 --- a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -23,3 +23,13 @@ CREATE FUNCTION pg_buffercache_evict_all( RETURNS record AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all' LANGUAGE C PARALLEL SAFE VOLATILE; + +CREATE FUNCTION pg_buffercache_mark_dirty(IN int) +RETURNS bool +AS 'MODULE_PATHNAME', 'pg_buffercache_mark_dirty' +LANGUAGE C PARALLEL SAFE VOLATILE STRICT; + +CREATE FUNCTION pg_buffercache_mark_dirty_all() +RETURNS INT4 +AS 'MODULE_PATHNAME', 'pg_buffercache_mark_dirty_all' +LANGUAGE C PARALLEL SAFE VOLATILE; diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index a76625307a7..a02067ec162 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -75,6 +75,8 @@ PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts); PG_FUNCTION_INFO_V1(pg_buffercache_evict); PG_FUNCTION_INFO_V1(pg_buffercache_evict_relation); PG_FUNCTION_INFO_V1(pg_buffercache_evict_all); +PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty); +PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty_all); Datum pg_buffercache_pages(PG_FUNCTION_ARGS) @@ -496,3 +498,44 @@ pg_buffercache_evict_all(PG_FUNCTION_ARGS) PG_RETURN_DATUM(result); } + +/* + * Try to mark a shared buffer as dirty. + */ +Datum +pg_buffercache_mark_dirty(PG_FUNCTION_ARGS) +{ + Buffer buf = PG_GETARG_INT32(0); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_mark_dirty function"))); + + if (buf < 1 || buf > NBuffers) + elog(ERROR, "bad buffer ID: %d", buf); + + PG_RETURN_BOOL(MarkUnpinnedBufferDirty(buf)); +} + +/* + * Try to mark all the shared buffers as dirty. + */ +Datum +pg_buffercache_mark_dirty_all(PG_FUNCTION_ARGS) +{ + int32 buffers_dirtied = 0; + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_mark_dirty_all function"))); + + for (int buf = 1; buf <= NBuffers; buf++) + { + if (MarkUnpinnedBufferDirty(buf)) + buffers_dirtied++; + } + + PG_RETURN_INT32(buffers_dirtied); +} diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index 681c74251d4..baf64e07b4e 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -35,14 +35,24 @@ <primary>pg_buffercache_evict_all</primary> </indexterm> + <indexterm> + <primary>pg_buffercache_mark_dirty</primary> + </indexterm> + + <indexterm> + <primary>pg_buffercache_mark_dirty_all</primary> + </indexterm> + <para> This module provides the <function>pg_buffercache_pages()</function> function (wrapped in the <structname>pg_buffercache</structname> view), the <function>pg_buffercache_summary()</function> function, the <function>pg_buffercache_usage_counts()</function> function, the - <function>pg_buffercache_evict()</function>, the - <function>pg_buffercache_evict_relation()</function> function and the - <function>pg_buffercache_evict_all()</function> function. + <function>pg_buffercache_evict()</function> function, the + <function>pg_buffercache_evict_relation()</function> function, the + <function>pg_buffercache_evict_all()</function> function, the + <function>pg_buffercache_mark_dirty()</function> function and the + <function>pg_buffercache_mark_dirty_all()</function> function. </para> <para> @@ -87,6 +97,18 @@ restricted to superusers only. </para> + <para> + The <function>pg_buffercache_mark_dirty()</function> function allows a block + to be marked as dirty from the buffer pool given a buffer identifier. Use of + this function is restricted to superusers only. + </para> + + <para> + The <function>pg_buffercache_mark_dirty_all()</function> function tries to + mark all buffers dirty in the buffer pool. Use of this function is + restricted to superusers only. + </para> + <sect2 id="pgbuffercache-pg-buffercache"> <title>The <structname>pg_buffercache</structname> View</title> @@ -433,6 +455,32 @@ </para> </sect2> + <sect2 id="pgbuffercache-pg-buffercache-mark-dirty"> + <title>The <structname>pg_buffercache_mark_dirty</structname> Function</title> + <para> + The <function>pg_buffercache_mark_dirty()</function> function takes a + buffer identifier, as shown in the <structfield>bufferid</structfield> + column of the <structname>pg_buffercache</structname> view. It returns + true on success, and false if the buffer wasn't valid or if it couldn't be + marked as dirty because it was pinned. The result is immediately out of + date upon return, as the buffer might become valid again at any time due to + concurrent activity. The function is intended for developer testing only. + </para> + </sect2> + + <sect2 id="pgbuffercache-pg-buffercache-mark-dirty-all"> + <title>The <structname>pg_buffercache_mark_dirty_all</structname> Function</title> + <para> + The <function>pg_buffercache_mark_dirty_all()</function> function is very + similar to the <function>pg_buffercache_mark_dirty()</function> function. + The difference is, the <function>pg_buffercache_mark_dirty_all()</function> + function does not take an argument; instead it tries to mark all buffers + dirty in the buffer pool. The result is immediately out of date upon + return, as the buffer might become valid again at any time due to + concurrent activity. The function is intended for developer testing only. + </para> + </sect2> + <sect2 id="pgbuffercache-sample-output"> <title>Sample Output</title> diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 6105c6d2d73..2decd8858b5 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -6208,3 +6208,57 @@ EvictRelUnpinnedBuffers(Relation rel, int32 *buffers_evicted, int32 *buffers_flu UnlockBufHdr(bufHdr, buf_state); } } + +/* + * Try to mark the provided shared buffer as dirty. + * + * This function is intended for testing/development use only! + * + * Same as EvictUnpinnedBuffer() but with MarkBufferDirty() call inside. + * + * Returns true if the buffer was already dirty or it has successfully been + * marked as dirty. + */ +bool +MarkUnpinnedBufferDirty(Buffer buf) +{ + BufferDesc *desc; + uint32 buf_state; + + Assert(!BufferIsLocal(buf)); + + /* Make sure we can pin the buffer. */ + ResourceOwnerEnlarge(CurrentResourceOwner); + ReservePrivateRefCountEntry(); + + desc = GetBufferDescriptor(buf - 1); + + /* Lock the header and check if it's valid. */ + buf_state = LockBufHdr(desc); + if ((buf_state & BM_VALID) == 0) + { + UnlockBufHdr(desc, buf_state); + return false; + } + + /* Check that it's not pinned already. */ + if (BUF_STATE_GET_REFCOUNT(buf_state) > 0) + { + UnlockBufHdr(desc, buf_state); + return false; + } + + PinBuffer_Locked(desc); /* releases spinlock */ + + /* If it was not already dirty, mark it as dirty. */ + if (!(buf_state & BM_DIRTY)) + { + LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_EXCLUSIVE); + MarkBufferDirty(buf); + LWLockRelease(BufferDescriptorGetContentLock(desc)); + } + + UnpinBuffer(desc); + + return true; +} diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index 952c26c01c1..f78cda19ec6 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -301,6 +301,7 @@ extern void LimitAdditionalLocalPins(uint32 *additional_pins); extern bool EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed); extern void EvictRelUnpinnedBuffers(Relation rel, int32 *buffers_evicted, int32 *buffers_flushed); +extern bool MarkUnpinnedBufferDirty(Buffer buf); /* in buf_init.c */ extern void BufferManagerShmemInit(void); -- 2.43.0 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-03-31 13:37 Aidar Imamov <[email protected]> parent: Nazir Bilal Yavuz <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Aidar Imamov @ 2025-03-31 13:37 UTC (permalink / raw) To: Nazir Bilal Yavuz <[email protected]>; +Cc: Joseph Koshakow <[email protected]>; Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]> Hi! I've reviewed the latest version of the patches and found a few things worth discussing. This is probably my final feedback on the patches at this point. Maybe Joseph has something to add. After this discussion, I think it would be helpful if one of the more experienced hackers could take a look at the overall picture (perhaps we should set the status "Ready for Committer"? To be honest, I'm not sure what step that status should be set at). pg_buffercache--1.5--1.6.sql: It seems that there is no need for the OR REPLACE clause after the pg_buffercache_evict() function has been dropped. Maybe we should remove the RETURNS clause from function declarations that have OUT parameters? Doc: "When there are OUT or INOUT parameters, the RETURNS clause can be omitted" pg_buffercache_evict_relation(): The function is marked as STRICT so I think we do not need for redundant check: > if (PG_ARGISNULL(0)) > ereport(ERROR, > (errcode(ERRCODE_INVALID_PARAMETER_VALUE), > errmsg("relation cannot be null"))); Doc: "returns null whenever any of its arguments are null", "the function is not executed when there are null arguments;". EvictUnpinnedBuffer(): Maybe edit this comment a little (just not to repeat the sentences): > buf_state is used to check if the buffer header lock has been acquired > before > calling this function. If buf_state has a non-zero value, it means that > the buffer > header has already been locked. This information is useful for evicting > specific > relation's buffers, as the buffers headers need to be locked before > this function > can be called with such a intention. EvictUnpinnedBuffer() & EvictRelUnpinnedBuffers(): Why did you decide to use the assert to check for NULLs? I understand that currently, the use of these functions is limited to a specific set of calls through the pg_buffercache interface. However, this may not always be the case. Wouldn't it be better to allow users to choose whether or not they want to receive additional information? For example, you could simply ignore any arguments that are passed as NULL. Additionally, I believe it would be beneficial to include some regression tests to check at least the following cases: return type, being a superuser, bad buffer id, local buffers case. Also, there's a little thing about declaring functions as PARALLEL SAFE. To be honest, I don't really have any solid arguments against it. I just have some doubts. For example, how will it work if the plan is split up and we try to work on an object in one part, while the other part of the plan evicts the pages of that object or marks them as dirty... I can't really say for sure about that. And in that context, I'd suggest referring to that awesome statement in the documentation: "If in doubt, functions should be labeled as UNSAFE, which is the default." regards, Aidar Imamov ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-03-31 16:49 Nazir Bilal Yavuz <[email protected]> parent: Aidar Imamov <[email protected]> 0 siblings, 0 replies; 18+ messages in thread From: Nazir Bilal Yavuz @ 2025-03-31 16:49 UTC (permalink / raw) To: Aidar Imamov <[email protected]>; +Cc: Joseph Koshakow <[email protected]>; Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]> Hi, On Mon, 31 Mar 2025 at 16:37, Aidar Imamov <[email protected]> wrote: > > Hi! > > I've reviewed the latest version of the patches and found a few things > worth > discussing. This is probably my final feedback on the patches at this > point. > Maybe Joseph has something to add. Thank you so much for the reviews, these were very helpful! > After this discussion, I think it would be helpful if one of the more > experienced > hackers could take a look at the overall picture (perhaps we should set > the > status "Ready for Committer"? To be honest, I'm not sure what step that > status > should be set at). I agree. I will mark this as 'ready for committer' after writing the tests. > pg_buffercache--1.5--1.6.sql: > It seems that there is no need for the OR REPLACE clause after the > pg_buffercache_evict() function has been dropped. > > Maybe we should remove the RETURNS clause from function declarations > that have > OUT parameters? > Doc: "When there are OUT or INOUT parameters, the RETURNS clause can be > omitted" You are right, both are done. > pg_buffercache_evict_relation(): > The function is marked as STRICT so I think we do not need for redundant > check: > > if (PG_ARGISNULL(0)) > > ereport(ERROR, > > (errcode(ERRCODE_INVALID_PARAMETER_VALUE), > > errmsg("relation cannot be null"))); > Doc: "returns null whenever any of its arguments are null", "the > function is not > executed when there are null arguments;". Done. > EvictUnpinnedBuffer(): > Maybe edit this comment a little (just not to repeat the sentences): > > buf_state is used to check if the buffer header lock has been acquired > > before > > calling this function. If buf_state has a non-zero value, it means that > > the buffer > > header has already been locked. This information is useful for evicting > > specific > > relation's buffers, as the buffers headers need to be locked before > > this function > > can be called with such a intention. This is better, done. > EvictUnpinnedBuffer() & EvictRelUnpinnedBuffers(): > Why did you decide to use the assert to check for NULLs? > I understand that currently, the use of these functions is limited to a > specific set > of calls through the pg_buffercache interface. However, this may not > always be the > case. Wouldn't it be better to allow users to choose whether or not they > want to > receive additional information? For example, you could simply ignore any > arguments > that are passed as NULL. I do not have a strong opinion on this. I agree that these functions can be used somewhere else later but it is easier to ignore these on the return instead of handling NULLs in the functions. If we want to consider the NULL possibility in the EvictUnpinnedBuffer() & EvictRelUnpinnedBuffers(), then we need to write additional if clauses. I think this increases the complexity unnecessarily. > Additionally, I believe it would be beneficial to include some > regression tests to > check at least the following cases: return type, being a superuser, bad > buffer id, > local buffers case. You are right, I will try to write the tests but I am sharing the new version without tests to speed things up. > Also, there's a little thing about declaring functions as PARALLEL SAFE. > To be honest, > I don't really have any solid arguments against it. I just have some > doubts. For > example, how will it work if the plan is split up and we try to work on > an object in > one part, while the other part of the plan evicts the pages of that > object or marks > them as dirty... I can't really say for sure about that. And in that > context, I'd > suggest referring to that awesome statement in the documentation: "If in > doubt, > functions should be labeled as UNSAFE, which is the default." You may be right. I thought they are parallel safe as we acquire the buffer header lock for each buffer but now, I have the same doubts as you. I want to hear other people's opinions on that before labeling them as UNSAFE. v5 is attached. -- Regards, Nazir Bilal Yavuz Microsoft Attachments: [application/octet-stream] v5-0001-Add-pg_buffercache_evict_-relation-all-functions-.patch (16.3K, ../../CAN55FZ0T60_DOtbrJUGvy=-qOjA4EJ79+TzVAKPvcWCGDZ5JrA@mail.gmail.com/2-v5-0001-Add-pg_buffercache_evict_-relation-all-functions-.patch) download | inline diff: From a59be4b50d7691ba952d0abd32198e972d4a6ea0 Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Mon, 24 Mar 2025 13:52:04 +0300 Subject: [PATCH v5 1/3] Add pg_buffercache_evict_[relation | all]() functions for testing pg_buffercache_evict_relation(): Evicts all shared buffers in a relation at once. pg_buffercache_evict_all(): Evicts all shared buffers at once. Both functions provide mechanism to evict multiple shared buffers at once. They are designed to address the inefficiency of repeatedly calling pg_buffercache_evict() for each individual buffer, which can be time-consuming when dealing with large shared buffer pools. (e.g., ~790ms vs. ~270ms for 16GB of shared buffers). These functions are intended for developer testing and debugging purposes and are available to superusers only. Author: Nazir Bilal Yavuz <[email protected]> Reviewed-by: Andres Freund <[email protected]> Reviewed-by: Aidar Imamov <[email protected]> Reviewed-by: Joseph Koshakow <[email protected]> Discussion: https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com --- contrib/pg_buffercache/Makefile | 3 +- contrib/pg_buffercache/meson.build | 1 + .../pg_buffercache--1.5--1.6.sql | 14 +++ contrib/pg_buffercache/pg_buffercache.control | 2 +- contrib/pg_buffercache/pg_buffercache_pages.c | 102 ++++++++++++++++++ doc/src/sgml/pgbuffercache.sgml | 56 +++++++++- src/backend/storage/buffer/bufmgr.c | 94 ++++++++++++++-- src/include/storage/bufmgr.h | 4 +- 8 files changed, 264 insertions(+), 12 deletions(-) create mode 100644 contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile index eae65ead9e5..2a33602537e 100644 --- a/contrib/pg_buffercache/Makefile +++ b/contrib/pg_buffercache/Makefile @@ -8,7 +8,8 @@ OBJS = \ EXTENSION = pg_buffercache DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \ pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \ - pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql + pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql \ + pg_buffercache--1.5--1.6.sql PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time" REGRESS = pg_buffercache diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build index 12d1fe48717..9b2e9393410 100644 --- a/contrib/pg_buffercache/meson.build +++ b/contrib/pg_buffercache/meson.build @@ -23,6 +23,7 @@ install_data( 'pg_buffercache--1.2.sql', 'pg_buffercache--1.3--1.4.sql', 'pg_buffercache--1.4--1.5.sql', + 'pg_buffercache--1.5--1.6.sql', 'pg_buffercache.control', kwargs: contrib_data_args, ) diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql new file mode 100644 index 00000000000..2e255f3fc10 --- /dev/null +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -0,0 +1,14 @@ +\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.6'" to load this file. \quit + +CREATE FUNCTION pg_buffercache_evict_relation( + IN regclass, + OUT buffers_evicted int4, + OUT buffers_flushed int4) +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_relation' +LANGUAGE C PARALLEL SAFE VOLATILE STRICT; + +CREATE FUNCTION pg_buffercache_evict_all( + OUT buffers_evicted int4, + OUT buffers_flushed int4) +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all' +LANGUAGE C PARALLEL SAFE VOLATILE; diff --git a/contrib/pg_buffercache/pg_buffercache.control b/contrib/pg_buffercache/pg_buffercache.control index 5ee875f77dd..b030ba3a6fa 100644 --- a/contrib/pg_buffercache/pg_buffercache.control +++ b/contrib/pg_buffercache/pg_buffercache.control @@ -1,5 +1,5 @@ # pg_buffercache extension comment = 'examine the shared buffer cache' -default_version = '1.5' +default_version = '1.6' module_pathname = '$libdir/pg_buffercache' relocatable = true diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index 62602af1775..fa8aae43afd 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -9,16 +9,21 @@ #include "postgres.h" #include "access/htup_details.h" +#include "access/relation.h" #include "catalog/pg_type.h" #include "funcapi.h" #include "storage/buf_internals.h" #include "storage/bufmgr.h" +#include "utils/builtins.h" +#include "utils/rel.h" #define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8 #define NUM_BUFFERCACHE_PAGES_ELEM 9 #define NUM_BUFFERCACHE_SUMMARY_ELEM 5 #define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4 +#define NUM_BUFFERCACHE_EVICT_RELATION_ELEM 2 +#define NUM_BUFFERCACHE_EVICT_ALL_ELEM 2 PG_MODULE_MAGIC_EXT( .name = "pg_buffercache", @@ -67,6 +72,8 @@ PG_FUNCTION_INFO_V1(pg_buffercache_pages); PG_FUNCTION_INFO_V1(pg_buffercache_summary); PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts); PG_FUNCTION_INFO_V1(pg_buffercache_evict); +PG_FUNCTION_INFO_V1(pg_buffercache_evict_relation); +PG_FUNCTION_INFO_V1(pg_buffercache_evict_all); Datum pg_buffercache_pages(PG_FUNCTION_ARGS) @@ -370,3 +377,98 @@ pg_buffercache_evict(PG_FUNCTION_ARGS) PG_RETURN_BOOL(EvictUnpinnedBuffer(buf)); } + +/* + * Try to evict specified relation. + */ +Datum +pg_buffercache_evict_relation(PG_FUNCTION_ARGS) +{ + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_RELATION_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_RELATION_ELEM] = {0}; + + Oid relOid; + Relation rel; + int32 buffers_evicted = 0; + int32 buffers_flushed = 0; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_evict_relation function"))); + + relOid = PG_GETARG_OID(0); + + /* Open relation. */ + rel = relation_open(relOid, AccessExclusiveLock); + + if (RelationUsesLocalBuffers(rel)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("relation uses local buffers," + "pg_buffercache_evict_relation function is intended to" + "be used for shared buffers only"))); + + EvictRelUnpinnedBuffers(rel, &buffers_evicted, &buffers_flushed); + + /* Close relation, release lock. */ + relation_close(rel, AccessExclusiveLock); + + values[0] = Int32GetDatum(buffers_evicted); + values[1] = Int32GetDatum(buffers_flushed); + + /* Build and return the tuple. */ + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); +} + + +/* + * Try to evict all shared buffers. + */ +Datum +pg_buffercache_evict_all(PG_FUNCTION_ARGS) +{ + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_ALL_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_ALL_ELEM] = {0}; + + int32 buffers_evicted = 0; + int32 buffers_flushed = 0; + bool flushed; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_evict_all function"))); + + for (int buf = 1; buf <= NBuffers; buf++) + { + if (EvictUnpinnedBuffer(buf, 0, &flushed)) + buffers_evicted++; + if (flushed) + buffers_flushed++; + } + + values[0] = Int32GetDatum(buffers_evicted); + values[1] = Int32GetDatum(buffers_flushed); + + /* Build and return the tuple. */ + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); +} diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index 802a5112d77..d99aa979410 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -27,12 +27,22 @@ <primary>pg_buffercache_evict</primary> </indexterm> + <indexterm> + <primary>pg_buffercache_evict_relation</primary> + </indexterm> + + <indexterm> + <primary>pg_buffercache_evict_all</primary> + </indexterm> + <para> This module provides the <function>pg_buffercache_pages()</function> function (wrapped in the <structname>pg_buffercache</structname> view), the <function>pg_buffercache_summary()</function> function, the - <function>pg_buffercache_usage_counts()</function> function and - the <function>pg_buffercache_evict()</function> function. + <function>pg_buffercache_usage_counts()</function> function, the + <function>pg_buffercache_evict()</function>, the + <function>pg_buffercache_evict_relation()</function> function and the + <function>pg_buffercache_evict_all()</function> function. </para> <para> @@ -65,6 +75,18 @@ function is restricted to superusers only. </para> + <para> + The <function>pg_buffercache_evict_relation()</function> function allows all + shared buffers in the relation to be evicted from the buffer pool given a + relation identifier. Use of this function is restricted to superusers only. + </para> + + <para> + The <function>pg_buffercache_evict_all()</function> function allows all + shared buffers to be evicted in the buffer pool. Use of this function is + restricted to superusers only. + </para> + <sect2 id="pgbuffercache-pg-buffercache"> <title>The <structname>pg_buffercache</structname> View</title> @@ -378,6 +400,36 @@ </para> </sect2> + <sect2 id="pgbuffercache-pg-buffercache-evict-relation"> + <title>The <structname>pg_buffercache_evict_relation</structname> Function</title> + <para> + The <function>pg_buffercache_evict_relation()</function> function is very + similar to the <function>pg_buffercache_evict()</function> function. The + difference is that the <function>pg_buffercache_evict_relation()</function> + takes a relation identifier instead of buffer identifier. Then, it tries + to evict all buffers in that relation. It returns the number of evicted and + flushed buffers. Flushed buffers aren't necessarily flushed by us, they + might be flushed by someone else. The result is immediately out of date + upon return, as the buffer might become valid again at any time due to + concurrent activity. The function is intended for developer testing only. + </para> + </sect2> + + <sect2 id="pgbuffercache-pg-buffercache-evict-all"> + <title>The <structname>pg_buffercache_evict_all</structname> Function</title> + <para> + The <function>pg_buffercache_evict_all()</function> function is very + similar to the <function>pg_buffercache_evict()</function> function. The + difference is, the <function>pg_buffercache_evict_all()</function> function + does not take an argument; instead it tries to evict all buffers in the + buffer pool. It returns the number of evicted and flushed buffers. + Flushed buffers aren't necessarily flushed by us, they might be flushed by + someone else. The result is immediately out of date upon return, as the + buffer might become valid again at any time due to concurrent activity. + The function is intended for developer testing only. + </para> + </sect2> + <sect2 id="pgbuffercache-sample-output"> <title>Sample Output</title> diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index f9681d09e1e..5d82e3fa297 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -6512,26 +6512,47 @@ ResOwnerPrintBufferPin(Datum res) * even by the same block. This inherent raciness without other interlocking * makes the function unsuitable for non-testing usage. * + * buf_state is used to check if the buffer header lock has been acquired + * before calling this function. If buf_state has a non-zero value, it means + * that the buffer header has already been locked. This information is useful + * for evicting a specific relation's buffers, as the buffers' headers need to + * be locked before this function can be called with such an intention. + * + * *flushed is set to true if the buffer was dirty and has been flushed. + * However, this does not necessarily mean that we flushed the buffer, it + * could have been flushed by someone else. + * * Returns true if the buffer was valid and it has now been made invalid. * Returns false if it wasn't valid, if it couldn't be evicted due to a pin, * or if the buffer becomes dirty again while we're trying to write it out. */ bool -EvictUnpinnedBuffer(Buffer buf) +EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed) { BufferDesc *desc; - uint32 buf_state; bool result; - /* Make sure we can pin the buffer. */ - ResourceOwnerEnlarge(CurrentResourceOwner); - ReservePrivateRefCountEntry(); + Assert(flushed); + *flushed = false; Assert(!BufferIsLocal(buf)); desc = GetBufferDescriptor(buf - 1); - /* Lock the header and check if it's valid. */ - buf_state = LockBufHdr(desc); + /* + * If the buffer is already locked, we assume that preparations to pinning + * buffer are already done. + */ + if (!buf_state) + { + /* Make sure we can pin the buffer. */ + ResourceOwnerEnlarge(CurrentResourceOwner); + ReservePrivateRefCountEntry(); + + /* Lock the header */ + buf_state = LockBufHdr(desc); + } + + /* Check if it's valid. */ if ((buf_state & BM_VALID) == 0) { UnlockBufHdr(desc, buf_state); @@ -6553,6 +6574,7 @@ EvictUnpinnedBuffer(Buffer buf) LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_SHARED); FlushBuffer(desc, NULL, IOOBJECT_RELATION, IOCONTEXT_NORMAL); LWLockRelease(BufferDescriptorGetContentLock(desc)); + *flushed = true; } /* This will return false if it becomes dirty or someone else pins it. */ @@ -6563,6 +6585,64 @@ EvictUnpinnedBuffer(Buffer buf) return result; } +/* + * Try to evict all the shared buffers containing provided relation's pages. + * + * This function is intended for testing/development use only! + * + * We need this helper function because of the following reason. + * ReservePrivateRefCountEntry() needs to be called before acquiring the + * buffer header lock but ReservePrivateRefCountEntry() is static and it would + * be better to have it as static. Hence, it can't be called from outside of + * this file. This helper function is created to bypass that problem. + * + * Before calling this function, it is important to acquire + * AccessExclusiveLock on the specified relation to avoid replacing the + * current block of this relation with another one during execution. + + * buffers_evicted and buffers_flushed are set the total number of buffers + * evicted and flushed respectively. + */ +void +EvictRelUnpinnedBuffers(Relation rel, int32 *buffers_evicted, int32 *buffers_flushed) +{ + bool flushed; + + Assert(buffers_evicted && buffers_flushed); + *buffers_evicted = *buffers_flushed = 0; + + Assert(!RelationUsesLocalBuffers(rel)); + + for (int buf = 1; buf <= NBuffers; buf++) + { + uint32 buf_state = 0; + BufferDesc *bufHdr = GetBufferDescriptor(buf - 1); + + /* An unlocked precheck should be safe and saves some cycles. */ + if (!BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator)) + continue; + + /* Make sure we can pin the buffer. */ + ResourceOwnerEnlarge(CurrentResourceOwner); + ReservePrivateRefCountEntry(); + + /* + * No need to call UnlockBufHdr() if BufTagMatchesRelFileLocator() + * returns true, EvictUnpinnedBuffer() will take care of it. + */ + buf_state = LockBufHdr(bufHdr); + if (BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator)) + { + if (EvictUnpinnedBuffer(buf, buf_state, &flushed)) + (*buffers_evicted)++; + if (flushed) + (*buffers_flushed)++; + } + else + UnlockBufHdr(bufHdr, buf_state); + } +} + /* * Generic implementation of the AIO handle staging callback for readv/writev * on local/shared buffers. diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index f2192ceb271..ac6a2d521f3 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -304,7 +304,9 @@ extern uint32 GetAdditionalLocalPinLimit(void); extern void LimitAdditionalPins(uint32 *additional_pins); extern void LimitAdditionalLocalPins(uint32 *additional_pins); -extern bool EvictUnpinnedBuffer(Buffer buf); +extern bool EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed); +extern void EvictRelUnpinnedBuffers(Relation rel, int32 *buffers_evicted, + int32 *buffers_flushed); /* in buf_init.c */ extern void BufferManagerShmemInit(void); -- 2.43.0 [application/octet-stream] v5-0002-Return-buffer-flushed-information-in-pg_buffercac.patch (4.5K, ../../CAN55FZ0T60_DOtbrJUGvy=-qOjA4EJ79+TzVAKPvcWCGDZ5JrA@mail.gmail.com/3-v5-0002-Return-buffer-flushed-information-in-pg_buffercac.patch) download | inline diff: From 4d6b08f5680ecf7f34578f52945d448007bbc0df Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Mon, 24 Mar 2025 13:53:26 +0300 Subject: [PATCH v5 2/3] Return buffer flushed information in pg_buffercache_evict function Prior commit added ability to get buffer flushed information from EvictUnpinnedBuffer() function. Show this information in pg_buffercache_evict() function too. Author: Nazir Bilal Yavuz <[email protected]> Suggested-by: Joseph Koshakow <[email protected]> Reviewed-by: Aidar Imamov <[email protected]> Discussion: https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com --- .../pg_buffercache--1.5--1.6.sql | 8 ++++++++ contrib/pg_buffercache/pg_buffercache_pages.c | 20 ++++++++++++++++++- doc/src/sgml/pgbuffercache.sgml | 15 ++++++++------ 3 files changed, 36 insertions(+), 7 deletions(-) diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql index 2e255f3fc10..d54bb1fd6f8 100644 --- a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -1,5 +1,13 @@ \echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.6'" to load this file. \quit +DROP FUNCTION pg_buffercache_evict(integer); +CREATE FUNCTION pg_buffercache_evict( + IN int, + OUT evicted boolean, + OUT flushed boolean) +AS 'MODULE_PATHNAME', 'pg_buffercache_evict' +LANGUAGE C PARALLEL SAFE VOLATILE STRICT; + CREATE FUNCTION pg_buffercache_evict_relation( IN regclass, OUT buffers_evicted int4, diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index fa8aae43afd..2f275354c56 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -22,6 +22,7 @@ #define NUM_BUFFERCACHE_PAGES_ELEM 9 #define NUM_BUFFERCACHE_SUMMARY_ELEM 5 #define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4 +#define NUM_BUFFERCACHE_EVICT_ELEM 2 #define NUM_BUFFERCACHE_EVICT_RELATION_ELEM 2 #define NUM_BUFFERCACHE_EVICT_ALL_ELEM 2 @@ -365,7 +366,17 @@ pg_buffercache_usage_counts(PG_FUNCTION_ARGS) Datum pg_buffercache_evict(PG_FUNCTION_ARGS) { + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_ELEM] = {0}; + Buffer buf = PG_GETARG_INT32(0); + bool flushed; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); if (!superuser()) ereport(ERROR, @@ -375,7 +386,14 @@ pg_buffercache_evict(PG_FUNCTION_ARGS) if (buf < 1 || buf > NBuffers) elog(ERROR, "bad buffer ID: %d", buf); - PG_RETURN_BOOL(EvictUnpinnedBuffer(buf)); + values[0] = BoolGetDatum(EvictUnpinnedBuffer(buf, 0, &flushed)); + values[1] = BoolGetDatum(flushed); + + /* Build and return the tuple. */ + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); } /* diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index d99aa979410..681c74251d4 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -391,12 +391,15 @@ <para> The <function>pg_buffercache_evict()</function> function takes a buffer identifier, as shown in the <structfield>bufferid</structfield> column of - the <structname>pg_buffercache</structname> view. It returns true on success, - and false if the buffer wasn't valid, if it couldn't be evicted because it - was pinned, or if it became dirty again after an attempt to write it out. - The result is immediately out of date upon return, as the buffer might - become valid again at any time due to concurrent activity. The function is - intended for developer testing only. + the <structname>pg_buffercache</structname> view. It returns the + information about whether the buffer is evicted and flushed. evicted + column is true on success, and false if the buffer wasn't valid, if it + couldn't be evicted because it was pinned, or if it became dirty again + after an attempt to write it out. flushed column is true if the buffer is + flushed. This does not necessarily mean that buffer is flushed by us, it + might be flushed by someone else. The result is immediately out of date + upon return, as the buffer might become valid again at any time due to + concurrent activity. The function is intended for developer testing only. </para> </sect2> -- 2.43.0 [application/octet-stream] v5-0003-Add-pg_buffercache_mark_dirty-_all-functions-for-.patch (9.5K, ../../CAN55FZ0T60_DOtbrJUGvy=-qOjA4EJ79+TzVAKPvcWCGDZ5JrA@mail.gmail.com/4-v5-0003-Add-pg_buffercache_mark_dirty-_all-functions-for-.patch) download | inline diff: From 570bdcc5387dc829eb891a58ef5fa4533a5982e1 Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Wed, 25 Dec 2024 15:46:10 +0300 Subject: [PATCH v5 3/3] Add pg_buffercache_mark_dirty[_all]() functions for testing This commit introduces two new functions for marking shared buffers as dirty: pg_buffercache_mark_dirty(): Marks a specific shared buffer as dirty. pg_buffercache_mark_dirty_all(): Marks all shared buffers as dirty in a single operation. The pg_buffercache_mark_dirty_all() function provides an efficient way to dirty the entire buffer pool (e.g., ~550ms vs. ~70ms for 16GB of shared buffers), complementing pg_buffercache_mark_dirty() for more granular control. These functions are intended for developer testing and debugging scenarios, enabling users to simulate various buffer pool states and test write-back behavior. Both functions are superuser-only. Author: Nazir Bilal Yavuz <[email protected]> Reviewed-by: Andres Freund <[email protected]> Reviewed-by: Aidar Imamov <[email protected]> Reviewed-by: Joseph Koshakow <[email protected]> Discussion: https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com --- .../pg_buffercache--1.5--1.6.sql | 10 ++++ contrib/pg_buffercache/pg_buffercache_pages.c | 43 +++++++++++++++ doc/src/sgml/pgbuffercache.sgml | 54 +++++++++++++++++-- src/backend/storage/buffer/bufmgr.c | 54 +++++++++++++++++++ src/include/storage/bufmgr.h | 1 + 5 files changed, 159 insertions(+), 3 deletions(-) diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql index d54bb1fd6f8..b40ee2599a4 100644 --- a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -20,3 +20,13 @@ CREATE FUNCTION pg_buffercache_evict_all( OUT buffers_flushed int4) AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all' LANGUAGE C PARALLEL SAFE VOLATILE; + +CREATE FUNCTION pg_buffercache_mark_dirty(IN int) +RETURNS bool +AS 'MODULE_PATHNAME', 'pg_buffercache_mark_dirty' +LANGUAGE C PARALLEL SAFE VOLATILE STRICT; + +CREATE FUNCTION pg_buffercache_mark_dirty_all() +RETURNS INT4 +AS 'MODULE_PATHNAME', 'pg_buffercache_mark_dirty_all' +LANGUAGE C PARALLEL SAFE VOLATILE; diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index 2f275354c56..23415016a8f 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -75,6 +75,8 @@ PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts); PG_FUNCTION_INFO_V1(pg_buffercache_evict); PG_FUNCTION_INFO_V1(pg_buffercache_evict_relation); PG_FUNCTION_INFO_V1(pg_buffercache_evict_all); +PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty); +PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty_all); Datum pg_buffercache_pages(PG_FUNCTION_ARGS) @@ -490,3 +492,44 @@ pg_buffercache_evict_all(PG_FUNCTION_ARGS) PG_RETURN_DATUM(result); } + +/* + * Try to mark a shared buffer as dirty. + */ +Datum +pg_buffercache_mark_dirty(PG_FUNCTION_ARGS) +{ + Buffer buf = PG_GETARG_INT32(0); + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_mark_dirty function"))); + + if (buf < 1 || buf > NBuffers) + elog(ERROR, "bad buffer ID: %d", buf); + + PG_RETURN_BOOL(MarkUnpinnedBufferDirty(buf)); +} + +/* + * Try to mark all the shared buffers as dirty. + */ +Datum +pg_buffercache_mark_dirty_all(PG_FUNCTION_ARGS) +{ + int32 buffers_dirtied = 0; + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use pg_buffercache_mark_dirty_all function"))); + + for (int buf = 1; buf <= NBuffers; buf++) + { + if (MarkUnpinnedBufferDirty(buf)) + buffers_dirtied++; + } + + PG_RETURN_INT32(buffers_dirtied); +} diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index 681c74251d4..baf64e07b4e 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -35,14 +35,24 @@ <primary>pg_buffercache_evict_all</primary> </indexterm> + <indexterm> + <primary>pg_buffercache_mark_dirty</primary> + </indexterm> + + <indexterm> + <primary>pg_buffercache_mark_dirty_all</primary> + </indexterm> + <para> This module provides the <function>pg_buffercache_pages()</function> function (wrapped in the <structname>pg_buffercache</structname> view), the <function>pg_buffercache_summary()</function> function, the <function>pg_buffercache_usage_counts()</function> function, the - <function>pg_buffercache_evict()</function>, the - <function>pg_buffercache_evict_relation()</function> function and the - <function>pg_buffercache_evict_all()</function> function. + <function>pg_buffercache_evict()</function> function, the + <function>pg_buffercache_evict_relation()</function> function, the + <function>pg_buffercache_evict_all()</function> function, the + <function>pg_buffercache_mark_dirty()</function> function and the + <function>pg_buffercache_mark_dirty_all()</function> function. </para> <para> @@ -87,6 +97,18 @@ restricted to superusers only. </para> + <para> + The <function>pg_buffercache_mark_dirty()</function> function allows a block + to be marked as dirty from the buffer pool given a buffer identifier. Use of + this function is restricted to superusers only. + </para> + + <para> + The <function>pg_buffercache_mark_dirty_all()</function> function tries to + mark all buffers dirty in the buffer pool. Use of this function is + restricted to superusers only. + </para> + <sect2 id="pgbuffercache-pg-buffercache"> <title>The <structname>pg_buffercache</structname> View</title> @@ -433,6 +455,32 @@ </para> </sect2> + <sect2 id="pgbuffercache-pg-buffercache-mark-dirty"> + <title>The <structname>pg_buffercache_mark_dirty</structname> Function</title> + <para> + The <function>pg_buffercache_mark_dirty()</function> function takes a + buffer identifier, as shown in the <structfield>bufferid</structfield> + column of the <structname>pg_buffercache</structname> view. It returns + true on success, and false if the buffer wasn't valid or if it couldn't be + marked as dirty because it was pinned. The result is immediately out of + date upon return, as the buffer might become valid again at any time due to + concurrent activity. The function is intended for developer testing only. + </para> + </sect2> + + <sect2 id="pgbuffercache-pg-buffercache-mark-dirty-all"> + <title>The <structname>pg_buffercache_mark_dirty_all</structname> Function</title> + <para> + The <function>pg_buffercache_mark_dirty_all()</function> function is very + similar to the <function>pg_buffercache_mark_dirty()</function> function. + The difference is, the <function>pg_buffercache_mark_dirty_all()</function> + function does not take an argument; instead it tries to mark all buffers + dirty in the buffer pool. The result is immediately out of date upon + return, as the buffer might become valid again at any time due to + concurrent activity. The function is intended for developer testing only. + </para> + </sect2> + <sect2 id="pgbuffercache-sample-output"> <title>Sample Output</title> diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 5d82e3fa297..1f902c4b54f 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -6643,6 +6643,60 @@ EvictRelUnpinnedBuffers(Relation rel, int32 *buffers_evicted, int32 *buffers_flu } } +/* + * Try to mark the provided shared buffer as dirty. + * + * This function is intended for testing/development use only! + * + * Same as EvictUnpinnedBuffer() but with MarkBufferDirty() call inside. + * + * Returns true if the buffer was already dirty or it has successfully been + * marked as dirty. + */ +bool +MarkUnpinnedBufferDirty(Buffer buf) +{ + BufferDesc *desc; + uint32 buf_state; + + Assert(!BufferIsLocal(buf)); + + /* Make sure we can pin the buffer. */ + ResourceOwnerEnlarge(CurrentResourceOwner); + ReservePrivateRefCountEntry(); + + desc = GetBufferDescriptor(buf - 1); + + /* Lock the header and check if it's valid. */ + buf_state = LockBufHdr(desc); + if ((buf_state & BM_VALID) == 0) + { + UnlockBufHdr(desc, buf_state); + return false; + } + + /* Check that it's not pinned already. */ + if (BUF_STATE_GET_REFCOUNT(buf_state) > 0) + { + UnlockBufHdr(desc, buf_state); + return false; + } + + PinBuffer_Locked(desc); /* releases spinlock */ + + /* If it was not already dirty, mark it as dirty. */ + if (!(buf_state & BM_DIRTY)) + { + LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_EXCLUSIVE); + MarkBufferDirty(buf); + LWLockRelease(BufferDescriptorGetContentLock(desc)); + } + + UnpinBuffer(desc); + + return true; +} + /* * Generic implementation of the AIO handle staging callback for readv/writev * on local/shared buffers. diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index ac6a2d521f3..a2e65a1d918 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -307,6 +307,7 @@ extern void LimitAdditionalLocalPins(uint32 *additional_pins); extern bool EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed); extern void EvictRelUnpinnedBuffers(Relation rel, int32 *buffers_evicted, int32 *buffers_flushed); +extern bool MarkUnpinnedBufferDirty(Buffer buf); /* in buf_init.c */ extern void BufferManagerShmemInit(void); -- 2.43.0 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-04-10 20:05 Robert Haas <[email protected]> parent: Aidar Imamov <[email protected]> 2 siblings, 1 reply; 18+ messages in thread From: Robert Haas @ 2025-04-10 20:05 UTC (permalink / raw) To: Aidar Imamov <[email protected]>; +Cc: Nazir Bilal Yavuz <[email protected]>; Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected] On Tue, Mar 18, 2025 at 6:03 PM Aidar Imamov <[email protected]> wrote: > > for (int buf = 1; buf < NBuffers; buf++) > Mb it would be more correct to use <= NBuffers? I agree that (int buf = 1; buf < NBuffers; buf++) isn't right because that iterates one fewer times than the number of buffers. What was ultimately committed was: + for (int buf = 1; buf <= NBuffers; buf++) + { + BufferDesc *desc = GetBufferDescriptor(buf - 1); Curiously, there is no other instance of <= NBuffers in the code. Elsewhere we instead do: for (i = 0; i < NBuffers; i++) { BufferDesc *bufHdr = GetBufferDescriptor(i); Or in BufferSync: for (buf_id = 0; buf_id < NBuffers; buf_id++) { BufferDesc *bufHdr = GetBufferDescriptor(buf_id); Nonetheless what was committed seems pretty defensible, because we have lots of other places that do GetBufferDescriptor(buffer - 1) and similar. Alternating between 0-based indexing and 1-based indexing like this seems rather error-prone somehow. :-( -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-04-11 08:02 Nazir Bilal Yavuz <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Nazir Bilal Yavuz @ 2025-04-11 08:02 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Aidar Imamov <[email protected]>; Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected] Hi, Thank you for looking into this! On Thu, 10 Apr 2025 at 23:06, Robert Haas <[email protected]> wrote: > > On Tue, Mar 18, 2025 at 6:03 PM Aidar Imamov <[email protected]> wrote: > > > for (int buf = 1; buf < NBuffers; buf++) > > Mb it would be more correct to use <= NBuffers? > > I agree that (int buf = 1; buf < NBuffers; buf++) isn't right because > that iterates one fewer times than the number of buffers. What was > ultimately committed was: > > + for (int buf = 1; buf <= NBuffers; buf++) > + { > + BufferDesc *desc = GetBufferDescriptor(buf - 1); > > Curiously, there is no other instance of <= NBuffers in the code. > Elsewhere we instead do: > > for (i = 0; i < NBuffers; i++) > { > BufferDesc *bufHdr = GetBufferDescriptor(i); > > Or in BufferSync: > > for (buf_id = 0; buf_id < NBuffers; buf_id++) > { > BufferDesc *bufHdr = GetBufferDescriptor(buf_id); > > Nonetheless what was committed seems pretty defensible, because we > have lots of other places that do GetBufferDescriptor(buffer - 1) and > similar. Alternating between 0-based indexing and 1-based indexing > like this seems rather error-prone somehow. :-( I understand your point. I did it like that because bufferids start from 1 and go to NBuffers inclusive in the pg_buffercache view, so it seems more natural to me to implement it like that. I am okay to replace these loops with [1] to make it standart everywhere: [1] for (int buf = 0; buf < NBuffers; buf++) { BufferDesc *desc = GetBufferDescriptor(buf); -- Regards, Nazir Bilal Yavuz Microsoft ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions @ 2025-04-11 11:43 Robert Haas <[email protected]> parent: Nazir Bilal Yavuz <[email protected]> 0 siblings, 0 replies; 18+ messages in thread From: Robert Haas @ 2025-04-11 11:43 UTC (permalink / raw) To: Nazir Bilal Yavuz <[email protected]>; +Cc: Aidar Imamov <[email protected]>; Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected] On Fri, Apr 11, 2025 at 4:02 AM Nazir Bilal Yavuz <[email protected]> wrote: > I understand your point. I did it like that because bufferids start > from 1 and go to NBuffers inclusive in the pg_buffercache view, so it > seems more natural to me to implement it like that. I am okay to > replace these loops with [1] to make it standart everywhere: > > [1] > for (int buf = 0; buf < NBuffers; buf++) > { > BufferDesc *desc = GetBufferDescriptor(buf); I'm more making an observation than asking for a change. If you and others think it should be changed, that is fine, but I'm uncertain myself what we ought to be doing here. I just wanted to raise the issue. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2025-04-11 11:43 UTC | newest] Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-10-17 19:10 [PATCH v2] explain: show JIT details in non-text format, even if zero Justin Pryzby <[email protected]> 2020-10-17 19:10 [PATCH v1] explain: show JIT details in non-text format, even if zero Justin Pryzby <[email protected]> 2024-12-25 12:57 Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Nazir Bilal Yavuz <[email protected]> 2025-02-17 16:59 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Andres Freund <[email protected]> 2025-03-12 16:15 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Nazir Bilal Yavuz <[email protected]> 2025-03-14 13:25 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Nazir Bilal Yavuz <[email protected]> 2025-03-18 22:02 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Aidar Imamov <[email protected]> 2025-03-20 22:25 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Joseph Koshakow <[email protected]> 2025-03-24 12:39 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Nazir Bilal Yavuz <[email protected]> 2025-03-24 12:55 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Nazir Bilal Yavuz <[email protected]> 2025-03-27 22:53 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Aidar Imamov <[email protected]> 2025-03-28 10:37 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Nazir Bilal Yavuz <[email protected]> 2025-03-31 13:37 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Aidar Imamov <[email protected]> 2025-03-31 16:49 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Nazir Bilal Yavuz <[email protected]> 2025-03-24 12:39 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Nazir Bilal Yavuz <[email protected]> 2025-04-10 20:05 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Robert Haas <[email protected]> 2025-04-11 08:02 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Nazir Bilal Yavuz <[email protected]> 2025-04-11 11:43 ` Re: Add pg_buffercache_evict_all() and pg_buffercache_mark_dirty[_all]() functions Robert Haas <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox