Hi All,
We have been sing Gafana since last month for plotting some metric into the dashboard,
Interestingly, When our data set starts to grow the performance of dashboard decreases drastically.
Use Case
- Panels used:- 8
- database :- Mysql
- Time taken by the query to run from sql client :- < 500 ms
- Dashboard load Time if i select time duration as 30 days > 10 sec and at many times one or more panels keeps on loading.
- Total Number of rows in DB >2.2 million records
- Records returned in response 15k in 1 panel
- Grafana version 5.2.1
- OS Ubuntu
Can you please guide us how to tune Grafana to avoid such issues?
Thanks
Akshat
2 Likes
Hi,
Iām experiencing same problem. In my case, it take nearly 30mins to load, as I have more than 53min records in the database (postgres). Whatās the way out?
If you run the same query in a different client (command line for example), on the same machine that is running grafana, does it take a long time?
Oh, yes!
With following query it takes nearly 15 minutes to finish execution
postgres=# SELECT date_trunc('minute', to_timestamp(recvtime,'YYYY-MM-DD HH24:MI:SS')) AS "time", avg(attrvalue::float) AS particles FROM urbansense.basic_003_airqualityobserved WHERE attrname = 'particles' AND attrvalue <> 'null' AND to_timestamp(recvtime,'YYYY-MM-DD HH24:MI:SS') > now()-'1d'::interval
And one other Panel is set to retrieve data points from table of almost three years time series (nearly 90 million records).
I am no expert in sql, but by doing calculations on the timestamp within the WHERE clause means, I think, that it needs to do that on every single record in the db before testing each one. Can you not get the timestamp into the db as a real timestamp?
" as a real timestamp ". Can you please elaborate this point?
Rather than me specifying what a real timestamp is can you explain what the above is doing? I canāt immediately see any documentation for to_timestamp
Ah, ok.
The column recvtime stores the time for which measures were recorded (sample table content below):
Ā±--------------------------+
| recvtime |
Ā±--------------------------+
| 2017-08-26T21:07:30+01:00 |
| 2017-08-26T21:07:30+01:00 |
| 2017-08-26T21:07:29+01:00 |
Ā±--------------------------+
Documentation: to_timestamp()
I had not noticed that you are using postgresql. So you are storing the timestamp as a string. Donāt do that, store it as a timestamp type. Then make sure there is an index on that column. Obviously the query then becomes something like
SELECT recvtime AS "time", avg(attrvalue::float) AS particles FROM urbansense.basic_003_airqualityobserved WHERE attrname = 'particles' AND attrvalue <> 'null' AND recvtime > now()-'1d'::interval
That should vastly speed up the query. Make sure you also have indices on attrname and attrvalue.
I donāt know much about postgres, what does the attrvalue::float mean in the avg() clause?
Just done some searching and it appears that attrvalue::float may mean cast the attrvalue to a float. Does that mean it is a string or is it an integer? If it is a string then that is horrible as it will have to convert all the values before averaging them. If it is an integer then that is ok.
I have done a check and it is actually a text (string). The schema was created by the application persisting the data to postgres.
What is text? The timestamp or attrvalue or both? For efficient lookup and averaging you need to change the schema so the timestamp a timestamp and attrvalue is a number type.
Im also getting same issue with below query and having 10 Million data and filtering last 30 mints is taking time and it will return 25k records at a time
SELECT sum(āvalueā) FROM āservice__activeā WHERE (āfaro_deploymentā =~ /^environment/ AND āregionā =~ /^region/ AND āservicenameā =~ /^service_name/) AND timeFilter GROUP BY time(__interval), āuriā, āhttpmethodā fill(null)
