integrate function does not return what I expected (maybe newbie misunderstanding?)

[bjoernbg]

Hi there,
I'm thinking about switching from InfluxDB to VictoriaMetrics in hope for better performance and reliability (I'm looking at storing a moderate number of metrics at minute resolution). As a first step to evaluate I exported my data from Influx into Influx line protocol and imported this into VM. The import of 300 MB of data was very fast so things were looking great. So I started fiddling around with queries in Grafana and am still a bit dumbfounded by PromQL/MetricsQL. InfluxQL with its SQL-like syntax for sure was easier accessible ;) But not to worry, I'm sure I'll get the hang of it.

However, as a first step I want to reproduce one of my most important calculations from Influx – an integral. I'm scraping data from my solar panel electricity production and want to calculate the area under the curve for a certain time frame (typical the selected time window in Grafana). However, the integrate(…) function returns vastly different results to InfluxQL's integral() function – much higher. There is also no documentation in its usage, so I'm not sure I'm doing everything correctly. The result of the integral in the InfluxQL example is definitely correct, I don't know what VictoriaMetrics is calculating there…

Btw.: VictoriaMetrics' data folder is 10% of the InfluxDB's data folder for the data I tested with (5 MB to 50-60 MB!), so great work!

[bjoernbg]

Doing some more tests I'm still not much further. I noticed that I made a mistake with the VM Stat Panel – the 6.135 MWh was shown as the MEAN, wheras for Influx I had selected LAST(NOT NULL). But even changing that doesn't help.

But to help with understanding my problem a bit better I created this comparison, showing the metric I'm integrating over as well as the integral. I think it's immediately obvious that the Integral can't be correct – it's not possible to reach ~20000000 Wh with an average of maybe 2000 oder 3000 over 6 hours…

Surely I must be overlooking something here?

[hagen1778]

Hi @bjoernbg! Thanks for report! Marking as bug for further prioritizing.

[valyala]

@bjoernbg , integrate(m[d]) function from MetricsQL calculates the real integral on m values for the last d time duration. If AC_Solarleistung is in Watts, then integrate(AC_Solarleistung[1h]) returns the energy in Joules produced during the last hour. The energy may be converted to kW*h by multiplying Joules by 2.78e-7, i.e. integrate(AC_Solarleistung[1h]) * 2.78e-7 would return kW*h.

[bjoernbg]

@valyala Thank you so much. The way InfluxDB handles the integral() function threw me off, with your adjustment it works:

The screenshot shows the same data taken from InfluxDB (right side) and from VictoriaMetrics (left side). Obviously the InfluxDB variant is a bit more fleshed out since its from my current dashboard. There is a small difference in the total value shown, but at around 1 to 3% difference this might be acceptable. I'm only taking measurements at 30s intervals anyway. It might be interesting to look into why InfluxDB is producing different values for you, maybe (I tried specifying the Joule-factor with more precision but that didn't change much). It is interesting though, that the integral() function in Influx gives me pretty much exactly the sums (over longer time ranges) that my inverters provide – VM ist around 3% off (lower).

However… It proved a bit difficult to me to get one result for the whole selected time range ("Energy Produced"). With InfluxDB this is quite simple by using an aggregate function. For VictoriaMetrics I had to set "Minimum Step" to 1h and take the "Total" with Grafana's Single Stat panel to get the result shown here:

This seems a bit cumbersome and maybe error prone to me. Is there a better way to go about this? I'm also quite unclear on how to do calculations with multiple (total) values since VictoriaMetrics always gives back a time series regardless if I only ask for an aggregate. Maybe I'm still to new with MetricsQL?

[valyala]

It is interesting though, that the integral() function in Influx gives me pretty much exactly the sums (over longer time ranges) that my inverters provide – VM ist around 3% off (lower).

That's interesting observation that needs additional investigation. I'll look into it.

It proved a bit difficult to me to get one result for the whole selected time range ("Energy Produced"). With InfluxDB this is quite simple by using an aggregate function

Try something like integrate(AC_Solarleistung[$__range]). This uses $__range template variable from Grafana, which is set to the selected time range - see https://grafana.com/docs/grafana/latest/variables/variable-types/global-variables/#__range for details.

Note also that the query from the last screenshot - integrate(sum(...)) - is usually incorrect - it must be written as sum(integrate(...)) instead. See https://www.robustperception.io/rate-then-sum-never-sum-then-rate for details.

[valyala]

@bjoernbg , could you build VictoriaMetrics from the commit e6da63d and verify whether it improves accuracy for integrate() function? See build instructions.

Take a look also at running_sum and range_sum functions from MetricsQL, which could be useful for your case:

• running_sum(integrate(AC_Solarleistung)) would return running sum of the produced energy on the selected time range.
• range_sum(integrate(AC_Solarleistung)) would return the total sum of the produced energy on the selected time range.

Note that square brackets inside integrate are omitted in queries above. In this case VictoriaMetrics automatically adjusts lookbehind window when calculating integral (and any other range function) depending on the distance between the returned points.

[bjoernbg]

@valyala I'm on the road on a business trip currently but appreciate your feedback and efforts a lot and will try to have a go at your suggestions on the weekend (als the custom build).

[valyala]

FYI, the commit mentioned above has been included in v1.41.0.

[bjoernbg]

Try something like integrate(AC_Solarleistung[$__range]). This uses $__range template variable from Grafana, which is set to the selected time range - see https://grafana.com/docs/grafana/latest/variables/variable-types/global-variables/#__range for details.

In my tests this produced results that didn't have anything to do with what I'm looking for. When I divide the result bei number of hours in my time range, the result becomes more realistic but still almost 100 kWh off:

Note also that the query from the last screenshot - integrate(sum(...)) - is usually incorrect - it must be written as sum(integrate(...)) instead. See https://www.robustperception.io/rate-then-sum-never-sum-then-rate for details.

Nicely spotted, but this actually has to do with my specific setup: I have two inverters that log their data to the database, which I can access like this: AC_Solarleistung{wr="wr1"}. Since I'm always interested in the total of both I take the sum first. This is something I can see becoming problematic but for now seems to be working fine. I could also always go back and do it the other way round.

@bjoernbg , could you build VictoriaMetrics from the commit e6da63d and verify whether it improves accuracy for integrate() function? See build instructions.

I updated my VictoriaMetrics build (using the 1.41.0 docker image) but didn't see any changes in Grafana.

Take a look also at running_sum and range_sum functions from MetricsQL, which could be useful for your case:

* `running_sum(integrate(AC_Solarleistung))` would return running sum of the produced energy on the selected time range.

* `range_sum(integrate(AC_Solarleistung))` would return the total sum of the produced energy on the selected time range.

range_sum(…) did indeed do the trick, as you can see from my screenshots above. Thanks!

Note that square brackets inside integrate are omitted in queries above. In this case VictoriaMetrics automatically adjusts lookbehind window when calculating integral (and any other range function) depending on the distance between the returned points.

When I omit the [1h] the results are working now together with range_sum(…) – they didn't really when I calculated the total with Grafana, but I guess that makes sense. The total result is minimally different when omitting the square brackets, though:

[valyala]

In my tests this produced results that didn't have anything to do with what I'm looking for. When I divide the result bei number of hours in my time range, the result becomes more realistic but still almost 100 kWh off:

There is no need to wrap integrate(m[$__range]) into range_sum(). The original query should return an integral for m over the selected time range in Grafana.

[petr-tichy]

Just to add my 2 cents here:
1 Wh = 3600 W*s (or Joule) so instead of the cumbersome and imprecise x * 0,0002777777778 one can just use simple
x / 1h
Thanks for clever design of MetricsQL

[valyala]

Closing this issue as resolved.