2

We use check_postgres.pl to monitor our database.

We use this to check the count of the locks:

https://bucardo.org/check_postgres/check_postgres.pl.html#locks

We often see more than 150 locks.

The question was: What is going on? We patched the script to output this sql statement, if the lock count was exceeded:

select * from pg_stat_activity order by datname

Unfortunately the result is not what I was expecting. Although there are more than 150 locks, pg_stat_activity shows only few (less then 10) queries.

This has happened about twice a day during the last days, and every time only few lines where returned by pg_stat_activity.

What is going on here?

How to debug the current DB state if there are too many locks?

Improve this question
5
  • 1
    A single DML statement can easily obtain millions of locks. There is no relation to the number of statements and the number of locks. But why are 150 locks a bad thing? Unless they are held for a long time (minutes, hours) I don't see any reason to check this. Also: pg_stat_activity doesn't show "queries", it shows open connections. That is something completely different.
    – user1822
    Commented Dec 17, 2015 at 18:32
  • @a_horse_with_no_name ok you see no reason to monitor the number of short living locks. What is your general advice: don't monitor locks at all, or monitor them but different. Then the question is: how to monitor them?
    – guettli
    Commented Dec 17, 2015 at 18:57
  • Just for the records, I asked on the mailing list roughly the same question: mail.endcrypt.com/pipermail/check_postgres/2015-December/…
    – guettli
    Commented Dec 17, 2015 at 19:01
  • The only monitoring that would make sense to me is the duration of the locks (e.g. locks held for more than 5 minutes). Or the number of transactions waiting for a lock.
    – user1822
    Commented Dec 17, 2015 at 19:11
  • I created a different question which asks "How can I check for the lock count and ignore all locks which are only N seconds old?" dba.stackexchange.com/questions/124213/… The above question is still valid: How to debug the current DB state if there are too many locks?
    – guettli
    Commented Dec 21, 2015 at 9:11

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.