[MDEV-24266] Possible optimizer regression on 10.4.17 with DELETE statements Created: 2020-11-23  Updated: 2021-02-16  Resolved: 2021-01-29

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.17
Fix Version/s: 10.2.37, 10.3.28, 10.4.18, 10.5.9

Type: Bug Priority: Major
Reporter: Manuel Arostegui Assignee: Sergei Petrunia
Resolution: Duplicate Votes: 0
Labels: debian, optimizer, regression
Environment:

debian

Issue Links:
Duplicate
duplicates MDEV-24275 InnoDB persistent stats analyze force... Closed
duplicates MDEV-24438 Primary KEY not used in range lookups Closed

 Description   

We have two hosts serving on the same replication topology with same hardware.
pc2007: runs 10.4.15
pc2010: runs 10.4.17

Those two hosts do not receive read traffic. All the traffic comes thru replication and it is mostly REPLACE statements.
However they do receive DELETEs from time to time.

After upgrading to 10.4.17 we noticed pc2010 (and another host) started to lag often, and it sort of matched the times where the DELETE arrived.

Investigating we saw that the host running 10.4.17 was having a very strange pattern regarding handler_read_next compared to the host running 10.4.15

10.4.17: https://phab.wmfusercontent.org/file/data/442ozs4b36ctxwzqdse5/PHID-FILE-hp6qf2cv6je4zoks3fvn/Captura_de_pantalla_2020-11-23_a_las_12.25.47.png
10.4.15: https://phab.wmfusercontent.org/file/data/byag7m3a4z3v5r4pe7l3/PHID-FILE-gt6pten2ty6no3czi23j/Captura_de_pantalla_2020-11-23_a_las_12.26.06.png

Both hosts run the same global variables and a diff doesn't really show any difference:
https://phabricator.wikimedia.org/P13361

There is +innodb_max_purge_lag_wait which was introduced on 10.4.16, but I asked Marko at https://jira.mariadb.org/browse/MDEV-16952 and he kindly pointed out that it should have no difference.

However, digging into the DELETEs optimizers behaviour we can see there's a big difference between 10.4.15 and 10.4.17

These are the handlers differences:

10.4.17: https://phabricator.wikimedia.org/P13362
10.4.15: https://phabricator.wikimedia.org/P13363

We can see the big difference there with the scans:
10.4.17

| Handler_read_next          | 15218 |

10.4.15:

| Handler_read_next          | 0     |

Checking the optimizer we can see there's a big difference on rows and cost:
Optimizer trace for 10.4.17: https://phabricator.wikimedia.org/P13365
Optimizer trace for 10.4.15: https://phabricator.wikimedia.org/P13364

This difference is interesting:
10.4.17:

 
 
              "rowid_ordered": true,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 100,
 
              "cost": 65515,
 
              "chosen": true
 
            },
 
            {
 
              "index": "exptime",
 
              "ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 26088,
 
              "cost": 33264,
 
              "chosen": true
 
            }
 
          ],
 
          "analyzing_index_merge_union": []
 
        },
 
        "chosen_range_access_summary": {
 
          "range_access_plan": {
 
            "type": "range_scan",
 
            "index": "exptime",
 
            "rows": 26088,
 
            "ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"]
 
          },
 
          "rows_for_plan": 26088,
 
          "cost_for_plan": 33264,
 
          "chosen": true

Vs the much lighter 10.4.15 plan

	              "rowid_ordered": true,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 100,
 
              "cost": 120.13,
 
              "chosen": true
 
            },
 
            {
 
              "index": "exptime",
 
              "ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 1,
 
              "cost": 2.5033,
 
              "chosen": true
 
            }
 
          ],
 
          "analyzing_index_merge_union": []
 
        },
 
        "chosen_range_access_summary": {
 
          "range_access_plan": {
 
            "type": "range_scan",
 
            "index": "exptime",
 
            "rows": 1,
 
            "ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"]
 
          },
 
          "rows_for_plan": 1,
 
          "cost_for_plan": 2.5033,
 
          "chosen": true

We are not sure if this might be the culprit of our lag, but it is definitely something that has regressed on 10.4.17.
The table schema:

CREATE TABLE `pc000` (
 
  `keyname` varbinary(255) NOT NULL DEFAULT '',
 
  `value` mediumblob DEFAULT NULL,
 
  `exptime` datetime DEFAULT NULL,
 
  UNIQUE KEY `keyname` (`keyname`),
 
  KEY `exptime` (`exptime`)
 
) ENGINE=InnoDB DEFAULT CHARSET=binary

This is being tracked publicly on our tracking system: https://phabricator.wikimedia.org/T268457

 Comments   
Comment by Robert Bindar [ 2020-12-16 ]

Hey marostegui! Thanks for reporting this issue and I'm very sorry for the delay. Daniel has just assigned Sergei Petrunia for this task and the reasoning is that at first glance, his optimizer expertise might help him come up with an explanation quicker than I can. If it turns out he is too busy to handle this task, I will give it a try as soon as possible.

Comment by Manuel Arostegui [ 2020-12-16 ]

Thank Daniel, Robert and Sergei!
For now, we are not going to roll 10.4.17 to other instances with even more intensive workloads - we want get to know a bit better what could be going on here and what could've changed on .17 and what can be causing these weird patterns

Thanks again for taking a look - much appreciated!

Comment by Manuel Arostegui [ 2021-01-01 ]

Did anyone have sometime to investigate this regression? We are not confident rolling 10.4.17 out until this has been investigated/resolved as the performance impact is quite high for us.

Thanks!

Comment by Sergei Petrunia [ 2021-01-02 ]

Apparently, not yet. Let me look

Comment by Sergei Petrunia [ 2021-01-02 ]

Diff'ing the traces: https://gist.github.com/spetrunia/1f09463a4fb708ef361044a0d0f077fb .

Note that the optimizer behavior is the same: the optimizer produces the same lists of ranges, and even picks the same query plan.

The costs numbers vary a lot, though, and also rows on index exptime.

Comment by Sergei Petrunia [ 2021-01-02 ]

The handler counters:

--- handler-10.4.15.txt
 
+++ handler-10.4.17.txt
 
@@ -15,15 +16,15 @@
 
 | Handler_mrr_init           | 0     |
 
 | Handler_mrr_key_refills    | 0     |
 
 | Handler_mrr_rowid_refills  | 0     |
 
 | Handler_prepare            | 0     |
 
 | Handler_read_first         | 0     |
 
 | Handler_read_key           | 1     |
 
 | Handler_read_last          | 0     |
 
-| Handler_read_next          | 0     |
 
+| Handler_read_next          | 15218 |
 
 | Handler_read_prev          | 0     |
 
 | Handler_read_retry         | 0     |
 
 | Handler_read_rnd           | 0     |
 
 | Handler_read_rnd_deleted   | 0     |
 
 | Handler_read_rnd_next      | 0     |
 
 | Handler_rollback           | 0     |
 
 | Handler_savepoint          | 0     |

show that

  • one range was scanned on both hosts (Handler_read_key=1)
  • in 10.4.15, it had no rows (Handler_read_next=0), while in 10.4.17 it had 15217 rows. This is consistent with the estimates shown in the optimizer trace, for both versions. (marostegui are you sure the data on the two nodes was the [almost] the same? Or, maybe, the DELETE query was run for the second time where there the first one has already deleted everything? (and so, we're not seeing the real query plan for 10.4.15?) )
Comment by Sergei Petrunia [ 2021-01-02 ]

For cost differences, I saw MDEV-24438 (aka MDEV-24275), for which the fix was pushed and will be released in 10.4.18 (does it mean it's better to wait for 10.4.18?)

But the data we see here doesn't point to MDEV-24438 - the query plan did not change.

Comment by Manuel Arostegui [ 2021-01-02 ]

Thanks Sergei.
The data is the same yes, they both replicate from the same primary master.

We can definitely wait for 10.4.18 and test again with that one

Comment by Manuel Arostegui [ 2021-01-02 ]

Sergei, I did the test again to confirm the data is the same:

10.4.17:

mysql:root@localhost [parsercache]> select @@version; select count(*) from pc143;
 
+---------------------+
 
| @@version           |
 
+---------------------+
 
| 10.4.17-MariaDB-log |
 
+---------------------+
 
1 row in set (0.000 sec)
 
 
 
+----------+
 
| count(*) |
 
+----------+
 
|  1268305 |
 
+----------+
 
1 row in set (1.620 sec)
 
 
 
mysql:root@localhost [parsercache]> FLUSH STATUS;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
mysql:root@localhost [parsercache]> DELETE /* SqlBagOStuff::deleteServerObjectsExpiringBefore  */ FROM `pc143` WHERE (exptime < '20201121180630') AND keyname IN ('enwiki:pcache:idoptions:60158592','wikidatawiki:pcache:idoptions:93705523','fawiki:pcache:idoptions:3618849','wikidatawiki:pcache:idoptions:40565110','commonswiki:pcache:idoptions:38878868','commonswiki:pcache:idoptions:38880520','commonswiki:pcache:idhash:21861132-1!userlang=zh!wb=3','dewiki:pcache:idoptions:4226101','kuwiktionary:pcache:idhash:773397-0!canonical!ku','ruwikisource:stable-pcache:idhash:403968-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:9748475-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:31646851-0!userlang=el!wb=3','commonswiki:pcache:idhash:9621653-1!wb=3','enwiki:pcache:idhash:35973163-0!userlang=pl','commonswiki:pcache:idhash:364914-1!userlang=be!wb=3','enwiktionary:pcache:idhash:1822018-0!canonical!responsiveimages=0','commonswiki:pcache:idoptions:19467353','commonswiki:pcache:idhash:217417-0!userlang=el!wb=3','zhwiki:pcache:idhash:2768566-0!canonical!zh','commonswiki:pcache:idoptions:63579041','commonswiki:pcache:idhash:89177255-1!userlang=sco!wb=3','itwiki:pcache:idhash:3786827-0!canonical','ptwiki:pcache:idoptions:5457598','cswiki:pcache:idhash:363299-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:52809852-0!wb=3','commonswiki:pcache:idhash:28239139-1!userlang=sv!wb=3','itwikisource:pcache:idhash:253605-0!canonical','commonswiki:pcache:idhash:438936-0!wb=3','frwiktionary:pcache:idhash:641708-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:49752797-0!canonical','jawiki:pcache:idhash:1122158-0!canonical','commonswiki:pcache:idoptions:70101965','plwikisource:stable-pcache:idhash:106198-0!canonical','wikidatawiki:pcache:idhash:87216524-0!termboxVersion=1!wb=3','incubatorwiki:pcache:idhash:3121734-0!canonical!responsiveimages=0','zhwiki:pcache:idhash:5199889-0!canonical!zh-hans','kawiki:stable-pcache:idhash:464328-0!canonical!responsiveimages=0','jawiki:pcache:idoptions:2720359','wikidatawiki:pcache:idhash:54147840-0!termboxVersion=22!wb=3!responsiveimages=0','wikidatawiki:pcache:idhash:60690177-0!termboxVersion=1!wb=3','commonswiki:pcache:idhash:15854638-1!userlang=es','commonswiki:pcache:idhash:29277237-1!userlang=de!wb=3','commonswiki:pcache:idhash:21690770-0!wb=3!responsiveimages=0','ruwikibooks:pcache:idhash:4887-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:95266583-0!canonical','commonswiki:pcache:idhash:67281819-0!wb=3','enwiki:pcache:idhash:5682984-0!userlang=fr','enwiki:pcache:idhash:47929188-0!canonical','enwiki:pcache:idoptions:47932100','enwiki:pcache:idoptions:47933370','commonswiki:pcache:idoptions:71408184','wikidatawiki:pcache:idhash:13452002-0!termboxVersion=1!wb=3','srwiki:pcache:idhash:263015-0!canonical!sr-el!responsiveimages=0','wikidatawiki:pcache:idhash:23271523-0!termboxVersion=22!wb=3!responsiveimages=0','commonswiki:pcache:idhash:34679268-1!userlang=de!wb=3','commonswiki:pcache:idhash:91613541-1!userlang=sco','commonswiki:pcache:idhash:76594236-0!canonical!responsiveimages=0','zhwiki:pcache:idhash:3150136-0!canonical!zh-my','commonswiki:pcache:idhash:86388141-1!userlang=sco!wb=3','warwiki:pcache:idhash:1135975-0!canonical','cewiki:pcache:idoptions:35150','commonswiki:pcache:idhash:18668668-1!userlang=es!wb=3','cewiki:pcache:idoptions:34634','commonswiki:pcache:idhash:73300761-0!wb=3','commonswiki:pcache:idhash:66587541-0!canonical','commonswiki:pcache:idhash:3097404-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:27111079-0!wb=3','commonswiki:pcache:idhash:48689322-1!userlang=it!wb=3','commonswiki:pcache:idhash:59641926-1!userlang=zh-hant!wb=3','commonswiki:pcache:idhash:12425211-0!wb=3','zhwiki:pcache:idhash:749757-0!userlang=zh-mo!zh-mo!responsiveimages=0','commonswiki:pcache:idhash:23536656-0!canonical','dewiki:pcache:idhash:10617254-0!canonical','dewikisource:pcache:idoptions:239527','commonswiki:pcache:idhash:46977621-1!userlang=fa!wb=3','commonswiki:pcache:idoptions:89989634','uzwiktionary:pcache:idhash:152325-0!canonical!uz!responsiveimages=0','mrwikisource:pcache:idhash:4528-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:55076859-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:56111634-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:27287209-0!canonical','commonswiki:pcache:idhash:63666276-1!userlang=zh-my!wb=3','arwiki:pcache:idhash:7148781-0!canonical','ptwiki:pcache:idoptions:2909880','enwiktionary:pcache:idhash:1311496-0!canonical!responsiveimages=0','dewiki:pcache:idoptions:10955254','commonswiki:pcache:idhash:14096350-0!wb=3','commonswiki:pcache:idhash:4629630-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:58299531-1!wb=3','be_x_oldwiki:pcache:idhash:81081-0!canonical','enwiki:pcache:idhash:43492085-0!canonical','frwikisource:pcache:idhash:1587082-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:6441225-1!userlang=fi!wb=3','itwiki:pcache:idhash:4787246-0!thumbsize=3','enwikisource:pcache:idoptions:452578','itwikisource:pcache:idhash:329111-0!canonical!responsiveimages=0','enwikisource:pcache:idhash:1102180-0!canonical!responsiveimages=0','enwiki:pcache:idhash:5727937-1!canonical','ruwikinews:pcache:idhash:1297738-0!canonical','ruwikinews:pcache:idhash:1298052-0!canonical');
 
Query OK, 0 rows affected (6.112 sec)
 
 
 
mysql:root@localhost [parsercache]> nopager;SHOW STATUS like 'Hand%';
 
PAGER set to stdout
 
+----------------------------+-------+
 
| Variable_name              | Value |
 
+----------------------------+-------+
 
| Handler_commit             | 1     |
 
| Handler_delete             | 0     |
 
| Handler_discover           | 0     |
 
| Handler_external_lock      | 0     |
 
| Handler_icp_attempts       | 0     |
 
| Handler_icp_match          | 0     |
 
| Handler_mrr_init           | 0     |
 
| Handler_mrr_key_refills    | 0     |
 
| Handler_mrr_rowid_refills  | 0     |
 
| Handler_prepare            | 0     |
 
| Handler_read_first         | 0     |
 
| Handler_read_key           | 1     |
 
| Handler_read_last          | 0     |
 
| Handler_read_next          | 15211 |
 
| Handler_read_prev          | 0     |
 
| Handler_read_retry         | 0     |
 
| Handler_read_rnd           | 0     |
 
| Handler_read_rnd_deleted   | 0     |
 
| Handler_read_rnd_next      | 0     |
 
| Handler_rollback           | 0     |
 
| Handler_savepoint          | 0     |
 
| Handler_savepoint_rollback | 0     |
 
| Handler_tmp_delete         | 0     |
 
| Handler_tmp_update         | 0     |
 
| Handler_tmp_write          | 0     |
 
| Handler_update             | 0     |
 
| Handler_write              | 0     |
 
+----------------------------+-------+
 
27 rows in set (0.001 sec)

And with 10.4.14:

 
 
 
 
 
 
mysql:root@localhost [parsercache]> select @@version; select count(*) from pc143;
 
+---------------------+
 
| @@version           |
 
+---------------------+
 
| 10.4.14-MariaDB-log |
 
+---------------------+
 
1 row in set (0.000 sec)
 
 
 
+----------+
 
| count(*) |
 
+----------+
 
|  1256169 |
 
+----------+
 
1 row in set (0.353 sec)
 
 
 
mysql:root@localhost [parsercache]> FLUSH STATUS;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
mysql:root@localhost [parsercache]> DELETE /* SqlBagOStuff::deleteServerObjectsExpiringBefore  */ FROM `pc143` WHERE (exptime < '20201121180630') AND keyname IN ('enwiki:pcache:idoptions:60158592','wikidatawiki:pcache:idoptions:93705523','fawiki:pcache:idoptions:3618849','wikidatawiki:pcache:idoptions:40565110','commonswiki:pcache:idoptions:38878868','commonswiki:pcache:idoptions:38880520','commonswiki:pcache:idhash:21861132-1!userlang=zh!wb=3','dewiki:pcache:idoptions:4226101','kuwiktionary:pcache:idhash:773397-0!canonical!ku','ruwikisource:stable-pcache:idhash:403968-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:9748475-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:31646851-0!userlang=el!wb=3','commonswiki:pcache:idhash:9621653-1!wb=3','enwiki:pcache:idhash:35973163-0!userlang=pl','commonswiki:pcache:idhash:364914-1!userlang=be!wb=3','enwiktionary:pcache:idhash:1822018-0!canonical!responsiveimages=0','commonswiki:pcache:idoptions:19467353','commonswiki:pcache:idhash:217417-0!userlang=el!wb=3','zhwiki:pcache:idhash:2768566-0!canonical!zh','commonswiki:pcache:idoptions:63579041','commonswiki:pcache:idhash:89177255-1!userlang=sco!wb=3','itwiki:pcache:idhash:3786827-0!canonical','ptwiki:pcache:idoptions:5457598','cswiki:pcache:idhash:363299-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:52809852-0!wb=3','commonswiki:pcache:idhash:28239139-1!userlang=sv!wb=3','itwikisource:pcache:idhash:253605-0!canonical','commonswiki:pcache:idhash:438936-0!wb=3','frwiktionary:pcache:idhash:641708-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:49752797-0!canonical','jawiki:pcache:idhash:1122158-0!canonical','commonswiki:pcache:idoptions:70101965','plwikisource:stable-pcache:idhash:106198-0!canonical','wikidatawiki:pcache:idhash:87216524-0!termboxVersion=1!wb=3','incubatorwiki:pcache:idhash:3121734-0!canonical!responsiveimages=0','zhwiki:pcache:idhash:5199889-0!canonical!zh-hans','kawiki:stable-pcache:idhash:464328-0!canonical!responsiveimages=0','jawiki:pcache:idoptions:2720359','wikidatawiki:pcache:idhash:54147840-0!termboxVersion=22!wb=3!responsiveimages=0','wikidatawiki:pcache:idhash:60690177-0!termboxVersion=1!wb=3','commonswiki:pcache:idhash:15854638-1!userlang=es','commonswiki:pcache:idhash:29277237-1!userlang=de!wb=3','commonswiki:pcache:idhash:21690770-0!wb=3!responsiveimages=0','ruwikibooks:pcache:idhash:4887-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:95266583-0!canonical','commonswiki:pcache:idhash:67281819-0!wb=3','enwiki:pcache:idhash:5682984-0!userlang=fr','enwiki:pcache:idhash:47929188-0!canonical','enwiki:pcache:idoptions:47932100','enwiki:pcache:idoptions:47933370','commonswiki:pcache:idoptions:71408184','wikidatawiki:pcache:idhash:13452002-0!termboxVersion=1!wb=3','srwiki:pcache:idhash:263015-0!canonical!sr-el!responsiveimages=0','wikidatawiki:pcache:idhash:23271523-0!termboxVersion=22!wb=3!responsiveimages=0','commonswiki:pcache:idhash:34679268-1!userlang=de!wb=3','commonswiki:pcache:idhash:91613541-1!userlang=sco','commonswiki:pcache:idhash:76594236-0!canonical!responsiveimages=0','zhwiki:pcache:idhash:3150136-0!canonical!zh-my','commonswiki:pcache:idhash:86388141-1!userlang=sco!wb=3','warwiki:pcache:idhash:1135975-0!canonical','cewiki:pcache:idoptions:35150','commonswiki:pcache:idhash:18668668-1!userlang=es!wb=3','cewiki:pcache:idoptions:34634','commonswiki:pcache:idhash:73300761-0!wb=3','commonswiki:pcache:idhash:66587541-0!canonical','commonswiki:pcache:idhash:3097404-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:27111079-0!wb=3','commonswiki:pcache:idhash:48689322-1!userlang=it!wb=3','commonswiki:pcache:idhash:59641926-1!userlang=zh-hant!wb=3','commonswiki:pcache:idhash:12425211-0!wb=3','zhwiki:pcache:idhash:749757-0!userlang=zh-mo!zh-mo!responsiveimages=0','commonswiki:pcache:idhash:23536656-0!canonical','dewiki:pcache:idhash:10617254-0!canonical','dewikisource:pcache:idoptions:239527','commonswiki:pcache:idhash:46977621-1!userlang=fa!wb=3','commonswiki:pcache:idoptions:89989634','uzwiktionary:pcache:idhash:152325-0!canonical!uz!responsiveimages=0','mrwikisource:pcache:idhash:4528-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:55076859-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:56111634-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:27287209-0!canonical','commonswiki:pcache:idhash:63666276-1!userlang=zh-my!wb=3','arwiki:pcache:idhash:7148781-0!canonical','ptwiki:pcache:idoptions:2909880','enwiktionary:pcache:idhash:1311496-0!canonical!responsiveimages=0','dewiki:pcache:idoptions:10955254','commonswiki:pcache:idhash:14096350-0!wb=3','commonswiki:pcache:idhash:4629630-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:58299531-1!wb=3','be_x_oldwiki:pcache:idhash:81081-0!canonical','enwiki:pcache:idhash:43492085-0!canonical','frwikisource:pcache:idhash:1587082-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:6441225-1!userlang=fi!wb=3','itwiki:pcache:idhash:4787246-0!thumbsize=3','enwikisource:pcache:idoptions:452578','itwikisource:pcache:idhash:329111-0!canonical!responsiveimages=0','enwikisource:pcache:idhash:1102180-0!canonical!responsiveimages=0','enwiki:pcache:idhash:5727937-1!canonical','ruwikinews:pcache:idhash:1297738-0!canonical','ruwikinews:pcache:idhash:1298052-0!canonical');
 
Query OK, 0 rows affected (0.093 sec)
 
 
 
mysql:root@localhost [parsercache]> nopager;SHOW STATUS like 'Hand%';
 
PAGER set to stdout
 
+----------------------------+-------+
 
| Variable_name              | Value |
 
+----------------------------+-------+
 
| Handler_commit             | 2     |
 
| Handler_delete             | 0     |
 
| Handler_discover           | 0     |
 
| Handler_external_lock      | 0     |
 
| Handler_icp_attempts       | 0     |
 
| Handler_icp_match          | 0     |
 
| Handler_mrr_init           | 0     |
 
| Handler_mrr_key_refills    | 0     |
 
| Handler_mrr_rowid_refills  | 0     |
 
| Handler_prepare            | 0     |
 
| Handler_read_first         | 0     |
 
| Handler_read_key           | 100   |
 
| Handler_read_last          | 0     |
 
| Handler_read_next          | 0     |
 
| Handler_read_prev          | 0     |
 
| Handler_read_retry         | 0     |
 
| Handler_read_rnd           | 0     |
 
| Handler_read_rnd_deleted   | 0     |
 
| Handler_read_rnd_next      | 0     |
 
| Handler_rollback           | 0     |
 
| Handler_savepoint          | 0     |
 
| Handler_savepoint_rollback | 0     |
 
| Handler_tmp_delete         | 0     |
 
| Handler_tmp_update         | 0     |
 
| Handler_tmp_write          | 0     |
 
| Handler_update             | 0     |
 
| Handler_write              | 0     |
 
+----------------------------+-------+
 
27 rows in set (0.001 sec)
 
 
 
 
 
 

Comment by Sergei Petrunia [ 2021-01-05 ]

marostegui , observe that this time the difference is :

--- jan02-10.4.14.txt
 
+++ jan02-10.4.17.txt
 
@@ -12,9 +12,9 @@
 
 | Handler_mrr_rowid_refills  | 0     |
 
 | Handler_prepare            | 0     |
 
 | Handler_read_first         | 0     |
 
-| Handler_read_key           | 100   |
 
+| Handler_read_key           | 1     |
 
 | Handler_read_last          | 0     |
 
-| Handler_read_next          | 0     |
 
+| Handler_read_next          | 15211 |
 
 | Handler_read_prev          | 0     |
 
 | Handler_read_retry         | 0     |
 
 | Handler_read_rnd           | 0     |

10.4.14 did 100 key lookups.

The only way this could happen is that the query plan was eq_ref access on key keyname, constructed from the 

keyname IN (list-of-100-constants)

that the query has.
That is, 10.4.14 used a different query plan.

Comment by Manuel Arostegui [ 2021-01-05 ]

I can try to test something else if that'd help your investigation. Let me know!
Also even if the query plan was different on 10.4.14, the runtime was a lot slower on 10.4.17 which could also correlate with the huge difference on the scanned rows and with what we are seeing in production with the lag.

Comment by Sergei Petrunia [ 2021-01-05 ]

.. It is still not very clear where the issue is. marostegui, would it be possible to get this data from the pair of servers last compared (10.4.14 and 10.4.17)? I would like the output of the following:

set optimizer_trace=1;
 
analyze format=json select ... # The query we're looking at
 
select * from information_schema.optimizer_trace;
 
show variables like 'innodb_stats%';

Comment by Manuel Arostegui [ 2021-01-05 ]

Regarding the tables data, I think it can be shared, however I have asked internally to get confirmation about it.

As requested, this is 10.4.14:

root@pc1010.eqiad.wmnet[parsercache]> select @@version; 
+---------------------+
 
| @@version           |
 
+---------------------+
 
| 10.4.14-MariaDB-log |
 
+---------------------+
 
1 row in set (0.001 sec)
 
 
 
root@pc1010.eqiad.wmnet[parsercache]>  FLUSH STATUS;
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
root@pc1010.eqiad.wmnet[parsercache]> set optimizer_trace=1;
 
 
 
root@pc1010.eqiad.wmnet[parsercache]> analyze format=json DELETE  FROM `pc221` WHERE (exptime < '20201121180630') AND keyname IN ('wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47172141','ruwikinews:pcache:idoptions:2274652','plwiki:stable-pcache:idhash:2985160-0!canonical','commonswiki:pcache:idhash:28470463-0!wb=3','eswiki:pcache:idhash:8407454-0!canonical','wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47171328','cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0','wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3','zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs','commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0','cswiki:pcache:idhash:238465-0!canonical','arwiki:stable-pcache:idoptions:5577217','ruwikinews:pcache:idhash:2275122-0!canonical','wikidatawiki:pcache:idoptions:47170426','commonswiki:pcache:idhash:67472202-0!canonical','frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0','avwiki:pcache:idoptions:5377','svwiki:pcache:idhash:3766069-0!canonical','wikidatawiki:pcache:idoptions:47170137','wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47170029','avwiki:pcache:idhash:5154-0!canonical','itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0','azbwiki:pcache:idoptions:215428','wikidatawiki:pcache:idoptions:47169595','azbwiki:pcache:idhash:217308-0!canonical','commonswiki:pcache:idhash:76967381-1!userlang=sco','barwiki:pcache:idoptions:54173','commonswiki:pcache:idhash:63992420-0!wb=3','commonswiki:pcache:idoptions:36008242','commonswiki:pcache:idhash:14182321-0!wb=3','commonswiki:pcache:idhash:14182500-0!wb=3','dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3','svwiki:pcache:idoptions:6396670','commonswiki:pcache:idhash:2234685-1!userlang=fr','commonswiki:pcache:idoptions:13832128','ruwikinews:pcache:idoptions:2275409','commonswiki:pcache:idoptions:85397714','wikidatawiki:pcache:idoptions:45230319','ruwikinews:pcache:idoptions:2275772','wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3','bgwikiquote:pcache:idhash:5470-0!canonical','bgwikiquote:pcache:idoptions:5475','enwiki:pcache:idhash:30141328-0!canonical','wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3','eswiktionary:pcache:idhash:461899-0!canonical','enwiki:pcache:idhash:33991562-0!thumbsize=3','commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3','ruwikinews:pcache:idhash:2276526-0!canonical','commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3','bnwikisource:pcache:idhash:759895-0!canonical','commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0','bowiki:pcache:idhash:5208-0!canonical','cswiki:pcache:idhash:799292-0!canonical','eswiktionary:pcache:idoptions:462181','commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3','commonswiki:pcache:idhash:39411274-0!canonical','commonswiki:pcache:idoptions:39411310','wikidatawiki:pcache:idoptions:47138756','cswiki:pcache:idoptions:739306','idwiktionary:pcache:idoptions:44095','srwiki:pcache:idoptions:1563379','commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3','commonswiki:pcache:idhash:74084336-0!wb=3','wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3','bswiki:pcache:idoptions:195228','bpywiki:pcache:idoptions:45261','commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0','bswiktionary:pcache:idhash:4994-0!canonical','ruwikinews:pcache:idoptions:2277615','commonswiki:pcache:idhash:30387844-1!userlang=es','cawiki:pcache:idoptions:510277','cawiki:pcache:idoptions:542825','commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:3706602-1!userlang=ms','jawiki:pcache:idoptions:372412','wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3','cawiki:pcache:idoptions:539285','wikidatawiki:pcache:idoptions:47137123','commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0','zhwikisource:pcache:idhash:284908-0!canonical!zh','eswiktionary:pcache:idhash:463096-0!canonical','commonswiki:pcache:idhash:56623174-0!userlang=it','cawiki:pcache:idhash:539521-0!canonical','commonswiki:pcache:idhash:54662835-0!canonical','wikidatawiki:pcache:idoptions:47136275','wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3','commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0','eswiktionary:pcache:idhash:463367-0!canonical','eswiktionary:pcache:idhash:463413-0!canonical','ruwikinews:pcache:idoptions:2279056','hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0','ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:94865712-0!wb=3','ruwikinews:pcache:idhash:2280030-0!canonical','commonswiki:pcache:idhash:13651795-0!wb=3')
 
    -> /*!*/;
 
 
 
| {
 
  "query_block": {
 
    "select_id": 1,
 
    "r_total_time_ms": 17.906,
 
    "table": {
 
      "delete": 1,
 
      "table_name": "pc221",
 
      "access_type": "range",
 
      "possible_keys": ["keyname", "exptime"],
 
      "key": "keyname",
 
      "key_length": "257",
 
      "used_key_parts": ["keyname"],
 
      "rows": 100,
 
      "r_rows": 64,
 
      "r_filtered": 0,
 
      "r_total_time_ms": 17.82,
 
      "attached_condition": "pc221.exptime < '20201121180630' and pc221.keyname in ('wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47172141','ruwikinews:pcache:idoptions:2274652','plwiki:stable-pcache:idhash:2985160-0!canonical','commonswiki:pcache:idhash:28470463-0!wb=3','eswiki:pcache:idhash:8407454-0!canonical','wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47171328','cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0','wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3','zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs','commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0','cswiki:pcache:idhash:238465-0!canonical','arwiki:stable-pcache:idoptions:5577217','ruwikinews:pcache:idhash:2275122-0!canonical','wikidatawiki:pcache:idoptions:47170426','commonswiki:pcache:idhash:67472202-0!canonical','frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0','avwiki:pcache:idoptions:5377','svwiki:pcache:idhash:3766069-0!canonical','wikidatawiki:pcache:idoptions:47170137','wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47170029','avwiki:pcache:idhash:5154-0!canonical','itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0','azbwiki:pcache:idoptions:215428','wikidatawiki:pcache:idoptions:47169595','azbwiki:pcache:idhash:217308-0!canonical','commonswiki:pcache:idhash:76967381-1!userlang=sco','barwiki:pcache:idoptions:54173','commonswiki:pcache:idhash:63992420-0!wb=3','commonswiki:pcache:idoptions:36008242','commonswiki:pcache:idhash:14182321-0!wb=3','commonswiki:pcache:idhash:14182500-0!wb=3','dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3','svwiki:pcache:idoptions:6396670','commonswiki:pcache:idhash:2234685-1!userlang=fr','commonswiki:pcache:idoptions:13832128','ruwikinews:pcache:idoptions:2275409','commonswiki:pcache:idoptions:85397714','wikidatawiki:pcache:idoptions:45230319','ruwikinews:pcache:idoptions:2275772','wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3','bgwikiquote:pcache:idhash:5470-0!canonical','bgwikiquote:pcache:idoptions:5475','enwiki:pcache:idhash:30141328-0!canonical','wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3','eswiktionary:pcache:idhash:461899-0!canonical','enwiki:pcache:idhash:33991562-0!thumbsize=3','commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3','ruwikinews:pcache:idhash:2276526-0!canonical','commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3','bnwikisource:pcache:idhash:759895-0!canonical','commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0','bowiki:pcache:idhash:5208-0!canonical','cswiki:pcache:idhash:799292-0!canonical','eswiktionary:pcache:idoptions:462181','commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3','commonswiki:pcache:idhash:39411274-0!canonical','commonswiki:pcache:idoptions:39411310','wikidatawiki:pcache:idoptions:47138756','cswiki:pcache:idoptions:739306','idwiktionary:pcache:idoptions:44095','srwiki:pcache:idoptions:1563379','commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3','commonswiki:pcache:idhash:74084336-0!wb=3','wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3','bswiki:pcache:idoptions:195228','bpywiki:pcache:idoptions:45261','commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0','bswiktionary:pcache:idhash:4994-0!canonical','ruwikinews:pcache:idoptions:2277615','commonswiki:pcache:idhash:30387844-1!userlang=es','cawiki:pcache:idoptions:510277','cawiki:pcache:idoptions:542825','commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:3706602-1!userlang=ms','jawiki:pcache:idoptions:372412','wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3','cawiki:pcache:idoptions:539285','wikidatawiki:pcache:idoptions:47137123','commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0','zhwikisource:pcache:idhash:284908-0!canonical!zh','eswiktionary:pcache:idhash:463096-0!canonical','commonswiki:pcache:idhash:56623174-0!userlang=it','cawiki:pcache:idhash:539521-0!canonical','commonswiki:pcache:idhash:54662835-0!canonical','wikidatawiki:pcache:idoptions:47136275','wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3','commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0','eswiktionary:pcache:idhash:463367-0!canonical','eswiktionary:pcache:idhash:463413-0!canonical','ruwikinews:pcache:idoptions:2279056','hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0','ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:94865712-0!wb=3','ruwikinews:pcache:idhash:2280030-0!canonical','commonswiki:pcache:idhash:13651795-0!wb=3')"
 
    }
 
  }
 
} |
 
 
 
root@pc1010.eqiad.wmnet[parsercache]> select * from information_schema.optimizer_trace;
 
 
 
 analyze format=json DELETE  FROM `pc221` WHERE (exptime < '20201121180630') AND keyname IN ('wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47172141','ruwikinews:pcache:idoptions:2274652','plwiki:stable-pcache:idhash:2985160-0!canonical','commonswiki:pcache:idhash:28470463-0!wb=3','eswiki:pcache:idhash:8407454-0!canonical','wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47171328','cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0','wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3','zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs','commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0','cswiki:pcache:idhash:238465-0!canonical','arwiki:stable-pcache:idoptions:5577217','ruwikinews:pcache:idhash:2275122-0!canonical','wikidatawiki:pcache:idoptions:47170426','commonswiki:pcache:idhash:67472202-0!canonical','frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0','avwiki:pcache:idoptions:5377','svwiki:pcache:idhash:3766069-0!canonical','wikidatawiki:pcache:idoptions:47170137','wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47170029','avwiki:pcache:idhash:5154-0!canonical','itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0','azbwiki:pcache:idoptions:215428','wikidatawiki:pcache:idoptions:47169595','azbwiki:pcache:idhash:217308-0!canonical','commonswiki:pcache:idhash:76967381-1!userlang=sco','barwiki:pcache:idoptions:54173','commonswiki:pcache:idhash:63992420-0!wb=3','commonswiki:pcache:idoptions:36008242','commonswiki:pcache:idhash:14182321-0!wb=3','commonswiki:pcache:idhash:14182500-0!wb=3','dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3','svwiki:pcache:idoptions:6396670','commonswiki:pcache:idhash:2234685-1!userlang=fr','commonswiki:pcache:idoptions:13832128','ruwikinews:pcache:idoptions:2275409','commonswiki:pcache:idoptions:85397714','wikidatawiki:pcache:idoptions:45230319','ruwikinews:pcache:idoptions:2275772','wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3','bgwikiquote:pcache:idhash:5470-0!canonical','bgwikiquote:pcache:idoptions:5475','enwiki:pcache:idhash:30141328-0!canonical','wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3','eswiktionary:pcache:idhash:461899-0!canonical','enwiki:pcache:idhash:33991562-0!thumbsize=3','commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3','ruwikinews:pcache:idhash:2276526-0!canonical','commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3','bnwikisource:pcache:idhash:759895-0!canonical','commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0','bowiki:pcache:idhash:5208-0!canonical','cswiki:pcache:idhash:799292-0!canonical','eswiktionary:pcache:idoptions:462181','commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3','commonswiki:pcache:idhash:39411274-0!canonical','commonswiki:pcache:idoptions:39411310','wikidatawiki:pcache:idoptions:47138756','cswiki:pcache:idoptions:739306','idwiktionary:pcache:idoptions:44095','srwiki:pcache:idoptions:1563379','commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3','commonswiki:pcache:idhash:74084336-0!wb=3','wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3','bswiki:pcache:idoptions:195228','bpywiki:pcache:idoptions:45261','commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0','bswiktionary:pcache:idhash:4994-0!canonical','ruwikinews:pcache:idoptions:2277615','commonswiki:pcache:idhash:30387844-1!userlang=es','cawiki:pcache:idoptions:510277','cawiki:pcache:idoptions:542825','commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:3706602-1!userlang=ms','jawiki:pcache:idoptions:372412','wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3','cawiki:pcache:idoptions:539285','wikidatawiki:pcache:idoptions:47137123','commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0','zhwikisource:pcache:idhash:284908-0!canonical!zh','eswiktionary:pcache:idhash:463096-0!canonical','commonswiki:pcache:idhash:56623174-0!userlang=it','cawiki:pcache:idhash:539521-0!canonical','commonswiki:pcache:idhash:54662835-0!canonical','wikidatawiki:pcache:idoptions:47136275','wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3','commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0','eswiktionary:pcache:idhash:463367-0!canonical','eswiktionary:pcache:idhash:463413-0!canonical','ruwikinews:pcache:idoptions:2279056','hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0','ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:94865712-0!wb=3','ruwikinews:pcache:idhash:2280030-0!canonical','commonswiki:pcache:idhash:13651795-0!wb=3')
 
/*!*/ | {
 
  "steps": [
 
    {
 
      "table": "pc221",
 
      "range_analysis": {
 
        "table_scan": {
 
          "rows": 591399,
 
          "cost": 832654
 
        },
 
        "potential_range_indexes": [
 
          {
 
            "index": "keyname",
 
            "usable": true,
 
            "key_parts": ["keyname"]
 
          },
 
          {
 
            "index": "exptime",
 
            "usable": true,
 
            "key_parts": ["exptime", "keyname"]
 
          }
 
        ],
 
        "setup_range_conditions": [],
 
        "group_index_range": {
 
          "chosen": false,
 
          "cause": "no join"
 
        },
 
        "analyzing_range_alternatives": {
 
          "range_scan_alternatives": [
 
            {
 
              "index": "keyname",
 
              "ranges": [
 
                "(arwiki:stable-pcache:idoptions:5577217) <= (keyname) <= (arwiki:stable-pcache:idoptions:5577217)",
 
                "(avwiki:pcache:idhash:5154-0!canonical) <= (keyname) <= (avwiki:pcache:idhash:5154-0!canonical)",
 
                "(avwiki:pcache:idoptions:5377) <= (keyname) <= (avwiki:pcache:idoptions:5377)",
 
                "(azbwiki:pcache:idhash:217308-0!canonical) <= (keyname) <= (azbwiki:pcache:idhash:217308-0!canonical)",
 
                "(azbwiki:pcache:idoptions:215428) <= (keyname) <= (azbwiki:pcache:idoptions:215428)",
 
                "(barwiki:pcache:idoptions:54173) <= (keyname) <= (barwiki:pcache:idoptions:54173)",
 
                "(bgwikiquote:pcache:idhash:5470-0!canonical) <= (keyname) <= (bgwikiquote:pcache:idhash:5470-0!canonical)",
 
                "(bgwikiquote:pcache:idoptions:5475) <= (keyname) <= (bgwikiquote:pcache:idoptions:5475)",
 
                "(bnwikisource:pcache:idhash:759895-0!canonical) <= (keyname) <= (bnwikisource:pcache:idhash:759895-0!canonical)",
 
                "(bowiki:pcache:idhash:5208-0!canonical) <= (keyname) <= (bowiki:pcache:idhash:5208-0!canonical)",
 
                "(bpywiki:pcache:idoptions:45261) <= (keyname) <= (bpywiki:pcache:idoptions:45261)",
 
                "(bswiki:pcache:idoptions:195228) <= (keyname) <= (bswiki:pcache:idoptions:195228)",
 
                "(bswiktionary:pcache:idhash:4994-0!canonical) <= (keyname) <= (bswiktionary:pcache:idhash:4994-0!canonical)",
 
                "(cawiki:pcache:idhash:539521-0!canonical) <= (keyname) <= (cawiki:pcache:idhash:539521-0!canonical)",
 
                "(cawiki:pcache:idoptions:510277) <= (keyname) <= (cawiki:pcache:idoptions:510277)",
 
                "(cawiki:pcache:idoptions:539285) <= (keyname) <= (cawiki:pcache:idoptions:539285)",
 
                "(cawiki:pcache:idoptions:542825) <= (keyname) <= (cawiki:pcache:idoptions:542825)",
 
                "(cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0) <= (keyname) <= (cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:13651795-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:13651795-0!wb=3)",
 
                "(commonswiki:pcache:idhash:14182321-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:14182321-0!wb=3)",
 
                "(commonswiki:pcache:idhash:14182500-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:14182500-0!wb=3)",
 
                "(commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:2234685-1!userlang=fr) <= (keyname) <= (commonswiki:pcache:idhash:2234685-1!userlang=fr)",
 
                "(commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:28470463-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:28470463-0!wb=3)",
 
                "(commonswiki:pcache:idhash:30387844-1!userlang=es) <= (keyname) <= (commonswiki:pcache:idhash:30387844-1!userlang=es)",
 
                "(commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:3706602-1!userlang=ms) <= (keyname) <= (commonswiki:pcache:idhash:3706602-1!userlang=ms)",
 
                "(commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:39411274-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:39411274-0!canonical)",
 
                "(commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3)",
 
                "(commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3)",
 
                "(commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3)",
 
                "(commonswiki:pcache:idhash:54662835-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:54662835-0!canonical)",
 
                "(commonswiki:pcache:idhash:56623174-0!userlang=it) <= (keyname) <= (commonswiki:pcache:idhash:56623174-0!userlang=it)",
 
                "(commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3)",
 
                "(commonswiki:pcache:idhash:63992420-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:63992420-0!wb=3)",
 
                "(commonswiki:pcache:idhash:67472202-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:67472202-0!canonical)",
 
                "(commonswiki:pcache:idhash:74084336-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:74084336-0!wb=3)",
 
                "(commonswiki:pcache:idhash:76967381-1!userlang=sco) <= (keyname) <= (commonswiki:pcache:idhash:76967381-1!userlang=sco)",
 
                "(commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3)",
 
                "(commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:94865712-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:94865712-0!wb=3)",
 
                "(commonswiki:pcache:idoptions:13832128) <= (keyname) <= (commonswiki:pcache:idoptions:13832128)",
 
                "(commonswiki:pcache:idoptions:36008242) <= (keyname) <= (commonswiki:pcache:idoptions:36008242)",
 
                "(commonswiki:pcache:idoptions:39411310) <= (keyname) <= (commonswiki:pcache:idoptions:39411310)",
 
                "(commonswiki:pcache:idoptions:85397714) <= (keyname) <= (commonswiki:pcache:idoptions:85397714)",
 
                "(cswiki:pcache:idhash:238465-0!canonical) <= (keyname) <= (cswiki:pcache:idhash:238465-0!canonical)",
 
                "(cswiki:pcache:idhash:799292-0!canonical) <= (keyname) <= (cswiki:pcache:idhash:799292-0!canonical)",
 
                "(cswiki:pcache:idoptions:739306) <= (keyname) <= (cswiki:pcache:idoptions:739306)",
 
                "(dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0) <= (keyname) <= (dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0)",
 
                "(enwiki:pcache:idhash:30141328-0!canonical) <= (keyname) <= (enwiki:pcache:idhash:30141328-0!canonical)",
 
                "(enwiki:pcache:idhash:33991562-0!thumbsize=3) <= (keyname) <= (enwiki:pcache:idhash:33991562-0!thumbsize=3)",
 
                "(eswiki:pcache:idhash:8407454-0!canonical) <= (keyname) <= (eswiki:pcache:idhash:8407454-0!canonical)",
 
                "(eswiktionary:pcache:idhash:461899-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:461899-0!canonical)",
 
                "(eswiktionary:pcache:idhash:463096-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463096-0!canonical)",
 
                "(eswiktionary:pcache:idhash:463367-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463367-0!canonical)",
 
                "(eswiktionary:pcache:idhash:463413-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463413-0!canonical)",
 
                "(eswiktionary:pcache:idoptions:462181) <= (keyname) <= (eswiktionary:pcache:idoptions:462181)",
 
                "(frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0) <= (keyname) <= (frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0)",
 
                "(hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0) <= (keyname) <= (hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0)",
 
                "(idwiktionary:pcache:idoptions:44095) <= (keyname) <= (idwiktionary:pcache:idoptions:44095)",
 
                "(itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0) <= (keyname) <= (itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0)",
 
                "(jawiki:pcache:idoptions:372412) <= (keyname) <= (jawiki:pcache:idoptions:372412)",
 
                "(plwiki:stable-pcache:idhash:2985160-0!canonical) <= (keyname) <= (plwiki:stable-pcache:idhash:2985160-0!canonical)",
 
                "(ruwikinews:pcache:idhash:2275122-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2275122-0!canonical)",
 
                "(ruwikinews:pcache:idhash:2276526-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2276526-0!canonical)",
 
                "(ruwikinews:pcache:idhash:2280030-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2280030-0!canonical)",
 
                "(ruwikinews:pcache:idoptions:2274652) <= (keyname) <= (ruwikinews:pcache:idoptions:2274652)",
 
                "(ruwikinews:pcache:idoptions:2275409) <= (keyname) <= (ruwikinews:pcache:idoptions:2275409)",
 
                "(ruwikinews:pcache:idoptions:2275772) <= (keyname) <= (ruwikinews:pcache:idoptions:2275772)",
 
                "(ruwikinews:pcache:idoptions:2277615) <= (keyname) <= (ruwikinews:pcache:idoptions:2277615)",
 
                "(ruwikinews:pcache:idoptions:2279056) <= (keyname) <= (ruwikinews:pcache:idoptions:2279056)",
 
                "(srwiki:pcache:idoptions:1563379) <= (keyname) <= (srwiki:pcache:idoptions:1563379)",
 
                "(svwiki:pcache:idhash:3766069-0!canonical) <= (keyname) <= (svwiki:pcache:idhash:3766069-0!canonical)",
 
                "(svwiki:pcache:idoptions:6396670) <= (keyname) <= (svwiki:pcache:idoptions:6396670)",
 
                "(ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0) <= (keyname) <= (ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0)",
 
                "(wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idoptions:45230319) <= (keyname) <= (wikidatawiki:pcache:idoptions:45230319)",
 
                "(wikidatawiki:pcache:idoptions:47136275) <= (keyname) <= (wikidatawiki:pcache:idoptions:47136275)",
 
                "(wikidatawiki:pcache:idoptions:47137123) <= (keyname) <= (wikidatawiki:pcache:idoptions:47137123)",
 
                "(wikidatawiki:pcache:idoptions:47138756) <= (keyname) <= (wikidatawiki:pcache:idoptions:47138756)",
 
                "(wikidatawiki:pcache:idoptions:47169595) <= (keyname) <= (wikidatawiki:pcache:idoptions:47169595)",
 
                "(wikidatawiki:pcache:idoptions:47170029) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170029)",
 
                "(wikidatawiki:pcache:idoptions:47170137) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170137)",
 
                "(wikidatawiki:pcache:idoptions:47170426) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170426)",
 
                "(wikidatawiki:pcache:idoptions:47171328) <= (keyname) <= (wikidatawiki:pcache:idoptions:47171328)",
 
                "(wikidatawiki:pcache:idoptions:47172141) <= (keyname) <= (wikidatawiki:pcache:idoptions:47172141)",
 
                "(zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs) <= (keyname) <= (zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs)",
 
                "(zhwikisource:pcache:idhash:284908-0!canonical!zh) <= (keyname) <= (zhwikisource:pcache:idhash:284908-0!canonical!zh)"
 
              ],
 
              "rowid_ordered": true,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 100,
 
              "cost": 120.12,
 
              "chosen": true
 
            },
 
            {
 
              "index": "exptime",
 
              "ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 102,
 
              "cost": 131.08,
 
              "chosen": false,
 
              "cause": "cost"
 
            }
 
          ],
 
          "analyzing_index_merge_union": []
 
        },
 
        "chosen_range_access_summary": {
 
          "range_access_plan": {
 
            "type": "range_scan",
 
            "index": "keyname",
 
            "rows": 100,
 
            "ranges": [
 
              "(arwiki:stable-pcache:idoptions:5577217) <= (keyname) <= (arwiki:stable-pcache:idoptions:5577217)",
 
              "(avwiki:pcache:idhash:5154-0!canonical) <= (keyname) <= (avwiki:pcache:idhash:5154-0!canonical)",
 
              "(avwiki:pcache:idoptions:5377) <= (keyname) <= (avwiki:pcache:idoptions:5377)",
 
              "(azbwiki:pcache:idhash:217308-0!canonical) <= (keyname) <= (azbwiki:pcache:idhash:217308-0!canonical)",
 
              "(azbwiki:pcache:idoptions:215428) <= (keyname) <= (azbwiki:pcache:idoptions:215428)",
 
              "(barwiki:pcache:idoptions:54173) <= (keyname) <= (barwiki:pcache:idoptions:54173)",
 
              "(bgwikiquote:pcache:idhash:5470-0!canonical) <= (keyname) <= (bgwikiquote:pcache:idhash:5470-0!canonical)",
 
              "(bgwikiquote:pcache:idoptions:5475) <= (keyname) <= (bgwikiquote:pcache:idoptions:5475)",
 
              "(bnwikisource:pcache:idhash:759895-0!canonical) <= (keyname) <= (bnwikisource:pcache:idhash:759895-0!canonical)",
 
              "(bowiki:pcache:idhash:5208-0!canonical) <= (keyname) <= (bowiki:pcache:idhash:5208-0!canonical)",
 
              "(bpywiki:pcache:idoptions:45261) <= (keyname) <= (bpywiki:pcache:idoptions:45261)",
 
              "(bswiki:pcache:idoptions:195228) <= (keyname) <= (bswiki:pcache:idoptions:195228)",
 
              "(bswiktionary:pcache:idhash:4994-0!canonical) <= (keyname) <= (bswiktionary:pcache:idhash:4994-0!canonical)",
 
              "(cawiki:pcache:idhash:539521-0!canonical) <= (keyname) <= (cawiki:pcache:idhash:539521-0!canonical)",
 
              "(cawiki:pcache:idoptions:510277) <= (keyname) <= (cawiki:pcache:idoptions:510277)",
 
              "(cawiki:pcache:idoptions:539285) <= (keyname) <= (cawiki:pcache:idoptions:539285)",
 
              "(cawiki:pcache:idoptions:542825) <= (keyname) <= (cawiki:pcache:idoptions:542825)",
 
              "(cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0) <= (keyname) <= (cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0)",
 
              "(commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0)",
 
              "(commonswiki:pcache:idhash:13651795-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:13651795-0!wb=3)",
 
              "(commonswiki:pcache:idhash:14182321-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:14182321-0!wb=3)",
 
              "(commonswiki:pcache:idhash:14182500-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:14182500-0!wb=3)",
 
              "(commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0)",
 
              "(commonswiki:pcache:idhash:2234685-1!userlang=fr) <= (keyname) <= (commonswiki:pcache:idhash:2234685-1!userlang=fr)",
 
              "(commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0)",
 
              "(commonswiki:pcache:idhash:28470463-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:28470463-0!wb=3)",
 
              "(commonswiki:pcache:idhash:30387844-1!userlang=es) <= (keyname) <= (commonswiki:pcache:idhash:30387844-1!userlang=es)",
 
              "(commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0)",
 
              "(commonswiki:pcache:idhash:3706602-1!userlang=ms) <= (keyname) <= (commonswiki:pcache:idhash:3706602-1!userlang=ms)",
 
              "(commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0)",
 
              "(commonswiki:pcache:idhash:39411274-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:39411274-0!canonical)",
 
              "(commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3)",
 
              "(commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3)",
 
              "(commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3)",
 
              "(commonswiki:pcache:idhash:54662835-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:54662835-0!canonical)",
 
              "(commonswiki:pcache:idhash:56623174-0!userlang=it) <= (keyname) <= (commonswiki:pcache:idhash:56623174-0!userlang=it)",
 
              "(commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3)",
 
              "(commonswiki:pcache:idhash:63992420-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:63992420-0!wb=3)",
 
              "(commonswiki:pcache:idhash:67472202-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:67472202-0!canonical)",
 
              "(commonswiki:pcache:idhash:74084336-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:74084336-0!wb=3)",
 
              "(commonswiki:pcache:idhash:76967381-1!userlang=sco) <= (keyname) <= (commonswiki:pcache:idhash:76967381-1!userlang=sco)",
 
              "(commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3)",
 
              "(commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0)",
 
              "(commonswiki:pcache:idhash:94865712-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:94865712-0!wb=3)",
 
              "(commonswiki:pcache:idoptions:13832128) <= (keyname) <= (commonswiki:pcache:idoptions:13832128)",
 
              "(commonswiki:pcache:idoptions:36008242) <= (keyname) <= (commonswiki:pcache:idoptions:36008242)",
 
              "(commonswiki:pcache:idoptions:39411310) <= (keyname) <= (commonswiki:pcache:idoptions:39411310)",
 
              "(commonswiki:pcache:idoptions:85397714) <= (keyname) <= (commonswiki:pcache:idoptions:85397714)",
 
              "(cswiki:pcache:idhash:238465-0!canonical) <= (keyname) <= (cswiki:pcache:idhash:238465-0!canonical)",
 
              "(cswiki:pcache:idhash:799292-0!canonical) <= (keyname) <= (cswiki:pcache:idhash:799292-0!canonical)",
 
              "(cswiki:pcache:idoptions:739306) <= (keyname) <= (cswiki:pcache:idoptions:739306)",
 
              "(dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0) <= (keyname) <= (dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0)",
 
              "(enwiki:pcache:idhash:30141328-0!canonical) <= (keyname) <= (enwiki:pcache:idhash:30141328-0!canonical)",
 
              "(enwiki:pcache:idhash:33991562-0!thumbsize=3) <= (keyname) <= (enwiki:pcache:idhash:33991562-0!thumbsize=3)",
 
              "(eswiki:pcache:idhash:8407454-0!canonical) <= (keyname) <= (eswiki:pcache:idhash:8407454-0!canonical)",
 
              "(eswiktionary:pcache:idhash:461899-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:461899-0!canonical)",
 
              "(eswiktionary:pcache:idhash:463096-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463096-0!canonical)",
 
              "(eswiktionary:pcache:idhash:463367-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463367-0!canonical)",
 
              "(eswiktionary:pcache:idhash:463413-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463413-0!canonical)",
 
              "(eswiktionary:pcache:idoptions:462181) <= (keyname) <= (eswiktionary:pcache:idoptions:462181)",
 
              "(frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0) <= (keyname) <= (frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0)",
 
              "(hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0) <= (keyname) <= (hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0)",
 
              "(idwiktionary:pcache:idoptions:44095) <= (keyname) <= (idwiktionary:pcache:idoptions:44095)",
 
              "(itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0) <= (keyname) <= (itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0)",
 
              "(jawiki:pcache:idoptions:372412) <= (keyname) <= (jawiki:pcache:idoptions:372412)",
 
              "(plwiki:stable-pcache:idhash:2985160-0!canonical) <= (keyname) <= (plwiki:stable-pcache:idhash:2985160-0!canonical)",
 
              "(ruwikinews:pcache:idhash:2275122-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2275122-0!canonical)",
 
              "(ruwikinews:pcache:idhash:2276526-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2276526-0!canonical)",
 
              "(ruwikinews:pcache:idhash:2280030-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2280030-0!canonical)",
 
              "(ruwikinews:pcache:idoptions:2274652) <= (keyname) <= (ruwikinews:pcache:idoptions:2274652)",
 
              "(ruwikinews:pcache:idoptions:2275409) <= (keyname) <= (ruwikinews:pcache:idoptions:2275409)",
 
              "(ruwikinews:pcache:idoptions:2275772) <= (keyname) <= (ruwikinews:pcache:idoptions:2275772)",
 
              "(ruwikinews:pcache:idoptions:2277615) <= (keyname) <= (ruwikinews:pcache:idoptions:2277615)",
 
              "(ruwikinews:pcache:idoptions:2279056) <= (keyname) <= (ruwikinews:pcache:idoptions:2279056)",
 
              "(srwiki:pcache:idoptions:1563379) <= (keyname) <= (srwiki:pcache:idoptions:1563379)",
 
              "(svwiki:pcache:idhash:3766069-0!canonical) <= (keyname) <= (svwiki:pcache:idhash:3766069-0!canonical)",
 
              "(svwiki:pcache:idoptions:6396670) <= (keyname) <= (svwiki:pcache:idoptions:6396670)",
 
              "(ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0) <= (keyname) <= (ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0)",
 
              "(wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3)",
 
              "(wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3)",
 
              "(wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3)",
 
              "(wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3)",
 
              "(wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3)",
 
              "(wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3)",
 
              "(wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3)",
 
              "(wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3)",
 
              "(wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3)",
 
              "(wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3)",
 
              "(wikidatawiki:pcache:idoptions:45230319) <= (keyname) <= (wikidatawiki:pcache:idoptions:45230319)",
 
              "(wikidatawiki:pcache:idoptions:47136275) <= (keyname) <= (wikidatawiki:pcache:idoptions:47136275)",
 
              "(wikidatawiki:pcache:idoptions:47137123) <= (keyname) <= (wikidatawiki:pcache:idoptions:47137123)",
 
              "(wikidatawiki:pcache:idoptions:47138756) <= (keyname) <= (wikidatawiki:pcache:idoptions:47138756)",
 
              "(wikidatawiki:pcache:idoptions:47169595) <= (keyname) <= (wikidatawiki:pcache:idoptions:47169595)",
 
              "(wikidatawiki:pcache:idoptions:47170029) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170029)",
 
              "(wikidatawiki:pcache:idoptions:47170137) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170137)",
 
              "(wikidatawiki:pcache:idoptions:47170426) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170426)",
 
              "(wikidatawiki:pcache:idoptions:47171328) <= (keyname) <= (wikidatawiki:pcache:idoptions:47171328)",
 
              "(wikidatawiki:pcache:idoptions:47172141) <= (keyname) <= (wikidatawiki:pcache:idoptions:47172141)",
 
              "(zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs) <= (keyname) <= (zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs)",
 
              "(zhwikisource:pcache:idhash:284908-0!canonical!zh) <= (keyname) <= (zhwikisource:pcache:idhash:284908-0!canonical!zh)"
 
            ]
 
          },
 
          "rows_for_plan": 100,
 
          "cost_for_plan": 120.12,
 
          "chosen": true
 
        }
 
      }
 
    }
 
  ]
 
} |
 
 
 
root@pc1010.eqiad.wmnet[parsercache]> show variables like 'innodb_stats%';
 
+--------------------------------------+---------------+
 
| Variable_name                        | Value         |
 
+--------------------------------------+---------------+
 
| innodb_stats_auto_recalc             | ON            |
 
| innodb_stats_include_delete_marked   | OFF           |
 
| innodb_stats_method                  | nulls_unequal |
 
| innodb_stats_modified_counter        | 0             |
 
| innodb_stats_on_metadata             | OFF           |
 
| innodb_stats_persistent              | ON            |
 
| innodb_stats_persistent_sample_pages | 20            |
 
| innodb_stats_sample_pages            | 16            |
 
| innodb_stats_traditional             | ON            |
 
| innodb_stats_transient_sample_pages  | 16            |
 
+--------------------------------------+---------------+
 
10 rows in set (0.002 sec)

And 10.4.17:

 
 
root@pc2010.codfw.wmnet[parsercache]>  select @@version;
 
+---------------------+
 
| @@version           |
 
+---------------------+
 
| 10.4.17-MariaDB-log |
 
+---------------------+
 
1 row in set (0.034 sec)
 
 
 
root@pc2010.codfw.wmnet[parsercache]>   FLUSH STATUS;
 
Query OK, 0 rows affected (0.034 sec)
 
 
 
root@pc2010.codfw.wmnet[parsercache]> set optimizer_trace=1;
 
 
 
root@pc2010.codfw.wmnet[parsercache]> analyze format=json DELETE  FROM `pc221` WHERE (exptime < '20201121180630') AND keyname IN ('wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47172141','ruwikinews:pcache:idoptions:2274652','plwiki:stable-pcache:idhash:2985160-0!canonical','commonswiki:pcache:idhash:28470463-0!wb=3','eswiki:pcache:idhash:8407454-0!canonical','wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47171328','cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0','wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3','zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs','commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0','cswiki:pcache:idhash:238465-0!canonical','arwiki:stable-pcache:idoptions:5577217','ruwikinews:pcache:idhash:2275122-0!canonical','wikidatawiki:pcache:idoptions:47170426','commonswiki:pcache:idhash:67472202-0!canonical','frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0','avwiki:pcache:idoptions:5377','svwiki:pcache:idhash:3766069-0!canonical','wikidatawiki:pcache:idoptions:47170137','wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47170029','avwiki:pcache:idhash:5154-0!canonical','itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0','azbwiki:pcache:idoptions:215428','wikidatawiki:pcache:idoptions:47169595','azbwiki:pcache:idhash:217308-0!canonical','commonswiki:pcache:idhash:76967381-1!userlang=sco','barwiki:pcache:idoptions:54173','commonswiki:pcache:idhash:63992420-0!wb=3','commonswiki:pcache:idoptions:36008242','commonswiki:pcache:idhash:14182321-0!wb=3','commonswiki:pcache:idhash:14182500-0!wb=3','dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3','svwiki:pcache:idoptions:6396670','commonswiki:pcache:idhash:2234685-1!userlang=fr','commonswiki:pcache:idoptions:13832128','ruwikinews:pcache:idoptions:2275409','commonswiki:pcache:idoptions:85397714','wikidatawiki:pcache:idoptions:45230319','ruwikinews:pcache:idoptions:2275772','wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3','bgwikiquote:pcache:idhash:5470-0!canonical','bgwikiquote:pcache:idoptions:5475','enwiki:pcache:idhash:30141328-0!canonical','wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3','eswiktionary:pcache:idhash:461899-0!canonical','enwiki:pcache:idhash:33991562-0!thumbsize=3','commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3','ruwikinews:pcache:idhash:2276526-0!canonical','commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3','bnwikisource:pcache:idhash:759895-0!canonical','commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0','bowiki:pcache:idhash:5208-0!canonical','cswiki:pcache:idhash:799292-0!canonical','eswiktionary:pcache:idoptions:462181','commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3','commonswiki:pcache:idhash:39411274-0!canonical','commonswiki:pcache:idoptions:39411310','wikidatawiki:pcache:idoptions:47138756','cswiki:pcache:idoptions:739306','idwiktionary:pcache:idoptions:44095','srwiki:pcache:idoptions:1563379','commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3','commonswiki:pcache:idhash:74084336-0!wb=3','wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3','bswiki:pcache:idoptions:195228','bpywiki:pcache:idoptions:45261','commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0','bswiktionary:pcache:idhash:4994-0!canonical','ruwikinews:pcache:idoptions:2277615','commonswiki:pcache:idhash:30387844-1!userlang=es','cawiki:pcache:idoptions:510277','cawiki:pcache:idoptions:542825','commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:3706602-1!userlang=ms','jawiki:pcache:idoptions:372412','wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3','cawiki:pcache:idoptions:539285','wikidatawiki:pcache:idoptions:47137123','commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0','zhwikisource:pcache:idhash:284908-0!canonical!zh','eswiktionary:pcache:idhash:463096-0!canonical','commonswiki:pcache:idhash:56623174-0!userlang=it','cawiki:pcache:idhash:539521-0!canonical','commonswiki:pcache:idhash:54662835-0!canonical','wikidatawiki:pcache:idoptions:47136275','wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3','commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0','eswiktionary:pcache:idhash:463367-0!canonical','eswiktionary:pcache:idhash:463413-0!canonical','ruwikinews:pcache:idoptions:2279056','hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0','ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:94865712-0!wb=3','ruwikinews:pcache:idhash:2280030-0!canonical','commonswiki:pcache:idhash:13651795-0!wb=3')
 
    -> /*!*/;
 
 
 
| {
 
  "query_block": {
 
    "select_id": 1,
 
    "r_total_time_ms": 2468.4,
 
    "table": {
 
      "delete": 1,
 
      "table_name": "pc221",
 
      "access_type": "range",
 
      "possible_keys": ["keyname", "exptime"],
 
      "key": "exptime",
 
      "key_length": "9",
 
      "used_key_parts": ["exptime"],
 
      "rows": 28926,
 
      "r_rows": 15087,
 
      "r_filtered": 0,
 
      "r_total_time_ms": 2456.7,
 
      "attached_condition": "pc221.exptime < '20201121180630' and pc221.keyname in ('wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47172141','ruwikinews:pcache:idoptions:2274652','plwiki:stable-pcache:idhash:2985160-0!canonical','commonswiki:pcache:idhash:28470463-0!wb=3','eswiki:pcache:idhash:8407454-0!canonical','wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47171328','cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0','wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3','zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs','commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0','cswiki:pcache:idhash:238465-0!canonical','arwiki:stable-pcache:idoptions:5577217','ruwikinews:pcache:idhash:2275122-0!canonical','wikidatawiki:pcache:idoptions:47170426','commonswiki:pcache:idhash:67472202-0!canonical','frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0','avwiki:pcache:idoptions:5377','svwiki:pcache:idhash:3766069-0!canonical','wikidatawiki:pcache:idoptions:47170137','wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47170029','avwiki:pcache:idhash:5154-0!canonical','itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0','azbwiki:pcache:idoptions:215428','wikidatawiki:pcache:idoptions:47169595','azbwiki:pcache:idhash:217308-0!canonical','commonswiki:pcache:idhash:76967381-1!userlang=sco','barwiki:pcache:idoptions:54173','commonswiki:pcache:idhash:63992420-0!wb=3','commonswiki:pcache:idoptions:36008242','commonswiki:pcache:idhash:14182321-0!wb=3','commonswiki:pcache:idhash:14182500-0!wb=3','dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3','svwiki:pcache:idoptions:6396670','commonswiki:pcache:idhash:2234685-1!userlang=fr','commonswiki:pcache:idoptions:13832128','ruwikinews:pcache:idoptions:2275409','commonswiki:pcache:idoptions:85397714','wikidatawiki:pcache:idoptions:45230319','ruwikinews:pcache:idoptions:2275772','wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3','bgwikiquote:pcache:idhash:5470-0!canonical','bgwikiquote:pcache:idoptions:5475','enwiki:pcache:idhash:30141328-0!canonical','wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3','eswiktionary:pcache:idhash:461899-0!canonical','enwiki:pcache:idhash:33991562-0!thumbsize=3','commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3','ruwikinews:pcache:idhash:2276526-0!canonical','commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3','bnwikisource:pcache:idhash:759895-0!canonical','commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0','bowiki:pcache:idhash:5208-0!canonical','cswiki:pcache:idhash:799292-0!canonical','eswiktionary:pcache:idoptions:462181','commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3','commonswiki:pcache:idhash:39411274-0!canonical','commonswiki:pcache:idoptions:39411310','wikidatawiki:pcache:idoptions:47138756','cswiki:pcache:idoptions:739306','idwiktionary:pcache:idoptions:44095','srwiki:pcache:idoptions:1563379','commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3','commonswiki:pcache:idhash:74084336-0!wb=3','wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3','bswiki:pcache:idoptions:195228','bpywiki:pcache:idoptions:45261','commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0','bswiktionary:pcache:idhash:4994-0!canonical','ruwikinews:pcache:idoptions:2277615','commonswiki:pcache:idhash:30387844-1!userlang=es','cawiki:pcache:idoptions:510277','cawiki:pcache:idoptions:542825','commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:3706602-1!userlang=ms','jawiki:pcache:idoptions:372412','wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3','cawiki:pcache:idoptions:539285','wikidatawiki:pcache:idoptions:47137123','commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0','zhwikisource:pcache:idhash:284908-0!canonical!zh','eswiktionary:pcache:idhash:463096-0!canonical','commonswiki:pcache:idhash:56623174-0!userlang=it','cawiki:pcache:idhash:539521-0!canonical','commonswiki:pcache:idhash:54662835-0!canonical','wikidatawiki:pcache:idoptions:47136275','wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3','commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0','eswiktionary:pcache:idhash:463367-0!canonical','eswiktionary:pcache:idhash:463413-0!canonical','ruwikinews:pcache:idoptions:2279056','hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0','ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:94865712-0!wb=3','ruwikinews:pcache:idhash:2280030-0!canonical','commonswiki:pcache:idhash:13651795-0!wb=3')"
 
    }
 
  }
 
} |
 
 
 
root@pc2010.codfw.wmnet[parsercache]> select * from information_schema.optimizer_trace;
 
 
 
| analyze format=json DELETE  FROM `pc221` WHERE (exptime < '20201121180630') AND keyname IN ('wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47172141','ruwikinews:pcache:idoptions:2274652','plwiki:stable-pcache:idhash:2985160-0!canonical','commonswiki:pcache:idhash:28470463-0!wb=3','eswiki:pcache:idhash:8407454-0!canonical','wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47171328','cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0','wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3','zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs','commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0','cswiki:pcache:idhash:238465-0!canonical','arwiki:stable-pcache:idoptions:5577217','ruwikinews:pcache:idhash:2275122-0!canonical','wikidatawiki:pcache:idoptions:47170426','commonswiki:pcache:idhash:67472202-0!canonical','frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0','avwiki:pcache:idoptions:5377','svwiki:pcache:idhash:3766069-0!canonical','wikidatawiki:pcache:idoptions:47170137','wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47170029','avwiki:pcache:idhash:5154-0!canonical','itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0','azbwiki:pcache:idoptions:215428','wikidatawiki:pcache:idoptions:47169595','azbwiki:pcache:idhash:217308-0!canonical','commonswiki:pcache:idhash:76967381-1!userlang=sco','barwiki:pcache:idoptions:54173','commonswiki:pcache:idhash:63992420-0!wb=3','commonswiki:pcache:idoptions:36008242','commonswiki:pcache:idhash:14182321-0!wb=3','commonswiki:pcache:idhash:14182500-0!wb=3','dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3','svwiki:pcache:idoptions:6396670','commonswiki:pcache:idhash:2234685-1!userlang=fr','commonswiki:pcache:idoptions:13832128','ruwikinews:pcache:idoptions:2275409','commonswiki:pcache:idoptions:85397714','wikidatawiki:pcache:idoptions:45230319','ruwikinews:pcache:idoptions:2275772','wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3','bgwikiquote:pcache:idhash:5470-0!canonical','bgwikiquote:pcache:idoptions:5475','enwiki:pcache:idhash:30141328-0!canonical','wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3','eswiktionary:pcache:idhash:461899-0!canonical','enwiki:pcache:idhash:33991562-0!thumbsize=3','commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3','ruwikinews:pcache:idhash:2276526-0!canonical','commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3','bnwikisource:pcache:idhash:759895-0!canonical','commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0','bowiki:pcache:idhash:5208-0!canonical','cswiki:pcache:idhash:799292-0!canonical','eswiktionary:pcache:idoptions:462181','commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3','commonswiki:pcache:idhash:39411274-0!canonical','commonswiki:pcache:idoptions:39411310','wikidatawiki:pcache:idoptions:47138756','cswiki:pcache:idoptions:739306','idwiktionary:pcache:idoptions:44095','srwiki:pcache:idoptions:1563379','commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3','commonswiki:pcache:idhash:74084336-0!wb=3','wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3','bswiki:pcache:idoptions:195228','bpywiki:pcache:idoptions:45261','commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0','bswiktionary:pcache:idhash:4994-0!canonical','ruwikinews:pcache:idoptions:2277615','commonswiki:pcache:idhash:30387844-1!userlang=es','cawiki:pcache:idoptions:510277','cawiki:pcache:idoptions:542825','commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:3706602-1!userlang=ms','jawiki:pcache:idoptions:372412','wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3','cawiki:pcache:idoptions:539285','wikidatawiki:pcache:idoptions:47137123','commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0','zhwikisource:pcache:idhash:284908-0!canonical!zh','eswiktionary:pcache:idhash:463096-0!canonical','commonswiki:pcache:idhash:56623174-0!userlang=it','cawiki:pcache:idhash:539521-0!canonical','commonswiki:pcache:idhash:54662835-0!canonical','wikidatawiki:pcache:idoptions:47136275','wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3','commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0','eswiktionary:pcache:idhash:463367-0!canonical','eswiktionary:pcache:idhash:463413-0!canonical','ruwikinews:pcache:idoptions:2279056','hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0','ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:94865712-0!wb=3','ruwikinews:pcache:idhash:2280030-0!canonical','commonswiki:pcache:idhash:13651795-0!wb=3')
 
/*!*/ | {
 
  "steps": [
 
    {
 
      "table": "pc221",
 
      "range_analysis": {
 
        "table_scan": {
 
          "rows": 1329621,
 
          "cost": 980042
 
        },
 
        "potential_range_indexes": [
 
          {
 
            "index": "keyname",
 
            "usable": true,
 
            "key_parts": ["keyname"]
 
          },
 
          {
 
            "index": "exptime",
 
            "usable": true,
 
            "key_parts": ["exptime", "keyname"]
 
          }
 
        ],
 
        "setup_range_conditions": [],
 
        "group_index_range": {
 
          "chosen": false,
 
          "cause": "no join"
 
        },
 
        "analyzing_range_alternatives": {
 
          "range_scan_alternatives": [
 
            {
 
              "index": "keyname",
 
              "ranges": [
 
                "(arwiki:stable-pcache:idoptions:5577217) <= (keyname) <= (arwiki:stable-pcache:idoptions:5577217)",
 
                "(avwiki:pcache:idhash:5154-0!canonical) <= (keyname) <= (avwiki:pcache:idhash:5154-0!canonical)",
 
                "(avwiki:pcache:idoptions:5377) <= (keyname) <= (avwiki:pcache:idoptions:5377)",
 
                "(azbwiki:pcache:idhash:217308-0!canonical) <= (keyname) <= (azbwiki:pcache:idhash:217308-0!canonical)",
 
                "(azbwiki:pcache:idoptions:215428) <= (keyname) <= (azbwiki:pcache:idoptions:215428)",
 
                "(barwiki:pcache:idoptions:54173) <= (keyname) <= (barwiki:pcache:idoptions:54173)",
 
                "(bgwikiquote:pcache:idhash:5470-0!canonical) <= (keyname) <= (bgwikiquote:pcache:idhash:5470-0!canonical)",
 
                "(bgwikiquote:pcache:idoptions:5475) <= (keyname) <= (bgwikiquote:pcache:idoptions:5475)",
 
                "(bnwikisource:pcache:idhash:759895-0!canonical) <= (keyname) <= (bnwikisource:pcache:idhash:759895-0!canonical)",
 
                "(bowiki:pcache:idhash:5208-0!canonical) <= (keyname) <= (bowiki:pcache:idhash:5208-0!canonical)",
 
                "(bpywiki:pcache:idoptions:45261) <= (keyname) <= (bpywiki:pcache:idoptions:45261)",
 
                "(bswiki:pcache:idoptions:195228) <= (keyname) <= (bswiki:pcache:idoptions:195228)",
 
                "(bswiktionary:pcache:idhash:4994-0!canonical) <= (keyname) <= (bswiktionary:pcache:idhash:4994-0!canonical)",
 
                "(cawiki:pcache:idhash:539521-0!canonical) <= (keyname) <= (cawiki:pcache:idhash:539521-0!canonical)",
 
                "(cawiki:pcache:idoptions:510277) <= (keyname) <= (cawiki:pcache:idoptions:510277)",
 
                "(cawiki:pcache:idoptions:539285) <= (keyname) <= (cawiki:pcache:idoptions:539285)",
 
                "(cawiki:pcache:idoptions:542825) <= (keyname) <= (cawiki:pcache:idoptions:542825)",
 
                "(cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0) <= (keyname) <= (cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:13651795-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:13651795-0!wb=3)",
 
                "(commonswiki:pcache:idhash:14182321-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:14182321-0!wb=3)",
 
                "(commonswiki:pcache:idhash:14182500-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:14182500-0!wb=3)",
 
                "(commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:2234685-1!userlang=fr) <= (keyname) <= (commonswiki:pcache:idhash:2234685-1!userlang=fr)",
 
                "(commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:28470463-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:28470463-0!wb=3)",
 
                "(commonswiki:pcache:idhash:30387844-1!userlang=es) <= (keyname) <= (commonswiki:pcache:idhash:30387844-1!userlang=es)",
 
                "(commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:3706602-1!userlang=ms) <= (keyname) <= (commonswiki:pcache:idhash:3706602-1!userlang=ms)",
 
                "(commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:39411274-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:39411274-0!canonical)",
 
                "(commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3)",
 
                "(commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3)",
 
                "(commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3)",
 
                "(commonswiki:pcache:idhash:54662835-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:54662835-0!canonical)",
 
                "(commonswiki:pcache:idhash:56623174-0!userlang=it) <= (keyname) <= (commonswiki:pcache:idhash:56623174-0!userlang=it)",
 
                "(commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3)",
 
                "(commonswiki:pcache:idhash:63992420-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:63992420-0!wb=3)",
 
                "(commonswiki:pcache:idhash:67472202-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:67472202-0!canonical)",
 
                "(commonswiki:pcache:idhash:74084336-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:74084336-0!wb=3)",
 
                "(commonswiki:pcache:idhash:76967381-1!userlang=sco) <= (keyname) <= (commonswiki:pcache:idhash:76967381-1!userlang=sco)",
 
                "(commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3)",
 
                "(commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0)",
 
                "(commonswiki:pcache:idhash:94865712-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:94865712-0!wb=3)",
 
                "(commonswiki:pcache:idoptions:13832128) <= (keyname) <= (commonswiki:pcache:idoptions:13832128)",
 
                "(commonswiki:pcache:idoptions:36008242) <= (keyname) <= (commonswiki:pcache:idoptions:36008242)",
 
                "(commonswiki:pcache:idoptions:39411310) <= (keyname) <= (commonswiki:pcache:idoptions:39411310)",
 
                "(commonswiki:pcache:idoptions:85397714) <= (keyname) <= (commonswiki:pcache:idoptions:85397714)",
 
                "(cswiki:pcache:idhash:238465-0!canonical) <= (keyname) <= (cswiki:pcache:idhash:238465-0!canonical)",
 
                "(cswiki:pcache:idhash:799292-0!canonical) <= (keyname) <= (cswiki:pcache:idhash:799292-0!canonical)",
 
                "(cswiki:pcache:idoptions:739306) <= (keyname) <= (cswiki:pcache:idoptions:739306)",
 
                "(dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0) <= (keyname) <= (dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0)",
 
                "(enwiki:pcache:idhash:30141328-0!canonical) <= (keyname) <= (enwiki:pcache:idhash:30141328-0!canonical)",
 
                "(enwiki:pcache:idhash:33991562-0!thumbsize=3) <= (keyname) <= (enwiki:pcache:idhash:33991562-0!thumbsize=3)",
 
                "(eswiki:pcache:idhash:8407454-0!canonical) <= (keyname) <= (eswiki:pcache:idhash:8407454-0!canonical)",
 
                "(eswiktionary:pcache:idhash:461899-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:461899-0!canonical)",
 
                "(eswiktionary:pcache:idhash:463096-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463096-0!canonical)",
 
                "(eswiktionary:pcache:idhash:463367-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463367-0!canonical)",
 
                "(eswiktionary:pcache:idhash:463413-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463413-0!canonical)",
 
                "(eswiktionary:pcache:idoptions:462181) <= (keyname) <= (eswiktionary:pcache:idoptions:462181)",
 
                "(frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0) <= (keyname) <= (frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0)",
 
                "(hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0) <= (keyname) <= (hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0)",
 
                "(idwiktionary:pcache:idoptions:44095) <= (keyname) <= (idwiktionary:pcache:idoptions:44095)",
 
                "(itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0) <= (keyname) <= (itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0)",
 
                "(jawiki:pcache:idoptions:372412) <= (keyname) <= (jawiki:pcache:idoptions:372412)",
 
                "(plwiki:stable-pcache:idhash:2985160-0!canonical) <= (keyname) <= (plwiki:stable-pcache:idhash:2985160-0!canonical)",
 
                "(ruwikinews:pcache:idhash:2275122-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2275122-0!canonical)",
 
                "(ruwikinews:pcache:idhash:2276526-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2276526-0!canonical)",
 
                "(ruwikinews:pcache:idhash:2280030-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2280030-0!canonical)",
 
                "(ruwikinews:pcache:idoptions:2274652) <= (keyname) <= (ruwikinews:pcache:idoptions:2274652)",
 
                "(ruwikinews:pcache:idoptions:2275409) <= (keyname) <= (ruwikinews:pcache:idoptions:2275409)",
 
                "(ruwikinews:pcache:idoptions:2275772) <= (keyname) <= (ruwikinews:pcache:idoptions:2275772)",
 
                "(ruwikinews:pcache:idoptions:2277615) <= (keyname) <= (ruwikinews:pcache:idoptions:2277615)",
 
                "(ruwikinews:pcache:idoptions:2279056) <= (keyname) <= (ruwikinews:pcache:idoptions:2279056)",
 
                "(srwiki:pcache:idoptions:1563379) <= (keyname) <= (srwiki:pcache:idoptions:1563379)",
 
                "(svwiki:pcache:idhash:3766069-0!canonical) <= (keyname) <= (svwiki:pcache:idhash:3766069-0!canonical)",
 
                "(svwiki:pcache:idoptions:6396670) <= (keyname) <= (svwiki:pcache:idoptions:6396670)",
 
                "(ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0) <= (keyname) <= (ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0)",
 
                "(wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3)",
 
                "(wikidatawiki:pcache:idoptions:45230319) <= (keyname) <= (wikidatawiki:pcache:idoptions:45230319)",
 
                "(wikidatawiki:pcache:idoptions:47136275) <= (keyname) <= (wikidatawiki:pcache:idoptions:47136275)",
 
                "(wikidatawiki:pcache:idoptions:47137123) <= (keyname) <= (wikidatawiki:pcache:idoptions:47137123)",
 
                "(wikidatawiki:pcache:idoptions:47138756) <= (keyname) <= (wikidatawiki:pcache:idoptions:47138756)",
 
                "(wikidatawiki:pcache:idoptions:47169595) <= (keyname) <= (wikidatawiki:pcache:idoptions:47169595)",
 
                "(wikidatawiki:pcache:idoptions:47170029) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170029)",
 
                "(wikidatawiki:pcache:idoptions:47170137) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170137)",
 
                "(wikidatawiki:pcache:idoptions:47170426) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170426)",
 
                "(wikidatawiki:pcache:idoptions:47171328) <= (keyname) <= (wikidatawiki:pcache:idoptions:47171328)",
 
                "(wikidatawiki:pcache:idoptions:47172141) <= (keyname) <= (wikidatawiki:pcache:idoptions:47172141)",
 
                "(zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs) <= (keyname) <= (zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs)",
 
                "(zhwikisource:pcache:idhash:284908-0!canonical!zh) <= (keyname) <= (zhwikisource:pcache:idhash:284908-0!canonical!zh)"
 
              ],
 
              "rowid_ordered": true,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 100,
 
              "cost": 65515,
 
              "chosen": true
 
            },
 
            {
 
              "index": "exptime",
 
              "ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 28926,
 
              "cost": 36881,
 
              "chosen": true
 
            }
 
          ],
 
          "analyzing_index_merge_union": []
 
        },
 
        "chosen_range_access_summary": {
 
          "range_access_plan": {
 
            "type": "range_scan",
 
            "index": "exptime",
 
            "rows": 28926,
 
            "ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"]
 
          },
 
          "rows_for_plan": 28926,
 
          "cost_for_plan": 36881,
 
          "chosen": true
 
        }
 
      }
 
    }
 
  ]
 
} |                                 0 |                       0 |
 
 
 
root@pc2010.codfw.wmnet[parsercache]> show variables like 'innodb_stats%';
 
+--------------------------------------+---------------+
 
| Variable_name                        | Value         |
 
+--------------------------------------+---------------+
 
| innodb_stats_auto_recalc             | ON            |
 
| innodb_stats_include_delete_marked   | OFF           |
 
| innodb_stats_method                  | nulls_unequal |
 
| innodb_stats_modified_counter        | 0             |
 
| innodb_stats_on_metadata             | OFF           |
 
| innodb_stats_persistent              | ON            |
 
| innodb_stats_persistent_sample_pages | 20            |
 
| innodb_stats_sample_pages            | 16            |
 
| innodb_stats_traditional             | ON            |
 
| innodb_stats_transient_sample_pages  | 16            |
 
+--------------------------------------+---------------+
 
10 rows in set (0.035 sec)

Tables have the same definition as expected:

+---------------------+
 
| @@version           |
 
+---------------------+
 
| 10.4.17-MariaDB-log |
 
+---------------------+
 
*************************** 1. row ***************************
 
       Table: pc221
 
Create Table: CREATE TABLE `pc221` (
 
  `keyname` varbinary(255) NOT NULL DEFAULT '',
 
  `value` mediumblob DEFAULT NULL,
 
  `exptime` datetime DEFAULT NULL,
 
  UNIQUE KEY `keyname` (`keyname`),
 
  KEY `exptime` (`exptime`)
 
) ENGINE=InnoDB DEFAULT CHARSET=binary
 
+---------------------+
 
| @@version           |
 
+---------------------+
 
| 10.4.14-MariaDB-log |
 
+---------------------+
 
*************************** 1. row ***************************
 
       Table: pc221
 
Create Table: CREATE TABLE `pc221` (
 
  `keyname` varbinary(255) NOT NULL DEFAULT '',
 
  `value` mediumblob DEFAULT NULL,
 
  `exptime` datetime DEFAULT NULL,
 
  UNIQUE KEY `keyname` (`keyname`),
 
  KEY `exptime` (`exptime`)
 
) ENGINE=InnoDB DEFAULT CHARSET=binary

Comment by Manuel Arostegui [ 2021-01-07 ]

Sergei, I am trying to work out if we can send some table dump, but it might not be as easy as I thought it would.

Comment by Manuel Arostegui [ 2021-01-07 ]

I just did the following test:

  • extracted a mysqldump from the table from a 10.4.17 host on where the optimizer decides to scan 15k rows for the DELETE and placed it on the same host (to discard table or data corruptions or anything like that) on a different database 

    | Handler_read_next          | 15087 |

  • placed it on a 10.4.14 host and run the DELETE: 

    | Handler_read_key           | 107   |

  • placed it on a different 10.4.15 host and run the DELETE 

    | Handler_read_key           | 107   |

  • placed it on a different 10.4.17 host and run the DELETE 

    | Handler_read_next          | 15087 |

I have run an analyze table and and a alter table forcing a table rebuild, but nothing changes on the 10.4.17

Comment by Manuel Arostegui [ 2021-01-20 ]

For the record, these are the explains (I am surprised I didn't add them before!):
10.4.15

root@pc2007.codfw.wmnet[parsercache]> explain DELETE  FROM `pc221` WHERE (exptime < '20201121180630') AND keyname IN ('wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47172141','ruwikinews:pcache:idoptions:2274652','plwiki:stable-pcache:idhash:2985160-0!canonical','commonswiki:pcache:idhash:28470463-0!wb=3','eswiki:pcache:idhash:8407454-0!canonical','wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47171328','cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0','wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3','zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs','commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0','cswiki:pcache:idhash:238465-0!canonical','arwiki:stable-pcache:idoptions:5577217','ruwikinews:pcache:idhash:2275122-0!canonical','wikidatawiki:pcache:idoptions:47170426','commonswiki:pcache:idhash:67472202-0!canonical','frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0','avwiki:pcache:idoptions:5377','svwiki:pcache:idhash:3766069-0!canonical','wikidatawiki:pcache:idoptions:47170137','wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47170029','avwiki:pcache:idhash:5154-0!canonical','itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0','azbwiki:pcache:idoptions:215428','wikidatawiki:pcache:idoptions:47169595','azbwiki:pcache:idhash:217308-0!canonical','commonswiki:pcache:idhash:76967381-1!userlang=sco','barwiki:pcache:idoptions:54173','commonswiki:pcache:idhash:63992420-0!wb=3','commonswiki:pcache:idoptions:36008242','commonswiki:pcache:idhash:14182321-0!wb=3','commonswiki:pcache:idhash:14182500-0!wb=3','dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3','svwiki:pcache:idoptions:6396670','commonswiki:pcache:idhash:2234685-1!userlang=fr','commonswiki:pcache:idoptions:13832128','ruwikinews:pcache:idoptions:2275409','commonswiki:pcache:idoptions:85397714','wikidatawiki:pcache:idoptions:45230319','ruwikinews:pcache:idoptions:2275772','wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3','bgwikiquote:pcache:idhash:5470-0!canonical','bgwikiquote:pcache:idoptions:5475','enwiki:pcache:idhash:30141328-0!canonical','wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3','eswiktionary:pcache:idhash:461899-0!canonical','enwiki:pcache:idhash:33991562-0!thumbsize=3','commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3','ruwikinews:pcache:idhash:2276526-0!canonical','commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3','bnwikisource:pcache:idhash:759895-0!canonical','commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0','bowiki:pcache:idhash:5208-0!canonical','cswiki:pcache:idhash:799292-0!canonical','eswiktionary:pcache:idoptions:462181','commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3','commonswiki:pcache:idhash:39411274-0!canonical','commonswiki:pcache:idoptions:39411310','wikidatawiki:pcache:idoptions:47138756','cswiki:pcache:idoptions:739306','idwiktionary:pcache:idoptions:44095','srwiki:pcache:idoptions:1563379','commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3','commonswiki:pcache:idhash:74084336-0!wb=3','wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3','bswiki:pcache:idoptions:195228','bpywiki:pcache:idoptions:45261','commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0','bswiktionary:pcache:idhash:4994-0!canonical','ruwikinews:pcache:idoptions:2277615','commonswiki:pcache:idhash:30387844-1!userlang=es','cawiki:pcache:idoptions:510277','cawiki:pcache:idoptions:542825','commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:3706602-1!userlang=ms','jawiki:pcache:idoptions:372412','wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3','cawiki:pcache:idoptions:539285','wikidatawiki:pcache:idoptions:47137123','commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0','zhwikisource:pcache:idhash:284908-0!canonical!zh','eswiktionary:pcache:idhash:463096-0!canonical','commonswiki:pcache:idhash:56623174-0!userlang=it','cawiki:pcache:idhash:539521-0!canonical','commonswiki:pcache:idhash:54662835-0!canonical','wikidatawiki:pcache:idoptions:47136275','wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3','commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0','eswiktionary:pcache:idhash:463367-0!canonical','eswiktionary:pcache:idhash:463413-0!canonical','ruwikinews:pcache:idoptions:2279056','hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0','ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:94865712-0!wb=3','ruwikinews:pcache:idhash:2280030-0!canonical','commonswiki:pcache:idhash:13651795-0!wb=3') /*!*/;
 
+------+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
 
| id   | select_type | table | type  | possible_keys   | key     | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
 
|    1 | SIMPLE      | pc221 | range | keyname,exptime | exptime | 9       | NULL | 1    | Using where |
 
+------+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
 
1 row in set (0.034 sec)

10.4.17

root@db1077.eqiad.wmnet[testing]> explain
 
    -> DELETE  FROM `pc221` WHERE (exptime < '20201121180630') AND keyname IN ('wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47172141','ruwikinews:pcache:idoptions:2274652','plwiki:stable-pcache:idhash:2985160-0!canonical','commonswiki:pcache:idhash:28470463-0!wb=3','eswiki:pcache:idhash:8407454-0!canonical','wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47171328','cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0','wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3','zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs','commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0','cswiki:pcache:idhash:238465-0!canonical','arwiki:stable-pcache:idoptions:5577217','ruwikinews:pcache:idhash:2275122-0!canonical','wikidatawiki:pcache:idoptions:47170426','commonswiki:pcache:idhash:67472202-0!canonical','frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0','avwiki:pcache:idoptions:5377','svwiki:pcache:idhash:3766069-0!canonical','wikidatawiki:pcache:idoptions:47170137','wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47170029','avwiki:pcache:idhash:5154-0!canonical','itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0','azbwiki:pcache:idoptions:215428','wikidatawiki:pcache:idoptions:47169595','azbwiki:pcache:idhash:217308-0!canonical','commonswiki:pcache:idhash:76967381-1!userlang=sco','barwiki:pcache:idoptions:54173','commonswiki:pcache:idhash:63992420-0!wb=3','commonswiki:pcache:idoptions:36008242','commonswiki:pcache:idhash:14182321-0!wb=3','commonswiki:pcache:idhash:14182500-0!wb=3','dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3','svwiki:pcache:idoptions:6396670','commonswiki:pcache:idhash:2234685-1!userlang=fr','commonswiki:pcache:idoptions:13832128','ruwikinews:pcache:idoptions:2275409','commonswiki:pcache:idoptions:85397714','wikidatawiki:pcache:idoptions:45230319','ruwikinews:pcache:idoptions:2275772','wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3','bgwikiquote:pcache:idhash:5470-0!canonical','bgwikiquote:pcache:idoptions:5475','enwiki:pcache:idhash:30141328-0!canonical','wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3','eswiktionary:pcache:idhash:461899-0!canonical','enwiki:pcache:idhash:33991562-0!thumbsize=3','commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3','ruwikinews:pcache:idhash:2276526-0!canonical','commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3','bnwikisource:pcache:idhash:759895-0!canonical','commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0','bowiki:pcache:idhash:5208-0!canonical','cswiki:pcache:idhash:799292-0!canonical','eswiktionary:pcache:idoptions:462181','commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3','commonswiki:pcache:idhash:39411274-0!canonical','commonswiki:pcache:idoptions:39411310','wikidatawiki:pcache:idoptions:47138756','cswiki:pcache:idoptions:739306','idwiktionary:pcache:idoptions:44095','srwiki:pcache:idoptions:1563379','commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3','commonswiki:pcache:idhash:74084336-0!wb=3','wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3','bswiki:pcache:idoptions:195228','bpywiki:pcache:idoptions:45261','commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0','bswiktionary:pcache:idhash:4994-0!canonical','ruwikinews:pcache:idoptions:2277615','commonswiki:pcache:idhash:30387844-1!userlang=es','cawiki:pcache:idoptions:510277','cawiki:pcache:idoptions:542825','commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:3706602-1!userlang=ms','jawiki:pcache:idoptions:372412','wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3','cawiki:pcache:idoptions:539285','wikidatawiki:pcache:idoptions:47137123','commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0','zhwikisource:pcache:idhash:284908-0!canonical!zh','eswiktionary:pcache:idhash:463096-0!canonical','commonswiki:pcache:idhash:56623174-0!userlang=it','cawiki:pcache:idhash:539521-0!canonical','commonswiki:pcache:idhash:54662835-0!canonical','wikidatawiki:pcache:idoptions:47136275','wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3','commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0','eswiktionary:pcache:idhash:463367-0!canonical','eswiktionary:pcache:idhash:463413-0!canonical','ruwikinews:pcache:idoptions:2279056','hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0','ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:94865712-0!wb=3','ruwikinews:pcache:idhash:2280030-0!canonical','commonswiki:pcache:idhash:13651795-0!wb=3')
 
    -> /*!*/;
 
+------+-------------+-------+-------+-----------------+---------+---------+------+-------+-------------+
 
| id   | select_type | table | type  | possible_keys   | key     | key_len | ref  | rows  | Extra       |
 
+------+-------------+-------+-------+-----------------+---------+---------+------+-------+-------------+
 
|    1 | SIMPLE      | pc221 | range | keyname,exptime | exptime | 6       | NULL | 30828 | Using where |
 
+------+-------------+-------+-------+-----------------+---------+---------+------+-------+-------------+
 
1 row in set (0.008 sec)

Comment by Manuel Arostegui [ 2021-01-20 ]

Done some testing on Percona Server:

+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
 
| id | select_type | table | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra       |
 
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
 
|  1 | DELETE      | pc221 | NULL       | range | keyname,exptime | keyname | 257     | const |  100 |   100.00 | Using where |
 
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
 
1 row in set, 1 warning (0.037 sec)

Run the delete and:

root@db2102.codfw.wmnet[testing]> SHOW STATUS like 'Hand%';
 
+----------------------------+-------+
 
| Variable_name              | Value |
 
+----------------------------+-------+
 
| Handler_commit             | 1     |
 
| Handler_delete             | 0     |
 
| Handler_discover           | 0     |
 
| Handler_external_lock      | 2     |
 
| Handler_mrr_init           | 0     |
 
| Handler_prepare            | 0     |
 
| Handler_read_first         | 0     |
 
| Handler_read_key           | 100   |
 
| Handler_read_last          | 0     |
 
| Handler_read_next          | 0     |
 
| Handler_read_prev          | 0     |
 
| Handler_read_rnd           | 0     |
 
| Handler_read_rnd_next      | 0     |
 
| Handler_rollback           | 0     |
 
| Handler_savepoint          | 0     |
 
| Handler_savepoint_rollback | 0     |
 
| Handler_update             | 0     |
 
| Handler_write              | 0     |
 
+----------------------------+-------+
 
18 rows in set (0.040 sec)

Then I did the same test on that same host but running 10.4.14:

+------+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
 
| id   | select_type | table | type  | possible_keys   | key     | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
 
|    1 | SIMPLE      | pc221 | range | keyname,exptime | keyname | 257     | NULL | 100  | Using where |
 
+------+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+

After running the query:

root@db2102.codfw.wmnet[testing]> SHOW STATUS like 'Hand%';
 
+----------------------------+-------+
 
| Variable_name              | Value |
 
+----------------------------+-------+
 
| Handler_commit             | 1     |
 
| Handler_delete             | 0     |
 
| Handler_discover           | 0     |
 
| Handler_external_lock      | 0     |
 
| Handler_icp_attempts       | 0     |
 
| Handler_icp_match          | 0     |
 
| Handler_mrr_init           | 0     |
 
| Handler_mrr_key_refills    | 0     |
 
| Handler_mrr_rowid_refills  | 0     |
 
| Handler_prepare            | 0     |
 
| Handler_read_first         | 0     |
 
| Handler_read_key           | 100   |
 
| Handler_read_last          | 0     |
 
| Handler_read_next          | 0     |
 
| Handler_read_prev          | 0     |
 
| Handler_read_retry         | 0     |
 
| Handler_read_rnd           | 0     |
 
| Handler_read_rnd_deleted   | 0     |
 
| Handler_read_rnd_next      | 0     |
 
| Handler_rollback           | 0     |
 
| Handler_savepoint          | 0     |
 
| Handler_savepoint_rollback | 0     |
 
| Handler_tmp_delete         | 0     |
 
| Handler_tmp_update         | 0     |
 
| Handler_tmp_write          | 0     |
 
| Handler_update             | 0     |
 
| Handler_write              | 0     |
 
+----------------------------+-------+
 
27 rows in set (0.035 sec)

That same host, with that same data gets upgraded to 10.4.17 and this is what we get right away:

+------+-------------+-------+-------+-----------------+---------+---------+------+-------+-------------+
 
| id   | select_type | table | type  | possible_keys   | key     | key_len | ref  | rows  | Extra       |
 
+------+-------------+-------+-------+-----------------+---------+---------+------+-------+-------------+
 
|    1 | SIMPLE      | pc221 | range | keyname,exptime | exptime | 6       | NULL | 30828 | Using where |
 
+------+-------------+-------+-------+-----------------+---------+---------+------+-------+-------------+
 
1 row in set (0.036 sec)

After running the DELETE:

root@db2102.codfw.wmnet[testing]> SHOW STATUS like 'Hand%';
 
+----------------------------+-------+
 
| Variable_name              | Value |
 
+----------------------------+-------+
 
| Handler_commit             | 1     |
 
| Handler_delete             | 0     |
 
| Handler_discover           | 0     |
 
| Handler_external_lock      | 0     |
 
| Handler_icp_attempts       | 0     |
 
| Handler_icp_match          | 0     |
 
| Handler_mrr_init           | 0     |
 
| Handler_mrr_key_refills    | 0     |
 
| Handler_mrr_rowid_refills  | 0     |
 
| Handler_prepare            | 0     |
 
| Handler_read_first         | 0     |
 
| Handler_read_key           | 1     |
 
| Handler_read_last          | 0     |
 
| Handler_read_next          | 15086 |
 
| Handler_read_prev          | 0     |
 
| Handler_read_retry         | 0     |
 
| Handler_read_rnd           | 0     |
 
| Handler_read_rnd_deleted   | 0     |
 
| Handler_read_rnd_next      | 0     |
 
| Handler_rollback           | 0     |
 
| Handler_savepoint          | 0     |
 
| Handler_savepoint_rollback | 0     |
 
| Handler_tmp_delete         | 0     |
 
| Handler_tmp_update         | 0     |
 
| Handler_tmp_write          | 0     |
 
| Handler_update             | 0     |
 
| Handler_write              | 0     |
 
+----------------------------+-------+

So this looks again very specific to 10.4.17, as 10.4.14 and Percona Server works well.
We are pretty worried about this bug as it is blocking us to keep upgrading within 10.4, is there anything else I can provide to help you identifying what the issue might be?
I am still working out internally a copy of the data for you, in order to advance things, once I have the green light, how should I proceed? It will be around 6GB.

Thanks

Comment by Sergei Petrunia [ 2021-01-22 ]

Ok, now back. I'm sorry for the delay.

Looking at the data provided in
https://jira.mariadb.org/browse/MDEV-24266?focusedCommentId=176471&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-176471

  • 10.4.14 uses range on keyname (makes 100 lookups)
  • 10.4.17 uses range exptime (scans 15K rows). which takes much more time.

In 10.4.17, range scan on keyname again has an odd (very high) cost value:

              "rows": 100,
 
              "cost": 65515,

In 10.4.14 the cost is 120.12 and it was 120.13 in 10.4.15.

As for index exptime
10.4.14:

            {
 
              "index": "exptime",
 
              "ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 102,
 
              "cost": 131.08,
 
              "chosen": false,
 
              "cause": "cost"
 
            }

rows=102, isn't this too low? We know the real number is 15K!

10.4.17:

            {
 
              "index": "exptime",
 
              "ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 28926,
 
              "cost": 36881,
 
              "chosen": true
 
            }

The number of rows is more realistic (but because of a huge over-estimate of the cost of keyname it didn't help)

But at least now the data makes sense: slow query DOES use a different query plan... which is caused by different cost numbers that the optimizer got from InnoDB.

Comment by Sergei Petrunia [ 2021-01-22 ]

https://jira.mariadb.org/browse/MDEV-24266?focusedCommentId=176586&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-176586

I just did the following test:

... Handler_read_ counters follow ...

Ok this agrees with the hypothesis that

  • 10.4.17 does a scan on exptime, which scans 15K and is slow
  • 10.4.14 and 10.4.15 do a scan on keyname, which is 100 lookups and is fast.

https://jira.mariadb.org/browse/MDEV-24266?focusedCommentId=177724&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-177724

For the record, these are the explains (I am surprised I didn't add them before!):
10.4.15

+------+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
 
| id   | select_type | table | type  | possible_keys   | key     | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
 
|    1 | SIMPLE      | pc221 | range | keyname,exptime | exptime | 9       | NULL | 1    | Using where |
 
+------+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+

Interesting. This shows 10.4.15 to use the slow query plan! And rows=1 (very wrong), which agrees with the optimizer trace from the original report:

Optimizer trace for 10.4.15: https://phabricator.wikimedia.org/P13364

Could it be that the query plan is unstable for some reason...
(note that #rows for non-equality range should not depend on index statistics collected by ANALYZE TABLE...)

Comment by Manuel Arostegui [ 2021-01-22 ]

Hey Sergei! Thanks for getting back to me!
The query plan could be unstable, but what is very strange is that it changes all of a sudden as soon as we upgrade from 10.4.14 or 10.4.15 to 10.4.17 on three different servers, isn't it?.
Has something changed between 10.4.15 and 10.4.17 that can cause this "consistent" issue with those DELETE with WHERE + IN?
(So far we have only seen this major change with that specific query, but as it is a bit uncertain, we have stopped the upgrades for now).
Is there anything else I can provide to help you identifying the culprit of this issue?.
We are ready to send a sanitize copy of one of the affected tables, to see if you can reproduce it yourself, would that help the investigation?.

Thanks again for your time!

Comment by Sergei Petrunia [ 2021-01-26 ]

The query plan could be unstable, but what is very strange is that it changes all of a sudden as soon as we upgrade from 10.4.14 or 10.4.15 to 10.4.17 on three different servers, isn't it?.

Yes, there is a clear dependency from the server version.

What I was getting to was as follows:

Looking from the data/traces we have so far , we can see that poor performance is TYPICALLY caused by the optimizer not picking range scan over primary key.

There are exceptions to this, but they could be explained by a background statistics update.

This looks like the same issue as in MDEV-24438 (as was guessed earlier here: https://jira.mariadb.org/browse/MDEV-24266?focusedCommentId=176265&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-176265 )

For 10.4 series, the problematic commit was introduced in 10.4.16:

commit afc9d00c66db946c8240fe1fa6b345a3a8b6fec1
 
Author: Eugene Kosov <claprix@yandex.ru>
 
Date:   Tue Oct 27 12:24:55 2020 +0300
 
 
 
    MDEV-23991 dict_table_stats_lock() has unnecessarily long scope
 
    
    Patch removes dict_index_t::stats_latch. Table/index statistics now
 
    protected with dict_sys->mutex. That way statistics computation can
 
    happen in parallel in several threads and dict_sys->mutex will be locked
 
    only for a short period of time.

and the fix is in this commit:

commit 5991bd6215054f21ec5c36fc9345ffb50f1b2d04
 
Author: Eugene Kosov <claprix@yandex.ru>
 
Date:   Wed Nov 25 16:01:38 2020 +0300
 
 
 
    MDEV-24275 InnoDB persistent stats analyze forces full scan forcing lock crash
 
    
    This is a fixup patch for MDEV-23991 afc9d00c66db946c8240fe1fa6b345a3a8b6fec1
 
    
    We really should read result.n_leaf_pages, which was set previously.

which is in the 10.4 tree but not in any commit, yet.

marostegui, can you try with MariaDB 10.4 built from the latest source (which includes the fix for MDEV-24438)?

You can build from github source yourself, or get the binaries from the CI system, e.g. http://hasky.askmonty.org/archive/10.4/build-37473/ (if that one is not available when you try, use the latest build from http://hasky.askmonty.org/archive/10.4).

(if the problem is still there, I would like to get a sanitized copy of the affected tables so I can investigate locally)

Comment by Manuel Arostegui [ 2021-01-26 ]

Thanks - I am going to compile 10.4.18. I will get back to you

Comment by Manuel Arostegui [ 2021-01-26 ]

Tried 10.4.18 and the issue is still there (verified that the version I compiled had the fix): https://github.com/MariaDB/server/commit/5991bd6215054f21ec5c36fc9345ffb50f1b2d04

Server version: 10.4.18-MariaDB-log MariaDB Server
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
root@pc2010.codfw.wmnet[parsercache]> FLUSH STATUS; pager cat > /dev/null;
 
Query OK, 0 rows affected (0.034 sec)
 
 
 
PAGER set to 'cat > /dev/null'
 
root@pc2010.codfw.wmnet[parsercache]> DELETE  FROM `pc221` WHERE (exptime < '20201121180630') AND keyname IN ('wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47172141','ruwikinews:pcache:idoptions:2274652','plwiki:stable-pcache:idhash:2985160-0!canonical','commonswiki:pcache:idhash:28470463-0!wb=3','eswiki:pcache:idhash:8407454-0!canonical','wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47171328','cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0','wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3','zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs','commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0','cswiki:pcache:idhash:238465-0!canonical','arwiki:stable-pcache:idoptions:5577217','ruwikinews:pcache:idhash:2275122-0!canonical','wikidatawiki:pcache:idoptions:47170426','commonswiki:pcache:idhash:67472202-0!canonical','frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0','avwiki:pcache:idoptions:5377','svwiki:pcache:idhash:3766069-0!canonical','wikidatawiki:pcache:idoptions:47170137','wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47170029','avwiki:pcache:idhash:5154-0!canonical','itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0','azbwiki:pcache:idoptions:215428','wikidatawiki:pcache:idoptions:47169595','azbwiki:pcache:idhash:217308-0!canonical','commonswiki:pcache:idhash:76967381-1!userlang=sco','barwiki:pcache:idoptions:54173','commonswiki:pcache:idhash:63992420-0!wb=3','commonswiki:pcache:idoptions:36008242','commonswiki:pcache:idhash:14182321-0!wb=3','commonswiki:pcache:idhash:14182500-0!wb=3','dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3','svwiki:pcache:idoptions:6396670','commonswiki:pcache:idhash:2234685-1!userlang=fr','commonswiki:pcache:idoptions:13832128','ruwikinews:pcache:idoptions:2275409','commonswiki:pcache:idoptions:85397714','wikidatawiki:pcache:idoptions:45230319','ruwikinews:pcache:idoptions:2275772','wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3','bgwikiquote:pcache:idhash:5470-0!canonical','bgwikiquote:pcache:idoptions:5475','enwiki:pcache:idhash:30141328-0!canonical','wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3','eswiktionary:pcache:idhash:461899-0!canonical','enwiki:pcache:idhash:33991562-0!thumbsize=3','commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3','ruwikinews:pcache:idhash:2276526-0!canonical','commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3','bnwikisource:pcache:idhash:759895-0!canonical','commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0','bowiki:pcache:idhash:5208-0!canonical','cswiki:pcache:idhash:799292-0!canonical','eswiktionary:pcache:idoptions:462181','commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3','commonswiki:pcache:idhash:39411274-0!canonical','commonswiki:pcache:idoptions:39411310','wikidatawiki:pcache:idoptions:47138756','cswiki:pcache:idoptions:739306','idwiktionary:pcache:idoptions:44095','srwiki:pcache:idoptions:1563379','commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3','commonswiki:pcache:idhash:74084336-0!wb=3','wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3','bswiki:pcache:idoptions:195228','bpywiki:pcache:idoptions:45261','commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0','bswiktionary:pcache:idhash:4994-0!canonical','ruwikinews:pcache:idoptions:2277615','commonswiki:pcache:idhash:30387844-1!userlang=es','cawiki:pcache:idoptions:510277','cawiki:pcache:idoptions:542825','commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:3706602-1!userlang=ms','jawiki:pcache:idoptions:372412','wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3','cawiki:pcache:idoptions:539285','wikidatawiki:pcache:idoptions:47137123','commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0','zhwikisource:pcache:idhash:284908-0!canonical!zh','eswiktionary:pcache:idhash:463096-0!canonical','commonswiki:pcache:idhash:56623174-0!userlang=it','cawiki:pcache:idhash:539521-0!canonical','commonswiki:pcache:idhash:54662835-0!canonical','wikidatawiki:pcache:idoptions:47136275','wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3','commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0','eswiktionary:pcache:idhash:463367-0!canonical','eswiktionary:pcache:idhash:463413-0!canonical','ruwikinews:pcache:idoptions:2279056','hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0','ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:94865712-0!wb=3','ruwikinews:pcache:idhash:2280030-0!canonical','commonswiki:pcache:idhash:13651795-0!wb=3') /*!*/;
 
Query OK, 0 rows affected (5.616 sec)
 
 
 
root@pc2010.codfw.wmnet[parsercache]> nopager; show status like 'Hand%';
 
PAGER set to stdout
 
+----------------------------+-------+
 
| Variable_name              | Value |
 
+----------------------------+-------+
 
| Handler_commit             | 2     |
 
| Handler_delete             | 0     |
 
| Handler_discover           | 0     |
 
| Handler_external_lock      | 0     |
 
| Handler_icp_attempts       | 0     |
 
| Handler_icp_match          | 0     |
 
| Handler_mrr_init           | 0     |
 
| Handler_mrr_key_refills    | 0     |
 
| Handler_mrr_rowid_refills  | 0     |
 
| Handler_prepare            | 0     |
 
| Handler_read_first         | 0     |
 
| Handler_read_key           | 8     |
 
| Handler_read_last          | 0     |
 
| Handler_read_next          | 15087 |
 
| Handler_read_prev          | 0     |
 
| Handler_read_retry         | 0     |
 
| Handler_read_rnd           | 0     |
 
| Handler_read_rnd_deleted   | 0     |
 
| Handler_read_rnd_next      | 0     |
 
| Handler_rollback           | 0     |
 
| Handler_savepoint          | 0     |
 
| Handler_savepoint_rollback | 0     |
 
| Handler_tmp_delete         | 0     |
 
| Handler_tmp_update         | 0     |
 
| Handler_tmp_write          | 0     |
 
| Handler_update             | 0     |
 
| Handler_write              | 0     |
 
+----------------------------+-------+
 
 
 
 
 
root@pc2010.codfw.wmnet[parsercache]> explain DELETE  FROM `pc221` WHERE (exptime < '20201121180630') AND keyname IN ('wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47172141','ruwikinews:pcache:idoptions:2274652','plwiki:stable-pcache:idhash:2985160-0!canonical','commonswiki:pcache:idhash:28470463-0!wb=3','eswiki:pcache:idhash:8407454-0!canonical','wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47171328','cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0','wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3','zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs','commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0','cswiki:pcache:idhash:238465-0!canonical','arwiki:stable-pcache:idoptions:5577217','ruwikinews:pcache:idhash:2275122-0!canonical','wikidatawiki:pcache:idoptions:47170426','commonswiki:pcache:idhash:67472202-0!canonical','frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0','avwiki:pcache:idoptions:5377','svwiki:pcache:idhash:3766069-0!canonical','wikidatawiki:pcache:idoptions:47170137','wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47170029','avwiki:pcache:idhash:5154-0!canonical','itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0','azbwiki:pcache:idoptions:215428','wikidatawiki:pcache:idoptions:47169595','azbwiki:pcache:idhash:217308-0!canonical','commonswiki:pcache:idhash:76967381-1!userlang=sco','barwiki:pcache:idoptions:54173','commonswiki:pcache:idhash:63992420-0!wb=3','commonswiki:pcache:idoptions:36008242','commonswiki:pcache:idhash:14182321-0!wb=3','commonswiki:pcache:idhash:14182500-0!wb=3','dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3','svwiki:pcache:idoptions:6396670','commonswiki:pcache:idhash:2234685-1!userlang=fr','commonswiki:pcache:idoptions:13832128','ruwikinews:pcache:idoptions:2275409','commonswiki:pcache:idoptions:85397714','wikidatawiki:pcache:idoptions:45230319','ruwikinews:pcache:idoptions:2275772','wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3','bgwikiquote:pcache:idhash:5470-0!canonical','bgwikiquote:pcache:idoptions:5475','enwiki:pcache:idhash:30141328-0!canonical','wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3','eswiktionary:pcache:idhash:461899-0!canonical','enwiki:pcache:idhash:33991562-0!thumbsize=3','commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3','ruwikinews:pcache:idhash:2276526-0!canonical','commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3','bnwikisource:pcache:idhash:759895-0!canonical','commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0','bowiki:pcache:idhash:5208-0!canonical','cswiki:pcache:idhash:799292-0!canonical','eswiktionary:pcache:idoptions:462181','commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3','commonswiki:pcache:idhash:39411274-0!canonical','commonswiki:pcache:idoptions:39411310','wikidatawiki:pcache:idoptions:47138756','cswiki:pcache:idoptions:739306','idwiktionary:pcache:idoptions:44095','srwiki:pcache:idoptions:1563379','commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3','commonswiki:pcache:idhash:74084336-0!wb=3','wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3','bswiki:pcache:idoptions:195228','bpywiki:pcache:idoptions:45261','commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0','bswiktionary:pcache:idhash:4994-0!canonical','ruwikinews:pcache:idoptions:2277615','commonswiki:pcache:idhash:30387844-1!userlang=es','cawiki:pcache:idoptions:510277','cawiki:pcache:idoptions:542825','commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:3706602-1!userlang=ms','jawiki:pcache:idoptions:372412','wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3','cawiki:pcache:idoptions:539285','wikidatawiki:pcache:idoptions:47137123','commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0','zhwikisource:pcache:idhash:284908-0!canonical!zh','eswiktionary:pcache:idhash:463096-0!canonical','commonswiki:pcache:idhash:56623174-0!userlang=it','cawiki:pcache:idhash:539521-0!canonical','commonswiki:pcache:idhash:54662835-0!canonical','wikidatawiki:pcache:idoptions:47136275','wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3','commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0','eswiktionary:pcache:idhash:463367-0!canonical','eswiktionary:pcache:idhash:463413-0!canonical','ruwikinews:pcache:idoptions:2279056','hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0','ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:94865712-0!wb=3','ruwikinews:pcache:idhash:2280030-0!canonical','commonswiki:pcache:idhash:13651795-0!wb=3') /*!*/;
 
+------+-------------+-------+-------+-----------------+---------+---------+------+-------+-------------+
 
| id   | select_type | table | type  | possible_keys   | key     | key_len | ref  | rows  | Extra       |
 
+------+-------------+-------+-------+-----------------+---------+---------+------+-------+-------------+
 
|    1 | SIMPLE      | pc221 | range | keyname,exptime | exptime | 6       | NULL | 33698 | Using where |
 
+------+-------------+-------+-------+-----------------+---------+---------+------+-------+-------------+
 
1 row in set (0.035 sec)

Sergei, how can I send the 6GB file with the table data so you can try it locally?

Comment by Sergei Petrunia [ 2021-01-26 ]

Oh.
marostegui, please find the FTP instructions here: https://mariadb.com/kb/en/meta/mariadb-ftp-server/ .
Please upload the file to the "private" folder and share the file name here, or over email.

Comment by Manuel Arostegui [ 2021-01-26 ]

I have sent the file. More details and md5 on your email

Comment by Manuel Arostegui [ 2021-01-29 ]

For what is worth, after a few days of running 10.4.18 on one of the hosts, it is performing better than the one doing 10.4.17 and the spikes on the 

 Handler_read_next

look gone. So it is performing as the the 10.4.14 or 10.4.15 on that regard.
I am going to upgrade one of the other slaves, from 10.4.15 to 10.4.18 and see how that one behaves.

Comment by Sergei Petrunia [ 2021-01-29 ]

Ok, I'm trying with the uploaded dataset. I take a particular server version, load the dataset into it, and run EXPLAIN.

  • Not reproducible on mariadb-10.4.15 ("good" explain)
  • Reproducible on mariadb-10.4.17 ("bad" explain)
  • Not reproducible on latest 10.4.18, "good" explain (tip cset: c2c23e598d49fc991b34853d864c77d02741b61c)

The Good explain is like so:

*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: pc221
 
         type: range
 
possible_keys: keyname,exptime
 
          key: keyname
 
      key_len: 257
 
          ref: NULL
 
         rows: 100
 
        Extra: Using where

The "bad" explain is like so:

*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: pc221
 
         type: range
 
possible_keys: keyname,exptime
 
          key: exptime
 
      key_len: 6
 
          ref: NULL
 
         rows: 30828
 
        Extra: Using where

marostegui, how did you try the query on the latest 10.4.18? Did you just start the binary on the data directory left from the previous version, or you re-loaded the data from the dump?

(As far as my experiment shows, one needs to either re-load the dataset or run "ANALYZE TABLE pc221" to see the effect)

Comment by Sergei Petrunia [ 2021-01-29 ]

Let me do another, "clean" run to confirm the above.

Comment by Manuel Arostegui [ 2021-01-29 ]

For the first 10.4.18 host I upgraded I didn't reload the dataset, and all of a sudden started to look fine on the graphs, which is very strange.
I have yet, upgraded another one earlier today (from 10.4.15 to 10.4.18) and it is showing the same query plan/handler counts, so no regression there.

If you want me to upgrade one more to 10.4.18 and reload the dataset and check the query plan, I can also do that.

Comment by Sergei Petrunia [ 2021-01-29 ]

Re-tried these steps:

  • start on an empty server
  • load the dataset
  • run the explain

on clean 10.4.17 - shows "BAD" explain
on 10.4.17 patched with 5991bd6215054f21ec5c36fc9345ffb50f1b2d04 - shows "Good" explain.
This confirms the above:

  • The issue should be already fixed in current 10.4.
  • If one is upgrading from a version later than 10.4.15, they need to run ANALYZE TABLE or re-load the data to get the good query plan.
Comment by Sergei Petrunia [ 2021-01-29 ]

For what is worth, after a few days of running 10.4.18 on one of the hosts, it is performing better than the one doing 10.4.17

Oh, missed this comment. Good!

Comment by Sergei Petrunia [ 2021-01-29 ]

Closing as Duplicate of MDEV-24438 . Feel free to reopen if something is not resolved.

Comment by Manuel Arostegui [ 2021-01-29 ]
If one is upgrading from a version later than 10.4.15, they need to run ANALYZE TABLE or re-load the data to get the good query plan.

Going to try this, as I still have one 10.4.17 showing the bad query plans. Going to upgrade to 10.4.18 + ANALYZE all its table and will report back
If this works, that's a good approach as we thankfully don't have many more hosts running 10.4.17 since we stopped after noticing this regression.

Comment by Manuel Arostegui [ 2021-01-30 ]

Confirmed the ANALYZE workaround works when upgrading from 10.4.17 -> 10.4.18.
Thank you for taking the time and effort to investigating this bug - much appreciated.

Generated at Thu Feb 08 09:28:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.