Query Timeout InfluxDB on 3Month Power Calculation

Grafana InfluxDB
1

Hi,

i have Grafana 9.4.7 installed on my Raspberry Pi 4B with 64bit Raspian OS

My Problem: I have a query for Power Consumption per Month for the last 3 Months. Grafana is calculating very long for this Query with the result that i get a Query Timeout.

How can i optimize that Query ?

import "timezone"
// Set location to be Europe/Berlin
option location = timezone.location(name: "Europe/Berlin")

from(bucket: "solarkraftwerk")
  |> range(start: -2mo, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "solarkraftwerk")
  |> filter(fn: (r) => r["_field"] == "totalPmeter")
  |> difference()
  |> truncateTimeColumn(unit: 1s)
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false, timeSrc:"_start")

In this case i have reduced the Query to 2 Months (this works) but calculates very long.

2

Hi @starfoxfs

How often are you sending the power readings into InfluxDB? Every 1 second? Every 1 minute? Something else?

3

I have a “Hichi” IR Connector with Tasmota Firmware, it sends data all 60 seconds.

4

Does the query perform better / faster by removing the truncateTimeColumn function, like this?

import "timezone"
// Set location to be Europe/Berlin
option location = timezone.location(name: "Europe/Berlin")

from(bucket: "solarkraftwerk")
  |> range(start: -2mo, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "solarkraftwerk")
  |> filter(fn: (r) => r["_field"] == "totalPmeter")
  |> difference()
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false, timeSrc:"_start")
5

Hi Grant2,

yes it´s faster, but then i have wrong calculation because the calculation without truncateTimeColumn(unit: 1s) is too “coarse”.

6

Does the above mean that you send data every second, or every 60 seconds?

7

Hi,

every 60 seconds

8

OK, since your data arrives every minute, does changing your truncateTimeColumn function to this improve the performance?

|> truncateTimeColumn(unit: 1m)

9

Hi grant2,

a little improvemend, but Query also timeouts if i do the Query for a Time Range of 3month.

2month works but needs very long to calculate.

:Update: I have deleted truncateTimeColumn(unit: 1m) for the Power Consumption, it makes no difference, the values with or without are the same.

For Solar yield i need the truncateTimeColumn(unit: 1m) line because if i delete this here the calculated values are wrong.

10

Hi @starfoxfs

Are you sure that difference() is the right function for this? Since you are recording data every minute, the difference() function is not going to calculate the difference between the first and last unless one defines the first and last points of the month (which one can do). I would think spread() would work, so long as your readings are always rising over the month, correct?

11

Hi Grant2,

my readings from the electrity meter are also every minute (60 seconds), so i calculate the summary for 1 month and use difference for the difference to the last month.

So far this work for me, i can test spread instead of difference

12

Are these true in your case?

Screenshot_20230423-121121_Chrome
Screenshot_20230423-121121_Chrome1080Ă—2220 103 KB

Do you have tags got your data

https://community.grafana.com/t/flux-querys-with-variables-are-very-slow-when-running-inside-grafana/75295/4

13

Hi yosiasz,

the Values of my Power Meter is automaticly safed in a Field with the marking “no group” and “double”

Unbenannt
Unbenannt1920Ă—1032 158 KB

14

I have now splitted my measurements into 2

Solar and Power
Solar with the Fields ampere, voltage e.g. and added for Solar a device1 Tag Shelly EM so it´s indexed
Power is from the Electric Meter with only 2 fields TotalPmeter and Watt. I also added here device2 Hichi as a Tag

Unbenannt
Unbenannt1920Ă—1021 161 KB

1 Like
15

Vwry good. Did you read the documentation I posted? All of it?

16

Not all but the most :grinning:

I understand that with Tags the Influxdb2 is indexed and that´s performing better.

17

That is a great start. Keep reading about other key speed improvement ideas. But also understand why exactly they improve performance

1 Like