Search code examples
prometheusgrafana

How to get histogram average per param

I've got the following histogram metric db_query_duration_ms_sum (it's histogram so I've got _sum, _count and _bucket).

db_query_duration_ms_sum has a param query_name which is the name of a db query.

How do I create a bar gauge which displays the average duration per query name? Something like: enter image description here

As you can see, I'm pretty close. I'm trying to calculate it using:

sum(rate(db_query_duration_ms_sum{}[$__rate_interval])) by (query_name) / 
sum(rate(db_query_duration_ms_count{}[$__rate_interval])) by (query_name)

However, there are 2 issues:

  1. The above is returning a bunch of NaN. Within the bar gauge grafana options, I've selected "Value options/Calculation" to be Last (last non-null value) but that doesn't seem to make a difference. I want to display the average duration per each query within the selected timeframe.
  2. The rows in the bar gauge are not sorted. How do I sort them in desc order? I tried wrapping my query with sort_desc but yet again, that doesn't do anything. How do I sort the rows?
Solution

  • I'm not sure how you are getting Nans in your data, but to avoid Infs (due to number of requests being 0), you can use this query:

    sum(increase(db_query_duration_ms_sum [$__range])) by (query_name)
     / sum(increase(db_query_duration_ms_count[$__range])) by (query_name) < +Inf
  or sum(increase(db_query_duration_ms_count[$__range])) by (query_name)

    It filters out values of infinity, and if the appear - replaces them with zeros.

    And if you don't want zeros and prefer them to be missing - omit everything beginning with or.

    Based on OP's report, same query filters out Nans too. But since I cannot explain why it happened in the first place, I cannot explain how it helped too.