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
From 813e5ec0da4c65970b4b1ce2ec2918e4652da9ab Mon Sep 17 00:00:00 2001
From: Nazir Bilal Yavuz <byavu...@gmail.com>
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
From 19b0a56e72525ee1c9754fb06ab5f31d41e43ca7 Mon Sep 17 00:00:00 2001
From: Nazir Bilal Yavuz <byavu...@gmail.com>
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
