public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bertrand Drouvot <[email protected]>
To: [email protected]
Subject: Adding locks statistics
Date: Fri, 1 Aug 2025 09:49:56 +0000
Message-ID: <[email protected]> (raw)
Hi hackers,
Please find attached a patch to add a new view (namely pg_stat_lock) that provides
lock statistics.
It’s output is like the following:
postgres=# select * from pg_stat_lock;
locktype | requests | waits | timeouts | deadlock_timeouts | deadlocks | fastpath | stats_reset
------------------+----------+-------+----------+-------------------+-----------+----------+-------------------------------
relation | 612775 | 1 | 0 | 0 | 0 | 531115 | 2025-08-01 09:18:26.476275+00
extend | 3128 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
frozenid | 11 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
page | 1 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
tuple | 3613 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
transactionid | 6130 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
virtualxid | 15390 | 0 | 0 | 0 | 0 | 15390 | 2025-08-01 09:18:26.476275+00
spectoken | 12 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
object | 8393 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
userlock | 0 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
advisory | 44 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
applytransaction | 0 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
It means that it provides historical trends of locks usage per lock type.
It can be used for example for:
1. checking if "waits" is close to "requests". Then it means you usually have to
wait before acquiring the lock, which means you may have a concurrency issue.
2. lock_timeout and deadlock_timeout tuning (lock_timeout is visible only in the
logs if log_min_error_statement is set appropriately).
3. checking the "requests"/"fastpath" ratio to see if "max_locks_per_transaction"
needs tuning (see c4d5cb71d2).
If any points need more details, it might be a good idea to start sampling pg_locks.
The patch is made of 2 sub-patches:
0001 - It adds 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: Number of requests for this lock type.
- waits: Number of times requests for this lock type had to wait.
- timeouts: Number of times requests for this lock type had to wait longer than
lock_timeout.
- deadlock_timeouts: Number of times requests for this lock type had to wait longer
than deadlock_timeout.
- deadlocks: Number of times a deadlock occurred on this lock type.
- fastpath: Number of times this lock type was taken via fast path.
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.
Those new counters are incremented outside of the wait events code path,
as suggested in [1].
There are no major design choices, it relies on the current statistics machinery.
0002 - It adds the pg_stat_lock view
It also adds documentation and some tests.
Remarks:
- maybe we could add some metrics related to the lock duration (we have some hints
thanks to the timeout ounters though)
- if this is merged, a next step could be to record those metrics per backend
[1]: https://www.postgresql.org/message-id/CA%2BTgmobptuUWo7X5zcQrWKh22qeAn4eL%2B%3Dwtb8_ajCOR%2B7_tcw%40...
Looking forward to your feedback,
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
view thread (43+ 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], [email protected]
Subject: Re: Adding locks statistics
In-Reply-To: <[email protected]>
* 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