PMM - Postgres Alarm creation for row lock contention

Percona Monitoring and Management (PMM)
1

Hi, I would need to implement an alter so that it is notified when a contention lasts more than a certain number of minutes, and therefore probably will not resolve itself, but could block the table indefinitely.

I tried to look in the various metrics, but I did not find anything specific, by chance has anyone of you already faced and resolved a similar situation?

Thanks so much

Have a nice day
Ivan

2

Hi Ivan,

It will involve several customization steps, but it can definitely be done.

First, you need to add a custom query exporter to collect data on locks. You can get an idea on what query to use from:
https://wiki.postgresql.org/wiki/Lock_Monitoring

From a PMM standpoint, seconds will be better than “age”, so just use EXTRACT(EPOCH …) instead. An example query could be:

SELECT a.datname,
         l.relation::regclass,
         l.mode,
         extract(epoch from age(now(), a.query_start)) AS "seconds"
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
JOIN pg_class c ON c.oid = l.relation
WHERE c.relkind = 'r';

And for custom queries (this is for MySQL, but the same applies for Postgres) check the following blog:

Then, once you have the metric you want, you can create a new custom template alert using it:

After you have all this in place, you can easily create a new alert.

Note that I haven’t tested any of this, but I think it’s a good starting point for what you should check/investigate further. Hope it helps :slight_smile:

3

Hi Agustin

thank you very much for your interest and for providing me with this solution.

I thought it was possible to retrieve this information directly from the default metrics, but apparently it is not so.
I will continue to analyze the solution and will try to follow your lines.

I hope to be able to give you updates as soon as possible.

If others in the Forum have any other indications, I would be very interested in learning more.

Thanks
Have a nice day

Ivan