public inbox for [email protected]
help / color / mirror / Atom feedRe: Adding locks statistics
5+ messages / 3 participants
[nested] [flat]
* Re: Adding locks statistics
@ 2026-02-13 07:36 Michael Paquier <[email protected]>
2026-02-13 10:24 ` Re: Adding locks statistics Bertrand Drouvot <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Paquier @ 2026-02-13 07:36 UTC (permalink / raw)
To: Bertrand Drouvot <[email protected]>; +Cc: Jeff Davis <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
On Tue, Feb 10, 2026 at 07:30:50AM +0000, Bertrand Drouvot wrote:
> New rebase due to 73d60ac385a.
I have been looking at this patch, and can get behind the data
gathered here in terms of being able to tune things, but not all. See
below for the details of my reasoning.
max_locks_per_xact is a PGC_POSTMASTER, so backend-level stats with
this data would not be relevant for its tuning. However, something
else can be said about deadlock_timeout, where one could rely on the
data gathered by this view to set it on a backend basis, particularly
if the load pattern is divided into a subsets of connections (say few
backend see a lot of the deadlock_timeout, for example). Same
argument for lock_timeout, which is user-settable.
As the set of data gathered, I think that I'm OK with timeouts (for
lock_timeout), deadlock_timeouts (for deadlock_timeout), fastpath (for
max_locks_per_xact), that can all be compared with the number of
requests.
Regarding "deadlocks" and "waits", these two are less useful than the
three others because not really actionable. They would become much
more relevant if and only if we know the distribution of the deadlocks
not only for the lock types, but for the objects involved, especially
if the activity is diluted across many objects. So these have less
value IMO because they are not really actionable in the system. The
other three can be directly tuned based on the GUCs we have.
So my suggestion for the moment would be to be more frugal (yeah I
know, sorry..) and limit ourselves to four fields: deadlock_timeout,
requests, fastpath and timeouts. Three fields to compare with
requests, one for each GUC.
Regarding the implementation, you are right to use a fixed-sized stats
kind for the job. I can see a lot of code has been copy-pasted from
pgstat_io.c, then slightly adjusted to fit into the picture. That's
fine here, it makes the implementation straight-forward to read.
Regarding the documentation, listing all the values for locktype is a
recipe for rot. I'd suggest to remove the list instead, with only a
link referring to pg_locks to avoid the duplication.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Adding locks statistics
2026-02-13 07:36 Re: Adding locks statistics Michael Paquier <[email protected]>
@ 2026-02-13 10:24 ` Bertrand Drouvot <[email protected]>
2026-02-13 21:13 ` Re: Adding locks statistics Andres Freund <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Bertrand Drouvot @ 2026-02-13 10:24 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Jeff Davis <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
Hi,
On Fri, Feb 13, 2026 at 04:36:57PM +0900, Michael Paquier wrote:
> On Tue, Feb 10, 2026 at 07:30:50AM +0000, Bertrand Drouvot wrote:
> > New rebase due to 73d60ac385a.
>
> I have been looking at this patch, and can get behind the data
> gathered here in terms of being able to tune things
Thanks!
>
> So my suggestion for the moment would be to be more frugal (yeah I
> know, sorry..) and limit ourselves to four fields: deadlock_timeout,
> requests, fastpath and timeouts. Three fields to compare with
> requests, one for each GUC.
That's fine by me. We could still add the others in the future if we feel the
need. Done that way in the attached.
> Regarding the implementation, you are right to use a fixed-sized stats
> kind for the job. I can see a lot of code has been copy-pasted from
> pgstat_io.c, then slightly adjusted to fit into the picture. That's
> fine here, it makes the implementation straight-forward to read.
Yeah, no need to reinvent the wheel.
> Regarding the documentation, listing all the values for locktype is a
> recipe for rot. I'd suggest to remove the list instead, with only a
> link referring to pg_locks to avoid the duplication.
Makes sense, done that way. Makes me think that we could do the same for pg_locks
and just link it to the Wait Events of Type Lock? (Table 27.11.)
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
[text/x-diff] v4-0001-Add-lock-statistics.patch (14.9K, 2-v4-0001-Add-lock-statistics.patch)
download | inline diff:
From 7e7f2cecfedce71c5aa68ac6843a003540ee33e2 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <[email protected]>
Date: Tue, 29 Jul 2025 08:36:35 +0000
Subject: [PATCH v4 1/2] Add lock statistics
Adding a new stat kind PGSTAT_KIND_LOCK for the lock statistics.
This new statistic kind is a fixed one because its key is the lock type
so that we know its size is LOCKTAG_LAST_TYPE + 1.
This statistic kind records the following counters:
requests
timeouts
deadlock_timeouts
fastpath
These counters enable tuning of lock related GUCs (max_locks_per_transaction,
lock_timeout, and deadlock_timeout).
No extra details is added (like the ones, i.e relation oid, database oid, we
can find in pg_locks). The idea is to provide an idea on what the locking
behaviour looks like.
XXX: Bump stat file format
---
src/backend/storage/lmgr/lock.c | 12 ++
src/backend/storage/lmgr/proc.c | 2 +
src/backend/tcop/postgres.c | 8 ++
src/backend/utils/activity/Makefile | 1 +
src/backend/utils/activity/meson.build | 1 +
src/backend/utils/activity/pgstat.c | 18 +++
src/backend/utils/activity/pgstat_lock.c | 160 +++++++++++++++++++++++
src/include/pgstat.h | 30 +++++
src/include/utils/pgstat_internal.h | 21 +++
src/include/utils/pgstat_kind.h | 5 +-
src/tools/pgindent/typedefs.list | 4 +
11 files changed, 260 insertions(+), 2 deletions(-)
9.7% src/backend/storage/lmgr/
3.0% src/backend/tcop/
66.2% src/backend/utils/activity/
9.1% src/include/utils/
10.9% src/include/
diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c
index e1168ad3837..30537ccfade 100644
--- a/src/backend/storage/lmgr/lock.c
+++ b/src/backend/storage/lmgr/lock.c
@@ -45,6 +45,7 @@
#include "storage/spin.h"
#include "storage/standby.h"
#include "utils/memutils.h"
+#include "utils/pgstat_internal.h"
#include "utils/ps_status.h"
#include "utils/resowner.h"
@@ -871,6 +872,9 @@ LockAcquireExtended(const LOCKTAG *locktag,
lockMethodTable->lockModeNames[lockmode]),
errhint("Only RowExclusiveLock or less can be acquired on database objects during recovery.")));
+ /* Increment the lock statistics requests counter */
+ pgstat_count_lock_requests(locktag->locktag_type);
+
#ifdef LOCK_DEBUG
if (LOCK_DEBUG_ENABLED(locktag))
elog(LOG, "LockAcquire: lock [%u,%u] %s",
@@ -2799,6 +2803,8 @@ FastPathGrantRelationLock(Oid relid, LOCKMODE lockmode)
{
Assert(!FAST_PATH_CHECK_LOCKMODE(MyProc, f, lockmode));
FAST_PATH_SET_LOCKMODE(MyProc, f, lockmode);
+ /* Increment the lock statistics fastpath counter */
+ pgstat_count_lock_fastpath(LOCKTAG_RELATION);
return true;
}
}
@@ -2808,6 +2814,8 @@ FastPathGrantRelationLock(Oid relid, LOCKMODE lockmode)
{
MyProc->fpRelId[unused_slot] = relid;
FAST_PATH_SET_LOCKMODE(MyProc, unused_slot, lockmode);
+ /* Increment the lock statistics fastpath counter */
+ pgstat_count_lock_fastpath(LOCKTAG_RELATION);
++FastPathLocalUseCounts[group];
return true;
}
@@ -4629,6 +4637,10 @@ VirtualXactLockTableInsert(VirtualTransactionId vxid)
MyProc->fpVXIDLock = true;
MyProc->fpLocalTransactionId = vxid.localTransactionId;
+ /* Increment the lock statistics requests and fastpath counters */
+ pgstat_count_lock_requests(LOCKTAG_VIRTUALTRANSACTION);
+ pgstat_count_lock_fastpath(LOCKTAG_VIRTUALTRANSACTION);
+
LWLockRelease(&MyProc->fpInfoLock);
}
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index fd8318bdf3d..5eee8d1f69d 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -1447,6 +1447,8 @@ ProcSleep(LOCALLOCK *locallock)
/* check for deadlocks first, as that's probably log-worthy */
if (got_deadlock_timeout)
{
+ /* Increment the lock statistics deadlock_timeouts counter */
+ pgstat_count_lock_deadlock_timeouts(locallock->tag.lock.locktag_type);
deadlock_state = CheckDeadLock();
got_deadlock_timeout = false;
}
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 21de158adbb..eadb43a283d 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -3472,6 +3472,14 @@ ProcessInterrupts(void)
if (lock_timeout_occurred)
{
+ LOCALLOCK *lockAwaited;
+
+ lockAwaited = GetAwaitedLock();
+
+ /* Increment the lock statistics timeouts counter */
+ if (lockAwaited)
+ pgstat_count_lock_timeouts(lockAwaited->tag.lock.locktag_type);
+
LockErrorCleanup();
ereport(ERROR,
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
diff --git a/src/backend/utils/activity/Makefile b/src/backend/utils/activity/Makefile
index c37bfb350bb..ca3ef89bf59 100644
--- a/src/backend/utils/activity/Makefile
+++ b/src/backend/utils/activity/Makefile
@@ -26,6 +26,7 @@ OBJS = \
pgstat_database.o \
pgstat_function.o \
pgstat_io.o \
+ pgstat_lock.o \
pgstat_relation.o \
pgstat_replslot.o \
pgstat_shmem.o \
diff --git a/src/backend/utils/activity/meson.build b/src/backend/utils/activity/meson.build
index 53bd5a246ca..1aa7ece5290 100644
--- a/src/backend/utils/activity/meson.build
+++ b/src/backend/utils/activity/meson.build
@@ -11,6 +11,7 @@ backend_sources += files(
'pgstat_database.c',
'pgstat_function.c',
'pgstat_io.c',
+ 'pgstat_lock.c',
'pgstat_relation.c',
'pgstat_replslot.c',
'pgstat_shmem.c',
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 11bb71cad5a..eb8ccbaa628 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -83,6 +83,7 @@
* - pgstat_database.c
* - pgstat_function.c
* - pgstat_io.c
+ * - pgstat_lock.c
* - pgstat_relation.c
* - pgstat_replslot.c
* - pgstat_slru.c
@@ -448,6 +449,23 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.snapshot_cb = pgstat_io_snapshot_cb,
},
+ [PGSTAT_KIND_LOCK] = {
+ .name = "lock",
+
+ .fixed_amount = true,
+ .write_to_file = true,
+
+ .snapshot_ctl_off = offsetof(PgStat_Snapshot, lock),
+ .shared_ctl_off = offsetof(PgStat_ShmemControl, lock),
+ .shared_data_off = offsetof(PgStatShared_Lock, stats),
+ .shared_data_len = sizeof(((PgStatShared_Lock *) 0)->stats),
+
+ .flush_static_cb = pgstat_lock_flush_cb,
+ .init_shmem_cb = pgstat_lock_init_shmem_cb,
+ .reset_all_cb = pgstat_lock_reset_all_cb,
+ .snapshot_cb = pgstat_lock_snapshot_cb,
+ },
+
[PGSTAT_KIND_SLRU] = {
.name = "slru",
diff --git a/src/backend/utils/activity/pgstat_lock.c b/src/backend/utils/activity/pgstat_lock.c
new file mode 100644
index 00000000000..110a370b875
--- /dev/null
+++ b/src/backend/utils/activity/pgstat_lock.c
@@ -0,0 +1,160 @@
+/* -------------------------------------------------------------------------
+ *
+ * pgstat_lock.c
+ * Implementation of lock statistics.
+ *
+ * This file contains the implementation of lock statistics. It is kept separate
+ * from pgstat.c to enforce the line between the statistics access / storage
+ * implementation and the details about individual types of statistics.
+ *
+ * Copyright (c) 2021-2025, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * src/backend/utils/activity/pgstat_lock.c
+ * -------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "utils/pgstat_internal.h"
+
+static PgStat_PendingLock PendingLockStats;
+static bool have_lockstats = false;
+
+/*
+ * Simpler wrapper of pgstat_lock_flush_cb()
+ */
+void
+pgstat_lock_flush(bool nowait)
+{
+ (void) pgstat_lock_flush_cb(nowait);
+}
+
+/*
+ * Flush out locally pending lock statistics
+ *
+ * If no stats have been recorded, this function returns false.
+ *
+ * If nowait is true, this function returns true if the lock could not be
+ * acquired. Otherwise, return false.
+ */
+bool
+pgstat_lock_flush_cb(bool nowait)
+{
+ LWLock *lcktype_lock;
+ PgStat_LockEntry *lck_shstats;
+ bool lock_not_acquired = false;
+
+ if (!have_lockstats)
+ return false;
+
+ for (int i = 0; i <= LOCKTAG_LAST_TYPE; i++)
+ {
+ lcktype_lock = &pgStatLocal.shmem->lock.locks[i];
+ lck_shstats =
+ &pgStatLocal.shmem->lock.stats.stats[i];
+
+ if (!nowait)
+ LWLockAcquire(lcktype_lock, LW_EXCLUSIVE);
+ else if (!LWLockConditionalAcquire(lcktype_lock, LW_EXCLUSIVE))
+ {
+ lock_not_acquired = true;
+ continue;
+ }
+
+#define LOCKSTAT_ACC(fld) \
+ (lck_shstats->fld += PendingLockStats.stats[i].fld)
+ LOCKSTAT_ACC(requests);
+ LOCKSTAT_ACC(timeouts);
+ LOCKSTAT_ACC(deadlock_timeouts);
+ LOCKSTAT_ACC(fastpath);
+#undef LOCKSTAT_ACC
+
+ LWLockRelease(lcktype_lock);
+ }
+
+ memset(&PendingLockStats, 0, sizeof(PendingLockStats));
+
+ have_lockstats = false;
+
+ return lock_not_acquired;
+}
+
+
+void
+pgstat_lock_init_shmem_cb(void *stats)
+{
+ PgStatShared_Lock *stat_shmem = (PgStatShared_Lock *) stats;
+
+ for (int i = 0; i <= LOCKTAG_LAST_TYPE; i++)
+ LWLockInitialize(&stat_shmem->locks[i], LWTRANCHE_PGSTATS_DATA);
+}
+
+void
+pgstat_lock_reset_all_cb(TimestampTz ts)
+{
+ for (int i = 0; i <= LOCKTAG_LAST_TYPE; i++)
+ {
+ LWLock *lcktype_lock = &pgStatLocal.shmem->lock.locks[i];
+ PgStat_LockEntry *lck_shstats = &pgStatLocal.shmem->lock.stats.stats[i];
+
+ LWLockAcquire(lcktype_lock, LW_EXCLUSIVE);
+
+ /*
+ * Use the lock in the first lock type PgStat_LockEntry to protect the
+ * reset timestamp as well.
+ */
+ if (i == 0)
+ pgStatLocal.shmem->lock.stats.stat_reset_timestamp = ts;
+
+ memset(lck_shstats, 0, sizeof(*lck_shstats));
+ LWLockRelease(lcktype_lock);
+ }
+}
+
+void
+pgstat_lock_snapshot_cb(void)
+{
+ for (int i = 0; i <= LOCKTAG_LAST_TYPE; i++)
+ {
+ LWLock *lcktype_lock = &pgStatLocal.shmem->lock.locks[i];
+ PgStat_LockEntry *lck_shstats = &pgStatLocal.shmem->lock.stats.stats[i];
+ PgStat_LockEntry *lck_snap = &pgStatLocal.snapshot.lock.stats[i];
+
+ LWLockAcquire(lcktype_lock, LW_SHARED);
+
+ /*
+ * Use the lock in the first lock type PgStat_LockEntry to protect the
+ * reset timestamp as well.
+ */
+ if (i == 0)
+ pgStatLocal.snapshot.lock.stat_reset_timestamp =
+ pgStatLocal.shmem->lock.stats.stat_reset_timestamp;
+
+ /* using struct assignment due to better type safety */
+ *lck_snap = *lck_shstats;
+ LWLockRelease(lcktype_lock);
+ }
+}
+
+#define PGSTAT_COUNT_LOCK_FUNC(stat) \
+void \
+CppConcat(pgstat_count_lock_,stat)(uint8 locktag_type) \
+{ \
+ Assert(locktag_type <= LOCKTAG_LAST_TYPE); \
+ PendingLockStats.stats[locktag_type].stat++; \
+ have_lockstats = true; \
+ pgstat_report_fixed = true; \
+}
+
+/* pgstat_count_lock_requests */
+PGSTAT_COUNT_LOCK_FUNC(requests)
+
+/* pgstat_count_lock_timeouts */
+PGSTAT_COUNT_LOCK_FUNC(timeouts)
+
+/* pgstat_count_lock_deadlock_timeouts */
+PGSTAT_COUNT_LOCK_FUNC(deadlock_timeouts)
+
+/* pgstat_count_lock_fastpath */
+PGSTAT_COUNT_LOCK_FUNC(fastpath)
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index fff7ecc2533..8460e2db159 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -17,6 +17,7 @@
#include "postmaster/pgarch.h" /* for MAX_XFN_CHARS */
#include "replication/conflict.h"
#include "replication/worker_internal.h"
+#include "storage/lock.h"
#include "utils/backend_progress.h" /* for backward compatibility */ /* IWYU pragma: export */
#include "utils/backend_status.h" /* for backward compatibility */ /* IWYU pragma: export */
#include "utils/pgstat_kind.h"
@@ -342,6 +343,25 @@ typedef struct PgStat_IO
PgStat_BktypeIO stats[BACKEND_NUM_TYPES];
} PgStat_IO;
+typedef struct PgStat_LockEntry
+{
+ PgStat_Counter requests;
+ PgStat_Counter timeouts;
+ PgStat_Counter deadlock_timeouts;
+ PgStat_Counter fastpath;
+} PgStat_LockEntry;
+
+typedef struct PgStat_PendingLock
+{
+ PgStat_LockEntry stats[LOCKTAG_LAST_TYPE + 1];
+} PgStat_PendingLock;
+
+typedef struct PgStat_Lock
+{
+ TimestampTz stat_reset_timestamp;
+ PgStat_LockEntry stats[LOCKTAG_LAST_TYPE + 1];
+} PgStat_Lock;
+
typedef struct PgStat_StatDBEntry
{
PgStat_Counter xact_commit;
@@ -614,6 +634,16 @@ extern bool pgstat_tracks_io_op(BackendType bktype, IOObject io_object,
IOContext io_context, IOOp io_op);
+/*
+ * Functions in pgstat_lock.c
+ */
+
+extern void pgstat_lock_flush(bool nowait);
+extern void pgstat_count_lock_requests(uint8 locktag_type);
+extern void pgstat_count_lock_timeouts(uint8 locktag_type);
+extern void pgstat_count_lock_deadlock_timeouts(uint8 locktag_type);
+extern void pgstat_count_lock_fastpath(uint8 locktag_type);
+
/*
* Functions in pgstat_database.c
*/
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 9b8fbae00ed..97704421a92 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -464,6 +464,16 @@ typedef struct PgStatShared_IO
PgStat_IO stats;
} PgStatShared_IO;
+typedef struct PgStatShared_Lock
+{
+ /*
+ * locks[i] protects stats.stats[i]. locks[0] also protects
+ * stats.stat_reset_timestamp.
+ */
+ LWLock locks[LOCKTAG_LAST_TYPE + 1];
+ PgStat_Lock stats;
+} PgStatShared_Lock;
+
typedef struct PgStatShared_SLRU
{
/* lock protects ->stats */
@@ -570,6 +580,7 @@ typedef struct PgStat_ShmemControl
PgStatShared_BgWriter bgwriter;
PgStatShared_Checkpointer checkpointer;
PgStatShared_IO io;
+ PgStatShared_Lock lock;
PgStatShared_SLRU slru;
PgStatShared_Wal wal;
@@ -602,6 +613,8 @@ typedef struct PgStat_Snapshot
PgStat_IO io;
+ PgStat_Lock lock;
+
PgStat_SLRUStats slru[SLRU_NUM_ELEMENTS];
PgStat_WalStats wal;
@@ -752,6 +765,14 @@ extern void pgstat_io_init_shmem_cb(void *stats);
extern void pgstat_io_reset_all_cb(TimestampTz ts);
extern void pgstat_io_snapshot_cb(void);
+/*
+ * Functions in pgstat_lock.c
+ */
+
+extern bool pgstat_lock_flush_cb(bool nowait);
+extern void pgstat_lock_init_shmem_cb(void *stats);
+extern void pgstat_lock_reset_all_cb(TimestampTz ts);
+extern void pgstat_lock_snapshot_cb(void);
/*
* Functions in pgstat_relation.c
diff --git a/src/include/utils/pgstat_kind.h b/src/include/utils/pgstat_kind.h
index c30b6235623..2d78a029683 100644
--- a/src/include/utils/pgstat_kind.h
+++ b/src/include/utils/pgstat_kind.h
@@ -36,8 +36,9 @@
#define PGSTAT_KIND_BGWRITER 8
#define PGSTAT_KIND_CHECKPOINTER 9
#define PGSTAT_KIND_IO 10
-#define PGSTAT_KIND_SLRU 11
-#define PGSTAT_KIND_WAL 12
+#define PGSTAT_KIND_LOCK 11
+#define PGSTAT_KIND_SLRU 12
+#define PGSTAT_KIND_WAL 13
#define PGSTAT_KIND_BUILTIN_MIN PGSTAT_KIND_DATABASE
#define PGSTAT_KIND_BUILTIN_MAX PGSTAT_KIND_WAL
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 6e2d876a40f..cd3688524af 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2255,6 +2255,7 @@ PgStatShared_Database
PgStatShared_Function
PgStatShared_HashEntry
PgStatShared_IO
+PgStatShared_Lock
PgStatShared_Relation
PgStatShared_ReplSlot
PgStatShared_SLRU
@@ -2277,8 +2278,11 @@ PgStat_HashKey
PgStat_IO
PgStat_KindInfo
PgStat_LocalState
+PgStat_Lock
+PgStat_LockEntry
PgStat_PendingDroppedStatsItem
PgStat_PendingIO
+PgStat_PendingLock
PgStat_SLRUStats
PgStat_ShmemControl
PgStat_Snapshot
--
2.34.1
[text/x-diff] v4-0002-Add-the-pg_stat_lock-view.patch (15.9K, 3-v4-0002-Add-the-pg_stat_lock-view.patch)
download | inline diff:
From f7b2a14597830286b08bc8a78722c96fc73cafd6 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <[email protected]>
Date: Thu, 31 Jul 2025 09:35:31 +0000
Subject: [PATCH v4 2/2] Add the pg_stat_lock view
This new view reports lock statistics.
This commit also adds documentation and a few tests.
XXX: Bump catversion
---
doc/src/sgml/monitoring.sgml | 117 ++++++++++++++++++
src/backend/catalog/system_views.sql | 10 ++
src/backend/utils/activity/pgstat_lock.c | 8 ++
src/backend/utils/adt/pgstatfuncs.c | 40 ++++++
src/include/catalog/pg_proc.dat | 9 ++
src/include/pgstat.h | 1 +
src/test/isolation/expected/deadlock-hard.out | 20 ++-
src/test/isolation/specs/deadlock-hard.spec | 5 +-
src/test/regress/expected/advisory_lock.out | 18 +++
src/test/regress/expected/rules.out | 7 ++
src/test/regress/sql/advisory_lock.sql | 4 +
11 files changed, 237 insertions(+), 2 deletions(-)
46.2% doc/src/sgml/
3.2% src/backend/catalog/
16.3% src/backend/utils/adt/
6.2% src/include/catalog/
9.3% src/test/isolation/expected/
6.7% src/test/isolation/specs/
7.3% src/test/regress/expected/
4.4% src/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index b77d189a500..1cba84c1542 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -493,6 +493,15 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_lock</structname><indexterm><primary>pg_stat_lock</primary></indexterm></entry>
+ <entry>
+ One row for each lock type, containing cluster-wide locks statistics.
+ See <link linkend="monitoring-pg-stat-lock-view">
+ <structname>pg_stat_lock</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_replication_slots</structname><indexterm><primary>pg_stat_replication_slots</primary></indexterm></entry>
<entry>One row per replication slot, showing statistics about the
@@ -3124,6 +3133,108 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</sect2>
+
+ <sect2 id="monitoring-pg-stat-lock-view">
+ <title><structname>pg_stat_lock</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_lock</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_lock</structname> view will contain one row for each
+ lock type, showing cluster-wide locks statistics.
+ </para>
+
+ <table id="pg-stat-lock-view" xreflabel="pg_stat_lock">
+ <title><structname>pg_stat_lock</structname> View</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para>
+ </entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>locktype</structfield> <type>text</type>
+ </para>
+ <para>
+ Type of the lockable object. See <link linkend="view-pg-locks">
+ <structname>pg_stat_lock</structname></link> for details.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>requests</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of requests for this lock type.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>timeouts</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times requests for this lock type had to wait longer
+ than <varname>lock_timeout</varname>.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>deadlock_timeouts</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times requests for this lock type had to wait longer
+ than <varname>deadlock_timeout</varname>.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>fastpath</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times this lock type was taken via fast path.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Time at which these statistics were last reset.
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect2>
+
<sect2 id="monitoring-pg-stat-bgwriter-view">
<title><structname>pg_stat_bgwriter</structname></title>
@@ -5195,6 +5306,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<structname>pg_stat_io</structname> view.
</para>
</listitem>
+ <listitem>
+ <para>
+ <literal>lock</literal>: Reset all the counters shown in the
+ <structname>pg_stat_lock</structname> view.
+ </para>
+ </listitem>
<listitem>
<para>
<literal>recovery_prefetch</literal>: Reset all the counters shown in
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 1ea8f1faa9e..a51ae67de05 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -985,6 +985,16 @@ CREATE VIEW pg_stat_slru AS
s.stats_reset
FROM pg_stat_get_slru() s;
+CREATE VIEW pg_stat_lock AS
+ SELECT
+ l.locktype,
+ l.requests,
+ l.timeouts,
+ l.deadlock_timeouts,
+ l.fastpath,
+ l.stats_reset
+ FROM pg_stat_get_lock() l;
+
CREATE VIEW pg_stat_wal_receiver AS
SELECT
s.pid,
diff --git a/src/backend/utils/activity/pgstat_lock.c b/src/backend/utils/activity/pgstat_lock.c
index 110a370b875..e26e24c260c 100644
--- a/src/backend/utils/activity/pgstat_lock.c
+++ b/src/backend/utils/activity/pgstat_lock.c
@@ -21,6 +21,14 @@
static PgStat_PendingLock PendingLockStats;
static bool have_lockstats = false;
+PgStat_Lock *
+pgstat_fetch_stat_lock(void)
+{
+ pgstat_snapshot_fixed(PGSTAT_KIND_LOCK);
+
+ return &pgStatLocal.snapshot.lock;
+}
+
/*
* Simpler wrapper of pgstat_lock_flush_cb()
*/
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index b1df96e7b0b..6a699da8bf0 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1737,6 +1737,43 @@ pg_stat_get_wal(PG_FUNCTION_ARGS)
wal_stats->stat_reset_timestamp));
}
+Datum
+pg_stat_get_lock(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_LOCK_COLS 6
+ ReturnSetInfo *rsinfo;
+ PgStat_Lock *lock_stats;
+
+ InitMaterializedSRF(fcinfo, 0);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ lock_stats = pgstat_fetch_stat_lock();
+
+ for (int lcktype = 0; lcktype <= LOCKTAG_LAST_TYPE; lcktype++)
+ {
+ const char *locktypename;
+ Datum values[PG_STAT_LOCK_COLS] = {0};
+ bool nulls[PG_STAT_LOCK_COLS] = {0};
+ PgStat_LockEntry *lck_stats = &lock_stats->stats[lcktype];
+ int i = 0;
+
+ locktypename = LockTagTypeNames[lcktype];
+
+ values[i++] = CStringGetTextDatum(locktypename);
+ values[i++] = Int64GetDatum(lck_stats->requests);
+ values[i++] = Int64GetDatum(lck_stats->timeouts);
+ values[i++] = Int64GetDatum(lck_stats->deadlock_timeouts);
+ values[i++] = Int64GetDatum(lck_stats->fastpath);
+ values[i] = TimestampTzGetDatum(lock_stats->stat_reset_timestamp);
+
+ Assert(i + 1 == PG_STAT_LOCK_COLS);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+ }
+
+ return (Datum) 0;
+}
+
/*
* Returns statistics of SLRU caches.
*/
@@ -1921,6 +1958,7 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER);
pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
pgstat_reset_of_kind(PGSTAT_KIND_IO);
+ pgstat_reset_of_kind(PGSTAT_KIND_LOCK);
XLogPrefetchResetStats();
pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
pgstat_reset_of_kind(PGSTAT_KIND_WAL);
@@ -1938,6 +1976,8 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
else if (strcmp(target, "io") == 0)
pgstat_reset_of_kind(PGSTAT_KIND_IO);
+ else if (strcmp(target, "lock") == 0)
+ pgstat_reset_of_kind(PGSTAT_KIND_LOCK);
else if (strcmp(target, "recovery_prefetch") == 0)
XLogPrefetchResetStats();
else if (strcmp(target, "slru") == 0)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 83f6501df38..e208ebdbbc3 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6026,6 +6026,15 @@
proargnames => '{backend_type,object,context,reads,read_bytes,read_time,writes,write_bytes,write_time,writebacks,writeback_time,extends,extend_bytes,extend_time,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}',
prosrc => 'pg_stat_get_io' },
+{ oid => '9375', descr => 'statistics: per lock type statistics',
+ proname => 'pg_stat_get_lock', prorows => '10', proretset => 't',
+ provolatile => 'v', proparallel => 'r', prorettype => 'record',
+ proargtypes => '',
+ proallargtypes => '{text,int8,int8,int8,int8,timestamptz}',
+ proargmodes => '{o,o,o,o,o,o}',
+ proargnames => '{locktype,requests,timeouts,deadlock_timeouts,fastpath,stats_reset}',
+ prosrc => 'pg_stat_get_lock' },
+
{ oid => '6386', descr => 'statistics: backend IO statistics',
proname => 'pg_stat_get_backend_io', prorows => '5', proretset => 't',
provolatile => 'v', proparallel => 'r', prorettype => 'record',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 8460e2db159..b06a261572b 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -643,6 +643,7 @@ extern void pgstat_count_lock_requests(uint8 locktag_type);
extern void pgstat_count_lock_timeouts(uint8 locktag_type);
extern void pgstat_count_lock_deadlock_timeouts(uint8 locktag_type);
extern void pgstat_count_lock_fastpath(uint8 locktag_type);
+extern PgStat_Lock *pgstat_fetch_stat_lock(void);
/*
* Functions in pgstat_database.c
diff --git a/src/test/isolation/expected/deadlock-hard.out b/src/test/isolation/expected/deadlock-hard.out
index 460653f2b86..5ae5a8bf953 100644
--- a/src/test/isolation/expected/deadlock-hard.out
+++ b/src/test/isolation/expected/deadlock-hard.out
@@ -1,6 +1,12 @@
Parsed test spec with 8 sessions
-starting permutation: s1a1 s2a2 s3a3 s4a4 s5a5 s6a6 s7a7 s8a8 s1a2 s2a3 s3a4 s4a5 s5a6 s6a7 s7a8 s8a1 s8c s7c s6c s5c s4c s3c s2c s1c
+starting permutation: s1rl s1a1 s2a2 s3a3 s4a4 s5a5 s6a6 s7a7 s8a8 s1a2 s2a3 s3a4 s4a5 s5a6 s6a7 s7a8 s8a1 s8c s8f s7c s6c s5c s4c s3c s2c s1c s1sl
+step s1rl: SELECT pg_stat_reset_shared('lock');
+pg_stat_reset_shared
+--------------------
+
+(1 row)
+
step s1a1: LOCK TABLE a1;
step s2a2: LOCK TABLE a2;
step s3a3: LOCK TABLE a3;
@@ -21,6 +27,12 @@ step s8a1: <... completed>
ERROR: deadlock detected
step s7a8: <... completed>
step s8c: COMMIT;
+step s8f: SELECT pg_stat_force_next_flush();
+pg_stat_force_next_flush
+------------------------
+
+(1 row)
+
step s7c: COMMIT;
step s6a7: <... completed>
step s6c: COMMIT;
@@ -34,3 +46,9 @@ step s2a3: <... completed>
step s2c: COMMIT;
step s1a2: <... completed>
step s1c: COMMIT;
+step s1sl: SELECT deadlock_timeouts > 0 FROM pg_stat_lock WHERE locktype = 'relation';
+?column?
+--------
+t
+(1 row)
+
diff --git a/src/test/isolation/specs/deadlock-hard.spec b/src/test/isolation/specs/deadlock-hard.spec
index 60bedca237a..d9ed99836e3 100644
--- a/src/test/isolation/specs/deadlock-hard.spec
+++ b/src/test/isolation/specs/deadlock-hard.spec
@@ -25,6 +25,8 @@ setup { BEGIN; SET deadlock_timeout = '100s'; }
step s1a1 { LOCK TABLE a1; }
step s1a2 { LOCK TABLE a2; }
step s1c { COMMIT; }
+step s1sl { SELECT deadlock_timeouts > 0 FROM pg_stat_lock WHERE locktype = 'relation'; }
+step s1rl { SELECT pg_stat_reset_shared('lock'); }
session s2
setup { BEGIN; SET deadlock_timeout = '100s'; }
@@ -67,6 +69,7 @@ setup { BEGIN; SET deadlock_timeout = '10ms'; }
step s8a8 { LOCK TABLE a8; }
step s8a1 { LOCK TABLE a1; }
step s8c { COMMIT; }
+step s8f { SELECT pg_stat_force_next_flush(); }
# Note: when s8a1 detects the deadlock and fails, s7a8 is released, making
# it timing-dependent which query completion is received first by the tester.
@@ -76,4 +79,4 @@ step s8c { COMMIT; }
# dummy blocking mark to s8a1 to ensure it will be reported as "waiting"
# regardless of that.
-permutation s1a1 s2a2 s3a3 s4a4 s5a5 s6a6 s7a7 s8a8 s1a2 s2a3 s3a4 s4a5 s5a6 s6a7 s7a8(s8a1) s8a1(*) s8c s7c s6c s5c s4c s3c s2c s1c
+permutation s1rl s1a1 s2a2 s3a3 s4a4 s5a5 s6a6 s7a7 s8a8 s1a2 s2a3 s3a4 s4a5 s5a6 s6a7 s7a8(s8a1) s8a1(*) s8c s8f s7c s6c s5c s4c s3c s2c s1c s1sl
diff --git a/src/test/regress/expected/advisory_lock.out b/src/test/regress/expected/advisory_lock.out
index 02e07765ac2..fdaa1756ba4 100644
--- a/src/test/regress/expected/advisory_lock.out
+++ b/src/test/regress/expected/advisory_lock.out
@@ -2,6 +2,12 @@
-- ADVISORY LOCKS
--
SELECT oid AS datoid FROM pg_database WHERE datname = current_database() \gset
+SELECT pg_stat_reset_shared('lock');
+ pg_stat_reset_shared
+----------------------
+
+(1 row)
+
BEGIN;
SELECT
pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
@@ -48,6 +54,12 @@ WARNING: you don't own a lock of type ShareLock
f | f | f | f
(1 row)
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
-- automatically release xact locks at commit
COMMIT;
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
@@ -56,6 +68,12 @@ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
0
(1 row)
+SELECT requests FROM pg_stat_lock WHERE locktype = 'advisory';
+ requests
+----------
+ 4
+(1 row)
+
BEGIN;
-- holding both session and xact locks on the same objects, xact first
SELECT
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 78a37d9fc8f..80999465baf 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1951,6 +1951,13 @@ pg_stat_io| SELECT backend_type,
fsync_time,
stats_reset
FROM pg_stat_get_io() b(backend_type, object, context, reads, read_bytes, read_time, writes, write_bytes, write_time, writebacks, writeback_time, extends, extend_bytes, extend_time, hits, evictions, reuses, fsyncs, fsync_time, stats_reset);
+pg_stat_lock| SELECT locktype,
+ requests,
+ timeouts,
+ deadlock_timeouts,
+ fastpath,
+ stats_reset
+ FROM pg_stat_get_lock() l(locktype, requests, timeouts, deadlock_timeouts, fastpath, stats_reset);
pg_stat_progress_analyze| SELECT s.pid,
s.datid,
d.datname,
diff --git a/src/test/regress/sql/advisory_lock.sql b/src/test/regress/sql/advisory_lock.sql
index 8513ab8e98f..f1bff60fd37 100644
--- a/src/test/regress/sql/advisory_lock.sql
+++ b/src/test/regress/sql/advisory_lock.sql
@@ -4,6 +4,8 @@
SELECT oid AS datoid FROM pg_database WHERE datname = current_database() \gset
+SELECT pg_stat_reset_shared('lock');
+
BEGIN;
SELECT
@@ -26,12 +28,14 @@ SELECT
pg_advisory_unlock(1), pg_advisory_unlock_shared(2),
pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2);
+SELECT pg_stat_force_next_flush();
-- automatically release xact locks at commit
COMMIT;
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
+SELECT requests FROM pg_stat_lock WHERE locktype = 'advisory';
BEGIN;
--
2.34.1
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Adding locks statistics
2026-02-13 07:36 Re: Adding locks statistics Michael Paquier <[email protected]>
2026-02-13 10:24 ` Re: Adding locks statistics Bertrand Drouvot <[email protected]>
@ 2026-02-13 21:13 ` Andres Freund <[email protected]>
2026-02-16 04:18 ` Re: Adding locks statistics Michael Paquier <[email protected]>
2026-02-16 10:10 ` Re: Adding locks statistics Bertrand Drouvot <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Andres Freund @ 2026-02-13 21:13 UTC (permalink / raw)
To: Bertrand Drouvot <[email protected]>; +Cc: Michael Paquier <[email protected]>; Jeff Davis <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
Hi,
On 2026-02-13 10:24:52 +0000, Bertrand Drouvot wrote:
> On Fri, Feb 13, 2026 at 04:36:57PM +0900, Michael Paquier wrote:
> > On Tue, Feb 10, 2026 at 07:30:50AM +0000, Bertrand Drouvot wrote:
> > > New rebase due to 73d60ac385a.
> > So my suggestion for the moment would be to be more frugal (yeah I
> > know, sorry..) and limit ourselves to four fields: deadlock_timeout,
> > requests, fastpath and timeouts. Three fields to compare with
> > requests, one for each GUC.
>
> That's fine by me. We could still add the others in the future if we feel the
> need. Done that way in the attached.
I'm not sure that it's unproblematic to add multiple pgstat count calls to
every lock acquisition, particularly if it's a fastpath acquisition or a
virtualxid lock. Notably these are external function calls, not just
increments of a counter in an inline function.
I also don't really know what one would do with some of the information?
What does the number of virtualxid lock acquisitions tell you that the numbers
of transactions doesn't already tell you in a more understandable way?
What does it tell you that the deadlock checker ran N times? It notably
doesn't count deadlocks, it counts how often we checked for deadlocks.
The percentage of fastpath locks also seems not really informative, because
that could be because we ran out of space for fastpath locks, or because a
lock mode that's ineligible for fastpath locks was used.
What I would actually count is the amount of time waiting for locks, that
seems vastly more useful than the number of acquisitions. We already do a
GetCurrentTimestamp() inside the timer activations for deadlock timeout, we
probably can figure out a way to reuse that to reduce the increase in overhead
due to timing. We could also just count the wait time after the deadlock
check has run.
> @@ -17,6 +17,7 @@
> #include "postmaster/pgarch.h" /* for MAX_XFN_CHARS */
> #include "replication/conflict.h"
> #include "replication/worker_internal.h"
> +#include "storage/lock.h"
> #include "utils/backend_progress.h" /* for backward compatibility */ /* IWYU pragma: export */
> #include "utils/backend_status.h" /* for backward compatibility */ /* IWYU pragma: export */
> #include "utils/pgstat_kind.h"
> @@ -342,6 +343,25 @@ typedef struct PgStat_IO
> PgStat_BktypeIO stats[BACKEND_NUM_TYPES];
> } PgStat_IO;
I don't like the amount of headers this addition will indirectly include in a
lot of places.
I'm also pretty unhappy about an include of access/transam.h recently having
been added. And worker_internal.h quite obviously, given the name, has no
business being included here, and also includes a lot more. Grrr. I'll start
a thread.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Adding locks statistics
2026-02-13 07:36 Re: Adding locks statistics Michael Paquier <[email protected]>
2026-02-13 10:24 ` Re: Adding locks statistics Bertrand Drouvot <[email protected]>
2026-02-13 21:13 ` Re: Adding locks statistics Andres Freund <[email protected]>
@ 2026-02-16 04:18 ` Michael Paquier <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Paquier @ 2026-02-16 04:18 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Bertrand Drouvot <[email protected]>; Jeff Davis <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
On Fri, Feb 13, 2026 at 04:13:39PM -0500, Andres Freund wrote:
> I'm not sure that it's unproblematic to add multiple pgstat count calls to
> every lock acquisition, particularly if it's a fastpath acquisition or a
> virtualxid lock. Notably these are external function calls, not just
> increments of a counter in an inline function.
Right. I am not completely sure if this is really free, either,
particularly for a fastpath lock that we want to be.. Faster.
> What I would actually count is the amount of time waiting for locks, that
> seems vastly more useful than the number of acquisitions. We already do a
> GetCurrentTimestamp() inside the timer activations for deadlock timeout, we
> probably can figure out a way to reuse that to reduce the increase in overhead
> due to timing. We could also just count the wait time after the deadlock
> check has run.
That sounds like an interesting suggestion, yes. That's not going to
be bounded by performance if we know that we are already waiting.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Adding locks statistics
2026-02-13 07:36 Re: Adding locks statistics Michael Paquier <[email protected]>
2026-02-13 10:24 ` Re: Adding locks statistics Bertrand Drouvot <[email protected]>
2026-02-13 21:13 ` Re: Adding locks statistics Andres Freund <[email protected]>
@ 2026-02-16 10:10 ` Bertrand Drouvot <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Bertrand Drouvot @ 2026-02-16 10:10 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Jeff Davis <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
Hi,
On Fri, Feb 13, 2026 at 04:13:39PM -0500, Andres Freund wrote:
> Hi,
>
> On 2026-02-13 10:24:52 +0000, Bertrand Drouvot wrote:
> >
> > That's fine by me. We could still add the others in the future if we feel the
> > need. Done that way in the attached.
>
> I'm not sure that it's unproblematic to add multiple pgstat count calls to
> every lock acquisition, particularly if it's a fastpath acquisition or a
> virtualxid lock. Notably these are external function calls, not just
> increments of a counter in an inline function.
Yeah, we could create inline functions instead.
> I also don't really know what one would do with some of the information?
>
> What does the number of virtualxid lock acquisitions tell you that the numbers
> of transactions doesn't already tell you in a more understandable way?
I agree not that much, except being able to compute a transactions/virtualxid
ratio or such. Also the idea was to report the same as pg_locks so that one
could start sampling pg_locks if he needs more details.
> What does it tell you that the deadlock checker ran N times? It notably
> doesn't count deadlocks, it counts how often we checked for deadlocks.
>
> The percentage of fastpath locks also seems not really informative, because
> that could be because we ran out of space for fastpath locks, or because a
> lock mode that's ineligible for fastpath locks was used.
Right, maybe we could add an "exceed fastpath slots" or such counter?
> What I would actually count is the amount of time waiting for locks, that
> seems vastly more useful than the number of acquisitions. We already do a
> GetCurrentTimestamp() inside the timer activations for deadlock timeout, we
> probably can figure out a way to reuse that to reduce the increase in overhead
> due to timing. We could also just count the wait time after the deadlock
> check has run.
Yeah, providing the wait_time would be great. Just to be sure, are you suggesting
to remove all the fields (i.e requests, timeouts, deadlock_timeouts and fastpath)
and just add a wait_time field instead? I think that keeping requests would make
sense to be able to get the average wait time per request.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-02-16 10:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-13 07:36 Re: Adding locks statistics Michael Paquier <[email protected]>
2026-02-13 10:24 ` Bertrand Drouvot <[email protected]>
2026-02-13 21:13 ` Andres Freund <[email protected]>
2026-02-16 04:18 ` Michael Paquier <[email protected]>
2026-02-16 10:10 ` Bertrand Drouvot <[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