Grafana does not kill mysql operations when a dashboard is closed

Grafana MySQL
1

  • What Grafana version and what operating system are you using?
    OS: Ubuntu 20.04.5
    Mysql: 8.0.32
    Grafana: 8.5.20

  • What are you trying to achieve?
    Some Grafana dashboards take a lot of time to load where the time interval is long (30 days), so I’m trying to improve database schemes and all configurations to use the server resources more efficiently.

  • What happened?
    I’ve found that when a grafana dashboard is opened (especially with a long period of time, so a large amount of data to be processed) several mysql operations are spawn to get the data, what I can see with the command ‘show processlist’. And if the dashboard is closed before the data is collected and the graphs are rendered, the mysql operations are not killed and the stay alive until mysql decide to kill them.

  • What did you expect to happen?
    I was expecting Grafana to close those operations when you close a dashboard, because the data isn`t already needed.

  • Can you copy/paste the configuration(s) that you are having problems with?
    I’ve not found any configuration to change this behavior.

  • 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 upgraded Grafana from version 7.4.3 to 8.5.20, but the problem persists.

2

Hi, I’ve been doing some test and I’ve found that if I open a dashboard, I set a long time interval and then immediately close the browser, the mysql operations initiated by grafana-server get closed after 4.5 minutes aproximately. I’ve setup mysql to close slept connections after 15 minutes and to close active queries that takes more than 30 minutes. I’ve registered the time that takes the dashboard to collect data and render graphs and it’s 4.5minutes approximately, so I guess that grafana-server is not realizing the client has closed the dashboard and continue collecting the requested data. Can it be configured so it could identify the client is not requiring the data any more and close those mysql operations?

3

hello

What problem are you trying to solve by killing these mysql operations? Is it a cost issue? a Performance issue?

4

Could you please share these queries that take a long time? How long does it take to run the same query in mysql itself?

5

Hi yosiasz, I’m experiencing performance issues when multiple dashboard are opened and closed through time.
This is one query of one of the panels:

SELECT
  addtime(fecha_hora, "03:00:00") AS "time",
  valor as "u_l1l2"
FROM mediciones JOIN valores_mediciones
ON mediciones.id=valores_mediciones.id_medicion
WHERE
  referencia="u_l1l2"
  AND
  id_ngs=1
  AND
  $__timeFilter(fecha_hora)
ORDER BY fecha_hora

I’ve not tried to execute the query directly in mysql but if a I open the dashboard and wait for it to finish it takes 4.5 minutes approximately. I will execute the shown query directly in mysql and then I’ll publish the time.
I’m improving the database schemas to get a better performance, but it’s big problem to have a set of mysql operations executing for a while after a dashboard is closed.

6

yes that is ridiculously long time for a query.
Remember if you query is not optimized and you do not have indices for the columns you are filtering on, it will be slow in mysql iteslf. anything slow in mysql will not get faster in grafana, in fact it might be slower.

So try it in mysql first and if it is slow in mysql then you know where to focus.
For example
do you have indices on

  1. mediciones.id
  2. valores.id_medicion?
  3. referencia
  4. id_ngs
  5. fecha_hora?

Test the above query in mysql 1st.

7

once grafana fires that query, it has to wait for mysql to give it a response. and if the reponse takes 4 minutes+, I doubt grafana has a mechanism of managing and closing connections that take such a long time.

8

I don’t know how grafana-server is implemented, but from python I’ve been able to kill mysql processes when I wanted. The KILL statement can be used for that or the corresponding API to that statement of the mysql library in use. Can grafana-server realized if a client has closed a dashboard? I guess it is possible, because the client will send a new http request, for example to move to the initial dashboard. I think in that case it would be sane that grafana-server kill the previous mysql operations, because those data are already not needed.

1 Like
9

how would it know those are not needed.
Great questions but you need to first sort out the performance issues you are having

10

Hi yosiasz, I think grafana-server could determine that a client has closed recently opened dashboard making use of cookies to differentiate each client and taking into account the last http request. In that way, if a client open a dashboard and close it before it ends collecting data and rendering graphs, grafana-server could kill the database operations for that dashboard.
The performance issues with my database schema is a different matter which I’ working in right now.

1 Like