Graph panels loading is getting slower each day as the database gets filled with data

Hi There

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:
SELECT
timecolumn AS “time”,
value1
FROM table
WHERE
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.