patternsqlMinor
Measuring how long PostgreSQL waits for locks
Viewed 0 times
postgresqlwaitslongformeasuringhowlocks
Problem
I want to figure out how long PostgreSQL spends waiting for locks during a period of time (during this period of time, many requests are served by PostgreSQL).
What I know
PostgreSQL system table
But I still cannot figure out how long it spends on locks.
Why I am asking this question
I find when I increase the concurrency of PostgreSQL, (e.g., increase parallel worker per gather, max parallel workers or some other configurations), my 100-second multi-threaded TPC-C-like workload gets slower (i.e., lower throughput). So I want to figure out if this is because of too much contention.
Similar answer
For SQL Server: How can I view how long a query has spent waiting for locks, without a profiler?
What I know
PostgreSQL system table
pg_locks show some information. Like:SELECT * FROM
pg_locks pl LEFT JOIN
pg_stat_activity psa
ON pl.pid = psa.pid;
But I still cannot figure out how long it spends on locks.
Why I am asking this question
I find when I increase the concurrency of PostgreSQL, (e.g., increase parallel worker per gather, max parallel workers or some other configurations), my 100-second multi-threaded TPC-C-like workload gets slower (i.e., lower throughput). So I want to figure out if this is because of too much contention.
Similar answer
For SQL Server: How can I view how long a query has spent waiting for locks, without a profiler?
Solution
You can enable
Other than that, monitor the
log_lock_waits to get information about sessions that have to wait more than deadlock_timeout for a lock. You can reduce deadlock_timeout to see shorter waits.Other than that, monitor the
wait_event_type and wait_event in pg_stat_activity. If you see locks regularly, that can be a problem.Context
StackExchange Database Administrators Q#294363, answer score: 6
Revisions (0)
No revisions yet.