Combine multiple Prometheus queries in Grafana

Question

I have a PostgreSQL cluster I am going to monitor with Grafana 7.2.0 and Prometheus. In particular, I want to show on three SingleStat (one for node) of the same dashboard who is the master, replica-sync, and replica-potential.

I have the following metrics to understand who is master and replica (notice the field role).

patroni_patroni_info{group="testdb",instance="host0:9547",job="db-test",name="my-node-0",role="master",scope="main",state="running",version="2000.0.1"} 1
patroni_patroni_info{group="testdb",instance="host1:9547",job="db-test",name="my-node-1",role="replica",scope="main",state="running",version="2000.0.1"}    1
patroni_patroni_info{group="testdb",instance="host2:9547",job="db-test",name="my-node-2",role="replica",scope="main",state="running",version="2000.0.1"}    1

the problem is that information about who is the replica sync is in another prometheus metrics (notice the field sync_state):

patroni_replication_info{application_name="patroni1", client_addr="10.0.0.1", group="testdb", instance="host0:9547",job="db-test",name="cfdb-node-0",state="streaming",sync_priority="1",sync_state="potential",usename="backup_user"}  1
patroni_replication_info{application_name="patroni2", client_addr="192.10.9.1", group="testdb",instance="host0:9547", job="db-test",name="cfdb-node-0",state="streaming",sync_priority="1",sync_state="sync",usename="backup_user"}

As you can notice node0 is the master, node1 is replica-potential, and node2 is replica-sync. My problem is that I do not know how to combine these metrics in Grafana SingleStat in order to show:

Master
Replica-Sync
Replica-Async

SingleStat in Grafana 7.2.0 uses Transformation to combine queries but it seems they don't fit my needs. Moreover, it is impossible to make join because there are no common fields. Can someone help me with this?

Jens Baitinger · Accepted Answer · 2021-01-25 13:20:55Z

1

A strategy I would use is to "calculate" the role in the query e.g. 1 for the master, 2 for the Replica-Sync, 3 for the Replica-Async and later use then use the value metrics feature to map these number to the corresponding strings (or maybe colors if you find that a better visualisation)

You can multiply the metrics and add them

# only one of these sub queries should return a result per node

# this will return 1 if the node is the master
patroni_patroni_info{role="master"} 
 or
# this will return 2 if the node is an async replica
2 * patroni_patroni_info{role="replica"} * patroni_replication_info{sync_state="potential"} 
 or
# this will return 3 if the node is a sync replica
3 * patroni_patroni_info{role="replica"} * patroni_replication_info{sync_state="sync"}

unfortunately I cannot test the query without a prometheus containg the data. So if it does not work try the 3 subqueries seperately and then add them up, so it gives you one number per node. Maybe you need to fiddle around with on, ignoring, group_left and group_right (see here)

edited Jan 25, 2021 at 13:20

answered Jan 25, 2021 at 11:11

Jens Baitinger

5334 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Salvatore D'angelo Over a year ago

Yes, in the end, this was the solution I adopted. Thank you for reply.

Collectives™ on Stack Overflow

Combine multiple Prometheus queries in Grafana

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related