Page MenuHomePhabricator
Create Task
Maniphest T240418

Slow query on arwiki for ApiQueryUserContribs::execute
Closed, ResolvedPublic
Actions

Description

I don't know if this query has been reported before, I have not been able to find it on phab, the most similar is: T221511
This query has shown up on arwiki from what I can see in Tendril in the last 120 hours.

root@db1136.eqiad.wmnet[arwiki]> explain SELECT /* ApiQueryUserContribs::execute */ rev_id, rev_page, rev_timestamp, rev_minor_edit, rev_deleted, rev_len, rev_parent_id, rev_sha1, comment_rev_comment.comment_text AS `rev_comment_text`, comment_rev_comment.comment_data AS `rev_comment_data`, comment_rev_comment.comment_id AS `rev_comment_cid`, actor_rev_user.actor_user AS `rev_user`, actor_rev_user.actor_name AS `rev_user_text`, temp_rev_user.revactor_actor AS `rev_actor`, page_namespace, page_title, page_id, page_latest, page_is_redirect, page_len FROM `revision` JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor)) JOIN `page` ON ((page_id = rev_page)) WHERE ((temp_rev_user.revactor_actor = 8)) AND ((rev_deleted & 4) != 4) ORDER BY revactor_timestamp, revactor_rev LIMIT 2 /* 57a5d1732dcbb90ea98302632482079e db1079 arwiki 54s */;
+------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+---------+---------------------------------+
| id   | select_type | table               | type   | possible_keys                                          | key                 | key_len | ref                                           | rows    | Extra                           |
+------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+---------+---------------------------------+
|    1 | SIMPLE      | actor_rev_user      | const  | PRIMARY                                                | PRIMARY             | 8       | const                                         |       1 | Using temporary; Using filesort |
|    1 | SIMPLE      | page                | ALL    | PRIMARY                                                | NULL                | NULL    | NULL                                          | 5981446 |                                 |
|    1 | SIMPLE      | revision            | ref    | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id | page_user_timestamp | 4       | arwiki.page.page_id                           |       2 | Using where                     |
|    1 | SIMPLE      | temp_rev_comment    | ref    | PRIMARY,revcomment_rev                                 | PRIMARY             | 4       | arwiki.revision.rev_id                        |       1 | Using index                     |
|    1 | SIMPLE      | comment_rev_comment | eq_ref | PRIMARY                                                | PRIMARY             | 8       | arwiki.temp_rev_comment.revcomment_comment_id |       1 |                                 |
|    1 | SIMPLE      | temp_rev_user       | eq_ref | PRIMARY,revactor_rev,actor_timestamp                   | PRIMARY             | 12      | arwiki.revision.rev_id,const                  |       1 |                                 |
+------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+---------+---------------------------------+
6 rows in set (0.00 sec)

And the real explain is the same:

root@db1136.eqiad.wmnet[(none)]> show explain for 2085709106;
+------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+---------+---------------------------------+
| id   | select_type | table               | type   | possible_keys                                          | key                 | key_len | ref                                           | rows    | Extra                           |
+------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+---------+---------------------------------+
|    1 | SIMPLE      | actor_rev_user      | const  | PRIMARY                                                | PRIMARY             | 8       | const                                         |       1 | Using temporary; Using filesort |
|    1 | SIMPLE      | page                | ALL    | PRIMARY                                                | NULL                | NULL    | NULL                                          | 5981447 |                                 |
|    1 | SIMPLE      | revision            | ref    | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id | page_user_timestamp | 4       | arwiki.page.page_id                           |       2 | Using where                     |
|    1 | SIMPLE      | temp_rev_comment    | ref    | PRIMARY,revcomment_rev                                 | PRIMARY             | 4       | arwiki.revision.rev_id                        |       1 | Using index                     |
|    1 | SIMPLE      | comment_rev_comment | eq_ref | PRIMARY                                                | PRIMARY             | 8       | arwiki.temp_rev_comment.revcomment_comment_id |       1 |                                 |
|    1 | SIMPLE      | temp_rev_user       | eq_ref | PRIMARY,revactor_rev,actor_timestamp                   | PRIMARY             | 12      | arwiki.revision.rev_id,const                  |       1 |                                 |
+------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+---------+---------------------------------+
6 rows in set, 1 warning (0.00 sec)

I killed the query after it has been run for more than 5 minutes.

Related Objects

Event Timeline

• Marostegui created this task.Dec 11 2019, 5:57 AM
Restricted Application added subscribers: alaa, Aklapper. · View Herald TranscriptDec 11 2019, 5:57 AM
• Marostegui updated the task description. (Show Details)Dec 11 2019, 6:01 AM
Anomie edited projects, added Platform Team Workboards (Clinic Duty Team); removed Platform Engineering.Dec 11 2019, 2:56 PM
Anomie subscribed.

Typical bad planner choice. It's meant to be using the actor_timestamp index on revision_actor_temp. If we force that:

wikiadmin@10.64.48.109(arwiki)> explain SELECT /* ApiQueryUserContribs::execute */ rev_id, rev_page, rev_timestamp, rev_minor_edit, rev_deleted, rev_len, rev_parent_id, rev_sha1, comment_rev_comment.comment_text AS `rev_comment_text`, comment_rev_comment.comment_data AS `rev_comment_data`, comment_rev_comment.comment_id AS `rev_comment_cid`, actor_rev_user.actor_user AS `rev_user`, actor_rev_user.actor_name AS `rev_user_text`, temp_rev_user.revactor_actor AS `rev_actor`, page_namespace, page_title, page_id, page_latest, page_is_redirect, page_len FROM `revision` JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` force index (actor_timestamp) ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor)) JOIN `page` ON ((page_id = rev_page)) WHERE ((temp_rev_user.revactor_actor = 8)) AND ((rev_deleted & 4) != 4) ORDER BY revactor_timestamp, revactor_rev LIMIT 2;
+------+-------------+---------------------+--------+--------------------------------------------------------+-----------------+---------+-----------------------------------------------+----------+--------------------------+
| id   | select_type | table               | type   | possible_keys                                          | key             | key_len | ref                                           | rows     | Extra                    |
+------+-------------+---------------------+--------+--------------------------------------------------------+-----------------+---------+-----------------------------------------------+----------+--------------------------+
|    1 | SIMPLE      | actor_rev_user      | const  | PRIMARY                                                | PRIMARY         | 8       | const                                         |        1 |                          |
|    1 | SIMPLE      | temp_rev_user       | ref    | actor_timestamp                                        | actor_timestamp | 8       | const                                         | 16759310 | Using where; Using index |
|    1 | SIMPLE      | temp_rev_comment    | ref    | PRIMARY,revcomment_rev                                 | PRIMARY         | 4       | arwiki.temp_rev_user.revactor_rev             |        1 | Using index              |
|    1 | SIMPLE      | comment_rev_comment | eq_ref | PRIMARY                                                | PRIMARY         | 8       | arwiki.temp_rev_comment.revcomment_comment_id |        1 |                          |
|    1 | SIMPLE      | revision            | eq_ref | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id | PRIMARY         | 4       | arwiki.temp_rev_user.revactor_rev             |        1 | Using where              |
|    1 | SIMPLE      | page                | eq_ref | PRIMARY                                                | PRIMARY         | 4       | arwiki.revision.rev_page                      |        1 |                          |
+------+-------------+---------------------+--------+--------------------------------------------------------+-----------------+---------+-----------------------------------------------+----------+--------------------------+
wikiadmin@10.64.48.109(arwiki)> SELECT /* ApiQueryUserContribs::execute */ rev_id, rev_page, rev_timestamp, rev_minor_edit, rev_deleted, rev_len, rev_parent_id, rev_sha1, comment_rev_comment.comment_text AS `rev_comment_text`, comment_rev_comment.comment_data AS `rev_comment_data`, comment_rev_comment.comment_id AS `rev_comment_cid`, actor_rev_user.actor_user AS `rev_user`, actor_rev_user.actor_name AS `rev_user_text`, temp_rev_user.revactor_actor AS `rev_actor`, page_namespace, page_title, page_id, page_latest, page_is_redirect, page_len FROM `revision` JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` force index (actor_timestamp) ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor)) JOIN `page` ON ((page_id = rev_page)) WHERE ((temp_rev_user.revactor_actor = 8)) AND ((rev_deleted & 4) != 4) ORDER BY revactor_timestamp, revactor_rev LIMIT 2;
[...]
2 rows in set (0.00 sec)
Anomie moved this task from Inbox to Discussing on the Platform Team Workboards (Clinic Duty Team) board.Dec 11 2019, 2:56 PM
• Marostegui added a project: mariadb-optimizer-bug.Dec 12 2019, 6:21 AM

Yeah, as usual...some hosts using one index and others using another index:

labsdb1012.eqiad.wmnet:3306
1	SIMPLE	page	ALL	PRIMARY	NULL	NULL	NULL	5350514
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_user_timestamp	4	arwiki.page.page_id	2	Using where
1	SIMPLE	temp_rev_user	eq_ref	PRIMARY,revactor_rev,actor_timestamp	PRIMARY	12	arwiki.revision.rev_id,const	1
labsdb1011.eqiad.wmnet:3306
1	SIMPLE	page	ALL	PRIMARY	NULL	NULL	NULL	6046053
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_user_timestamp	4	arwiki.page.page_id	2	Using where
1	SIMPLE	temp_rev_user	eq_ref	PRIMARY,revactor_rev,actor_timestamp	PRIMARY	12	arwiki.revision.rev_id,const	1
labsdb1010.eqiad.wmnet:3306
1	SIMPLE	temp_rev_user	ref	PRIMARY,revactor_rev,actor_timestamp	actor_timestamp	8	const	15446253	Using where; Using index
1	SIMPLE	page	eq_ref	PRIMARY	PRIMARY	4	arwiki.revision.rev_page	1
labsdb1009.eqiad.wmnet:3306
1	SIMPLE	temp_rev_user	ref	PRIMARY,revactor_rev,actor_timestamp	actor_timestamp	8	const	14568650	Using where; Using index
1	SIMPLE	page	eq_ref	PRIMARY	PRIMARY	4	arwiki.revision.rev_page	1
dbstore1003.eqiad.wmnet:3317
1	SIMPLE	temp_rev_user	ref	PRIMARY,revactor_rev,actor_timestamp	actor_timestamp	8	const	15847846	Using where; Using index
1	SIMPLE	page	eq_ref	PRIMARY	PRIMARY	4	arwiki.revision.rev_page	1
db2122.codfw.wmnet:3306
1	SIMPLE	temp_rev_user	ref	PRIMARY,revactor_rev,actor_timestamp	actor_timestamp	8	const	14548202	Using where; Using index
1	SIMPLE	page	eq_ref	PRIMARY	PRIMARY	4	arwiki.revision.rev_page	1
db2121.codfw.wmnet:3306
1	SIMPLE	temp_rev_user	ref	PRIMARY,revactor_rev,actor_timestamp	actor_timestamp	8	const	14548202	Using where; Using index
1	SIMPLE	page	eq_ref	PRIMARY	PRIMARY	4	arwiki.revision.rev_page	1
db2120.codfw.wmnet:3306
1	SIMPLE	page	ALL	PRIMARY	NULL	NULL	NULL	5958884
1	SIMPLE	revision	ref	PRIMARY,rev_page_id,page_timestamp,page_user_timestamp	page_timestamp	4	arwiki.page.page_id	2	Using where
1	SIMPLE	temp_rev_user	eq_ref	PRIMARY,revactor_rev,actor_timestamp	PRIMARY	12	arwiki.revision.rev_id,const	1
db2118.codfw.wmnet:3306
1	SIMPLE	temp_rev_user	ref	PRIMARY,revactor_rev,actor_timestamp	actor_timestamp	8	const	14534917	Using where; Using index
1	SIMPLE	page	eq_ref	PRIMARY	PRIMARY	4	arwiki.revision.rev_page	1
db2100.codfw.wmnet:3317
1	SIMPLE	temp_rev_user	ref	PRIMARY,revactor_rev,actor_timestamp	actor_timestamp	8	const	14776252	Using where; Using index
1	SIMPLE	page	eq_ref	PRIMARY	PRIMARY	4	arwiki.revision.rev_page	1
db2095.codfw.wmnet:3317
1	SIMPLE	temp_rev_user	ref	PRIMARY,revactor_rev,actor_timestamp	actor_timestamp	8	const	14925902	Using where; Using index
1	SIMPLE	page	eq_ref	PRIMARY	PRIMARY	4	arwiki.revision.rev_page	1
db2087.codfw.wmnet:3317
1	SIMPLE	page	ALL	PRIMARY	NULL	NULL	NULL	6190448
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,rev_page_id,page_user_timestamp	page_timestamp	4	arwiki.page.page_id	1	Using where
1	SIMPLE	temp_rev_user	eq_ref	PRIMARY,revactor_rev,actor_timestamp	PRIMARY	12	arwiki.revision.rev_id,const	1
db2086.codfw.wmnet:3317
1	SIMPLE	page	ALL	PRIMARY	NULL	NULL	NULL	6048275
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,rev_page_id,page_user_timestamp	page_timestamp	4	arwiki.page.page_id	1	Using where
1	SIMPLE	temp_rev_user	eq_ref	PRIMARY,revactor_rev,actor_timestamp	PRIMARY	12	arwiki.revision.rev_id,const	1
db2077.codfw.wmnet:3306
1	SIMPLE	page	ALL	PRIMARY	NULL	NULL	NULL	6296450
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	arwiki.page.page_id	2	Using where
1	SIMPLE	temp_rev_user	eq_ref	PRIMARY,revactor_rev,actor_timestamp	PRIMARY	12	arwiki.revision.rev_id,const	1
db1136.eqiad.wmnet:3306
1	SIMPLE	page	ALL	PRIMARY	NULL	NULL	NULL	5910436
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_user_timestamp	4	arwiki.page.page_id	2	Using where
1	SIMPLE	temp_rev_user	eq_ref	PRIMARY,revactor_rev,actor_timestamp	PRIMARY	12	arwiki.revision.rev_id,const	1
db1125.eqiad.wmnet:3317
1	SIMPLE	page	ALL	PRIMARY	NULL	NULL	NULL	5735925
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_user_timestamp	4	arwiki.page.page_id	2	Using where
1	SIMPLE	temp_rev_user	eq_ref	PRIMARY,revactor_rev,actor_timestamp	PRIMARY	12	arwiki.revision.rev_id,const	1
db1116.eqiad.wmnet:3317
1	SIMPLE	temp_rev_user	ref	PRIMARY,revactor_rev,actor_timestamp	actor_timestamp	8	const	13971378	Using where; Using index
1	SIMPLE	page	eq_ref	PRIMARY	PRIMARY	4	arwiki.revision.rev_page	1
db1101.eqiad.wmnet:3317
1	SIMPLE	temp_rev_user	ref	PRIMARY,revactor_rev,actor_timestamp	actor_timestamp	8	const	16003106	Using where; Using index
1	SIMPLE	page	eq_ref	PRIMARY	PRIMARY	4	arwiki.revision.rev_page	1
db1098.eqiad.wmnet:3317
1	SIMPLE	temp_rev_user	ref	PRIMARY,revactor_rev,actor_timestamp	actor_timestamp	8	const	15364418	Using where; Using index
1	SIMPLE	page	eq_ref	PRIMARY	PRIMARY	4	arwiki.revision.rev_page	1
db1094.eqiad.wmnet:3306
1	SIMPLE	page	ALL	PRIMARY	NULL	NULL	NULL	5807484
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_user_timestamp	4	arwiki.page.page_id	2	Using where
1	SIMPLE	temp_rev_user	eq_ref	PRIMARY,revactor_rev,actor_timestamp	PRIMARY	12	arwiki.revision.rev_id,const	1
db1090.eqiad.wmnet:3317
1	SIMPLE	page	ALL	PRIMARY	NULL	NULL	NULL	6194033
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	arwiki.page.page_id	2	Using where
1	SIMPLE	temp_rev_user	eq_ref	PRIMARY,revactor_rev,actor_timestamp	PRIMARY	12	arwiki.revision.rev_id,const	1
db1086.eqiad.wmnet:3306
1	SIMPLE	temp_rev_user	ref	PRIMARY,revactor_rev,actor_timestamp	actor_timestamp	8	const	14607944	Using where; Using index
1	SIMPLE	page	eq_ref	PRIMARY	PRIMARY	4	arwiki.revision.rev_page	1
db1079.eqiad.wmnet:3306
1	SIMPLE	page	ALL	PRIMARY	NULL	NULL	NULL	5709713
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	arwiki.page.page_id	2	Using where
1	SIMPLE	temp_rev_user	eq_ref	PRIMARY,revactor_rev,actor_timestamp	PRIMARY	12	arwiki.revision.rev_id,const	1
Krinkle removed a project: MediaWiki-libs-Rdbms.Jan 29 2020, 4:33 AM
Aklapper removed a subscriber: Anomie.Oct 16 2020, 5:01 PM
ArielGlenn subscribed.Dec 17 2020, 12:18 PM
ArielGlenn closed this task as Resolved.Dec 17 2020, 1:15 PM
ArielGlenn claimed this task.

A check of tendril for the last 96 hours shows none of these, so the hope is that the optimizer isn't making bad choices at the moment for whatever the initial query is out of MediaWiki. After talkign with @Marostegui we decided to go ahead and close this task. If the query or the issue resurfaces, it can be re-opened.

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL