CODEWITHSUNDEEP
prometheusgrafana
Salvatore D'angelo
Salvatore D'angelo

Reputation: 1149

Combine multiple Prometheus queries in Grafana

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:

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?

Upvotes: 0

Views: 2581

Answers (1)

Jens Baitinger
Jens Baitinger

Reputation: 527

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)

Upvotes: 1

Related Questions