My Following installation is as follows:
- Grafana 7.3.6
-Datasource : MySQL
-Windows Server 2019
-4 vCores Machine with 8Gb of RAM
-A table with 8 columns in the database that a gets new row with numerical values each 10 seconds arranged with respect to a date/time index (Time Series Data)
In Grafana I have 2 Graph panels that are plotting 2 columns with respect to time.
My Problem is as follows:
The graph panels in the dashboard displays the “Last 1 Hour” data.
as the database is empty in the beginning the displayed last 1 hour is updated successfully each time I log on to the dashboard to see the data. after about 2-3 days of operation the database gets filled up with data and the dashboard starts to load slower and slower each day until after about 5 days the dashboard can take up 40-60 seconds to update the graphs (and sometime it doesn’t update completely) .I also noticed that the server’s CPU starts to spike.
In each time I recall the last 1 hour of data which means the number of rows returned each time from the query is the same. so why it takes longer when the database has a higher number of total rows ?
My Query is as follows:
timecolumn AS “time”,
value2 = 1113
ORDER BY timecolumn
Notice: the refresh time of the dashboard is irrelevant I tried to set it off and refresh on demand, the time it takes to refresh the graph is the same.