Page MenuHomePhabricator
Create Task
Maniphest T305427

Slow recentchanges DB queries on 10.6
Open, MediumPublic
Actions

Description

In order to make this easier to find/read I am creating a subtask of this query regression I have noticed on 10.6 (which is big enough to have its own task). This is created from T301879#7799599

Upstream bug: https://jira.mariadb.org/browse/MDEV-28155

SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE ct_rc_id=rc_id  ) AS `ts_tags`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1)   WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 500;

10.4 picks rc_timestamp and 10.6 picks rc_name_type_patrolled_timestamp. Which is faster: 0.6 vs 4 seconds.

root@db1132.eqiad.wmnet[enwiki]> explain SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE ct_rc_id=rc_id  ) AS `ts_tags`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1)   WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 500;
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------------------+---------+----------------------------------------------------+
| id   | select_type        | table               | type   | possible_keys                                                                                                                       | key                              | key_len | ref                                            | rows    | Extra                                              |
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------------------+---------+----------------------------------------------------+
|    1 | PRIMARY            | recentchanges       | ref    | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_this_oldid,rc_new_name_timestamp | rc_name_type_patrolled_timestamp | 4       | const                                          | 4808652 | Using index condition; Using where; Using filesort |
|    1 | PRIMARY            | page                | eq_ref | PRIMARY                                                                                                                             | PRIMARY                          | 4       | enwiki.recentchanges.rc_cur_id                 | 1       | Using where                                        |
|    1 | PRIMARY            | flaggedpages        | eq_ref | PRIMARY                                                                                                                             | PRIMARY                          | 4       | enwiki.recentchanges.rc_cur_id                 | 1       |                                                    |
|    1 | PRIMARY            | ores_damaging_cls   | eq_ref | oresc_rev_model_class                                                                                                               | oresc_rev_model_class            | 7       | enwiki.recentchanges.rc_this_oldid,const,const | 1       |                                                    |
|    1 | PRIMARY            | ores_goodfaith_cls  | eq_ref | oresc_rev_model_class                                                                                                               | oresc_rev_model_class            | 7       | enwiki.recentchanges.rc_this_oldid,const,const | 1       |                                                    |
|    1 | PRIMARY            | recentchanges_actor | eq_ref | PRIMARY,actor_user                                                                                                                  | PRIMARY                          | 8       | enwiki.recentchanges.rc_actor                  | 1       | Using where                                        |
|    1 | PRIMARY            | comment_rc_comment  | eq_ref | PRIMARY                                                                                                                             | PRIMARY                          | 8       | enwiki.recentchanges.rc_comment_id             | 1       |                                                    |
|    2 | DEPENDENT SUBQUERY | change_tag          | ref    | ct_rc_tag_id,ct_tag_id_id                                                                                                           | ct_rc_tag_id                     | 5       | enwiki.recentchanges.rc_id                     | 1       | Using index                                        |
|    2 | DEPENDENT SUBQUERY | change_tag_def      | eq_ref | PRIMARY                                                                                                                             | PRIMARY                          | 4       | enwiki.change_tag.ct_tag_id                    | 1       |                                                    |
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------------------+---------+----------------------------------------------------+
9 rows in set (0.002 sec)

mysql:root@localhost [enwiki]> explain SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE ct_rc_id=rc_id  ) AS `ts_tags`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1)   WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 500;
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+------------------------------------------------+---------+-------------+
| id   | select_type        | table               | type   | possible_keys                                                                                                                       | key                   | key_len | ref                                            | rows    | Extra       |
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+------------------------------------------------+---------+-------------+
|    1 | PRIMARY            | recentchanges       | range  | rc_timestamp,rc_name_type_patrolled_timestamp,rc_namespace_title_timestamp,rc_ns_actor,rc_actor,rc_this_oldid,rc_new_name_timestamp | rc_timestamp          | 14      | NULL                                           | 4502872 | Using where |
|    1 | PRIMARY            | page                | eq_ref | PRIMARY                                                                                                                             | PRIMARY               | 4       | enwiki.recentchanges.rc_cur_id                 | 1       | Using where |
|    1 | PRIMARY            | flaggedpages        | eq_ref | PRIMARY                                                                                                                             | PRIMARY               | 4       | enwiki.recentchanges.rc_cur_id                 | 1       |             |
|    1 | PRIMARY            | ores_damaging_cls   | eq_ref | oresc_rev_model_class                                                                                                               | oresc_rev_model_class | 7       | enwiki.recentchanges.rc_this_oldid,const,const | 1       |             |
|    1 | PRIMARY            | ores_goodfaith_cls  | eq_ref | oresc_rev_model_class                                                                                                               | oresc_rev_model_class | 7       | enwiki.recentchanges.rc_this_oldid,const,const | 1       |             |
|    1 | PRIMARY            | recentchanges_actor | eq_ref | PRIMARY,actor_user                                                                                                                  | PRIMARY               | 8       | enwiki.recentchanges.rc_actor                  | 1       | Using where |
|    1 | PRIMARY            | comment_rc_comment  | eq_ref | PRIMARY                                                                                                                             | PRIMARY               | 8       | enwiki.recentchanges.rc_comment_id             | 1       |             |
|    2 | DEPENDENT SUBQUERY | change_tag          | ref    | ct_rc_tag_id,ct_tag_id_id                                                                                                           | ct_rc_tag_id          | 5       | enwiki.recentchanges.rc_id                     | 1       | Using index |
|    2 | DEPENDENT SUBQUERY | change_tag_def      | eq_ref | PRIMARY                                                                                                                             | PRIMARY               | 4       | enwiki.change_tag.ct_tag_id                    | 1       |             |
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+------------------------------------------------+---------+-------------+
9 rows in set (0.001 sec)

mysql:root@localhost [enwiki]>

If forcing 10.6 to use rc_timestamp the query goes as fast as the one in 10.4:

SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE ct_rc_id=rc_id  ) AS `ts_tags`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score`  FROM `recentchanges` USE INDEX (rc_timestamp) JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1)   WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 500;
<snip>
500 rows in set (0.048 sec)

Second query:
Upstream bug: https://jira.mariadb.org/browse/MDEV-28476

This query is also different on 10.6 than on 10.4 (which performs better)

root@db1135.eqiad.wmnet[enwiki]> select @@version
    -> ;
+---------------------+
| @@version           |
+---------------------+
| 10.4.21-MariaDB-log |
+---------------------+
1 row in set (0.001 sec)

root@db1135.eqiad.wmnet[enwiki]> explain SELECT  actor_name,actor_user,rc_actor,rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`  FROM `recentchanges` JOIN `actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id))   WHERE rc_namespace = 0 AND rc_type IN (0,1,3,6)   ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 501  ;
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+--------------+---------+------------------------------------+------+-------------+
| id   | select_type | table              | type   | possible_keys                                                                      | key          | key_len | ref                                | rows | Extra       |
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+--------------+---------+------------------------------------+------+-------------+
|    1 | SIMPLE      | recentchanges      | index  | rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp | rc_timestamp | 14      | NULL                               | 1002 | Using where |
|    1 | SIMPLE      | actor              | eq_ref | PRIMARY                                                                            | PRIMARY      | 8       | enwiki.recentchanges.rc_actor      | 1    |             |
|    1 | SIMPLE      | comment_rc_comment | eq_ref | PRIMARY                                                                            | PRIMARY      | 8       | enwiki.recentchanges.rc_comment_id | 1    |             |
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+--------------+---------+------------------------------------+------+-------------+
3 rows in set (0.001 sec)

10.6:

root@db1132.eqiad.wmnet[enwiki]> select @@version; explain SELECT  actor_name,actor_user,rc_actor,rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`  FROM `recentchanges` JOIN `actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id))   WHERE rc_namespace = 0 AND rc_type IN (0,1,3,6)   ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 501  ;
+--------------------+
| @@version          |
+--------------------+
| 10.6.7-MariaDB-log |
+--------------------+
1 row in set (0.000 sec)

+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------+---------+----------------------------------------------------+
| id   | select_type | table              | type   | possible_keys                                                                      | key                              | key_len | ref                                | rows    | Extra                                              |
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------+---------+----------------------------------------------------+
|    1 | SIMPLE      | recentchanges      | ref    | rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp | rc_name_type_patrolled_timestamp | 4       | const                              | 4610595 | Using index condition; Using where; Using filesort |
|    1 | SIMPLE      | actor              | eq_ref | PRIMARY                                                                            | PRIMARY                          | 8       | enwiki.recentchanges.rc_actor      | 1       |                                                    |
|    1 | SIMPLE      | comment_rc_comment | eq_ref | PRIMARY                                                                            | PRIMARY                          | 8       | enwiki.recentchanges.rc_comment_id | 1       |                                                    |
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------+---------+----------------------------------------------------+
3 rows in set (0.001 sec)

10.6 query with the USE INDEX

root@db1132.eqiad.wmnet[enwiki]> select @@version; explain SELECT  actor_name,actor_user,rc_actor,rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`  FROM `recentchanges` USE INDEX (rc_timestamp) JOIN `actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id))   WHERE rc_namespace = 0 AND rc_type IN (0,1,3,6)   ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 501  ;
+--------------------+
| @@version          |
+--------------------+
| 10.6.7-MariaDB-log |
+--------------------+
1 row in set (0.000 sec)

+------+-------------+--------------------+--------+---------------+--------------+---------+------------------------------------+------+-------------+
| id   | select_type | table              | type   | possible_keys | key          | key_len | ref                                | rows | Extra       |
+------+-------------+--------------------+--------+---------------+--------------+---------+------------------------------------+------+-------------+
|    1 | SIMPLE      | recentchanges      | index  | NULL          | rc_timestamp | 14      | NULL                               | 1298 | Using where |
|    1 | SIMPLE      | actor              | eq_ref | PRIMARY       | PRIMARY      | 8       | enwiki.recentchanges.rc_actor      | 1    |             |
|    1 | SIMPLE      | comment_rc_comment | eq_ref | PRIMARY       | PRIMARY      | 8       | enwiki.recentchanges.rc_comment_id | 1    |             |
+------+-------------+--------------------+--------+---------------+--------------+---------+------------------------------------+------+-------------+
3 rows in set (0.001 sec)

Third query:
Upstream bug: Not needed for now, fixed by refreshing table stats.

This query is also different on 10.6 than on 10.4 (which performs better 0.07 vs more than 60 seconds)

root@db1127.eqiad.wmnet[metawiki]> select @@version; explain SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE ct_rc_id=rc_id  ) AS `ts_tags`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20220505123521') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 50;
+--------------------+
| @@version          |
+--------------------+
| 10.6.7-MariaDB-log |
+--------------------+
1 row in set (0.000 sec)

+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------+----------+--------------->
| id   | select_type        | table               | type   | possible_keys                                                                                                         | key          | key_len | ref                                   | rows     | Extra         >
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------+----------+--------------->
|    1 | PRIMARY            | recentchanges_actor | ALL    | PRIMARY                                                                                                               | NULL         | NULL    | NULL                                  | 21528923 | Using temporar>
|    1 | PRIMARY            | recentchanges       | ref    | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_new_name_timestamp | rc_actor     | 8       | metawiki.recentchanges_actor.actor_id | 19       | Using index co>
|    1 | PRIMARY            | page                | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.recentchanges.rc_cur_id      | 1        |               >
|    1 | PRIMARY            | comment_rc_comment  | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 8       | metawiki.recentchanges.rc_comment_id  | 1        |               >
|    2 | DEPENDENT SUBQUERY | change_tag          | ref    | ct_rc_tag_id,ct_tag_id_id                                                                                             | ct_rc_tag_id | 5       | metawiki.recentchanges.rc_id          | 1        | Using index   >
|    2 | DEPENDENT SUBQUERY | change_tag_def      | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.change_tag.ct_tag_id         | 1        |               >
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------+----------+--------------->
6 rows in set (0.001 sec)

10.4:

root@db1136.eqiad.wmnet[metawiki]> select @@version; explain SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE ct_rc_id=rc_id  ) AS `ts_tags`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20220505123521') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 50;
+---------------------+
| @@version           |
+---------------------+
| 10.4.22-MariaDB-log |
+---------------------+
1 row in set (0.000 sec)

+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+--------------------------------------+-------+-------------+
| id   | select_type        | table               | type   | possible_keys                                                                                                         | key          | key_len | ref                                  | rows  | Extra       |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+--------------------------------------+-------+-------------+
|    1 | PRIMARY            | recentchanges       | range  | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_new_name_timestamp | rc_timestamp | 14      | NULL                                 | 97016 | Using where |
|    1 | PRIMARY            | comment_rc_comment  | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 8       | metawiki.recentchanges.rc_comment_id | 1     |             |
|    1 | PRIMARY            | page                | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.recentchanges.rc_cur_id     | 1     |             |
|    1 | PRIMARY            | recentchanges_actor | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 8       | metawiki.recentchanges.rc_actor      | 1     |             |
|    2 | DEPENDENT SUBQUERY | change_tag          | ref    | ct_rc_tag_id,ct_tag_id_id                                                                                             | ct_rc_tag_id | 5       | metawiki.recentchanges.rc_id         | 1     | Using index |
|    2 | DEPENDENT SUBQUERY | change_tag_def      | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.change_tag.ct_tag_id        | 1     |             |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+--------------------------------------+-------+-------------+
6 rows in set (0.004 sec)

Forcing the index on 10.6 the query goes super fast (0.07):

root@db1127.eqiad.wmnet[metawiki]> select @@version; explain SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE ct_rc_id=rc_id  ) AS `ts_tags`  FROM `recentchanges` USE INDEX (rc_timestamp) JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20220505123521') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 50;
+--------------------+
| @@version          |
+--------------------+
| 10.6.7-MariaDB-log |
+--------------------+
1 row in set (0.000 sec)

+------+--------------------+---------------------+--------+---------------------------+--------------+---------+--------------------------------------+--------+-------------+
| id   | select_type        | table               | type   | possible_keys             | key          | key_len | ref                                  | rows   | Extra       |
+------+--------------------+---------------------+--------+---------------------------+--------------+---------+--------------------------------------+--------+-------------+
|    1 | PRIMARY            | recentchanges       | range  | rc_timestamp              | rc_timestamp | 14      | NULL                                 | 103132 | Using where |
|    1 | PRIMARY            | comment_rc_comment  | eq_ref | PRIMARY                   | PRIMARY      | 8       | metawiki.recentchanges.rc_comment_id | 1      |             |
|    1 | PRIMARY            | page                | eq_ref | PRIMARY                   | PRIMARY      | 4       | metawiki.recentchanges.rc_cur_id     | 1      |             |
|    1 | PRIMARY            | recentchanges_actor | eq_ref | PRIMARY                   | PRIMARY      | 8       | metawiki.recentchanges.rc_actor      | 1      |             |
|    2 | DEPENDENT SUBQUERY | change_tag          | ref    | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5       | metawiki.recentchanges.rc_id         | 1      | Using index |
|    2 | DEPENDENT SUBQUERY | change_tag_def      | eq_ref | PRIMARY                   | PRIMARY      | 4       | metawiki.change_tag.ct_tag_id        | 1      |             |
+------+--------------------+---------------------+--------+---------------------------+--------------+---------+--------------------------------------+--------+-------------+
6 rows in set (0.001 sec)

Related Objects
Search...

Event Timeline

Marostegui created this task.Apr 5 2022, 7:06 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 5 2022, 7:06 AM
Marostegui triaged this task as Medium priority.Apr 5 2022, 7:07 AM
Marostegui moved this task from Triage to Blocked on the DBA board.
Marostegui added a subscriber: Ladsgroup.

@Ladsgroup In case mariadb doesn't really fix this (at all or in a timely manner) how usual is this query? Maybe hinting the index in code is the only work around we have for it.

LSobanski subscribed.Apr 5 2022, 9:22 AM
Stashbot added a comment.Apr 5 2022, 11:39 AM

Mentioned in SAL (#wikimedia-operations) [2022-04-05T11:39:44Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1132 T305427', diff saved to https://phabricator.wikimedia.org/P24112 and previous config saved to /var/cache/conftool/dbconfig/20220405-113944-root.json

Zabe subscribed.Apr 5 2022, 1:49 PM
Ladsgroup added a comment.Apr 5 2022, 7:27 PM

It is not that bad. It's not a common query, it returns 500 rows and 4s is not much and this is enwiki so much better in smaller wikis. We might need to watch out in commons or wikidata. Can we run this query in those wikis and see the difference between 10.4 and 10.6?

Marostegui added a comment.Apr 6 2022, 4:36 AM

I don't have any 10.6 on commons or wikidata yet. But I will keep that in mind for my next tests.

Marostegui mentioned this in T307546: Migrate a wikidata DB to MariaDB 10.6.May 4 2022, 7:52 AM
In T305427#7833311, @Ladsgroup wrote:

It is not that bad. It's not a common query, it returns 500 rows and 4s is not much and this is enwiki so much better in smaller wikis. We might need to watch out in commons or wikidata. Can we run this query in those wikis and see the difference between 10.4 and 10.6?

I will work on this on a wikidata host as part of T307546: Migrate a wikidata DB to MariaDB 10.6

Marostegui added a subtask: T307546: Migrate a wikidata DB to MariaDB 10.6.May 4 2022, 7:53 AM
Marostegui added a comment.EditedMay 4 2022, 1:32 PM

This query is also different on 10.6 than on 10.4 (which performs better)

root@db1135.eqiad.wmnet[enwiki]> select @@version
    -> ;
+---------------------+
| @@version           |
+---------------------+
| 10.4.21-MariaDB-log |
+---------------------+
1 row in set (0.001 sec)

root@db1135.eqiad.wmnet[enwiki]> explain SELECT  actor_name,actor_user,rc_actor,rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`  FROM `recentchanges` JOIN `actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id))   WHERE rc_namespace = 0 AND rc_type IN (0,1,3,6)   ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 501  ;
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+--------------+---------+------------------------------------+------+-------------+
| id   | select_type | table              | type   | possible_keys                                                                      | key          | key_len | ref                                | rows | Extra       |
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+--------------+---------+------------------------------------+------+-------------+
|    1 | SIMPLE      | recentchanges      | index  | rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp | rc_timestamp | 14      | NULL                               | 1002 | Using where |
|    1 | SIMPLE      | actor              | eq_ref | PRIMARY                                                                            | PRIMARY      | 8       | enwiki.recentchanges.rc_actor      | 1    |             |
|    1 | SIMPLE      | comment_rc_comment | eq_ref | PRIMARY                                                                            | PRIMARY      | 8       | enwiki.recentchanges.rc_comment_id | 1    |             |
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+--------------+---------+------------------------------------+------+-------------+
3 rows in set (0.001 sec)

10.6:

root@db1132.eqiad.wmnet[enwiki]> select @@version; explain SELECT  actor_name,actor_user,rc_actor,rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`  FROM `recentchanges` JOIN `actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id))   WHERE rc_namespace = 0 AND rc_type IN (0,1,3,6)   ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 501  ;
+--------------------+
| @@version          |
+--------------------+
| 10.6.7-MariaDB-log |
+--------------------+
1 row in set (0.000 sec)

+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------+---------+----------------------------------------------------+
| id   | select_type | table              | type   | possible_keys                                                                      | key                              | key_len | ref                                | rows    | Extra                                              |
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------+---------+----------------------------------------------------+
|    1 | SIMPLE      | recentchanges      | ref    | rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp | rc_name_type_patrolled_timestamp | 4       | const                              | 4610595 | Using index condition; Using where; Using filesort |
|    1 | SIMPLE      | actor              | eq_ref | PRIMARY                                                                            | PRIMARY                          | 8       | enwiki.recentchanges.rc_actor      | 1       |                                                    |
|    1 | SIMPLE      | comment_rc_comment | eq_ref | PRIMARY                                                                            | PRIMARY                          | 8       | enwiki.recentchanges.rc_comment_id | 1       |                                                    |
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------+---------+----------------------------------------------------+
3 rows in set (0.001 sec)
Marostegui added a comment.May 4 2022, 1:33 PM

I am going to try to refresh some stats and if doesn't get fixed I will report it to mariadb as part of https://jira.mariadb.org/browse/MDEV-28155

Marostegui added a comment.May 4 2022, 1:34 PM

Forgot to paste the USE INDEX force:

root@db1132.eqiad.wmnet[enwiki]> select @@version; explain SELECT  actor_name,actor_user,rc_actor,rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`  FROM `recentchanges` USE INDEX (rc_timestamp) JOIN `actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id))   WHERE rc_namespace = 0 AND rc_type IN (0,1,3,6)   ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 501  ;
+--------------------+
| @@version          |
+--------------------+
| 10.6.7-MariaDB-log |
+--------------------+
1 row in set (0.000 sec)

+------+-------------+--------------------+--------+---------------+--------------+---------+------------------------------------+------+-------------+
| id   | select_type | table              | type   | possible_keys | key          | key_len | ref                                | rows | Extra       |
+------+-------------+--------------------+--------+---------------+--------------+---------+------------------------------------+------+-------------+
|    1 | SIMPLE      | recentchanges      | index  | NULL          | rc_timestamp | 14      | NULL                               | 1298 | Using where |
|    1 | SIMPLE      | actor              | eq_ref | PRIMARY       | PRIMARY      | 8       | enwiki.recentchanges.rc_actor      | 1    |             |
|    1 | SIMPLE      | comment_rc_comment | eq_ref | PRIMARY       | PRIMARY      | 8       | enwiki.recentchanges.rc_comment_id | 1    |             |
+------+-------------+--------------------+--------+---------------+--------------+---------+------------------------------------+------+-------------+
3 rows in set (0.001 sec)
Marostegui added a comment.May 4 2022, 3:29 PM
In T305427#7903232, @Marostegui wrote:

I am going to try to refresh some stats and if doesn't get fixed I will report it to mariadb as part of https://jira.mariadb.org/browse/MDEV-28155

This didn't help so I am going to report it to mariadb in a new bug.

Marostegui renamed this task from Slow DB query on 10.6 to Slow recentchanges DB queries on 10.6.May 4 2022, 3:29 PM
Marostegui updated the task description. (Show Details)
Marostegui updated the task description. (Show Details)
Marostegui updated the task description. (Show Details)
Marostegui added a comment.May 5 2022, 5:26 AM

I have reported the second query upstream: https://jira.mariadb.org/browse/MDEV-28476

Marostegui updated the task description. (Show Details)May 5 2022, 5:26 AM
Marostegui added a comment.May 9 2022, 5:17 AM

So after the weekend, the slowest query arriving to db1132 (mariadb 10.6) is the one we already reported in the first place:

SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE ct_rc_id=rc_id  ) AS `ts_tags`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1)   WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 500;
Marostegui mentioned this in T301879: Test MariaDB 10.6 on Bullseye.May 9 2022, 5:18 AM
Marostegui added a comment.May 9 2022, 11:58 AM
In T305427#7833311, @Ladsgroup wrote:

It is not that bad. It's not a common query, it returns 500 rows and 4s is not much and this is enwiki so much better in smaller wikis. We might need to watch out in commons or wikidata. Can we run this query in those wikis and see the difference between 10.4 and 10.6?

So the original query cannot be tested on wikidata as we don't have flaggedpages there.

Ladsgroup added a comment.May 9 2022, 1:26 PM

Obviously. Sorry, I forgot. Then dewiki would be the next biggest one with actual massive flaggedrevs. Sorry for the mistake.

Marostegui added a comment.May 10 2022, 12:37 PM

No problen @Ladsgroup - I will migrate an s5 host soon (probably next week) as I want to try a new thing to see if I can identify exactly what is creating T307082: Investigate spikes on db1132 (mariadb 10.6 host)

Marostegui updated the task description. (Show Details)May 10 2022, 12:41 PM

@Ladsgroup now that I check it, we don't have dewiki.ores_classification so we cannot test it there with the combination of flaggedpages

Ladsgroup added a comment.May 11 2022, 5:38 AM

:( Can you check plwiki or arwiki then? But that wouldn't be too big which is good in itself.

Marostegui mentioned this in T308126: Migrate a s7 DB host to mariadb 10.6.May 12 2022, 6:45 AM
Marostegui added a comment.May 12 2022, 12:40 PM

There's a similar to the previous queries that also behaves bad on 10.6 (metawiki)

root@db1127.eqiad.wmnet[metawiki]> explain SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE ct_rc_id=rc_id  ) AS `ts_tags`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20220505123521') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 50;
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------+----------+--------------->
| id   | select_type        | table               | type   | possible_keys                                                                                                         | key          | key_len | ref                                   | rows     | Extra         >
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------+----------+--------------->
|    1 | PRIMARY            | recentchanges_actor | ALL    | PRIMARY                                                                                                               | NULL         | NULL    | NULL                                  | 21528905 | Using temporar>
|    1 | PRIMARY            | recentchanges       | ref    | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_new_name_timestamp | rc_actor     | 8       | metawiki.recentchanges_actor.actor_id | 19       | Using index co>
|    1 | PRIMARY            | page                | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.recentchanges.rc_cur_id      | 1        |               >
|    1 | PRIMARY            | comment_rc_comment  | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 8       | metawiki.recentchanges.rc_comment_id  | 1        |               >
|    2 | DEPENDENT SUBQUERY | change_tag          | ref    | ct_rc_tag_id,ct_tag_id_id                                                                                             | ct_rc_tag_id | 5       | metawiki.recentchanges.rc_id          | 1        | Using index   >
|    2 | DEPENDENT SUBQUERY | change_tag_def      | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.change_tag.ct_tag_id         | 1        |               >
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------+----------+--------------->
6 rows in set (0.001 sec)

And on 10.4:

root@db1136.eqiad.wmnet[metawiki]> explain SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE ct_rc_id=rc_id  ) AS `ts_tags`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20220505123521') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 50;
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+--------------------------------------+-------+-------------+
| id   | select_type        | table               | type   | possible_keys                                                                                                         | key          | key_len | ref                                  | rows  | Extra       |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+--------------------------------------+-------+-------------+
|    1 | PRIMARY            | recentchanges       | range  | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_new_name_timestamp | rc_timestamp | 14      | NULL                                 | 95300 | Using where |
|    1 | PRIMARY            | comment_rc_comment  | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 8       | metawiki.recentchanges.rc_comment_id | 1     |             |
|    1 | PRIMARY            | page                | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.recentchanges.rc_cur_id     | 1     |             |
|    1 | PRIMARY            | recentchanges_actor | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 8       | metawiki.recentchanges.rc_actor      | 1     |             |
|    2 | DEPENDENT SUBQUERY | change_tag          | ref    | ct_rc_tag_id,ct_tag_id_id                                                                                             | ct_rc_tag_id | 5       | metawiki.recentchanges.rc_id         | 1     | Using index |
|    2 | DEPENDENT SUBQUERY | change_tag_def      | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.change_tag.ct_tag_id        | 1     |             |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+--------------------------------------+-------+-------------+
6 rows in set (0.003 sec)
Marostegui updated the task description. (Show Details)May 12 2022, 12:43 PM

Added to the original task description and I am going to report it to mariadb

Marostegui added a comment.May 12 2022, 12:46 PM

So the recreating the table stats actually fixed that above query. Good!

Marostegui updated the task description. (Show Details)May 12 2022, 12:46 PM
Marostegui closed subtask T307546: Migrate a wikidata DB to MariaDB 10.6 as Resolved.May 13 2022, 6:55 AM
Universal_Omega subscribed.Jun 18 2022, 4:48 PM
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