divide metrics by instance

[Yashar Nesabian]

Hi

I want to calculate Postgres locks using Postgres exporter, I found this template here :

- alert: PostgresqlTooManyLocksAcquired

    expr: ((sum (pg_locks_count)) / (pg_settings_max_locks_per_transaction * pg_settings_max_connections)) > 0.20

    for: 5m

    labels:

      severity: critical

    annotations:

      summary: "Postgresql too many locks acquired (instance {{ $labels.instance }})"

      description: "Too many locks acquired on the database. If this alert happens frequently, we may need to increase the postgres setting max_locks_per_transaction.\n  VALUE = {{ $value }}\n  LABELS: {{ $labels }}"

the problem is, pg_locks_count counts all the locks on all of my Postgres instances and pg_settings_max_locks_per_transaction * pg_settings_max_connections shows the result per each Postgres host. So I don't get any result,

I've Also tried this query:

((sum (pg_locks_count))  / (pg_settings_max_locks_per_transaction * pg_settings_max_connections)) by (instance)

and

((sum (pg_locks_count)by (instance))  / (pg_settings_max_locks_per_transaction * pg_settings_max_connections)) 

But none of them works (I guess it is because of many to many division)

How can I get this result per instance?

[Brian Candler]

Can you show some examples of the raw metrics:

pg_locks_count

pg_settings_max_locks_per_transaction

pg_settings_max_connections

including their complete label sets.  It should then be clearer how to combine these.

[Yashar Nesabian]

Sure,

pg_locks_count:

pg_locks_count{datname="postgres",instance="postgres-01.db.x.y.z:9187",job="postgres_exporter",mode="accessexclusivelock",server="127.0.0.1:5432"}

 pg_locks_count{datname="postgres",instance="postgres-01.db.x.y.z:9187",job="postgres_exporter",mode="accesssharelock",server="127.0.0.1:5432"}    
... 

pg_settings_max_locks_per_transaction:

pg_settings_max_locks_per_transaction{instance="postgres-01.db.x.y.z:9187",job="postgres_exporter",server="127.0.0.1:5432"}
...

pg_settings_max_connections:

pg_settings_max_connections{instance="postgres-01.db.x.y.z:9187",job="postgres_exporter",server="127.0.0.1:5432"}
...

[Brian Candler]

OK, the instance labels look consistent.  So the first thing I suggest is breaking your attempted query into parts and look at the results:

sum (pg_locks_count) by (instance)

pg_settings_max_locks_per_transaction * pg_settings_max_connections

Do both of those give reasonable answers?  If so you should be able to combine them with the division operator.  But if label sets are not identical you'll need to tell it which labels to match or ignore.  You could use

A / on(instance) B

A / ignoring(mode,datname) B

So in this case, given that you've already summed by instance, I would suggest:

(sum (pg_locks_count) by (instance)) / on (instance) (pg_settings_max_locks_per_transaction * pg_settings_max_connections)

[Yashar Nesabian]

Thanks for your help, it works!