Page MenuHomePhabricator
Create Task
Maniphest T268457

Investigate possible optimizer regression on 10.4.17 with DELETE statements
Closed, ResolvedPublic
Actions

Assigned To
Marostegui
Authored By
Marostegui
Nov 23 2020, 11:59 AM
Tags
Referenced Files
F33922938: Captura de pantalla 2020-11-23 a las 12.24.26.png
Nov 23 2020, 11:59 AM
F33922944: Captura de pantalla 2020-11-23 a las 12.26.06.png
Nov 23 2020, 11:59 AM
F33922942: Captura de pantalla 2020-11-23 a las 12.25.47.png
Nov 23 2020, 11:59 AM
F33922946: Captura de pantalla 2020-11-23 a las 12.26.46.png
Nov 23 2020, 11:59 AM
F33922940: Captura de pantalla 2020-11-23 a las 12.25.00.png
Nov 23 2020, 11:59 AM
Subscribers
Aklapper
LSobanski
Marostegui

Description

We upgraded pc2010 and db1077 to 10.4.17. They both serve on pc1.
After that we noticed strange patterns on their graphs compared to 10.4.15 hosts:

pc2010 (10.4.17)

Captura de pantalla 2020-11-23 a las 12.24.26.png (788×1 px, 153 KB)

Captura de pantalla 2020-11-23 a las 12.25.47.png (792×1 px, 153 KB)

pc2007 (10.4.15)
Captura de pantalla 2020-11-23 a las 12.25.00.png (788×1 px, 127 KB)

Captura de pantalla 2020-11-23 a las 12.26.06.png (789×1 px, 243 KB)

Those spikes on handlers seem to translate on replication lag on pc2010 (which don't happen on pc2007).:

Captura de pantalla 2020-11-23 a las 12.26.46.png (790×1 px, 209 KB)

I did a comparison of the running variables and there's nothing there that is relevant:

P13361 pc2007 vs pc2010 global variables
1root@cumin1001:/home/marostegui# diff -u pc2007 pc2010
2--- pc2007 2020-11-22 07:41:41.663281632 +0000
3+++ pc2010 2020-11-22 07:41:35.291277185 +0000
4@@ -101,16 +101,16 @@
5 ft_query_expansion_limit 20
6 ft_stopword_file (built-in)
7 general_log OFF
8-general_log_file pc2007.log
9+general_log_file pc2010.log
10 group_concat_max_len 1048576
11-gtid_binlog_pos 0-171966644-49693925073
12-gtid_binlog_state 0-180355176-49693924645,0-171966476-27213441963,0-171978926-27444003403,0-171966644-49693925073
13+gtid_binlog_pos 0-171966644-49693921815
14+gtid_binlog_state 0-180355176-49621941336,0-171966476-27213442014,0-180367374-49693921686,0-171978926-49122531009,0-171966644-49693921816
15 gtid_cleanup_batch_size 64
16-gtid_current_pos 0-171966644-49693925073
17+gtid_current_pos 0-171966644-49693921815
18 gtid_domain_id 0
19 gtid_ignore_duplicates OFF
20 gtid_pos_auto_engines
21-gtid_slave_pos 0-171966644-49693925073
22+gtid_slave_pos 0-171966644-49693921816
23 gtid_strict_mode OFF
24 have_compress YES
25 have_crypt YES
26@@ -125,7 +125,7 @@
27 histogram_size 254
28 histogram_type DOUBLE_PREC_HB
29 host_cache_size 853
30-hostname pc2007
31+hostname pc2010
32 idle_readonly_transaction_timeout 0
33 idle_transaction_timeout 0
34 idle_write_transaction_timeout 0
35@@ -237,6 +237,7 @@
36 innodb_max_dirty_pages_pct_lwm 0.000000
37 innodb_max_purge_lag 0
38 innodb_max_purge_lag_delay 0
39+innodb_max_purge_lag_wait 0
40 innodb_max_undo_log_size 10485760
41 innodb_monitor_disable
42 innodb_monitor_enable
43@@ -291,7 +292,7 @@
44 innodb_undo_tablespaces 0
45 innodb_use_atomic_writes ON
46 innodb_use_native_aio ON
47-innodb_version 10.4.15
48+innodb_version 10.4.17
49 innodb_write_io_threads 4
50 interactive_timeout 28800
51 join_buffer_size 262144
52@@ -315,10 +316,10 @@
53 lock_wait_timeout 86400
54 locked_in_memory OFF
55 log_bin ON
56-log_bin_basename /srv/sqldata-cache/pc2007-bin
57+log_bin_basename /srv/sqldata-cache/pc2010-bin
58 log_bin_compress OFF
59 log_bin_compress_min_len 256
60-log_bin_index /srv/sqldata-cache/pc2007-bin.index
61+log_bin_index /srv/sqldata-cache/pc2010-bin.index
62 log_bin_trust_function_creators OFF
63 log_disabled_statements sp
64 log_error
65@@ -428,7 +429,7 @@
66 performance_schema_setup_actors_size 100
67 performance_schema_setup_objects_size 100
68 performance_schema_users_size 100
69-pid_file /srv/sqldata-cache/pc2007.pid
70+pid_file /srv/sqldata-cache/pc2010.pid
71 plugin_dir /opt/wmf-mariadb104/lib/plugin/
72 plugin_maturity gamma
73 port 3306
74@@ -466,7 +467,7 @@
75 replicate_ignore_table
76 replicate_wild_do_table
77 replicate_wild_ignore_table
78-report_host pc2007.codfw.wmnet
79+report_host pc2010.codfw.wmnet
80 report_password
81 report_port 3306
82 report_user
83@@ -483,7 +484,7 @@
84 secure_auth ON
85 secure_file_priv /dev/null/
86 secure_timestamp NO
87-server_id 180355176
88+server_id 180367374
89 session_track_schema ON
90 session_track_state_change OFF
91 session_track_system_variables autocommit,character_set_client,character_set_connection,character_set_results,time_zone
92@@ -512,7 +513,7 @@
93 slave_type_conversions
94 slow_launch_time 2
95 slow_query_log OFF
96-slow_query_log_file pc2007-slow.log
97+slow_query_log_file pc2010-slow.log
98 socket /run/mysqld/mysqld.sock
99 sort_buffer_size 2097152
100 sql_auto_is_null OFF
101@@ -580,12 +581,12 @@
102 updatable_views_with_limit YES
103 use_stat_tables PREFERABLY
104 userstat OFF
105-version 10.4.15-MariaDB-log
106+version 10.4.17-MariaDB-log
107 version_comment MariaDB Server
108 version_compile_machine x86_64
109 version_compile_os Linux
110 version_malloc_library system
111-version_source_revision a6e451dc7272c408ace8b3fb4d86c79263f7247e
112+version_source_revision 5fbfdae130950d0a5a07d4b909f3bf1ff0498d34
113 version_ssl_library OpenSSL 1.1.1d 10 Sep 2019
114 wait_timeout 3600
115 wsrep_osu_method TOI
116@@ -614,7 +615,7 @@
117 wsrep_mysql_replication_bundle 0
118 wsrep_node_address
119 wsrep_node_incoming_address AUTO
120-wsrep_node_name pc2007
121+wsrep_node_name pc2010
122 wsrep_notify_cmd
123 wsrep_on OFF
124 wsrep_patch_version wsrep_26.22

There is +innodb_max_purge_lag_wait which was introduced on 10.4.16, but I asked Marko from MariaDB and doesn't seem relevant (and I have played with and indeed had no effect: https://jira.mariadb.org/browse/MDEV-16952).

Considering the fact that these two hosts do not have reads, as they are on codfw,
These hosts only receive REPLACE and some DELETE statements.

REPLACE seem to behave the same way on both versions.
However, the DELETE seem to be having different scanning patterns, which matches the spikes on the graphs.

pc2010 (10.4.17)

P13362 pc2010 DELETE
1mysql: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');
2Query OK, 0 rows affected (0.322 sec)
3
4mysql:root@localhost [parsercache]> nopager;
5PAGER set to stdout
6mysql:root@localhost [parsercache]> SHOW STATUS like 'Hand%';
7+----------------------------+-------+
8| Variable_name | Value |
9+----------------------------+-------+
10| Handler_commit | 1 |
11| Handler_delete | 0 |
12| Handler_discover | 0 |
13| Handler_external_lock | 0 |
14| Handler_icp_attempts | 0 |
15| Handler_icp_match | 0 |
16| Handler_mrr_init | 0 |
17| Handler_mrr_key_refills | 0 |
18| Handler_mrr_rowid_refills | 0 |
19| Handler_prepare | 0 |
20| Handler_read_first | 0 |
21| Handler_read_key | 1 |
22| Handler_read_last | 0 |
23| Handler_read_next | 15218 |
24| Handler_read_prev | 0 |
25| Handler_read_retry | 0 |
26| Handler_read_rnd | 0 |
27| Handler_read_rnd_deleted | 0 |
28| Handler_read_rnd_next | 0 |
29| Handler_rollback | 0 |
30| Handler_savepoint | 0 |
31| Handler_savepoint_rollback | 0 |
32| Handler_tmp_delete | 0 |
33| Handler_tmp_update | 0 |
34| Handler_tmp_write | 0 |
35| Handler_update | 0 |
36| Handler_write | 0 |
37+----------------------------+-------+
3827 rows in set (0.001 sec)

pc2007 (10.4.15)

P13363 pc2007 DELETE
1mysql: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');
2Query OK, 0 rows affected (0.005 sec)
3
4mysql:root@localhost [parsercache]> nopager;SHOW STATUS like 'Hand%';
5PAGER set to stdout
6+----------------------------+-------+
7| Variable_name | Value |
8+----------------------------+-------+
9| Handler_commit | 1 |
10| Handler_delete | 0 |
11| Handler_discover | 0 |
12| Handler_external_lock | 0 |
13| Handler_icp_attempts | 0 |
14| Handler_icp_match | 0 |
15| Handler_mrr_init | 0 |
16| Handler_mrr_key_refills | 0 |
17| Handler_mrr_rowid_refills | 0 |
18| Handler_prepare | 0 |
19| Handler_read_first | 0 |
20| Handler_read_key | 1 |
21| Handler_read_last | 0 |
22| Handler_read_next | 0 |
23| Handler_read_prev | 0 |
24| Handler_read_retry | 0 |
25| Handler_read_rnd | 0 |
26| Handler_read_rnd_deleted | 0 |
27| Handler_read_rnd_next | 0 |
28| Handler_rollback | 0 |
29| Handler_savepoint | 0 |
30| Handler_savepoint_rollback | 0 |
31| Handler_tmp_delete | 0 |
32| Handler_tmp_update | 0 |
33| Handler_tmp_write | 0 |
34| Handler_update | 0 |
35| Handler_write | 0 |
36+----------------------------+-------+
3727 rows in set (0.001 sec)

There's a big difference between 10.4.17 and 10.4.15 there:
| Handler_read_next | 15218 | vs | Handler_read_next | 0 |

The optimizer also shows differences:
pc2007

P13364 pc2007 optimizer
1
2mysql:root@localhost [parsercache]> select * from information_schema.optimizer_trace limit 1\G
3*************************** 1. row ***************************
4 QUERY: 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')
5/*!*/
6 TRACE: {
7 "steps": [
8 {
9 "table": "pc221",
10 "range_analysis": {
11 "table_scan": {
12 "rows": 533021,
13 "cost": 810035
14 },
15 "potential_range_indexes": [
16 {
17 "index": "keyname",
18 "usable": true,
19 "key_parts": ["keyname"]
20 },
21 {
22 "index": "exptime",
23 "usable": true,
24 "key_parts": ["exptime", "keyname"]
25 }
26 ],
27 "setup_range_conditions": [],
28 "group_index_range": {
29 "chosen": false,
30 "cause": "no join"
31 },
32 "analyzing_range_alternatives": {
33 "range_scan_alternatives": [
34 {
35 "index": "keyname",
36 "ranges": [
37 "(arwiki:stable-pcache:idoptions:5577217) <= (keyname) <= (arwiki:stable-pcache:idoptions:5577217)",
38 "(avwiki:pcache:idhash:5154-0!canonical) <= (keyname) <= (avwiki:pcache:idhash:5154-0!canonical)",
39 "(avwiki:pcache:idoptions:5377) <= (keyname) <= (avwiki:pcache:idoptions:5377)",
40 "(azbwiki:pcache:idhash:217308-0!canonical) <= (keyname) <= (azbwiki:pcache:idhash:217308-0!canonical)",
41 "(azbwiki:pcache:idoptions:215428) <= (keyname) <= (azbwiki:pcache:idoptions:215428)",
42 "(barwiki:pcache:idoptions:54173) <= (keyname) <= (barwiki:pcache:idoptions:54173)",
43 "(bgwikiquote:pcache:idhash:5470-0!canonical) <= (keyname) <= (bgwikiquote:pcache:idhash:5470-0!canonical)",
44 "(bgwikiquote:pcache:idoptions:5475) <= (keyname) <= (bgwikiquote:pcache:idoptions:5475)",
45 "(bnwikisource:pcache:idhash:759895-0!canonical) <= (keyname) <= (bnwikisource:pcache:idhash:759895-0!canonical)",
46 "(bowiki:pcache:idhash:5208-0!canonical) <= (keyname) <= (bowiki:pcache:idhash:5208-0!canonical)",
47 "(bpywiki:pcache:idoptions:45261) <= (keyname) <= (bpywiki:pcache:idoptions:45261)",
48 "(bswiki:pcache:idoptions:195228) <= (keyname) <= (bswiki:pcache:idoptions:195228)",
49 "(bswiktionary:pcache:idhash:4994-0!canonical) <= (keyname) <= (bswiktionary:pcache:idhash:4994-0!canonical)",
50 "(cawiki:pcache:idhash:539521-0!canonical) <= (keyname) <= (cawiki:pcache:idhash:539521-0!canonical)",
51 "(cawiki:pcache:idoptions:510277) <= (keyname) <= (cawiki:pcache:idoptions:510277)",
52 "(cawiki:pcache:idoptions:539285) <= (keyname) <= (cawiki:pcache:idoptions:539285)",
53 "(cawiki:pcache:idoptions:542825) <= (keyname) <= (cawiki:pcache:idoptions:542825)",
54 "(cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0) <= (keyname) <= (cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0)",
55 "(commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0)",
56 "(commonswiki:pcache:idhash:13651795-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:13651795-0!wb=3)",
57 "(commonswiki:pcache:idhash:14182321-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:14182321-0!wb=3)",
58 "(commonswiki:pcache:idhash:14182500-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:14182500-0!wb=3)",
59 "(commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0)",
60 "(commonswiki:pcache:idhash:2234685-1!userlang=fr) <= (keyname) <= (commonswiki:pcache:idhash:2234685-1!userlang=fr)",
61 "(commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0)",
62 "(commonswiki:pcache:idhash:28470463-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:28470463-0!wb=3)",
63 "(commonswiki:pcache:idhash:30387844-1!userlang=es) <= (keyname) <= (commonswiki:pcache:idhash:30387844-1!userlang=es)",
64 "(commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0)",
65 "(commonswiki:pcache:idhash:3706602-1!userlang=ms) <= (keyname) <= (commonswiki:pcache:idhash:3706602-1!userlang=ms)",
66 "(commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0)",
67 "(commonswiki:pcache:idhash:39411274-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:39411274-0!canonical)",
68 "(commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3)",
69 "(commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3)",
70 "(commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3)",
71 "(commonswiki:pcache:idhash:54662835-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:54662835-0!canonical)",
72 "(commonswiki:pcache:idhash:56623174-0!userlang=it) <= (keyname) <= (commonswiki:pcache:idhash:56623174-0!userlang=it)",
73 "(commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3)",
74 "(commonswiki:pcache:idhash:63992420-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:63992420-0!wb=3)",
75 "(commonswiki:pcache:idhash:67472202-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:67472202-0!canonical)",
76 "(commonswiki:pcache:idhash:74084336-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:74084336-0!wb=3)",
77 "(commonswiki:pcache:idhash:76967381-1!userlang=sco) <= (keyname) <= (commonswiki:pcache:idhash:76967381-1!userlang=sco)",
78 "(commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3)",
79 "(commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0)",
80 "(commonswiki:pcache:idhash:94865712-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:94865712-0!wb=3)",
81 "(commonswiki:pcache:idoptions:13832128) <= (keyname) <= (commonswiki:pcache:idoptions:13832128)",
82 "(commonswiki:pcache:idoptions:36008242) <= (keyname) <= (commonswiki:pcache:idoptions:36008242)",
83 "(commonswiki:pcache:idoptions:39411310) <= (keyname) <= (commonswiki:pcache:idoptions:39411310)",
84 "(commonswiki:pcache:idoptions:85397714) <= (keyname) <= (commonswiki:pcache:idoptions:85397714)",
85 "(cswiki:pcache:idhash:238465-0!canonical) <= (keyname) <= (cswiki:pcache:idhash:238465-0!canonical)",
86 "(cswiki:pcache:idhash:799292-0!canonical) <= (keyname) <= (cswiki:pcache:idhash:799292-0!canonical)",
87 "(cswiki:pcache:idoptions:739306) <= (keyname) <= (cswiki:pcache:idoptions:739306)",
88 "(dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0) <= (keyname) <= (dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0)",
89 "(enwiki:pcache:idhash:30141328-0!canonical) <= (keyname) <= (enwiki:pcache:idhash:30141328-0!canonical)",
90 "(enwiki:pcache:idhash:33991562-0!thumbsize=3) <= (keyname) <= (enwiki:pcache:idhash:33991562-0!thumbsize=3)",
91 "(eswiki:pcache:idhash:8407454-0!canonical) <= (keyname) <= (eswiki:pcache:idhash:8407454-0!canonical)",
92 "(eswiktionary:pcache:idhash:461899-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:461899-0!canonical)",
93 "(eswiktionary:pcache:idhash:463096-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463096-0!canonical)",
94 "(eswiktionary:pcache:idhash:463367-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463367-0!canonical)",
95 "(eswiktionary:pcache:idhash:463413-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463413-0!canonical)",
96 "(eswiktionary:pcache:idoptions:462181) <= (keyname) <= (eswiktionary:pcache:idoptions:462181)",
97 "(frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0) <= (keyname) <= (frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0)",
98 "(hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0) <= (keyname) <= (hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0)",
99 "(idwiktionary:pcache:idoptions:44095) <= (keyname) <= (idwiktionary:pcache:idoptions:44095)",
100 "(itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0) <= (keyname) <= (itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0)",
101 "(jawiki:pcache:idoptions:372412) <= (keyname) <= (jawiki:pcache:idoptions:372412)",
102 "(plwiki:stable-pcache:idhash:2985160-0!canonical) <= (keyname) <= (plwiki:stable-pcache:idhash:2985160-0!canonical)",
103 "(ruwikinews:pcache:idhash:2275122-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2275122-0!canonical)",
104 "(ruwikinews:pcache:idhash:2276526-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2276526-0!canonical)",
105 "(ruwikinews:pcache:idhash:2280030-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2280030-0!canonical)",
106 "(ruwikinews:pcache:idoptions:2274652) <= (keyname) <= (ruwikinews:pcache:idoptions:2274652)",
107 "(ruwikinews:pcache:idoptions:2275409) <= (keyname) <= (ruwikinews:pcache:idoptions:2275409)",
108 "(ruwikinews:pcache:idoptions:2275772) <= (keyname) <= (ruwikinews:pcache:idoptions:2275772)",
109 "(ruwikinews:pcache:idoptions:2277615) <= (keyname) <= (ruwikinews:pcache:idoptions:2277615)",
110 "(ruwikinews:pcache:idoptions:2279056) <= (keyname) <= (ruwikinews:pcache:idoptions:2279056)",
111 "(srwiki:pcache:idoptions:1563379) <= (keyname) <= (srwiki:pcache:idoptions:1563379)",
112 "(svwiki:pcache:idhash:3766069-0!canonical) <= (keyname) <= (svwiki:pcache:idhash:3766069-0!canonical)",
113 "(svwiki:pcache:idoptions:6396670) <= (keyname) <= (svwiki:pcache:idoptions:6396670)",
114 "(ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0) <= (keyname) <= (ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0)",
115 "(wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3)",
116 "(wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3)",
117 "(wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3)",
118 "(wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3)",
119 "(wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3)",
120 "(wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3)",
121 "(wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3)",
122 "(wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3)",
123 "(wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3)",
124 "(wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3)",
125 "(wikidatawiki:pcache:idoptions:45230319) <= (keyname) <= (wikidatawiki:pcache:idoptions:45230319)",
126 "(wikidatawiki:pcache:idoptions:47136275) <= (keyname) <= (wikidatawiki:pcache:idoptions:47136275)",
127 "(wikidatawiki:pcache:idoptions:47137123) <= (keyname) <= (wikidatawiki:pcache:idoptions:47137123)",
128 "(wikidatawiki:pcache:idoptions:47138756) <= (keyname) <= (wikidatawiki:pcache:idoptions:47138756)",
129 "(wikidatawiki:pcache:idoptions:47169595) <= (keyname) <= (wikidatawiki:pcache:idoptions:47169595)",
130 "(wikidatawiki:pcache:idoptions:47170029) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170029)",
131 "(wikidatawiki:pcache:idoptions:47170137) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170137)",
132 "(wikidatawiki:pcache:idoptions:47170426) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170426)",
133 "(wikidatawiki:pcache:idoptions:47171328) <= (keyname) <= (wikidatawiki:pcache:idoptions:47171328)",
134 "(wikidatawiki:pcache:idoptions:47172141) <= (keyname) <= (wikidatawiki:pcache:idoptions:47172141)",
135 "(zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs) <= (keyname) <= (zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs)",
136 "(zhwikisource:pcache:idhash:284908-0!canonical!zh) <= (keyname) <= (zhwikisource:pcache:idhash:284908-0!canonical!zh)"
137 ],
138 "rowid_ordered": true,
139 "using_mrr": false,
140 "index_only": false,
141 "rows": 100,
142 "cost": 120.13,
143 "chosen": true
144 },
145 {
146 "index": "exptime",
147 "ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"],
148 "rowid_ordered": false,
149 "using_mrr": false,
150 "index_only": false,
151 "rows": 1,
152 "cost": 2.5033,
153 "chosen": true
154 }
155 ],
156 "analyzing_index_merge_union": []
157 },
158 "chosen_range_access_summary": {
159 "range_access_plan": {
160 "type": "range_scan",
161 "index": "exptime",
162 "rows": 1,
163 "ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"]
164 },
165 "rows_for_plan": 1,
166 "cost_for_plan": 2.5033,
167 "chosen": true
168 }
169 }
170 }
171 ]
172}
173MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
174 INSUFFICIENT_PRIVILEGES: 0
1751 row in set (0.001 sec)

pc2010

P13365 pc2010 optimizer
1mysql:root@localhost [parsercache]> select * from information_schema.optimizer_trace limit 1\G
2*************************** 1. row ***************************
3 QUERY: 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')
4/*!*/
5 TRACE: {
6 "steps": [
7 {
8 "table": "pc221",
9 "range_analysis": {
10 "table_scan": {
11 "rows": 1141750,
12 "cost": 942468
13 },
14 "potential_range_indexes": [
15 {
16 "index": "keyname",
17 "usable": true,
18 "key_parts": ["keyname"]
19 },
20 {
21 "index": "exptime",
22 "usable": true,
23 "key_parts": ["exptime", "keyname"]
24 }
25 ],
26 "setup_range_conditions": [],
27 "group_index_range": {
28 "chosen": false,
29 "cause": "no join"
30 },
31 "analyzing_range_alternatives": {
32 "range_scan_alternatives": [
33 {
34 "index": "keyname",
35 "ranges": [
36 "(arwiki:stable-pcache:idoptions:5577217) <= (keyname) <= (arwiki:stable-pcache:idoptions:5577217)",
37 "(avwiki:pcache:idhash:5154-0!canonical) <= (keyname) <= (avwiki:pcache:idhash:5154-0!canonical)",
38 "(avwiki:pcache:idoptions:5377) <= (keyname) <= (avwiki:pcache:idoptions:5377)",
39 "(azbwiki:pcache:idhash:217308-0!canonical) <= (keyname) <= (azbwiki:pcache:idhash:217308-0!canonical)",
40 "(azbwiki:pcache:idoptions:215428) <= (keyname) <= (azbwiki:pcache:idoptions:215428)",
41 "(barwiki:pcache:idoptions:54173) <= (keyname) <= (barwiki:pcache:idoptions:54173)",
42 "(bgwikiquote:pcache:idhash:5470-0!canonical) <= (keyname) <= (bgwikiquote:pcache:idhash:5470-0!canonical)",
43 "(bgwikiquote:pcache:idoptions:5475) <= (keyname) <= (bgwikiquote:pcache:idoptions:5475)",
44 "(bnwikisource:pcache:idhash:759895-0!canonical) <= (keyname) <= (bnwikisource:pcache:idhash:759895-0!canonical)",
45 "(bowiki:pcache:idhash:5208-0!canonical) <= (keyname) <= (bowiki:pcache:idhash:5208-0!canonical)",
46 "(bpywiki:pcache:idoptions:45261) <= (keyname) <= (bpywiki:pcache:idoptions:45261)",
47 "(bswiki:pcache:idoptions:195228) <= (keyname) <= (bswiki:pcache:idoptions:195228)",
48 "(bswiktionary:pcache:idhash:4994-0!canonical) <= (keyname) <= (bswiktionary:pcache:idhash:4994-0!canonical)",
49 "(cawiki:pcache:idhash:539521-0!canonical) <= (keyname) <= (cawiki:pcache:idhash:539521-0!canonical)",
50 "(cawiki:pcache:idoptions:510277) <= (keyname) <= (cawiki:pcache:idoptions:510277)",
51 "(cawiki:pcache:idoptions:539285) <= (keyname) <= (cawiki:pcache:idoptions:539285)",
52 "(cawiki:pcache:idoptions:542825) <= (keyname) <= (cawiki:pcache:idoptions:542825)",
53 "(cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0) <= (keyname) <= (cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0)",
54 "(commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0)",
55 "(commonswiki:pcache:idhash:13651795-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:13651795-0!wb=3)",
56 "(commonswiki:pcache:idhash:14182321-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:14182321-0!wb=3)",
57 "(commonswiki:pcache:idhash:14182500-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:14182500-0!wb=3)",
58 "(commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0)",
59 "(commonswiki:pcache:idhash:2234685-1!userlang=fr) <= (keyname) <= (commonswiki:pcache:idhash:2234685-1!userlang=fr)",
60 "(commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0)",
61 "(commonswiki:pcache:idhash:28470463-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:28470463-0!wb=3)",
62 "(commonswiki:pcache:idhash:30387844-1!userlang=es) <= (keyname) <= (commonswiki:pcache:idhash:30387844-1!userlang=es)",
63 "(commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0)",
64 "(commonswiki:pcache:idhash:3706602-1!userlang=ms) <= (keyname) <= (commonswiki:pcache:idhash:3706602-1!userlang=ms)",
65 "(commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0)",
66 "(commonswiki:pcache:idhash:39411274-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:39411274-0!canonical)",
67 "(commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3)",
68 "(commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3)",
69 "(commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3)",
70 "(commonswiki:pcache:idhash:54662835-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:54662835-0!canonical)",
71 "(commonswiki:pcache:idhash:56623174-0!userlang=it) <= (keyname) <= (commonswiki:pcache:idhash:56623174-0!userlang=it)",
72 "(commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3)",
73 "(commonswiki:pcache:idhash:63992420-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:63992420-0!wb=3)",
74 "(commonswiki:pcache:idhash:67472202-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:67472202-0!canonical)",
75 "(commonswiki:pcache:idhash:74084336-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:74084336-0!wb=3)",
76 "(commonswiki:pcache:idhash:76967381-1!userlang=sco) <= (keyname) <= (commonswiki:pcache:idhash:76967381-1!userlang=sco)",
77 "(commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3)",
78 "(commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0)",
79 "(commonswiki:pcache:idhash:94865712-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:94865712-0!wb=3)",
80 "(commonswiki:pcache:idoptions:13832128) <= (keyname) <= (commonswiki:pcache:idoptions:13832128)",
81 "(commonswiki:pcache:idoptions:36008242) <= (keyname) <= (commonswiki:pcache:idoptions:36008242)",
82 "(commonswiki:pcache:idoptions:39411310) <= (keyname) <= (commonswiki:pcache:idoptions:39411310)",
83 "(commonswiki:pcache:idoptions:85397714) <= (keyname) <= (commonswiki:pcache:idoptions:85397714)",
84 "(cswiki:pcache:idhash:238465-0!canonical) <= (keyname) <= (cswiki:pcache:idhash:238465-0!canonical)",
85 "(cswiki:pcache:idhash:799292-0!canonical) <= (keyname) <= (cswiki:pcache:idhash:799292-0!canonical)",
86 "(cswiki:pcache:idoptions:739306) <= (keyname) <= (cswiki:pcache:idoptions:739306)",
87 "(dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0) <= (keyname) <= (dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0)",
88 "(enwiki:pcache:idhash:30141328-0!canonical) <= (keyname) <= (enwiki:pcache:idhash:30141328-0!canonical)",
89 "(enwiki:pcache:idhash:33991562-0!thumbsize=3) <= (keyname) <= (enwiki:pcache:idhash:33991562-0!thumbsize=3)",
90 "(eswiki:pcache:idhash:8407454-0!canonical) <= (keyname) <= (eswiki:pcache:idhash:8407454-0!canonical)",
91 "(eswiktionary:pcache:idhash:461899-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:461899-0!canonical)",
92 "(eswiktionary:pcache:idhash:463096-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463096-0!canonical)",
93 "(eswiktionary:pcache:idhash:463367-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463367-0!canonical)",
94 "(eswiktionary:pcache:idhash:463413-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463413-0!canonical)",
95 "(eswiktionary:pcache:idoptions:462181) <= (keyname) <= (eswiktionary:pcache:idoptions:462181)",
96 "(frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0) <= (keyname) <= (frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0)",
97 "(hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0) <= (keyname) <= (hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0)",
98 "(idwiktionary:pcache:idoptions:44095) <= (keyname) <= (idwiktionary:pcache:idoptions:44095)",
99 "(itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0) <= (keyname) <= (itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0)",
100 "(jawiki:pcache:idoptions:372412) <= (keyname) <= (jawiki:pcache:idoptions:372412)",
101 "(plwiki:stable-pcache:idhash:2985160-0!canonical) <= (keyname) <= (plwiki:stable-pcache:idhash:2985160-0!canonical)",
102 "(ruwikinews:pcache:idhash:2275122-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2275122-0!canonical)",
103 "(ruwikinews:pcache:idhash:2276526-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2276526-0!canonical)",
104 "(ruwikinews:pcache:idhash:2280030-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2280030-0!canonical)",
105 "(ruwikinews:pcache:idoptions:2274652) <= (keyname) <= (ruwikinews:pcache:idoptions:2274652)",
106 "(ruwikinews:pcache:idoptions:2275409) <= (keyname) <= (ruwikinews:pcache:idoptions:2275409)",
107 "(ruwikinews:pcache:idoptions:2275772) <= (keyname) <= (ruwikinews:pcache:idoptions:2275772)",
108 "(ruwikinews:pcache:idoptions:2277615) <= (keyname) <= (ruwikinews:pcache:idoptions:2277615)",
109 "(ruwikinews:pcache:idoptions:2279056) <= (keyname) <= (ruwikinews:pcache:idoptions:2279056)",
110 "(srwiki:pcache:idoptions:1563379) <= (keyname) <= (srwiki:pcache:idoptions:1563379)",
111 "(svwiki:pcache:idhash:3766069-0!canonical) <= (keyname) <= (svwiki:pcache:idhash:3766069-0!canonical)",
112 "(svwiki:pcache:idoptions:6396670) <= (keyname) <= (svwiki:pcache:idoptions:6396670)",
113 "(ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0) <= (keyname) <= (ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0)",
114 "(wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3)",
115 "(wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3)",
116 "(wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3)",
117 "(wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3)",
118 "(wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3)",
119 "(wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3)",
120 "(wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3)",
121 "(wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3)",
122 "(wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3)",
123 "(wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3)",
124 "(wikidatawiki:pcache:idoptions:45230319) <= (keyname) <= (wikidatawiki:pcache:idoptions:45230319)",
125 "(wikidatawiki:pcache:idoptions:47136275) <= (keyname) <= (wikidatawiki:pcache:idoptions:47136275)",
126 "(wikidatawiki:pcache:idoptions:47137123) <= (keyname) <= (wikidatawiki:pcache:idoptions:47137123)",
127 "(wikidatawiki:pcache:idoptions:47138756) <= (keyname) <= (wikidatawiki:pcache:idoptions:47138756)",
128 "(wikidatawiki:pcache:idoptions:47169595) <= (keyname) <= (wikidatawiki:pcache:idoptions:47169595)",
129 "(wikidatawiki:pcache:idoptions:47170029) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170029)",
130 "(wikidatawiki:pcache:idoptions:47170137) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170137)",
131 "(wikidatawiki:pcache:idoptions:47170426) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170426)",
132 "(wikidatawiki:pcache:idoptions:47171328) <= (keyname) <= (wikidatawiki:pcache:idoptions:47171328)",
133 "(wikidatawiki:pcache:idoptions:47172141) <= (keyname) <= (wikidatawiki:pcache:idoptions:47172141)",
134 "(zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs) <= (keyname) <= (zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs)",
135 "(zhwikisource:pcache:idhash:284908-0!canonical!zh) <= (keyname) <= (zhwikisource:pcache:idhash:284908-0!canonical!zh)"
136 ],
137 "rowid_ordered": true,
138 "using_mrr": false,
139 "index_only": false,
140 "rows": 100,
141 "cost": 65515,
142 "chosen": true
143 },
144 {
145 "index": "exptime",
146 "ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"],
147 "rowid_ordered": false,
148 "using_mrr": false,
149 "index_only": false,
150 "rows": 26088,
151 "cost": 33264,
152 "chosen": true
153 }
154 ],
155 "analyzing_index_merge_union": []
156 },
157 "chosen_range_access_summary": {
158 "range_access_plan": {
159 "type": "range_scan",
160 "index": "exptime",
161 "rows": 26088,
162 "ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"]
163 },
164 "rows_for_plan": 26088,
165 "cost_for_plan": 33264,
166 "chosen": true
167 }
168 }
169 }
170 ]
171}
172MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
173 INSUFFICIENT_PRIVILEGES: 0
1741 row in set (0.001 sec)

There is a massive difference on this part regarding cost and rows:
pc2007

      "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

pc2010:

      "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

Number of rows on these two tables are pretty much the same:

mysql:root@localhost [parsercache]> select @@hostname;select count(*) from pc221;
+------------+
| @@hostname |
+------------+
| pc2007     |
+------------+
1 row in set (0.000 sec)

+----------+
| count(*) |
+----------+
|  1126732 |
+----------+
1 row in set (0.313 sec)

mysql:root@localhost [parsercache]> select @@hostname;select count(*) from pc221;
+------------+
| @@hostname |
+------------+
| pc2010     |
+------------+
1 row in set (0.000 sec)

+----------+
| count(*) |
+----------+
|  1141826 |
+----------+
1 row in set (0.313 sec)

I am going to file a bug with MariaDB about this issue. I am not sure it can be causing the delays, but it is definitely having an impact

Related Objects

Event Timeline

Marostegui created this task.Nov 23 2020, 11:59 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 23 2020, 11:59 AM
Marostegui added a comment.Nov 23 2020, 12:10 PM

Reported to mariadb: https://jira.mariadb.org/browse/MDEV-24266

Marostegui triaged this task as High priority.Nov 23 2020, 12:11 PM
Marostegui moved this task from Triage to In progress on the DBA board.

Let's not upgrade more hosts until we are sure on what's going on here.
I want to upgrade another pc1 host (maybe pc2007) to see if that behaviour changes there too.

LSobanski subscribed.Nov 23 2020, 12:16 PM
Marostegui claimed this task.Nov 26 2020, 2:03 PM
Marostegui added a comment.Jan 18 2021, 1:09 PM

I am going to install 10.4.17 on db2071 to see the effect there

Stashbot added a comment.Jan 18 2021, 1:12 PM

Mentioned in SAL (#wikimedia-operations) [2021-01-18T13:12:04Z] <marostegui> Upgrade db2071 to 10.4.17 - T268457

Marostegui added a comment.Jan 18 2021, 3:00 PM

For now I haven't seen bad regressions on the enwiki slave I put 10.4.17 on.
So far it looks specifically related to that DELETE happening on parsercache which is scanning way more rows than it should as shown on the mariadb bug itself: https://jira.mariadb.org/browse/MDEV-24266?focusedCommentId=176586&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-176586

Marostegui added a comment.EditedJan 26 2021, 1:35 PM

There might be a fix for this on the current (non official yet released) 10.4.18 code. I am compiling it and will try it on the affected hosts to check if that commit fixes the issue.

This is the change we will be testing: https://github.com/MariaDB/server/commit/5991bd6215054f21ec5c36fc9345ffb50f1b2d04

Stashbot added a comment.Jan 26 2021, 2:21 PM

Mentioned in SAL (#wikimedia-operations) [2021-01-26T14:21:48Z] <marostegui> Install mariadb 10.4.18 on pc2010 - T268457

Marostegui added a comment.Jan 26 2021, 6:16 PM

So far 10.4.18 doesn't seem to change the query plan and keeps picking the wrong one. The file with the sanitized data has been sent to MariaDB to see if they can reproduce the bug locally.

Marostegui added a comment.Jan 29 2021, 7:09 AM

Cross posting from what I just wrote on the mariadb bug:
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.

Stashbot added a comment.Jan 29 2021, 7:11 AM

Mentioned in SAL (#wikimedia-operations) [2021-01-29T07:11:00Z] <marostegui> Upgrade pc2007 to 10.4.18 T268457

Marostegui closed this task as Resolved.Feb 1 2021, 7:32 AM

So the summary is:
Bug confirmed on 10.4.17 and fixed on 10.4.18.

The workaround:

  • If upgrading from 10.4.15 (or older) to 10.4.18 -> nothing to be done.
  • If upgrading from 10.4.17 to 10.4.18 -> an analyze table is required, otherwise the bug shows up.

Thankfully we don't have many hosts running 10.4.17 as we didn't upgrade more than just a few testing hosts until we saw the issue.
The current 10.4.17 hosts that have been upgraded to 10.4.18:

  • db1077 (analyze was run)
  • pc2010 (analyze was run)
  • pc2007 (analyze was run)
  • db2102 (analyze on-going)
  • db2071 (analyze on-going)

The 10.4.18 version that is installed there isn't the final released one, but one built from the daily builds mariadb create from the developer repo which has this patch applied. Once it is officially released I will create the package for it. On the repo the version we have is up to 10.4.15.
All details and troubleshooting can be found on the mariadb ticket: https://jira.mariadb.org/browse/MDEV-24266

Marostegui added a comment.Feb 24 2021, 11:37 AM

For the record 10.4.18 has been released

Marostegui mentioned this in T263817: DBA review: conversation subscriptions.Apr 28 2021, 5:45 AM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits