Btw, here's the new promql I ended up with, in case it's helpful to anyone
else:
((50 - 0) / (12 - 0) * ((avg_over_time(ambient_pm25_env{instance=~"$room.*"
}[$aqi_interval]) <= 12) - 0) + 0) or
((100 - 51) / (35.4 - 12.1) * ((avg_over_time(ambient_pm25_env{instance=~
"$room.*"}[$aqi_interval]) <= 35.4) - 12.1) + 51) or
((150 - 101) / (55.4 - 35.5) * ((avg_over_time(ambient_pm25_env{instance=~
"$room.*"}[$aqi_interval]) <= 55.4) - 35.5) + 101) or
((200 - 151) / (150.4 - 55.5) * ((avg_over_time(ambient_pm25_env{instance=~
"$room.*"}[$aqi_interval]) <= 150.4) - 55.5) + 151) or
((300 - 201) / (250.4 - 150.5) * ((avg_over_time(ambient_pm25_env{instance=~
"$room.*"}[$aqi_interval]) <= 250.4) - 150.5) + 201) or
((400 - 301) / (350.4 - 250.5) * ((avg_over_time(ambient_pm25_env{instance=~
"$room.*"}[$aqi_interval]) <= 350.4) - 250.5) + 301) or
((500 - 401) / (500.4 - 350.5) * ((avg_over_time(ambient_pm25_env{instance=~
"$room.*"}[$aqi_interval]) <= 500.4) - 350.5) + 401) or
clamp_max(avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval
]), 600)
On Tuesday, September 19, 2023 at 11:24:58 PM UTC-4 David Leibovic wrote:
> Thanks very much to you both for the suggestions! Changing the regexp
> matching to exact string matching didn't help noticeably in my case,
> perhaps because I have less than 5 instances over which it has to do a
> regexp match. But removing the unnecessary lower bounds checks from my
> inequality checks reduced loading time by about 3/5.
>
> It's too bad that prometheus doesn't support something like variables that
> could be repeatedly referenced in promql - that would probably speed things
> up even more.
>
> On Monday, September 18, 2023 at 3:23:43 AM UTC-4 Brian Candler wrote:
>
>> One possibility is to use a recording rule for the expensive repeated
>> query.
>>
>> If I
>> rewrite avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval])
>> to X, I get:
>>
>> ((50 - 0) / (12 - 0) * ((X <= 12) - 0) + 0) or
>> ((100 - 51) / (35.4 - 12.1) * ((X > 12 and X <= 35.4) - 12.1) + 51) or
>> ((150 - 101) / (55.4 - 35.5) * ((X > 35.4 and X <= 55.4) - 35.5) + 101) or
>> ((200 - 151) / (150.4 - 55.5) * ((X > 55.4 and X <= 150.4) - 55.5) + 151)
>> or
>> ((300 - 201) / (250.4 - 150.5) * ((X > 150.4 and X <= 250.4) - 150.5) +
>> 201) or
>> ((400 - 301) / (350.4 - 250.5) * ((X > 250.4 and X <= 350.4) - 250.5) +
>> 301) or
>> ((500 - 401) / (500.4 - 350.5) * ((X > 350.4 and X <= 500.4) - 350.5) +
>> 401) or
>> clamp_max(X, 600)
>>
>> I guess you're trying to apply different scaling for different ranges of
>> X:
>> - if X is between 0 and 12 (or negative) then rescale to 0 to 50
>> - if X is between 12 and 35.4 then rescale to 50(?) to 100
>> - if X is between 35.4 and 55.4 then rescale to 100(?) to 150
>> etc (except there seem to be some small discontinuities at the
>> boundaries, e.g. 12 versus 12.1, 50 versus 51)
>>
>> "A or B" will suppress elements in the B vector where the A vector has a
>> value (i.e. with a matching label set). That means it's unnecessary to test
>> the lower bounds, and I think your expression could simplify to something
>> like this:
>>
>> (X <= 12) * k1 + o1 or
>> (X <= 35.4) * k2 + o2 or
>> (X <= 55.4) * k3 + o3 or
>> (X <= 150.4) * k4 + o4 or
>> (X <= 250.4) * k5 + o5 or
>> (X <= 350.4) * k6 + o6 or
>> (X <= 500.4) * k7 + o7 or
>> clamp_max(X, 600)
>>
>> That would roughly halve the number of the subexpressions X.
>>
>> On Monday, 18 September 2023 at 07:01:50 UTC+1 Ben Kochie wrote:
>>
>>> One thing you can do to speed things up is to eliminate the `=~` in your
>>> query. Using regexp matching means it has to do a string search over every
>>> instance in your Prometheus for each metric. Using exact matching (`=`)
>>> will speed things up a lot. Although you won't be able to do multiple
>>> matching if you want that in your dashboard variables.
>>>
>>> On Mon, Sep 18, 2023 at 7:58 AM David Leibovic <[email protected]>
>>> wrote:
>>>
>>>> Hi there, I'm trying to optimize a slow query of this form:
>>>>
>>>> (1 * avg_over_time(foo{instance=~"$i"}[$interval]) <= 10) or
>>>> (2 * avg_over_time(foo{instance=~"$i"}[$interval]) <= 20) or
>>>> (3 * avg_over_time(foo{instance=~"$i"}[$interval]) <= 30) or
>>>> (10 * avg_over_time(foo{instance=~"$i"}[$interval]))
>>>>
>>>> I suspect it's slow because of the many duplicate calls to
>>>> avg_over_time(foo{instance=~"$i"}[$interval])
>>>>
>>>> Is there some way to only call the avg_over_time function once and
>>>> re-use the results subsequently? I'm using Prometheus in conjunction with
>>>> Grafana, in case it's relevant.
>>>>
>>>> The full query I'm trying to optimize is much more complicated, but I
>>>> figured the above would be enough to understand the problem. But in case
>>>> it's helpful, here is the full query I am trying to optimize (it's an Air
>>>> Quality Index computation):
>>>>
>>>> ((50 - 0) / (12 - 0) *
>>>> ((avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]) <=
>>>> 12) - 0) + 0) or
>>>> ((100 - 51) / (35.4 - 12.1) *
>>>> ((avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]) > 12
>>>> and avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]) <=
>>>> 35.4) - 12.1) + 51) or
>>>> ((150 - 101) / (55.4 - 35.5) *
>>>> ((avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]) >
>>>> 35.4 and
>>>> avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]) <=
>>>> 55.4) - 35.5) + 101) or
>>>> ((200 - 151) / (150.4 - 55.5) *
>>>> ((avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]) >
>>>> 55.4 and
>>>> avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]) <=
>>>> 150.4) - 55.5) + 151) or
>>>> ((300 - 201) / (250.4 - 150.5) *
>>>> ((avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]) >
>>>> 150.4 and
>>>> avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]) <=
>>>> 250.4) - 150.5) + 201) or
>>>> ((400 - 301) / (350.4 - 250.5) *
>>>> ((avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]) >
>>>> 250.4 and
>>>> avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]) <=
>>>> 350.4) - 250.5) + 301) or
>>>> ((500 - 401) / (500.4 - 350.5) *
>>>> ((avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]) >
>>>> 350.4 and
>>>> avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]) <=
>>>> 500.4) - 350.5) + 401) or
>>>> clamp_max(avg_over_time(ambient_pm25_env{instance=~"$room.*"}[$aqi_interval]),
>>>>
>>>> 600)
>>>>
>>>> Thanks for any help you can provide!
>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google
>>>> Groups "Prometheus Users" group.
>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>> an email to [email protected].
>>>> To view this discussion on the web visit
>>>> https://groups.google.com/d/msgid/prometheus-users/664689b3-9f45-4b05-9438-4225e2dce773n%40googlegroups.com
>>>>
>>>> <https://groups.google.com/d/msgid/prometheus-users/664689b3-9f45-4b05-9438-4225e2dce773n%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>> .
>>>>
>>>
--
You received this message because you are subscribed to the Google Groups
"Prometheus Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/prometheus-users/46b7e4ee-fb4e-4f6c-9b65-5955bc93b7b2n%40googlegroups.com.
