Page MenuHomePhabricator
Create Task
Maniphest T206103

recentchanges table indexes: tmp1, tmp2 and tmp3
Closed, ResolvedPublic
Actions

Description

While troubleshooting T202764 we discovered there are three indexes placed on the recentchanges table called:

KEY `tmp_1` (`rc_this_oldid`),
KEY `tmp_2` (`rc_bot`,`rc_timestamp`),
KEY `tmp_3` (`rc_namespace`,`rc_timestamp`),

tmp1 is going to be addressed with: T202167: Schema change for rc_this_oldid index - it is quite messy as it doesn't exist everywhere. Example: exists on enwiki hosts but not on commons
However, tmp2 and tmp3 there is no trace of those on tables.sql. They are most likely indexes that were added on the fly to mitigate something and then it was forgotten to:

  1. be added to tables.sql
  2. get them added with a proper name

tmp2 and tmp3 exist mostly, on all the hosts, but not everywhere, not even within the same section. It is a mess.

Example for enwiki: they exist everywhere but on db1105 (one of the recentchanges slave), db1106 (one of the api hosts), db1067 (enwiki master)
Example for commonswiki: exists everywhere
Example for wikidatawiki: Only exists on recentchanges slaves (db1101 and db1099) and the master (db1071)
Example for eswiki: Exists everywhere
Example for dewiki: Exists almost everywhere but not on: the master (db1070), db1110 (main traffic), db1113 (vslow), db1082 (api+main)

A quick check on enwiki checks (using sys.schema_unused_indexes) doesn't report that any of those indexes are unused, so I guess some queries are using them,. I guess not in a very dramatic way as otherwise we'd have seen issues on the slaves that doesn't have them (on enwiki and wikidatawiki)?

Any thoughts on how to move forward? What I can think of:

  1. Add them with a proper name to tables.sql and drop+create on production with the proper name (and create them where they don't exist)
  2. Just drop them in production and make production the same as tables.sql

Progress:

  • s1
  • s2
  • s3
  • s4
  • s5
  • s6
  • s7
  • s8

labswiki: not present
labtestwiki: not present

Related Objects
Search...

StatusSubtypeAssignedTask
 ResolvedMarosteguiT206103 recentchanges table indexes: tmp1, tmp2 and tmp3
 ResolvedLadsgroupT104459 Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas
 ResolvedNoneT57455 Provide dynamic report of differences between replica databases and production databases
 ResolvedMarosteguiT51199 Add index log_type_action
 ResolvedMarosteguiT217397 logging.log_title_time and logging.log_title_type_time indexes are not on tables.sql but they exist on most of the wikis
 ResolvedMarosteguiT250055 Remove image.img_deleted column from production
 ResolvedMarosteguiT250067 user_newtalk has two indexes not renamed in s4
 ResolvedMarosteguiT250062 ipb_parent_block_id_2 index on ipblocks table on s8 only
 ResolvedMarosteguiT250060 tl_namespace index on templatelinks is unique only in s8
 ResolvedReedyT249972 Wikitech has lots of database drifts with core and rest of the databases
 ResolvedMarosteguiT249973 db1110 has 5 important database drifts that are unique to the host
 ResolvedMarosteguiT250056 lc_lang_key index is lingering in production
 ResolvedMarosteguiT250057 type_acton index in logging table is lingering in production
 DeclinedNoneT250058 searchindex indexes are missing in production
 ResolvedMarosteguiT250063 inverse_timestamp column exists in text table, it shouldn't
 ResolvedMarosteguiT250066 text table still has old_* fields and indexes on some hosts
 ResolvedLadsgroupT250071 Rename ipb_address index on ipb_address to ipb_address_unique
 ResolvedMarosteguiT252126 tl_from index on templatelinks is lingering in production
 ResolvedMarosteguiT256679 text table in db1144 drifts from core considerably
 ResolvedMarosteguiT256680 imagelinks has index mismatch on s8
 ResolvedMarosteguiT256682 page_restrictions indexes have been majestically drifting from code
 ResolvedMarosteguiT256684 pl_from index still lingers in random hosts
 ResolvedMarosteguiT256685 pl_namespace index on pagelinks is unique only in s8
 ResolvedMarosteguiT260111 All sorts of random drifts in wikis in s3
 ResolvedLadsgroupT265321 ipblocks_restrictions.ir_type is tinyint(1) in code but tinyint(4) in production
 ResolvedMarosteguiT265349 querycache qc_type and qc_title have different nullabality on s1 only
 ResolvedMarosteguiT265445 Index uniqueness mismatches in links tables in wikis that were moved from s3 to s5
 ResolvedLadsgroupT269348 wikitech database has almost all of its varbinary fields wrong
 ResolvedKormatT277118 iw_url in interwiki is varbinary(127) in production but blob in code
 ResolvedMarosteguiT277354 "chemical" major mime type was never added to production database
 ResolvedKormatT278619 ipb_timestamp is varbinary(14) in old wikis while being binary(14) in the code since 2007
 ResolvedMarosteguiT278621 job_cmd is varbinary(255) in production while being varbinary(60) in code since 2007
 ResolvedKormatT277116 fa_deleted_timestamp and fa_timestamp are binary(14) in code but varbinary(14) in production
 ResolvedMarosteguiT308206 Slow query on echo_event table
Restricted Task

Event Timeline

Marostegui created this task.Oct 3 2018, 8:01 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 3 2018, 8:01 AM
Marostegui triaged this task as Medium priority.Oct 3 2018, 8:01 AM
Marostegui updated the task description. (Show Details)
Marostegui added a parent task: T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.Oct 3 2018, 8:13 AM
Ladsgroup added a comment.Oct 3 2018, 10:29 AM

tmp_1 is actually recently added in the code under name of rc_this_oldid (patch, Ticket to implement: T202167)

Marostegui updated the task description. (Show Details)Oct 3 2018, 10:34 AM
jcrespo removed a parent task: T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.Feb 8 2019, 9:57 AM
jcrespo added a subtask: T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.
Krinkle moved this task from Untriaged to Schema changes on the MediaWiki-libs-Rdbms board.Apr 3 2019, 2:55 AM
Krinkle edited projects, added MediaWiki-Recent-changes, Schema-change; removed MediaWiki-libs-Rdbms.Jul 18 2019, 10:16 PM
Restricted Application added a project: Growth-Team. · View Herald TranscriptJul 18 2019, 10:16 PM
JTannerWMF moved this task from Inbox to Needs Discussion on the Growth-Team board.Jul 23 2019, 8:37 PM
JTannerWMF added subscribers: Catrope, JTannerWMF.

Hey @Catrope, we need you to triage this task

Catrope moved this task from Needs Discussion to External on the Growth-Team board.Jul 23 2019, 9:46 PM

I think we can drop these indexes. I'm not aware of any code that needs them. If MySQL's metrics show that these indexes really are unused in production, then let's drop them.

Reedy moved this task from Unsorted to Delete / Drop / Remove on the Schema-change board.Feb 23 2020, 11:10 PM
Anomie mentioned this in T246069: Slow query on 10.4: SpecialRecentChanges::doMainQuery.Feb 25 2020, 3:51 PM
Ladsgroup added a comment.May 1 2020, 4:31 PM

@Marostegui Do you think we can move forward with dropping them?

Marostegui added a comment.May 4 2020, 5:19 AM

@Ladsgroup yes, we need to double check again if the indexes are being used (via sys schema) - also we need to move slowly here to make sure we don't cause any regression on query execution times.
I would propose to drop them on one of the big recenthanges hosts for s1 and maybe s4 and monitor the slow query logs for a few days.

Ladsgroup added a comment.May 4 2020, 10:17 AM

Cool. I was thinking let's do tmp_2 first given the notes in T246069: Slow query on 10.4: SpecialRecentChanges::doMainQuery

Marostegui added a comment.May 4 2020, 11:27 AM

Keep in mind that T246069 is partially bad because of the mentioned MariaDB bug.
Those tmp indexes are being used, the key here is to see if by removing them, the other ones the optimizer would choose are ok or make things a lot worse.

Stashbot added a comment.May 4 2020, 11:45 AM

Mentioned in SAL (#wikimedia-operations) [2020-05-04T11:45:39Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1099:3311 T206103 to remove tmp_2 index', diff saved to https://phabricator.wikimedia.org/P11118 and previous config saved to /var/cache/conftool/dbconfig/20200504-114539-marostegui.json

Stashbot added a comment.May 4 2020, 11:46 AM

Mentioned in SAL (#wikimedia-operations) [2020-05-04T11:46:12Z] <marostegui> Remove index tmp_2 from recentchanges on db1099:3311 T206103

Marostegui added a comment.May 4 2020, 11:47 AM

Remove on db1099:3311 for enwiki.
Let's see how this host performs:

root@db1099.eqiad.wmnet[enwiki]> show create table recentchanges\G
*************************** 1. row ***************************
       Table: recentchanges
Create Table: CREATE TABLE `recentchanges` (
  `rc_id` int(8) NOT NULL AUTO_INCREMENT,
  `rc_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rc_actor` bigint(20) unsigned NOT NULL,
  `rc_namespace` int(11) NOT NULL DEFAULT '0',
  `rc_title` varbinary(255) NOT NULL DEFAULT '',
  `rc_comment_id` bigint(20) unsigned NOT NULL,
  `rc_minor` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rc_bot` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rc_new` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rc_cur_id` int(10) unsigned NOT NULL DEFAULT '0',
  `rc_this_oldid` int(10) unsigned NOT NULL DEFAULT '0',
  `rc_last_oldid` int(10) unsigned NOT NULL DEFAULT '0',
  `rc_type` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rc_source` varbinary(16) NOT NULL DEFAULT '',
  `rc_patrolled` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rc_ip` varbinary(40) NOT NULL DEFAULT '',
  `rc_old_len` int(10) DEFAULT NULL,
  `rc_new_len` int(10) DEFAULT NULL,
  `rc_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rc_logid` int(10) unsigned NOT NULL DEFAULT '0',
  `rc_log_type` varbinary(255) DEFAULT NULL,
  `rc_log_action` varbinary(255) DEFAULT NULL,
  `rc_params` blob NOT NULL,
  PRIMARY KEY (`rc_id`),
  KEY `rc_timestamp` (`rc_timestamp`),
  KEY `rc_cur_id` (`rc_cur_id`),
  KEY `new_name_timestamp` (`rc_new`,`rc_namespace`,`rc_timestamp`),
  KEY `rc_ip` (`rc_ip`),
  KEY `tmp_3` (`rc_namespace`,`rc_timestamp`),
  KEY `rc_name_type_patrolled_timestamp` (`rc_namespace`,`rc_type`,`rc_patrolled`,`rc_timestamp`),
  KEY `rc_ns_actor` (`rc_namespace`,`rc_actor`),
  KEY `rc_actor` (`rc_actor`,`rc_timestamp`),
  KEY `rc_namespace_title_timestamp` (`rc_namespace`,`rc_title`,`rc_timestamp`),
  KEY `rc_this_oldid` (`rc_this_oldid`)
) ENGINE=InnoDB AUTO_INCREMENT=1257853219 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.00 sec)
Stashbot added a comment.May 4 2020, 11:47 AM

Mentioned in SAL (#wikimedia-operations) [2020-05-04T11:47:27Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db1099:3311 T206103 after removing tmp_2 index', diff saved to https://phabricator.wikimedia.org/P11119 and previous config saved to /var/cache/conftool/dbconfig/20200504-114727-marostegui.json

Stashbot added a comment.May 4 2020, 12:10 PM

Mentioned in SAL (#wikimedia-operations) [2020-05-04T12:10:00Z] <marostegui> Temporary enable slow query log on db1099:3311 - T206103

Marostegui added a comment.May 4 2020, 1:44 PM

ok, so analyzing all the slow queries arriving to db1099:3311 (enwiki) in the last 2 hours - without tmp_2 index, I cannot see any obvious regression.
I am going to leave it running like this till tomorrow and I will analyze them again.

Marostegui edited projects, added DBA; removed User-Marostegui.May 4 2020, 1:44 PM
Marostegui moved this task from Triage to In progress on the DBA board.
Marostegui added a comment.May 4 2020, 2:38 PM

For the record, I have caught this query using tmp_3:

explain SELECT /* SpecialRecentChanges::doMainQuery */ /*! STRAIGHT_JOIN */ 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, 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`, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor, wl_user, wl_notificationtimestamp, 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 `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) JOIN `actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) LEFT JOIN `watchlist` ON (wl_user = 25112844 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) 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 = 37 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 = 38 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1) WHERE rc_bot = 0 AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND ((ores_goodfaith_cls.oresc_probability BETWEEN 0 AND 0.358)) AND (rc_type NOT IN (3, 5)) AND (rc_namespace = '0') AND (rc_timestamp >= '20200404124958') AND rc_new IN (0, 1) ORDER BY rc_timestamp DESC LIMIT 250 /* 391c48d0e90517b73df99837c1fa8413 db1099:3311 enwiki 3s */
    -> ;
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+
| id   | select_type        | table              | type   | possible_keys                                                                                                                          | key                   | key_len | ref                                                                   | rows    | Extra       |
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+
|    1 | PRIMARY            | recentchanges      | ref    | rc_timestamp,new_name_timestamp,tmp_3,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_this_oldid | tmp_3                 | 4       | const                                                                 | 4816184 | Using where |
|    1 | PRIMARY            | comment_rc_comment | eq_ref | PRIMARY                                                                                                                                | PRIMARY               | 8       | enwiki.recentchanges.rc_comment_id                                    |       1 |             |
|    1 | PRIMARY            | actor_rc_user      | eq_ref | PRIMARY                                                                                                                                | PRIMARY               | 8       | enwiki.recentchanges.rc_actor                                         |       1 |             |
|    1 | PRIMARY            | watchlist          | eq_ref | wl_user,namespace_title,wl_user_notificationtimestamp                                                                                  | wl_user               | 265     | const,enwiki.recentchanges.rc_namespace,enwiki.recentchanges.rc_title |       1 |             |
|    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 | Using where |
|    2 | DEPENDENT SUBQUERY | change_tag         | ref    | change_tag_rc_tag_id,change_tag_tag_id_id                                                                                              | change_tag_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 |             |
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+

Ignoring that index results on a similar plan - and the query time is almost the same 1.38 vs 1.37:

root@db1099.eqiad.wmnet[enwiki]> explain SELECT /* SpecialRecentChanges::doMainQuery */ /*! STRAIGHT_JOIN */ 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, 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`, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor, wl_user, wl_notificationtimestamp, 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` IGNORE INDEX (tmp_3) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) JOIN `actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) LEFT JOIN `watchlist` ON (wl_user = 25112844 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) 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 = 37 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 = 38 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1) WHERE rc_bot = 0 AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND ((ores_goodfaith_cls.oresc_probability BETWEEN 0 AND 0.358)) AND (rc_type NOT IN (3, 5)) AND (rc_namespace = '0') AND (rc_timestamp >= '20200404124958') AND rc_new IN (0, 1) ORDER BY rc_timestamp DESC LIMIT 250 /* 391c48d0e90517b73df99837c1fa8413 db1099:3311 enwiki 3s */;
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+
| id   | select_type        | table              | type   | possible_keys                                                                                                                    | key                   | key_len | ref                                                                   | rows    | Extra       |
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+
|    1 | PRIMARY            | recentchanges      | range  | rc_timestamp,new_name_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_this_oldid | rc_timestamp          | 16      | NULL                                                                  | 4816226 | Using where |
|    1 | PRIMARY            | comment_rc_comment | eq_ref | PRIMARY                                                                                                                          | PRIMARY               | 8       | enwiki.recentchanges.rc_comment_id                                    |       1 |             |
|    1 | PRIMARY            | actor_rc_user      | eq_ref | PRIMARY                                                                                                                          | PRIMARY               | 8       | enwiki.recentchanges.rc_actor                                         |       1 |             |
|    1 | PRIMARY            | watchlist          | eq_ref | wl_user,namespace_title,wl_user_notificationtimestamp                                                                            | wl_user               | 265     | const,enwiki.recentchanges.rc_namespace,enwiki.recentchanges.rc_title |       1 |             |
|    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 | Using where |
|    2 | DEPENDENT SUBQUERY | change_tag         | ref    | change_tag_rc_tag_id,change_tag_tag_id_id                                                                                        | change_tag_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 |             |
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+
Marostegui added a comment.May 5 2020, 11:51 AM

@Ladsgroup to make this even more interesting, I just realised that db1105:3311 doesn't have any of the tmp_ indexes.

I am going to analyze all the slow queries capture on the last 24h on db1099:3311 and check if we still have something using tmp_3 (tmp2 and tmp1 are gone) there and compare those plans.

Ladsgroup added a comment.May 5 2020, 11:53 AM
In T206103#6108179, @Marostegui wrote:

@Ladsgroup to make this even more interesting, I just realised that db1105:3311 doesn't have any of the tmp_ indexes.

I am going to analyze all the slow queries capture on the last 24h on db1099:3311 and check if we still have something using tmp_3 (tmp2 and tmp1 are gone) there and compare those plans.

Thanks!

Marostegui added a comment.May 5 2020, 12:46 PM

I found a few queries only, and the query plans are very similar.
The query time with and without the index almost doesn't change, or just changes around 0.2 seconds in a 3-4 seconds query.

The only query I have found that works a lot better using tmp_3 is:

SELECT /* SpecialRecentChanges::doMainQuery */ /*! STRAIGHT_JOIN */ 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, 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`, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor, wl_user, wl_notificationtimestamp, 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` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) LEFT JOIN `user` ON ((actor_rc_user.actor_user = user_id)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `watchlist` ON (wl_user = 25958220 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) 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 = 37 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 = 38 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1) WHERE ((actor_rc_user.actor_user IS NULL) OR (NOT ( (user_editcount >= 10) AND (user_registration <= '20200501090228') ))) AND rc_bot = 0 AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 1) AND (rc_type != 3) AND (rc_type != 6) AND (rc_source != 'wb') AND ((ores_damaging_cls.oresc_probability BETWEEN 0.919 AND 1)) AND (rc_type NOT IN (3, 5)) AND (wl_user IS NULL) AND (rc_namespace = '0') AND (rc_timestamp >= '20200502090228') AND rc_new IN (0, 1) ORDER BY rc_timestamp DESC LIMIT 500 /* 6c2b875866d2b64aa4ad7bebf2ff3e61 db1099:3311 enwiki 13s */;

Using the tmp_3 index it runs in half the time (11 seconds vs 22). The host that doesn't have the index (db1105) uses rc_timestamp instead, which doesn't seem to be the fastest one. Using new_name_timestamp makes the query run a lot faster than even with the tmp_3 index.
4 seconds vs 11.

Marostegui changed the status of subtask Restricted Task from Open to Stalled.May 5 2020, 1:11 PM
Marostegui added a comment.May 5 2020, 1:18 PM

@Ladsgroup right now this is the situation with both recentchanges hosts on enwiki.
db1099 only has tmp_3 index
db1105 has neither tmp_2 nor tmp_3. So 50% of those requests hitting the recentchanges groups on enwiki are not using tmp_3.
Let's give it a few more days before going ahead and dropping both everywhere.

Marostegui added a comment.May 8 2020, 9:28 AM

So, I haven't found any more queries that regress without those two indexes being removed.
The only query I have found that uses tmp_3 on one host and rc_timestamp on the host that doesn't have tmp_3 doesn't have a significant change: 6.50 sec vs 6.56 sec so it is assumible

Marostegui added a comment.May 8 2020, 9:30 AM

@Ladsgroup can you run your script against this table to get an overview of how messy this is in prod?

Ladsgroup added a comment.May 8 2020, 2:42 PM
In T206103#6118337, @Marostegui wrote:

@Ladsgroup can you run your script against this table to get an overview of how messy this is in prod?

Surio.

"recentchanges tmp_2 index-mismatch-prod-extra": {
     "s1": [
         "db1083.eqiad.wmnet",
         "db1134.eqiad.wmnet",
         "db1089.eqiad.wmnet",
         "db1080.eqiad.wmnet",
         "db1107.eqiad.wmnet",
         "db1119.eqiad.wmnet"
     ],
     "s3": [
         "db1075.eqiad.wmnet",
         "db1112.eqiad.wmnet"
     ],
     "s6": [
         "db1131.eqiad.wmnet",
         "db1085.eqiad.wmnet",
         "db1113.eqiad.wmnet",
         "db1093.eqiad.wmnet",
         "db1098.eqiad.wmnet",
         "db1096.eqiad.wmnet",
         "db1088.eqiad.wmnet"
     ],
     "s7": [
         "db1086.eqiad.wmnet",
         "db1098.eqiad.wmnet",
         "db1094.eqiad.wmnet",
         "db1079.eqiad.wmnet",
         "db1090.eqiad.wmnet",
         "db1136.eqiad.wmnet",
         "db1101.eqiad.wmnet"
     ],
     "s8": [
         "db1099.eqiad.wmnet",
         "db1101.eqiad.wmnet"
     ]
 },
 "recentchanges tmp_3 index-mismatch-prod-extra": {
     "s1": [
         "db1083.eqiad.wmnet",
         "db1134.eqiad.wmnet",
         "db1099.eqiad.wmnet",
         "db1089.eqiad.wmnet",
         "db1080.eqiad.wmnet",
         "db1107.eqiad.wmnet",
         "db1119.eqiad.wmnet"
     ],
     "s3": [
         "db1075.eqiad.wmnet",
         "db1112.eqiad.wmnet"
     ],
     "s6": [
         "db1131.eqiad.wmnet",
         "db1085.eqiad.wmnet",
         "db1113.eqiad.wmnet",
         "db1093.eqiad.wmnet",
         "db1098.eqiad.wmnet",
         "db1096.eqiad.wmnet",
         "db1088.eqiad.wmnet"
     ],
     "s7": [
         "db1086.eqiad.wmnet",
         "db1098.eqiad.wmnet",
         "db1094.eqiad.wmnet",
         "db1079.eqiad.wmnet",
         "db1090.eqiad.wmnet",
         "db1136.eqiad.wmnet",
         "db1101.eqiad.wmnet"
     ],
     "s8": [
         "db1099.eqiad.wmnet",
         "db1101.eqiad.wmnet"
     ]
 },

These are the only drifts found on rc table.

Krinkle unsubscribed.May 8 2020, 4:46 PM
Stashbot added a comment.May 27 2020, 5:17 AM

Mentioned in SAL (#wikimedia-operations) [2020-05-27T05:17:10Z] <marostegui> Remove tmp_3 key from enwiki.recentchanges on db1099:3311 - T206103

Marostegui updated the task description. (Show Details)Jun 5 2020, 12:01 PM
Marostegui updated the task description. (Show Details)Jun 8 2020, 9:46 AM
Marostegui updated the task description. (Show Details)Jun 8 2020, 9:57 AM
Marostegui updated the task description. (Show Details)Jun 8 2020, 11:31 AM
Stashbot added a comment.Jun 9 2020, 6:40 AM

Mentioned in SAL (#wikimedia-operations) [2020-06-09T06:40:22Z] <marostegui> Deploy schema change on s2 T206103

Marostegui updated the task description. (Show Details)Jun 9 2020, 6:57 AM
Stashbot added a comment.Jun 9 2020, 10:07 AM

Mentioned in SAL (#wikimedia-operations) [2020-06-09T10:07:50Z] <marostegui> Deploy schema change on s7 T206103

Marostegui updated the task description. (Show Details)Jun 9 2020, 10:28 AM
Marostegui updated the task description. (Show Details)Jun 10 2020, 5:09 AM
Stashbot added a comment.Jun 10 2020, 5:10 AM

Mentioned in SAL (#wikimedia-operations) [2020-06-10T05:10:54Z] <marostegui> Deploy schema change on s3 master with 2 minutes sleep between wikis - T206103

Marostegui added a comment.Jun 10 2020, 7:11 AM

I am altering s3 directly on the master, with a big sleep between alters. This will take around 30 hours to complete.

Marostegui closed this task as Resolved.Jun 11 2020, 11:23 AM
Marostegui claimed this task.
Marostegui updated the task description. (Show Details)

This is finally all done!

Aklapper removed subscribers: Anomie, • Banyek.Oct 16 2020, 5:41 PM
Ladsgroup closed subtask T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas as Resolved.May 15 2021, 11:43 AM
Marostegui changed the status of subtask Restricted Task from Stalled to Open.Sep 30 2021, 7:10 AM
Ladsgroup mentioned this in T312538: Collect list of tickets done for fixing core drifts .Jul 7 2022, 1:07 PM
Maintenance_bot moved this task from In progress to Done on the DBA board.Jul 7 2022, 1:31 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