Hello all
When doing an upgrade to Grafana 9.3.8 yesterday, we noticed our MySQL database that Grafana is using is somewhat slow. It appears the following query is consuming our IOPS credits faster than what they can accumulate over time.
SELECT (SELECT COUNT(*) FROM `user` WHERE is_service_account = 0) AS users,(SELECT COUNT(*) FROM `org`) AS orgs,(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 is_service_account = 0 AND last_seen_at > '2023-02-08 10:55:31.432654272') AS active_users,(SELECT COUNT(*) FROM `user` WHERE is_service_account = 0 AND last_seen_at > '2023-03-09 10:55:31.432654272') AS daily_active_users,(SELECT COUNT(*) FROM `user` WHERE is_service_account = 0 AND last_seen_at > '2023-03-01') AS monthly_active_users,(SELECT COUNT(id) FROM `dashboard` WHERE is_folder = '0') AS dashboards,(SELECT COUNT(id) FROM `dashboard` WHERE is_folder = '1') 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 = '0' ) 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 = '1' ) AS folder_permissions,( SELECT COUNT(*) FROM `dashboard_acl` AS acl INNER JOIN `dashboard` AS d ON d.id = acl.dashboard_id WHERE acl.role = 'Viewer' AND d.is_folder = 0 AND acl.permission = 2 ) AS dashboards_viewers_can_edit, ( SELECT COUNT(*) FROM `dashboard_acl` AS acl INNER JOIN `dashboard` AS d ON d.id = acl.dashboard_id WHERE acl.role = 'Viewer' AND d.is_folder = 0 AND acl.permission = 4 ) AS dashboards_viewers_can_admin, ( SELECT COUNT(*) FROM `dashboard_acl` AS acl INNER JOIN `dashboard` AS d ON d.id = acl.dashboard_id WHERE acl.role = 'Viewer' AND d.is_folder = 1 AND acl.permission = 2 ) AS folders_viewers_can_edit, ( SELECT COUNT(*) FROM `dashboard_acl` AS acl INNER JOIN `dashboard` AS d ON d.id = acl.dashboard_id WHERE acl.role = 'Viewer' AND d.is_folder = 1 AND acl.permission = 4 ) AS folders_viewers_can_admin, (SELECT COUNT(id) FROM `dashboard_provisioning`) AS provisioned_dashboards,(SELECT COUNT(id) FROM `dashboard_snapshot`) AS snapshots,(SELECT COUNT(id) FROM `dashboard_version`) AS dashboard_versions,(SELECT COUNT(id) FROM `annotation`) AS annotations,(SELECT COUNT(id) FROM `team`) AS teams,(SELECT COUNT(id) FROM `user_auth_token`) AS auth_tokens,(SELECT COUNT(id) FROM `alert_rule`) AS alert_rules,(SELECT COUNT(id) FROM `api_key`WHERE service_account_id IS NULL) AS api_keys,(SELECT COUNT(id) FROM `library_element` WHERE kind = 1) AS library_panels,(SELECT COUNT(id) FROM `library_element` WHERE kind = 2) AS library_variables,(SELECT COUNT(*) FROM `data_keys`) AS data_keys,(SELECT COUNT(*) FROM `data_keys`WHERE active = true) AS active_data_keys,(SELECT COUNT(*) FROM `dashboard_public`WHERE is_enabled = true) AS public_dashboards,14 AS admins, 10 AS editors, 16 AS viewers, 10 AS active_admins, 6 AS active_editors, 10 AS active_viewers, 0 AS daily_active_admins, 0 AS daily_active_editors, 0 AS daily_active_viewers
This query gets executed twice every 30 minutes from what I can gather. The read throughput spikes to 50 MB/s around the time this query goes at it
Anybody familiar with this query? And if there are some indexes I can add to speed it up?
