agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Bertrand Drouvot <[email protected]>
Subject: [PATCH v1 2/2] Add per-backend lock statistics
Date: Wed, 3 Jun 2026 13:05:03 +0000
This commit adds per-backend lock statistics, providing the same information as
pg_stat_lock, except that it is now possible to retrieve those stats (lock wait
counts, wait times, and fast-path exceeded count) on a per-backend basis.
This data can be retrieved with a new system function called
pg_stat_get_backend_lock(), that returns one tuple per lock type based on the PID
provided in input. Like pg_stat_get_backend_io(), this is useful when joined
with pg_stat_activity to get a live picture of the locks behavior for each running
backend.
pgstat_flush_backend() gains a new flag value, able to control the flush of the
lock stats.
This commit relies mostly on the infrastructure provided by 9aea73fc61d4, that
has introduced backend statistics.
XXX: Bump catalog version. A bump of PGSTAT_FILE_FORMAT_ID is not required,
as backend stats do not persist on disk.
Author: Bertrand Drouvot <[email protected]>
Reviewed-by:
Discussion:
---
doc/src/sgml/monitoring.sgml | 19 ++++++
src/backend/utils/activity/pgstat_backend.c | 70 +++++++++++++++++++++
src/backend/utils/activity/pgstat_lock.c | 4 ++
src/backend/utils/adt/pgstatfuncs.c | 29 ++++++++-
src/include/catalog/pg_proc.dat | 8 +++
src/include/pgstat.h | 11 ++++
src/include/utils/pgstat_internal.h | 3 +-
src/test/regress/expected/stats.out | 12 ++++
src/test/regress/sql/stats.sql | 9 +++
9 files changed, 162 insertions(+), 3 deletions(-)
14.7% doc/src/sgml/
38.6% src/backend/utils/activity/
14.8% src/backend/utils/adt/
8.7% src/include/catalog/
9.9% src/include/
6.8% src/test/regress/expected/
6.1% src/test/regress/sql/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 08d5b824552..3936fb62a5d 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5545,6 +5545,25 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry id="pg-stat-get-backend-lock" role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_stat_get_backend_lock</primary>
+ </indexterm>
+ <function>pg_stat_get_backend_lock</function> ( <type>integer</type> )
+ <returnvalue>setof record</returnvalue>
+ </para>
+ <para>
+ Returns lock statistics about the backend with the specified
+ process ID. The output fields are exactly the same as the ones in the
+ <structname>pg_stat_lock</structname> view.
+ </para>
+ <para>
+ The function does not return lock statistics for the checkpointer,
+ the background writer, the startup process and the autovacuum launcher.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index 73461c9bca5..297eda0a489 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -39,6 +39,7 @@
*/
static PgStat_BackendPending PendingBackendStats;
static bool backend_has_iostats = false;
+static bool backend_has_lockstats = false;
/*
* WAL usage counters saved from pgWalUsage at the previous call to
@@ -86,6 +87,37 @@ pgstat_count_backend_io_op(IOObject io_object, IOContext io_context,
pgstat_report_fixed = true;
}
+/*
+ * Utility routines to report lock stats for backends, kept here to avoid
+ * exposing PendingBackendStats to the outside world.
+ */
+void
+pgstat_count_backend_lock_waits(uint8 locktag_type, long msecs)
+{
+ if (!pgstat_tracks_backend_bktype(MyBackendType))
+ return;
+
+ Assert(locktag_type <= LOCKTAG_LAST_TYPE);
+ PendingBackendStats.pending_lock.stats[locktag_type].waits++;
+ PendingBackendStats.pending_lock.stats[locktag_type].wait_time += (PgStat_Counter) msecs;
+
+ backend_has_lockstats = true;
+ pgstat_report_fixed = true;
+}
+
+void
+pgstat_count_backend_lock_fastpath_exceeded(uint8 locktag_type)
+{
+ if (!pgstat_tracks_backend_bktype(MyBackendType))
+ return;
+
+ Assert(locktag_type <= LOCKTAG_LAST_TYPE);
+ PendingBackendStats.pending_lock.stats[locktag_type].fastpath_exceeded++;
+
+ backend_has_lockstats = true;
+ pgstat_report_fixed = true;
+}
+
/*
* Returns statistics of a backend by proc number.
*/
@@ -262,6 +294,36 @@ pgstat_flush_backend_entry_wal(PgStat_EntryRef *entry_ref)
prevBackendWalUsage = pgWalUsage;
}
+/*
+ * Flush out locally pending backend lock statistics. Locking is managed
+ * by the caller.
+ */
+static void
+pgstat_flush_backend_entry_lock(PgStat_EntryRef *entry_ref)
+{
+ PgStatShared_Backend *shbackendent;
+ PgStat_PendingLock *bktype_shstats;
+
+ if (!backend_has_lockstats)
+ return;
+
+ shbackendent = (PgStatShared_Backend *) entry_ref->shared_stats;
+ bktype_shstats = &shbackendent->stats.lock_stats;
+
+ for (int i = 0; i <= LOCKTAG_LAST_TYPE; i++)
+ {
+#define LOCKSTAT_ACC(fld) \
+ (bktype_shstats->stats[i].fld += PendingBackendStats.pending_lock.stats[i].fld)
+ LOCKSTAT_ACC(waits);
+ LOCKSTAT_ACC(wait_time);
+ LOCKSTAT_ACC(fastpath_exceeded);
+#undef LOCKSTAT_ACC
+ }
+
+ MemSet(&PendingBackendStats.pending_lock, 0, sizeof(PgStat_PendingLock));
+ backend_has_lockstats = false;
+}
+
/*
* Flush out locally pending backend statistics
*
@@ -286,6 +348,10 @@ pgstat_flush_backend(bool nowait, uint32 flags)
pgstat_backend_wal_have_pending())
has_pending_data = true;
+ /* Some lock data pending? */
+ if ((flags & PGSTAT_BACKEND_FLUSH_LOCK) && backend_has_lockstats)
+ has_pending_data = true;
+
if (!has_pending_data)
return false;
@@ -301,6 +367,9 @@ pgstat_flush_backend(bool nowait, uint32 flags)
if (flags & PGSTAT_BACKEND_FLUSH_WAL)
pgstat_flush_backend_entry_wal(entry_ref);
+ if (flags & PGSTAT_BACKEND_FLUSH_LOCK)
+ pgstat_flush_backend_entry_lock(entry_ref);
+
pgstat_unlock_entry(entry_ref);
return false;
@@ -339,6 +408,7 @@ pgstat_create_backend(ProcNumber procnum)
MemSet(&PendingBackendStats, 0, sizeof(PgStat_BackendPending));
backend_has_iostats = false;
+ backend_has_lockstats = false;
/*
* Initialize prevBackendWalUsage with pgWalUsage so that
diff --git a/src/backend/utils/activity/pgstat_lock.c b/src/backend/utils/activity/pgstat_lock.c
index aec64f8fb4b..76116db3593 100644
--- a/src/backend/utils/activity/pgstat_lock.c
+++ b/src/backend/utils/activity/pgstat_lock.c
@@ -131,6 +131,8 @@ pgstat_count_lock_fastpath_exceeded(uint8 locktag_type)
PendingLockStats.stats[locktag_type].fastpath_exceeded++;
have_lockstats = true;
pgstat_report_fixed = true;
+
+ pgstat_count_backend_lock_fastpath_exceeded(locktag_type);
}
/*
@@ -147,4 +149,6 @@ pgstat_count_lock_waits(uint8 locktag_type, long msecs)
PendingLockStats.stats[locktag_type].wait_time += (PgStat_Counter) msecs;
have_lockstats = true;
pgstat_report_fixed = true;
+
+ pgstat_count_backend_lock_waits(locktag_type, msecs);
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 353607954ad..3f7c238e557 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1740,8 +1740,8 @@ pg_stat_get_wal(PG_FUNCTION_ARGS)
/*
* pg_stat_lock_build_tuples
*
- * Helper routine for pg_stat_get_lock(), filling a result tuplestore with one
- * tuple for each lock type.
+ * Helper routine for pg_stat_get_lock() and pg_stat_get_backend_lock(),
+ * filling a result tuplestore with one tuple for each lock type.
*/
static void
pg_stat_lock_build_tuples(ReturnSetInfo *rsinfo,
@@ -1788,6 +1788,31 @@ pg_stat_get_lock(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+/*
+ * Returns lock statistics for a backend with given PID.
+ */
+Datum
+pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
+{
+ int pid;
+ ReturnSetInfo *rsinfo;
+ PgStat_Backend *backend_stats;
+
+ InitMaterializedSRF(fcinfo, 0);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ pid = PG_GETARG_INT32(0);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
+
+ if (!backend_stats)
+ return (Datum) 0;
+
+ pg_stat_lock_build_tuples(rsinfo, backend_stats->lock_stats.stats,
+ backend_stats->stat_reset_timestamp);
+
+ return (Datum) 0;
+}
+
/*
* Returns statistics of SLRU caches.
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index be157a5fbe9..87f7161527e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6092,6 +6092,14 @@
proargmodes => '{i,o,o,o,o,o,o}',
proargnames => '{backend_pid,wal_records,wal_fpi,wal_bytes,wal_fpi_bytes,wal_buffers_full,stats_reset}',
prosrc => 'pg_stat_get_backend_wal' },
+{ oid => '9682', descr => 'statistics: backend lock statistics',
+ proname => 'pg_stat_get_backend_lock', prorows => '10', proretset => 't',
+ provolatile => 'v', proparallel => 'r', prorettype => 'record',
+ proargtypes => 'int4',
+ proallargtypes => '{int4,text,int8,int8,int8,timestamptz}',
+ proargmodes => '{i,o,o,o,o,o}',
+ proargnames => '{backend_pid,locktype,waits,wait_time,fastpath_exceeded,stats_reset}',
+ prosrc => 'pg_stat_get_backend_lock' },
{ oid => '6248', descr => 'statistics: information about WAL prefetching',
proname => 'pg_stat_get_recovery_prefetch', prorows => '1', proretset => 't',
provolatile => 'v', prorettype => 'record', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index dfa2e837638..b5e69e6250e 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -523,6 +523,7 @@ typedef struct PgStat_Backend
TimestampTz stat_reset_timestamp;
PgStat_BktypeIO io_stats;
PgStat_WalCounters wal_counters;
+ PgStat_PendingLock lock_stats;
} PgStat_Backend;
/* ---------
@@ -535,6 +536,12 @@ typedef struct PgStat_BackendPending
* Backend statistics store the same amount of IO data as PGSTAT_KIND_IO.
*/
PgStat_PendingIO pending_io;
+
+ /*
+ * Backend statistics store the same amount of lock data as
+ * PGSTAT_KIND_LOCK.
+ */
+ PgStat_PendingLock pending_lock;
} PgStat_BackendPending;
/*
@@ -586,6 +593,10 @@ extern void pgstat_count_backend_io_op(IOObject io_object,
IOContext io_context,
IOOp io_op, uint32 cnt,
uint64 bytes);
+
+/* used by pgstat_lock.c for lock stats tracked in backends */
+extern void pgstat_count_backend_lock_waits(uint8 locktag_type, long msecs);
+extern void pgstat_count_backend_lock_fastpath_exceeded(uint8 locktag_type);
extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber);
extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid,
BackendType *bktype);
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index fe463faaf63..b0788336ae3 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -705,7 +705,8 @@ extern void pgstat_archiver_snapshot_cb(void);
/* flags for pgstat_flush_backend() */
#define PGSTAT_BACKEND_FLUSH_IO (1 << 0) /* Flush I/O statistics */
#define PGSTAT_BACKEND_FLUSH_WAL (1 << 1) /* Flush WAL statistics */
-#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL)
+#define PGSTAT_BACKEND_FLUSH_LOCK (1 << 2) /* Flush lock statistics */
+#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL | PGSTAT_BACKEND_FLUSH_LOCK)
extern bool pgstat_flush_backend(bool nowait, uint32 flags);
extern bool pgstat_backend_flush_cb(bool nowait);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index bbb1db3c433..fa550676f83 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -2019,6 +2019,10 @@ BEGIN
END;
$$;
SELECT fastpath_exceeded AS fastpath_exceeded_before FROM pg_stat_lock WHERE locktype = 'relation' \gset
+-- Test pg_stat_get_backend_lock()
+SELECT fastpath_exceeded AS backend_fastpath_exceeded_before
+ FROM pg_stat_get_backend_lock(pg_backend_pid())
+ WHERE locktype = 'relation' \gset
-- Needs a lock on each partition
SELECT count(*) FROM part_test;
count
@@ -2039,5 +2043,13 @@ SELECT fastpath_exceeded > :fastpath_exceeded_before FROM pg_stat_lock WHERE loc
t
(1 row)
+SELECT fastpath_exceeded > :backend_fastpath_exceeded_before
+ FROM pg_stat_get_backend_lock(pg_backend_pid())
+ WHERE locktype = 'relation';
+ ?column?
+----------
+ t
+(1 row)
+
DROP TABLE part_test;
-- End of Stats Test
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 610fd21fae4..f5683302a75 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -998,6 +998,11 @@ $$;
SELECT fastpath_exceeded AS fastpath_exceeded_before FROM pg_stat_lock WHERE locktype = 'relation' \gset
+-- Test pg_stat_get_backend_lock()
+SELECT fastpath_exceeded AS backend_fastpath_exceeded_before
+ FROM pg_stat_get_backend_lock(pg_backend_pid())
+ WHERE locktype = 'relation' \gset
+
-- Needs a lock on each partition
SELECT count(*) FROM part_test;
@@ -1006,6 +1011,10 @@ SELECT pg_stat_force_next_flush();
SELECT fastpath_exceeded > :fastpath_exceeded_before FROM pg_stat_lock WHERE locktype = 'relation';
+SELECT fastpath_exceeded > :backend_fastpath_exceeded_before
+ FROM pg_stat_get_backend_lock(pg_backend_pid())
+ WHERE locktype = 'relation';
+
DROP TABLE part_test;
-- End of Stats Test
--
2.34.1
--caEBMJLxMU8EntZL--
view thread (1157+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v1 2/2] Add per-backend lock statistics
In-Reply-To: <no-message-id-483446@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox