1

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?

5

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.

2
  • Thanks for the answer! I prefer pg_stat_activityway, but I find that it only shows the current status, while cannot record them persistently and let me know something like "how many wait events happen during the last 10 seconds?" @Albe – Tim He Jun 17 at 12:51
  • pg_stat_activity only shows what's going on right now. Historical data can only be got from the log. – Laurenz Albe Jun 17 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.