Page MenuHomePhabricator

recentchanges table indexes: tmp1, tmp2 and tmp3
Open, MediumPublic

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

Related Objects

StatusSubtypeAssignedTask
OpenNone
OpenNone
OpenNone
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedReedy
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
DeclinedNone
StalledNone
OpenNone
ResolvedLadsgroup
ResolvedMarostegui

Event Timeline

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)

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
Restricted Application added a project: Growth-Team. · View Herald TranscriptJul 18 2019, 10:16 PM
JTannerWMF added subscribers: Catrope, JTannerWMF.

Hey @Catrope, we need you to triage this task

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.

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

@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.

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

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.

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

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

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)

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

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

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.Mon, May 4, 1:44 PM
Marostegui moved this task from Triage to In progress on the DBA board.

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 |             |
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+

@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 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!

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.Tue, May 5, 1:11 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.

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

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

@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 removed a subscriber: Krinkle.Fri, May 8, 4:46 PM

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