Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.12
-
None
-
None
-
debian bullseye
Description
We have the following set of tables:
root@db2122.codfw.wmnet[arwiki]> show create table page\G
|
*************************** 1. row ***************************
|
Table: page
|
Create Table: CREATE TABLE `page` (
|
`page_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
|
`page_namespace` int(11) NOT NULL DEFAULT 0,
|
`page_title` varbinary(255) NOT NULL DEFAULT '',
|
`page_is_redirect` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`page_is_new` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`page_random` double unsigned NOT NULL DEFAULT 0,
|
`page_touched` binary(14) NOT NULL,
|
`page_links_updated` varbinary(14) DEFAULT NULL,
|
`page_latest` int(8) unsigned NOT NULL DEFAULT 0,
|
`page_len` int(8) unsigned NOT NULL DEFAULT 0,
|
`page_content_model` varbinary(32) DEFAULT NULL,
|
`page_lang` varbinary(35) DEFAULT NULL,
|
PRIMARY KEY (`page_id`),
|
UNIQUE KEY `page_name_title` (`page_namespace`,`page_title`),
|
KEY `page_random` (`page_random`),
|
KEY `page_len` (`page_len`),
|
KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`)
|
) ENGINE=InnoDB AUTO_INCREMENT=9135123 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
|
1 row in set (0.032 sec)
|
|
|
root@db2122.codfw.wmnet[arwiki]> show create table revision\G
|
*************************** 1. row ***************************
|
Table: revision
|
Create Table: CREATE TABLE `revision` (
|
`rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
|
`rev_page` int(8) unsigned NOT NULL DEFAULT 0,
|
`rev_comment_id` bigint(20) unsigned NOT NULL DEFAULT 0,
|
`rev_actor` bigint(20) unsigned NOT NULL DEFAULT 0,
|
`rev_timestamp` binary(14) NOT NULL,
|
`rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`rev_len` int(8) unsigned DEFAULT NULL,
|
`rev_parent_id` int(8) unsigned DEFAULT NULL,
|
`rev_sha1` varbinary(32) NOT NULL DEFAULT '',
|
PRIMARY KEY (`rev_id`),
|
KEY `rev_timestamp` (`rev_timestamp`),
|
KEY `rev_actor_timestamp` (`rev_actor`,`rev_timestamp`,`rev_id`),
|
KEY `rev_page_actor_timestamp` (`rev_page`,`rev_actor`,`rev_timestamp`),
|
KEY `rev_page_timestamp` (`rev_page`,`rev_timestamp`)
|
) ENGINE=InnoDB AUTO_INCREMENT=61400866 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
|
1 row in set (0.032 sec)
|
|
|
root@db2122.codfw.wmnet[arwiki]> show create table change_tag\G
|
*************************** 1. row ***************************
|
Table: change_tag
|
Create Table: CREATE TABLE `change_tag` (
|
`ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`ct_rc_id` int(10) unsigned DEFAULT NULL,
|
`ct_log_id` int(10) unsigned DEFAULT NULL,
|
`ct_rev_id` int(10) unsigned DEFAULT NULL,
|
`ct_params` blob DEFAULT NULL,
|
`ct_tag_id` int(10) unsigned NOT NULL,
|
PRIMARY KEY (`ct_id`),
|
UNIQUE KEY `ct_rc_tag_id` (`ct_rc_id`,`ct_tag_id`),
|
UNIQUE KEY `ct_log_tag_id` (`ct_log_id`,`ct_tag_id`),
|
UNIQUE KEY `ct_rev_tag_id` (`ct_rev_id`,`ct_tag_id`),
|
KEY `ct_tag_id_id` (`ct_tag_id`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=21936222 DEFAULT CHARSET=binary
|
1 row in set (0.032 sec)
|
The following query:
SELECT ct_tag_id,page_namespace,page_title,rev_timestamp FROM `revision` JOIN `page` ON ((rev_page = page_id)) LEFT JOIN `change_tag` ON ((rev_id = ct_rev_id) AND ct_tag_id IN (176,167,179,180,181,178,182) ) WHERE rev_actor = 105 AND ((rev_deleted & 4) = 0) ORDER BY rev_timestamp DESC LIMIT 1000;
|
On 10.6.12 this query is terribly slow (more than 2 minutes) and on 10.4 this query flies.
These are the different explain outputs and query plans:
10.6.12
root@db2122.codfw.wmnet[arwiki]> analyze format=json SELECT ct_tag_id,page_namespace,page_title,rev_timestamp FROM `revision` JOIN `page` ON ((rev_page = page_id)) LEFT JOIN `change_tag` ON ((rev_id = ct_rev_id) AND ct_tag_id IN (176,167,179,180,181,178,182) ) WHERE rev_actor = 105 AND ((rev_deleted & 4) = 0) ORDER BY rev_timestamp DESC LIMIT 1000;
|
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 95012.20285,
|
"filesort": {
|
"sort_key": "revision.rev_timestamp desc",
|
"r_loops": 1,
|
"r_total_time_ms": 1271.421239,
|
"r_limit": 1000,
|
"r_used_priority_queue": true,
|
"r_output_rows": 1001,
|
"r_sort_mode": "sort_key,addon_fields",
|
"temporary_table": {
|
"table": {
|
"table_name": "page",
|
"access_type": "index",
|
"possible_keys": ["PRIMARY"],
|
"key": "page_name_title",
|
"key_length": "261",
|
"used_key_parts": ["page_namespace", "page_title"],
|
"r_loops": 1,
|
"rows": 8029330,
|
"r_rows": 8029332,
|
"r_table_time_ms": 2803.389817,
|
"r_other_time_ms": 694.9256431,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "revision",
|
"access_type": "ref",
|
"possible_keys": [
|
"rev_actor_timestamp",
|
"rev_page_actor_timestamp",
|
"rev_page_timestamp"
|
],
|
"key": "rev_page_actor_timestamp",
|
"key_length": "12",
|
"used_key_parts": ["rev_page", "rev_actor"],
|
"ref": ["arwiki.page.page_id", "const"],
|
"r_loops": 8029332,
|
"rows": 1,
|
"r_rows": 0.989499126,
|
"r_table_time_ms": 69921.4377,
|
"r_other_time_ms": 1275.853865,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "revision.rev_deleted & 4 = 0"
|
},
|
"table": {
|
"table_name": "change_tag",
|
"access_type": "ref",
|
"possible_keys": ["ct_rev_tag_id", "ct_tag_id_id"],
|
"key": "ct_rev_tag_id",
|
"key_length": "5",
|
"used_key_parts": ["ct_rev_id"],
|
"ref": ["arwiki.revision.rev_id"],
|
"r_loops": 7945017,
|
"rows": 1,
|
"r_rows": 0.00973755,
|
"r_table_time_ms": 14351.13748,
|
"r_other_time_ms": 4235.411193,
|
"filtered": 0.472014844,
|
"r_filtered": 0,
|
"attached_condition": "trigcond(change_tag.ct_tag_id in (176,167,179,180,181,178,182))",
|
"using_index": true
|
}
|
}
|
}
|
}
|
} |
|
|
|
|
|
root@db2122.codfw.wmnet[arwiki]> explain SELECT ct_tag_id,page_namespace,page_title,rev_timestamp FROM `revision` JOIN `page` ON ((rev_page = page_id)) LEFT JOIN `change_tag` ON ((rev_id = ct_rev_id) AND ct_tag_id IN (176,167,179,180,181,178,182) ) WHERE rev_actor = 105 AND ((rev_deleted & 4) = 0) ORDER BY rev_timestamp DESC LIMIT 1000;
|
+------+-------------+------------+-------+-----------------------------------------------------------------+--------------------------+---------+---------------------------+---------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+-------+-----------------------------------------------------------------+--------------------------+---------+---------------------------+---------+----------------------------------------------+
|
| 1 | SIMPLE | page | index | PRIMARY | page_name_title | 261 | NULL | 8029330 | Using index; Using temporary; Using filesort |
|
| 1 | SIMPLE | revision | ref | rev_actor_timestamp,rev_page_actor_timestamp,rev_page_timestamp | rev_page_actor_timestamp | 12 | arwiki.page.page_id,const | 1 | Using where |
|
| 1 | SIMPLE | change_tag | ref | ct_rev_tag_id,ct_tag_id_id | ct_rev_tag_id | 5 | arwiki.revision.rev_id | 1 | Using where; Using index |
|
+------+-------------+------------+-------+-----------------------------------------------------------------+--------------------------+---------+---------------------------+---------+----------------------------------------------+
|
3 rows in set (0.033 sec)
|
|
On 10.4.25
root@db2108.codfw.wmnet[arwiki]> analyze format=json SELECT ct_tag_id,page_namespace,page_title,rev_timestamp FROM `revision` JOIN `page` ON ((rev_page = page_id)) LEFT JOIN `change_tag` ON ((rev_id = ct_rev_id) AND ct_tag_id IN (176,167,179,180,181,178,182) ) WHERE rev_actor = 105 AND ((rev_deleted & 4) = 0) ORDER BY rev_timestamp DESC LIMIT 1000;
|
|
|
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 21.31,
|
"table": {
|
"table_name": "revision",
|
"access_type": "ref",
|
"possible_keys": [
|
"rev_actor_timestamp",
|
"rev_page_actor_timestamp",
|
"rev_page_timestamp"
|
],
|
"key": "rev_actor_timestamp",
|
"key_length": "8",
|
"used_key_parts": ["rev_actor"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 11168070,
|
"r_rows": 1000,
|
"r_total_time_ms": 7.7386,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "revision.rev_actor <=> 105 and revision.rev_deleted & 4 = 0"
|
},
|
"table": {
|
"table_name": "page",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["page_id"],
|
"ref": ["arwiki.revision.rev_page"],
|
"r_loops": 1000,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 7.306,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "change_tag",
|
"access_type": "ref",
|
"possible_keys": ["ct_rev_tag_id", "ct_tag_id_id"],
|
"key": "ct_rev_tag_id",
|
"key_length": "5",
|
"used_key_parts": ["ct_rev_id"],
|
"ref": ["arwiki.revision.rev_id"],
|
"r_loops": 1000,
|
"rows": 1,
|
"r_rows": 0.001,
|
"r_total_time_ms": 4.8213,
|
"filtered": 0.4176,
|
"r_filtered": 0,
|
"attached_condition": "trigcond(change_tag.ct_tag_id in (176,167,179,180,181,178,182))",
|
"using_index": true
|
}
|
}
|
} |
|
|
|
|
|
root@db2108.codfw.wmnet[arwiki]> explain SELECT ct_tag_id,page_namespace,page_title,rev_timestamp FROM `revision` JOIN `page` ON ((rev_page = page_id)) LEFT JOIN `change_tag` ON ((rev_id = ct_rev_id) AND ct_tag_id IN (176,167,179,180,181,178,182) ) WHERE rev_actor = 105 AND ((rev_deleted & 4) = 0) ORDER BY rev_timestamp DESC LIMIT 1000;
|
+------+-------------+------------+--------+-----------------------------------------------------------------+---------------------+---------+--------------------------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+--------+-----------------------------------------------------------------+---------------------+---------+--------------------------+----------+--------------------------+
|
| 1 | SIMPLE | revision | ref | rev_actor_timestamp,rev_page_actor_timestamp,rev_page_timestamp | rev_actor_timestamp | 8 | const | 11168070 | Using where |
|
| 1 | SIMPLE | page | eq_ref | PRIMARY | PRIMARY | 4 | arwiki.revision.rev_page | 1 | |
|
| 1 | SIMPLE | change_tag | ref | ct_rev_tag_id,ct_tag_id_id | ct_rev_tag_id | 5 | arwiki.revision.rev_id | 1 | Using where; Using index |
|
+------+-------------+------------+--------+-----------------------------------------------------------------+---------------------+---------+--------------------------+----------+--------------------------+
|
3 rows in set (0.037 sec)
|
|
We have a mix of 10.6.12 and 10.4.2X hosts and this pattern is repeated on all our 10.6.12 ones.
We've tried refreshing table stats (analyze) on the three tables involved but the query plans doesn't change and keeps generating a slow query.