Queries against Large Numbers of Annotations can overload/lock MySQL

[kylebrandt]

What happened:

MySQL database locks up on Annotation query when there are a large (millions) of annotation rows. Grafana-Server subsequently fails as it loses access to its database.

What you expected to happen:

Queries that don't have to scan the entire table, and lock up the db.

How to reproduce it (as minimally and precisely as possible):

Unhappy Query:

MySQL [hg_redacted]> select id,text from annotation where org_id=1 and epoch<=unix_timestamp(now())*1000 and epoch_end>=unix_timestamp(now())*1000-60*10
^CCtrl-C -- query killed. Continuing normally.
ERROR 1317 (70100): Query execution was interrupted
MySQL [hg_redacted]> explain select id,text from annotation where org_id=1 and epoch<=unix_timestamp(now())*1000 and epoch_end>=unix_timestamp(now())*10
+----+-------------+------------+------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+---------+-------+---------+--
| id | select_type | table      | partitions | type | possible_keys                                                                                                                                                                                 | key                            | key_len | ref   | rows    | f
+----+-------------+------------+------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+---------+-------+---------+--
|  1 | SIMPLE      | annotation | NULL       | ref  | IDX_annotation_org_id_alert_id,IDX_annotation_org_id_type,IDX_annotation_org_id_dashboard_id_panel_itation_org_id_created,IDX_annotation_org_id_updated,IDX_annotation_org_id_epoch_epoch_end | IDX_annotation_org_id_alert_id | 8       | const | 2102754 |
+----+-------------+------------+------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+---------+-------+---------+--
1 row in set, 1 warning (0.037 sec)

MySQL [hg_redacted]> explain select id,text from annotation where org_id=1 and epoch<=unix_timestamp(now())*1000 and epoch_end>=unix_timestamp(now())*10
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: annotation
   partitions: NULL
         type: ref
possible_keys: IDX_annotation_org_id_alert_id,IDX_annotation_org_id_type,IDX_annotation_org_id_dashboard_id_panel_id_epoch,IDX_annotation_org_id_epoch,IDX
          key: IDX_annotation_org_id_alert_id
      key_len: 8
          ref: const
         rows: 2102754
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.002 sec)

MySQL [hg_redacted]> show create table annotation;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| annotation | CREATE TABLE `annotation` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `org_id` bigint(20) NOT NULL,
  `alert_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `dashboard_id` bigint(20) DEFAULT NULL,
  `panel_id` bigint(20) DEFAULT NULL,
  `category_id` bigint(20) DEFAULT NULL,
  `type` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `text` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `metric` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `prev_state` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
  `new_state` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
  `data` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `epoch` bigint(20) NOT NULL,
  `region_id` bigint(20) DEFAULT '0',
  `tags` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created` bigint(20) DEFAULT '0',
  `updated` bigint(20) DEFAULT '0',
  `epoch_end` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `IDX_annotation_org_id_alert_id` (`org_id`,`alert_id`),
  KEY `IDX_annotation_org_id_type` (`org_id`,`type`),
  KEY `IDX_annotation_org_id_dashboard_id_panel_id_epoch` (`org_id`,`dashboard_id`,`panel_id`,`epoch`),
  KEY `IDX_annotation_org_id_epoch` (`org_id`,`epoch`),
  KEY `IDX_annotation_org_id_created` (`org_id`,`created`),
  KEY `IDX_annotation_org_id_updated` (`org_id`,`updated`),
  KEY `IDX_annotation_org_id_epoch_epoch_end` (`org_id`,`epoch`,`epoch_end`)
) ENGINE=InnoDB AUTO_INCREMENT=12444472 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

Environment:

Introduced I think in 6.4.x by #17673

TODO:

• Figure out index and/or query options that fix this above query to a reasonable about of rows
• Come up with some other queries likely to come out of https://github.com/grafana/grafana/blob/master/pkg/services/sqlstore/annotation.go#L122 , and make sure that they also have acceptable performance plans.
• Implement necessary migrations for the above.

[kylebrandt]

Note from @DanCech :

"It occurs to me that given our usual query patterns ask for data near the current time, an index on org_id, epoch_end would likely be much more efficient for the majority of queries as it'll cut down the number of rows very quickly. Unsure whether org_id, epoch_end, epoch would be worth the overhead, but the main thing is that epoch_end is the useful column for cutting out the majority of rows"

[kylebrandt]

Still populating my local instance with annotations (following is ~10k), but using Dan's suggestion, which I took as:

DROP INDEX IDX_annotation_org_id_epoch ON annotation;
DROP index IDX_annotation_org_id_epoch_epoch_end ON annotation;
ALTER TABLE annotation ADD INDEX IDX_annotation_org_id_epoch_end (`org_id` , `epoch_end`) ;

Seems to do better:

mysql> explain select id,text from annotation where org_id=1 and epoch<=unix_timestamp(now())*1000 and epoch_end>=unix_timestamp(now())*1000-60*10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: annotation
         type: range
possible_keys: IDX_annotation_org_id_alert_id,IDX_annotation_org_id_type,IDX_annotation_org_id_dashboard_id_panel_id_epoch,IDX_annotation_org_id_created,IDX_annotation_org_id_updated,IDX_annotation_org_id_epoch_end
          key: IDX_annotation_org_id_epoch_end
      key_len: 16
          ref: NULL
         rows: 6
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

[marefr]

Note from @DanCech :

"It occurs to me that given our usual query patterns ask for data near the current time, an index on org_id, epoch_end would likely be much more efficient for the majority of queries as it'll cut down the number of rows very quickly. Unsure whether org_id, epoch_end, epoch would be worth the overhead, but the main thing is that epoch_end is the useful column for cutting out the majority of rows"

We need both because when using absolute time range in dashboard we only want to get the annotations in that range.

[DanCech]

Note from @DanCech :
"It occurs to me that given our usual query patterns ask for data near the current time, an index on org_id, epoch_end would likely be much more efficient for the majority of queries as it'll cut down the number of rows very quickly. Unsure whether org_id, epoch_end, epoch would be worth the overhead, but the main thing is that epoch_end is the useful column for cutting out the majority of rows"

We need both because when using absolute time range in dashboard we only want to get the annotations in that range.

I'm talking about indexes, not queries. We definitely need both parameters in the query, the point is that if the index order is org_id, epoch, epoch_end the index will likely be less efficient than if it were org_id, epoch_end, epoch for the majority of our queries (which occur near the current time) because it will cut down the list of candidate rows faster.

I'm not sure whether there is a benefit to having org_id, epoch_end, epoch vs just org_id, epoch_end, there is a cost to indexing additional fields and we may be just as well off using the rows themselves for the epoch condition.