MySQL query efficiency

Grafana MySQL
1
  • What Grafana version and what operating system are you using?
    v9.2.2
  • What are you trying to achieve?
    Create Time series Panel with data in MySQL having indexed time column and filtering on small time interval
  • How are you trying to achieve it?
    indexed time column and filtering on small time interval - but needs long time to respond
    using additional view on same data with filter on one day - resond is much more faster
    similar access via SQL filter via Excel on same db responds within 2 seconds, so very fast
  • What happened?
    long time needed to get data view
  • What did you expect to happen?
    to be faster, similar as excel
  • Can you copy/paste the configuration(s) that you are having problems with?
    one db with 2 mio data rows and 63 columns, indexed on timestamp-column
    Timeseries is created with:
    SELECT time,PVPower,Gridpower,Battpower FROM db ODER BY time ASC
  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    no
  • Did you follow any online instructions? If so, what is the URL?

I have 2 millions records on 63 column, usually one record every 5 seconds.
I am using
SELECT time,PVPower,Gridpower,Battpower FROM db ODER BY time ASC
to fetch the data for display, and using filter within grafana to show last 6 hours.
Respond time is approximately 40 sec.
I do the exact same within excel, using a connection to the MySQL-Server and adding the filter to the sql
SELECT * FROM db WHERE time BETWEEN ā€œ2022-10-28 00:00:00ā€ AND ā€œ2022-10-29 00:00:00ā€;
which responds with the data within 2 seconds.
Right now i am using the trick to have an additional view in MySQL which filters the amount of data to the last day only, which speeds up the respond but is not what i preferā€¦ as this would disable and view on older data in the same panel.
Also i can use a
WHERE time BETWEEN ā€œ2022-10-30 00:00:00ā€ AND ā€œ2022-10-31 00:00:00ā€
in the sql statement which will also speed up the respond. My assumption would have been that grafana is doing exactly this kind of where-clause to fetch the data?
So what am i doing wrongā€¦ shouldnā€™t the amount of data transferred and the process of selecting data be the same as i do with excel?
Maybe i am missing a trick here, how to connect more sophisticated to the mysql-dbā€¦ any hint would be very much appreciated.

2

Grafana does not automagically filter your data. You need a WHERE clause with $timefilter.

Check out the documentation for $timeFilter

3

I did read this

and therefore did try with
SELECT time,PVPower,Gridpower,Battpower FROM db WHERE $__timeFilter ODER BY time ASC
i did also check with $timeFilterā€¦ nosuccess.
In both cases i get
db query error: Error 1054: Unknown column ā€˜$__timeFilterā€™ in ā€˜where clauseā€™
The expression
SELECT time,PVPower,Gridpower,Battpower FROM db $__timeFilter ODER BY time ASC
shows a resultā€¦ but not an improved response timeā€¦

4

Got It !!!
You have moved my search into the right direction.
The syntax seems to be
SELECT time,PVPower,Gridpower,Battpower FROM db WHERE $__timeFilter(time) ODER BY time ASC
This speeds up things

2 Likes