Assistance converting influx query to prometheus

[AK Mitchel]

Hi,

I am trying to convert the following query to Prometheus but am seeing some odd behaviour:

SELECT   non_negative_derivative("wait_time_ms", 1s) / non_negative_derivative("waiting_tasks_count", 1s)   FROM "sqlserver_azuredb_waitstats" WHERE ("sql_instance" =~ /^$InstanceName$/ AND "database_name'" =~ /^$DatabaseName$/) AND $timeFilter GROUP BY "wait_type"

This is what I have in prom

rate(sqlserver_azuredb_waitstats_wait_time_ms{sql_instance="$sql_instance",database_name="$databaseName"}[5m])>=0/rate(sqlserver_azuredb_waitstats_waiting_tasks_count{sql_instance="$sql_instance", database_name="$databaseName"}[5m])>=0 

The problem I have here is that the group by in the influx query ensures the division is done on the same label where as in prom it is matching with a label with a different name, I'm sure this is possible rather than me having to add a seperate query for each label in the above metrics but not sure how this can be achieved. 

[Brian Candler]

I'm not going to attempt to analyse your query in detail, since you haven't provided any examples of the metrics and labels in question, but I would expect you need to look at

foo / ON(...) bar

or

foo / IGNORING(...) bar

as described here, or GROUP_LEFT / GROUP_RIGHT as described here.

However I just wanted to point out that your expression is missing parentheses.  Operator precedence says that

foo >= 0 / bar >= 0

is parsed as

foo >= (0 / bar) >= 0

you want:

(foo >= 0) / (bar >= 0)

Having said that, the ">= 0" is redundant here, because rate() can only ever return a positive value.  If the input value drops, it's treated as a counter reset.

[AK Mitchel]

Thanks for the reply and feedback, I did try these but was still am still seeing some inconsistency, I have add the metrics just give an idea of what I am working with:

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="ASYNC_NETWORK_IO"} 20461

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="CMEMTHREAD"} 3080

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="CXCONSUMER"} 3083

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="CXPACKET"} 7046

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="INSTANCE_LOG_RATE_GOVERNOR"} 185

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="IO_COMPLETION"} 1180

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="LATCH_SH"} 184

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PAGEIOLATCH_EX"} 238

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PAGEIOLATCH_SH"} 2940

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PAGELATCH_EX"} 836677

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PAGELATCH_SH"} 440660

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PERFORMANCE_COUNTERS_RWLOCK"} 293

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="POOL_LOG_RATE_GOVERNOR"} 10127

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PREEMPTIVE_NTJOB_CALLS"} 74935

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PREEMPTIVE_OS_CRYPTACQUIRECONTEXT"} 866371

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PREEMPTIVE_XHTTP"} 718482

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="RESOURCE_GOVERNOR_IDLE"} 20840

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="RESOURCE_SEMAPHORE_QUERY_COMPILE"} 336606

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="SOS_SCHEDULER_YIELD"} 2.870625e+06

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="WRITELOG"} 2268

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="ASYNC_NETWORK_IO"} 20461

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="CMEMTHREAD"} 3080

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="CXCONSUMER"} 3083

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="CXPACKET"} 7046

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="INSTANCE_LOG_RATE_GOVERNOR"} 185

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="IO_COMPLETION"} 1180

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="LATCH_SH"} 184

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PAGEIOLATCH_EX"} 238

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PAGEIOLATCH_SH"} 2940

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PAGELATCH_EX"} 836677

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PAGELATCH_SH"} 440660

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PERFORMANCE_COUNTERS_RWLOCK"} 293

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="POOL_LOG_RATE_GOVERNOR"} 10127

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PREEMPTIVE_NTJOB_CALLS"} 74935

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PREEMPTIVE_OS_CRYPTACQUIRECONTEXT"} 866371

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="PREEMPTIVE_XHTTP"} 718482

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="RESOURCE_GOVERNOR_IDLE"} 20840

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="RESOURCE_SEMAPHORE_QUERY_COMPILE"} 336606

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="SOS_SCHEDULER_YIELD"} 2.870625e+06

sqlserver_azuredb_waitstats_wait_time_ms{database_name="$databaseName",sql_instance="$server",wait_type="WRITELOG"} 2268

And then I want to take each wait_type here and divide it by the following metric on the same wait_type label name if it is present

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="ASYNC_NETWORK_IO"} 18757

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="CMEMTHREAD"} 148

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="CXCONSUMER"} 780

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="CXPACKET"} 909

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="INSTANCE_LOG_RATE_GOVERNOR"} 33

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="IO_COMPLETION"} 437

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="LATCH_SH"} 19

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="PAGEIOLATCH_EX"} 60

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="PAGEIOLATCH_SH"} 437

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="PAGELATCH_EX"} 59317

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="PAGELATCH_SH"} 48521

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="PERFORMANCE_COUNTERS_RWLOCK"} 293

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="POOL_LOG_RATE_GOVERNOR"} 1059

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="PREEMPTIVE_NTJOB_CALLS"} 25600

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="PREEMPTIVE_OS_CRYPTACQUIRECONTEXT"} 179204

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="PREEMPTIVE_XHTTP"} 76800

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="RESOURCE_GOVERNOR_IDLE"} 2994

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="RESOURCE_SEMAPHORE_QUERY_COMPILE"} 5409

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="SOS_SCHEDULER_YIELD"} 1.391014e+06

sqlserver_azuredb_waitstats_waiting_tasks_count{database_name="$databaseName",sql_instance="$server",wait_type="WRITELOG"} 551

Here is my most recent query:

rate(sqlserver_azuredb_waitstats_wait_time_ms{sql_instance=“$server”,database_name= “$databaseName”}[5m]) / on (wait_type)  rate(sqlserver_azuredb_waitstats_waiting_tasks_count{sql_instance=“$server”, database_name=“$databaseName”}[5m])

now if I add a separate query and specify each wait_type as it's own query I get the desired result, but doing the above returns weirdness 

[Brian Candler]

What's the weirdness?  What do you see, and what would you expect to see?

What if you do the query halves separately:

rate(sqlserver_azuredb_waitstats_wait_time_ms{sql_instance=“$server”,database_name= “$databaseName”}[5m])

rate(sqlserver_azuredb_waitstats_waiting_tasks_count{sql_instance=“$server”, database_name=“$databaseName”}[5m])

Do they look reasonable?  And if you divide them by hand?

[AK Mitchel]

They are fine done manually and on their own own the only issue appears when I to the division in Prometheus on the label, if I specify the labels separately in their own query and do the division it is also fine. What I have noticed is there is some extra wait_types in the   sqlserver_azuredb_waitstats_wait_time_ms vector that aren't present in  sqlserver_azuredb_waitstats_waiting_tasks_count but don't think that should make a difference here

[Brian Candler]

Sorry, without seeing concrete examples I can't offer any opinion as to what might be the case.

Expression A shows X

Expression B shows Y

Expression A/B shows Z but I was expecting Q