Measuring how long PostgreSQL waits for locks

All we need is an easy explanation of the problem, so here it is.

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 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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can enable 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply