Page MenuHomePhabricator

DBQueryDisconnectedError upon editing en:Template:COVID-19 pandemic data
Closed, ResolvedPublicPRODUCTION ERROR

Description

Steps to replicate the issue (include links if applicable):

What happens?:

Database error
A database query error has occurred. This may indicate a bug in the software.

[887b57cc-139c-4762-84a1-d7dfa90f9b0e] 2023-12-26 06:35:33: Fatal exception of type "Wikimedia\Rdbms\DBQueryDisconnectedError"

A connection error occurred during a query. 
Function: MediaWiki\Revision\RevisionStore::findIdenticalRevision
Error: 2006 MySQL server has gone away

What should have happened instead?:
The edit should save.

Software version (skip for WMF-hosted wikis like Wikipedia):
n/a

Other information (browser name/version, screenshots, etc.):

  • I am running chrome v120.0.6099.109
  • The bug persists after:
    • Restarting computer
    • Clearing cache
    • Using safe mode (note that the page is semi-protected so it cannot be edited while logged out)
    • Disabling browser extensions
    • All of the above at once

I have not been able to reproduce the bug on a different template, but at least one other person has been able to reproduce the bug on the same template (i.e. Template:COVID-19 pandemic data).

It should be noted that the template has almost 90,000 revisions.

Related Objects

Mentioned In
rSCHCH7a8b04a342f0: Merge branch 'T354015' into 'main'
T358892: db1169 is lagged over 16000 seconds
rSCHCH33a79405d7b3: optimize_revision_T354015.py: New change
Mentioned Here
P58392 dbctl commit (dc=all): 'Repooling after maintenance db1235 (T354015)'
P58384 dbctl commit (dc=all): 'Repooling after maintenance db1235 (T354015)'
P58342 dbctl commit (dc=all): 'Depooling db1235 (T354015)'
P58341 dbctl commit (dc=all): 'Repooling after maintenance db1234 (T354015)'
P58338 dbctl commit (dc=all): 'Repooling after maintenance db1234 (T354015)'
P58329 dbctl commit (dc=all): 'Depooling db1234 (T354015)'
P58328 dbctl commit (dc=all): 'Repooling after maintenance db1232 (T354015)'
P58325 dbctl commit (dc=all): 'Repooling after maintenance db1232 (T354015)'
P58324 dbctl commit (dc=all): 'Depooling db1232 (T354015)'
P58323 dbctl commit (dc=all): 'Repooling after maintenance db1228 (T354015)'
P58320 dbctl commit (dc=all): 'Repooling after maintenance db1228 (T354015)'
P58319 dbctl commit (dc=all): 'Depooling db1228 (T354015)'
P58318 dbctl commit (dc=all): 'Repooling after maintenance db1219 (T354015)'
P58315 dbctl commit (dc=all): 'Repooling after maintenance db1219 (T354015)'
P58314 dbctl commit (dc=all): 'Depooling db1219 (T354015)'
P58313 dbctl commit (dc=all): 'Repooling after maintenance db1218 (T354015)'
P58310 dbctl commit (dc=all): 'Repooling after maintenance db1218 (T354015)'
P58309 dbctl commit (dc=all): 'Depooling db1218 (T354015)'
P58308 dbctl commit (dc=all): 'Repooling after maintenance db1206 (T354015)'
P58305 dbctl commit (dc=all): 'Repooling after maintenance db1206 (T354015)'
P58304 dbctl commit (dc=all): 'Depooling db1206 (T354015)'
P58303 dbctl commit (dc=all): 'Repooling after maintenance db1196 (T354015)'
P58300 dbctl commit (dc=all): 'Repooling after maintenance db1196 (T354015)'
P58299 dbctl commit (dc=all): 'Depooling db1196 (T354015)'
P58298 dbctl commit (dc=all): 'Repooling after maintenance db1186 (T354015)'
P58295 dbctl commit (dc=all): 'Repooling after maintenance db1186 (T354015)'
P58288 dbctl commit (dc=all): 'Depooling db1186 (T354015)'
P58285 dbctl commit (dc=all): 'Repooling after maintenance db1163 (T354015)'
P58282 dbctl commit (dc=all): 'Repooling after maintenance db1163 (T354015)'
P58274 dbctl commit (dc=all): 'Depooling db1163 (T354015)'
P58254 dbctl commit (dc=all): 'Depooling db1169 (T354015)'
P58253 dbctl commit (dc=all): 'Repooling after maintenance db1163 (T354015)'
P58246 dbctl commit (dc=all): 'Repooling after maintenance db1163 (T354015)'
P58188 dbctl commit (dc=all): 'Depooling db1163 (T354015)'
P58111 dbctl commit (dc=all): 'Depool db1169 T354015'
P58014 dbctl commit (dc=all): 'Depool db1232 - optimizing revision table T354015'

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

Going to try in db1169 which currently tries to use rev_timestamp:

+------+-------------+---------------------+--------+-----------------------------------------------------------------+---------------+---------+--------------------------------+------------+-------------+
| id   | select_type | table               | type   | possible_keys                                                   | key           | key_len | ref                            | rows       | Extra       |
+------+-------------+---------------------+--------+-----------------------------------------------------------------+---------------+---------+--------------------------------+------------+-------------+
|    1 | PRIMARY     | <derived2>          | ALL    | NULL                                                            | NULL          | NULL    | NULL                           | 16         | Using where |
|    2 | DERIVED     | revision            | index  | rev_actor_timestamp,rev_page_actor_timestamp,rev_page_timestamp | rev_timestamp | 14      | const                          | 1124215878 | Using where |
|    2 | DERIVED     | actor_rev_user      | eq_ref | PRIMARY                                                         | PRIMARY       | 8       | enwiki.revision.rev_actor      | 1          |             |
|    2 | DERIVED     | comment_rev_comment | eq_ref | PRIMARY                                                         | PRIMARY       | 8       | enwiki.revision.rev_comment_id | 1          |             |
+------+-------------+---------------------+--------+-----------------------------------------------------------------+---------------+---------+--------------------------------+------------+-------------+
4 rows in set (0.001 sec)

Mentioned in SAL (#wikimedia-operations) [2024-02-28T16:44:53Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Depool db1169 T354015', diff saved to https://phabricator.wikimedia.org/P58111 and previous config saved to /var/cache/conftool/dbconfig/20240228-164451-root.json

Mentioned in SAL (#wikimedia-operations) [2024-02-28T16:45:21Z] <marostegui@cumin1002> START - Cookbook sre.hosts.downtime for 12:00:00 on db1169.eqiad.wmnet with reason: Optimize revision table T354015

Mentioned in SAL (#wikimedia-operations) [2024-02-28T16:45:40Z] <marostegui@cumin1002> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 12:00:00 on db1169.eqiad.wmnet with reason: Optimize revision table T354015

Going to try in db1169 which currently tries to use rev_timestamp:

+------+-------------+---------------------+--------+-----------------------------------------------------------------+---------------+---------+--------------------------------+------------+-------------+
| id   | select_type | table               | type   | possible_keys                                                   | key           | key_len | ref                            | rows       | Extra       |
+------+-------------+---------------------+--------+-----------------------------------------------------------------+---------------+---------+--------------------------------+------------+-------------+
|    1 | PRIMARY     | <derived2>          | ALL    | NULL                                                            | NULL          | NULL    | NULL                           | 16         | Using where |
|    2 | DERIVED     | revision            | index  | rev_actor_timestamp,rev_page_actor_timestamp,rev_page_timestamp | rev_timestamp | 14      | const                          | 1124215878 | Using where |
|    2 | DERIVED     | actor_rev_user      | eq_ref | PRIMARY                                                         | PRIMARY       | 8       | enwiki.revision.rev_actor      | 1          |             |
|    2 | DERIVED     | comment_rev_comment | eq_ref | PRIMARY                                                         | PRIMARY       | 8       | enwiki.revision.rev_comment_id | 1          |             |
+------+-------------+---------------------+--------+-----------------------------------------------------------------+---------------+---------+--------------------------------+------------+-------------+
4 rows in set (0.001 sec)

Worked here too:

root@db1169.eqiad.wmnet[enwiki]> explain SELECT  *  FROM (SELECT  rev_id,rev_page,rev_actor,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,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`  FROM `revision` JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = rev_actor)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = rev_comment_id))   WHERE rev_page = 1052128  ORDER BY rev_timestamp DESC,rev_id DESC LIMIT 1,15  ) `recent_revs`    WHERE rev_sha1 = 'agfza7fvp7wzopfh6enqvgvr1kfwyah'  LIMIT 1 ;
+------+-------------+---------------------+--------+-----------------------------------------------------------------+--------------------+---------+--------------------------------+--------+-------------+
| id   | select_type | table               | type   | possible_keys                                                   | key                | key_len | ref                            | rows   | Extra       |
+------+-------------+---------------------+--------+-----------------------------------------------------------------+--------------------+---------+--------------------------------+--------+-------------+
|    1 | PRIMARY     | <derived2>          | ALL    | NULL                                                            | NULL               | NULL    | NULL                           | 16     | Using where |
|    2 | DERIVED     | revision            | ref    | rev_actor_timestamp,rev_page_actor_timestamp,rev_page_timestamp | rev_page_timestamp | 4       | const                          | 146840 | Using where |
|    2 | DERIVED     | actor_rev_user      | eq_ref | PRIMARY                                                         | PRIMARY            | 8       | enwiki.revision.rev_actor      | 1      |             |
|    2 | DERIVED     | comment_rev_comment | eq_ref | PRIMARY                                                         | PRIMARY            | 8       | enwiki.revision.rev_comment_id | 1      |             |
+------+-------------+---------------------+--------+-----------------------------------------------------------------+--------------------+---------+--------------------------------+--------+-------------+
4 rows in set (0.002 sec)

root@db1169.eqiad.wmnet[enwiki]> SELECT  *  FROM (SELECT  rev_id,rev_page,rev_actor,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,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`  FROM `revision` JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = rev_actor)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = rev_comment_id))   WHERE rev_page = 1052128  ORDER BY rev_timestamp DESC,rev_id DESC LIMIT 1,15  ) `recent_revs`    WHERE rev_sha1 = 'agfza7fvp7wzopfh6enqvgvr1kfwyah'  LIMIT 1 ;
Empty set (0.017 sec)

Mentioned in SAL (#wikimedia-operations) [2024-02-29T08:26:02Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Depooling db1163 (T354015)', diff saved to https://phabricator.wikimedia.org/P58188 and previous config saved to /var/cache/conftool/dbconfig/20240229-082602-marostegui.json

Marostegui triaged this task as Medium priority.

I have started our automatic schema change to optimize this table across all the s1 hosts. This is going to generate lots of notifications on this ticket, so people might want to mute it :)

Mentioned in SAL (#wikimedia-operations) [2024-02-29T14:51:26Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1163 (T354015)', diff saved to https://phabricator.wikimedia.org/P58246 and previous config saved to /var/cache/conftool/dbconfig/20240229-145125-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-02-29T15:36:46Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1163 (T354015)', diff saved to https://phabricator.wikimedia.org/P58253 and previous config saved to /var/cache/conftool/dbconfig/20240229-153646-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-02-29T15:37:01Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Depooling db1169 (T354015)', diff saved to https://phabricator.wikimedia.org/P58254 and previous config saved to /var/cache/conftool/dbconfig/20240229-153658-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-01T06:36:34Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Depooling db1163 (T354015)', diff saved to https://phabricator.wikimedia.org/P58274 and previous config saved to /var/cache/conftool/dbconfig/20240301-063633-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-01T12:43:06Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1163 (T354015)', diff saved to https://phabricator.wikimedia.org/P58282 and previous config saved to /var/cache/conftool/dbconfig/20240301-124306-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-01T13:28:26Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1163 (T354015)', diff saved to https://phabricator.wikimedia.org/P58285 and previous config saved to /var/cache/conftool/dbconfig/20240301-132824-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-01T18:50:47Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Depooling db1186 (T354015)', diff saved to https://phabricator.wikimedia.org/P58288 and previous config saved to /var/cache/conftool/dbconfig/20240301-185046-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-02T01:36:38Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1186 (T354015)', diff saved to https://phabricator.wikimedia.org/P58295 and previous config saved to /var/cache/conftool/dbconfig/20240302-013637-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-02T02:21:57Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1186 (T354015)', diff saved to https://phabricator.wikimedia.org/P58298 and previous config saved to /var/cache/conftool/dbconfig/20240302-022156-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-02T02:22:48Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Depooling db1196 (T354015)', diff saved to https://phabricator.wikimedia.org/P58299 and previous config saved to /var/cache/conftool/dbconfig/20240302-022247-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-02T09:13:13Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1196 (T354015)', diff saved to https://phabricator.wikimedia.org/P58300 and previous config saved to /var/cache/conftool/dbconfig/20240302-091312-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-02T09:58:32Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1196 (T354015)', diff saved to https://phabricator.wikimedia.org/P58303 and previous config saved to /var/cache/conftool/dbconfig/20240302-095831-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-02T09:58:54Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Depooling db1206 (T354015)', diff saved to https://phabricator.wikimedia.org/P58304 and previous config saved to /var/cache/conftool/dbconfig/20240302-095854-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-02T16:38:33Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1206 (T354015)', diff saved to https://phabricator.wikimedia.org/P58305 and previous config saved to /var/cache/conftool/dbconfig/20240302-163832-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-02T17:23:52Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1206 (T354015)', diff saved to https://phabricator.wikimedia.org/P58308 and previous config saved to /var/cache/conftool/dbconfig/20240302-172351-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-02T22:37:42Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Depooling db1218 (T354015)', diff saved to https://phabricator.wikimedia.org/P58309 and previous config saved to /var/cache/conftool/dbconfig/20240302-223741-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-03T05:09:06Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1218 (T354015)', diff saved to https://phabricator.wikimedia.org/P58310 and previous config saved to /var/cache/conftool/dbconfig/20240303-050905-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-03T05:54:25Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1218 (T354015)', diff saved to https://phabricator.wikimedia.org/P58313 and previous config saved to /var/cache/conftool/dbconfig/20240303-055424-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-03T05:54:47Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Depooling db1219 (T354015)', diff saved to https://phabricator.wikimedia.org/P58314 and previous config saved to /var/cache/conftool/dbconfig/20240303-055447-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-03T12:39:56Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1219 (T354015)', diff saved to https://phabricator.wikimedia.org/P58315 and previous config saved to /var/cache/conftool/dbconfig/20240303-123955-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-03T13:25:15Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1219 (T354015)', diff saved to https://phabricator.wikimedia.org/P58318 and previous config saved to /var/cache/conftool/dbconfig/20240303-132514-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-03T13:25:36Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Depooling db1228 (T354015)', diff saved to https://phabricator.wikimedia.org/P58319 and previous config saved to /var/cache/conftool/dbconfig/20240303-132536-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-03T19:47:22Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1228 (T354015)', diff saved to https://phabricator.wikimedia.org/P58320 and previous config saved to /var/cache/conftool/dbconfig/20240303-194721-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-03T20:32:41Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1228 (T354015)', diff saved to https://phabricator.wikimedia.org/P58323 and previous config saved to /var/cache/conftool/dbconfig/20240303-203240-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-03T20:33:03Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Depooling db1232 (T354015)', diff saved to https://phabricator.wikimedia.org/P58324 and previous config saved to /var/cache/conftool/dbconfig/20240303-203302-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-04T02:57:50Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1232 (T354015)', diff saved to https://phabricator.wikimedia.org/P58325 and previous config saved to /var/cache/conftool/dbconfig/20240304-025750-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-04T03:43:09Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1232 (T354015)', diff saved to https://phabricator.wikimedia.org/P58328 and previous config saved to /var/cache/conftool/dbconfig/20240304-034309-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-04T03:43:33Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Depooling db1234 (T354015)', diff saved to https://phabricator.wikimedia.org/P58329 and previous config saved to /var/cache/conftool/dbconfig/20240304-034333-marostegui.json

As expected, some hosts aren't being fixed with the stats refresh, and not even with a mariadb upgrade (I went from 10.4 to 10.6 on db1186 for instance). While the majority of eqiad hosts are getting their query plans updated...this won't fix 100% of them. Even if it did, it could be "reverted" anytime as soon as the stats get populated again.

@matmarex I think we should:

  1. keep refreshing the rest of the hosts including codfw hosts (there is no harm in doing so)
  2. Prepare a patch to hint the use of the preferred index.

Mentioned in SAL (#wikimedia-operations) [2024-03-04T10:28:43Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1234 (T354015)', diff saved to https://phabricator.wikimedia.org/P58338 and previous config saved to /var/cache/conftool/dbconfig/20240304-102842-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-04T11:14:01Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1234 (T354015)', diff saved to https://phabricator.wikimedia.org/P58341 and previous config saved to /var/cache/conftool/dbconfig/20240304-111401-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-04T11:14:25Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Depooling db1235 (T354015)', diff saved to https://phabricator.wikimedia.org/P58342 and previous config saved to /var/cache/conftool/dbconfig/20240304-111424-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-04T17:46:53Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1235 (T354015)', diff saved to https://phabricator.wikimedia.org/P58384 and previous config saved to /var/cache/conftool/dbconfig/20240304-174653-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2024-03-04T18:32:12Z] <marostegui@cumin1002> dbctl commit (dc=all): 'Repooling after maintenance db1235 (T354015)', diff saved to https://phabricator.wikimedia.org/P58392 and previous config saved to /var/cache/conftool/dbconfig/20240304-183212-marostegui.json

Eqiad hosts are done, the following ones didn't change their query plan
db1186
db1218
db1207

I am going to try a full table rebuild and see what happens.

Mentioned in SAL (#wikimedia-operations) [2024-03-05T12:52:13Z] <marostegui@cumin1002> START - Cookbook sre.hosts.downtime for 1 day, 0:00:00 on db1186.eqiad.wmnet with reason: Optimize revision table T354015

Mentioned in SAL (#wikimedia-operations) [2024-03-05T12:52:38Z] <marostegui@cumin1002> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 1 day, 0:00:00 on db1186.eqiad.wmnet with reason: Optimize revision table T354015

Change 1008968 had a related patch set uploaded (by Bartosz Dziewoński; author: Bartosz Dziewoński):

[mediawiki/core@master] RevisionStore: Force index to fix slow query in findIdenticalRevision

https://gerrit.wikimedia.org/r/1008968

@matmarex I think we should:

  1. keep refreshing the rest of the hosts including codfw hosts (there is no harm in doing so)
  2. Prepare a patch to hint the use of the preferred index.

Done, see above.

While working on that, I also noticed that we force the same index in a number of other places: https://codesearch.wmcloud.org/search/?q=rev_page_timestamp&files=\.php%24 in a way that's reassuring that we took the same decision a few times before, but it also suggests that this is a common problem with MySQL and MariaDB that we run into.

Thanks. Forcing a table rebuilt fixed db1186, but if we are adding the index, there's no point in spending time doing this anywhere else.

but it also suggests that this is a common problem with MySQL and MariaDB that we run into.

It is for huge tables like ours. The optimizer bases its plans on the stats and sometimes with huge tables, that's the problem you run into. We've reported a number of bugs (and they usually get fixed) but it is not something MariaDB can address faster and sometimes it is just easier to force the index. Unfortunately, the stats aren't always bullet-proof and something we can fix with an optimize, it might come back as soon as that table gets fragmented and/or the stats get outdated.

Once the patch is merged I'll close this task if that's fine with everybody.

Thanks

Change 1008968 merged by jenkins-bot:

[mediawiki/core@master] RevisionStore: Force index to fix slow query in findIdenticalRevision

https://gerrit.wikimedia.org/r/1008968

Closing this as the change is merged and will be deployed with the next train. Thanks everyone!