Adding locks statistics
От | Bertrand Drouvot |
---|---|
Тема | Adding locks statistics |
Дата | |
Msg-id | aIyNxBWFCybgBZBS@ip-10-97-1-34.eu-west-3.compute.internal обсуждение исходный текст |
Список | pgsql-hackers |
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%40mail.gmail.com Looking forward to your feedback, Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
Вложения
В списке pgsql-hackers по дате отправления: