Grafana mysql cpu load

Grafana
1

Hello,

I have an issue with grafana and our mysql server . I have this query whose taking more than 30 second to be execute and my cpu is too high.

I don’t know why and who make this query :

 SELECT (SELECT COUNT(*) FROM `user`) AS users,(SELECT COUNT(*) FROM `org`) AS orgs,(SELECT COUNT(*) FROM `dashboard`) AS dashboards,(SELECT COUNT(*) FROM `data_source`) AS datasources,(SELECT COUNT(*) FROM `star`) AS stars,(SELECT COUNT(*) FROM `playlist`) AS playlists,(SELECT COUNT(*) FROM `alert`) AS alerts,(SELECT COUNT(*) FROM `user` where last_seen_at > ?) AS active_users,(SELECT COUNT(id) FROM `dashboard` where is_folder = ?) AS folders,(
            SELECT COUNT(acl.id)
            FROM `dashboard_acl` as acl
                    inner join `dashboard` as d
                    on d.id = acl.dashboard_id
            WHERE d.is_folder = ?
    ) AS dashboard_permissions,(
            SELECT COUNT(acl.id)
            FROM `dashboard_acl` as acl
                    inner join `dashboard` as d
                    on d.id = acl.dashboard_id
            WHERE d.is_folder = ?
    ) AS folder_permissions,(SELECT COUNT(id) FROM `dashboard_provisioning`) AS provisioned_dashboards,(SELECT COUNT(id) FROM `dashboard_snapshot`) AS snapshots,(SELECT COUNT(id) FROM `team`) AS teams,(SELECT COUNT(id) FROM `user_auth_token`) AS auth_tokens,
            (
                    SELECT COUNT(*)
                    FROM `user` as u
                    WHERE
                    (SELECT COUNT(*)
                            FROM org_user
                            WHERE org_user.user_id=u.id
                            AND org_user.role='Viewer')>0
            ) as viewers,
            (
                    SELECT COUNT(*)
                    FROM `user` as u
                    WHERE
                    (SELECT COUNT(*)
                            FROM org_user
                            WHERE org_user.user_id=u.id
                            AND org_user.role='Viewer')>0
                    AND u.last_seen_at>?
            ) as active_viewers,
            (
                    SELECT COUNT(*)
                    FROM `user` as u
                    WHERE
                    (SELECT COUNT(*)
                            FROM org_user
                            WHERE org_user.user_id=u.id
                            AND org_user.role='Editor')>0
            ) as editors,
            (
                    SELECT COUNT(*)
                    FROM `user` as u
                    WHERE
                    (SELECT COUNT(*)
                            FROM org_user
                            WHERE org_user.user_id=u.id
                            AND org_user.role='Editor')>0
                    AND u.last_seen_at>?
            ) as active_editors,
            (
                    SELECT COUNT(*)
                    FROM `user` as u
                    WHERE
                    (SELECT COUNT(*)
                            FROM org_user
                            WHERE org_user.user_id=u.id
                            AND org_user.role='Admin')>0
            ) as admins,
            (
                    SELECT COUNT(*)
                    FROM `user` as u
                    WHERE
                    (SELECT COUNT(*)
                            FROM org_user
                            WHERE org_user.user_id=u.id
                            AND org_user.role='Admin')>0
                    AND u.last_seen_at>?
            ) as active_admins

Do you know How can I stop this query and why grafana is doing this query everytime.

Best regards

2

Hello,

I have an issue with grafana and our mysql server . I have this query whose
taking more than 30 second to be execute and my cpu is too high.

I don’t know why and who make this query :

… long and complex SQL …

Do you know How can I stop this query and why grafana is doing this query
everytime.

I know neither why it’s happening nor how to stop it, but assuming for a
moment that there’s a good reason for it, have you tried using “explain” at
the start of it in MySQL to find out where the efficiency bottleneck might be,
and whether you can allow the query to run provided it is made more efficient,
perhaps by adjusting your table indexes or similar?

Antony.

3

Hi modogo2000,

Grafana was made for doing the querys everytime.

5

To reduce the execution time do I need to change some variable or doing something or it’s my mysql server ?

6

You can change de refresh interval or disable it, also can limit the number of rows that are queried

7

I am a bit confused by the query. Are the tables it is querying ones that you have control over or are they grafana admin tables? If the latter what are they doing in your mysql database, or are you using mysql for the grafana admin?

8

How can I change the refresh interval where ?

9

I’m using mysql for my grafana server. This query is made by grafana himself.

10

Can you explain in more detail what you mean by that? What exactly are you using mysql for?

12

I’m not using sqlite for my backend but mysql

[database]

Grafana needs a database to store users and dashboards (and other things). By default it is configured to use sqlite3 which is an embedded database (included in the main Grafana binary).

url

Use either URL or the other fields below to configure the database Example: mysql://user:secret@host:port/database

type

Either mysql , postgres or sqlite3 , it’s your choice.

13

Well unless you have thousands of users, dashboards etc configured it is difficult to see how any such query could take that long, assuming your processor is not ECL83 based. If you run a command line mysql interface and make simple queries does it run ok?

14

you can change on the timepicker

15

My suggestion is that you let Grafana use sqlite3 as its own internally manage and process, and check how it works, you can still connecting Grafana to a MYSQL as a data source if you are using it for your graphics. sqlite3 is file based and this kind of recurrent queries are managed better rather than mysql.