Check whether `FORCE INDEX page_timestamp` is still needed in LazyVariableComputer.php
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Daimona
	Apr 30 2021, 2:25 PM

Description

We have a query on the revision table which has an explicit FORCE INDEX page_timestamp that was added after the investigation at T116557, a few years ago, since the query was filesorting.

I'm not entirely sure that forcing the index is still necessary. Perhaps this is because MariaDB was updated, or maybe some change to the revision table; for instance, during the actor migration the page_user_timestamp index (previously chosen by the optimizer) was replaced with rev_page_actor_timestamp. Below is a test in prod, but I'd like DBAs to confirm.

New EXPLAIN for T116557#3204852:

daimona@mwmaint1002:~$ mwscript mysql.php --wiki=enwiki

wikiadmin@10.64.16.186(enwiki)> EXPLAIN SELECT /* AFComputedVariable::{closure}  */  *  FROM `revision`    WHERE rev_page = '1743794'   ORDER BY rev_timestamp DESC LIMIT 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: revision
         type: ref
possible_keys: page_timestamp,rev_page_id,rev_page_actor_timestamp
          key: page_timestamp
      key_len: 4
          ref: const
         rows: 192464
        Extra: Using where
1 row in set (0.01 sec)

wikiadmin@10.64.16.186(enwiki)> SELECT version()\g
+---------------------+
| version()           |
+---------------------+
| 10.4.18-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)

Related Objects

Mentioned Here: T254688: SpecialWhatLinksHere::showIndirectLinks on wikidatawiki needs optimizing
T116557: AFComputedVariable::compute query timeouts

Event Timeline

Daimona created this task.Apr 30 2021, 2:25 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 30 2021, 2:25 PM

LSobanski triaged this task as Medium priority.May 4 2021, 9:35 AM

LSobanski moved this task from Triage to Pending comment on the DBA board.

This query is still filesorting on 10.1 and takes around 30 seconds to complete.

root@PRODUCTION s1 slave[(none)]> show explain for 2586065580;
+------+-------------+----------+------+-----------------------------------------------------+--------------------------+---------+-------+--------+-----------------------------+
| id   | select_type | table    | type | possible_keys                                       | key                      | key_len | ref   | rows   | Extra                       |
+------+-------------+----------+------+-----------------------------------------------------+--------------------------+---------+-------+--------+-----------------------------+
|    1 | SIMPLE      | revision | ref  | rev_page_id,page_timestamp,rev_page_actor_timestamp | rev_page_actor_timestamp | 4       | const | 194690 | Using where; Using filesort |
+------+-------------+----------+------+-----------------------------------------------------+--------------------------+---------+-------+--------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

On 10.4 it doesn't and it takes 0.06 to run.
We are in process of moving away from 10.1 but it will still take a few months for that to be completed, so it is still something we need (we have other queries waiting for the migration to be completed, ie: T254688)

We have no replicas on 10.1 (only s1 and s8 masters, which aren't supposed to receive this query). Closing this as fixed.

Check whether `FORCE INDEX page_timestamp` is still needed in LazyVariableComputer.phpClosed, ResolvedPublicActions

Description

Related Objects

Event Timeline

Check whether `FORCE INDEX page_timestamp` is still needed in LazyVariableComputer.php
Closed, ResolvedPublic
Actions