Page MenuHomePhabricator

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


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@> 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@> SELECT version()\g
| version()           |
| 10.4.18-MariaDB-log |
1 row in set (0.00 sec)

Event Timeline

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.
Marostegui changed the task status from Open to Stalled.May 5 2021, 6:18 AM
Marostegui moved this task from Pending comment to Blocked on the DBA board.
Marostegui subscribed.

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)

Marostegui claimed this task.

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