Обсуждение: Locks
Hi
How can i find out when a lock happened?
Im writing a function that will check why some queries ran slowly at particular times, so i want to know if i can store lock information somewhere and retrieve what time the lock happened?
regards
On Monday, November 16, 2020, Yambu <hyambu@gmail.com> wrote:
HiHow can i find out when a lock happened?Im writing a function that will check why some queries ran slowly at particular times, so i want to know if i can store lock information somewhere and retrieve what time the lock happened?
No. The cost/benefit of such a capability makes it seem undesirable.
David J.
Hi.
If you mean currently held locks, there is some info in pg_locks & pg_stat_activity which may be helpful for you. Check the Wiki: https://wiki.postgresql.org/wiki/Lock_Monitoring
Regards,
Alvaro Aguayo
From: Yambu <hyambu@gmail.com>
Sent: 16 November 2020 12:51
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Locks
Hi
How can i find out when a lock happened?
Im writing a function that will check why some queries ran slowly at particular times, so i want to know if i can store lock information somewhere and retrieve what time the lock happened?
regards
On Mon, Nov 16, 2020 at 12:50 PM Yambu <hyambu@gmail.com> wrote:
HiHow can i find out when a lock happened?Im writing a function that will check why some queries ran slowly at particular times, so i want to know if i can store lock information somewhere and retrieve what time the lock happened?regards
That historical information is not kept in the database. However, you can turn on logging of lock waits if they are longer than a given amount of time. Just set it to your desired threshold in the postgresql.conf and reload
Note that if you set this very low, you can greatly increase the size of your log files. So I'd recommend figuring out a threshold value where it's actually a problem if the wait is that long.
Couple this with the log_min_duration_statement, which will also log queries that have run longer than a given time, and you could then do some log analysis to match up long running queries with associated locks. Again, be cautious with setting this too low and making your logs too large to manage.
If you're looking for something to help with log analysis, I'd recommend pgbadger. If you have the above to options turned on, it allows the reports it generates to be very informative.