Page MenuHomePhabricator

Schema change for renaming namespace_title index on watchlist
Closed, ResolvedPublic

Description

(Implementing T266228: Rename namespace_title index on watchlist)

  1. ALTERs to run: https://gerrit.wikimedia.org/r/c/mediawiki/core/+/641177/1/maintenance/archives/patch-watchlist-namespace_title-rename-index.sql
  2. Where to run those changes: all.dblist
  3. When to run those changes: At any time
  4. If the schema change is backwards compatible: Yes
  5. If the schema change has been tested already on some of the test/beta wikis: Tested in beta cluster.
  6. if the data should be made available on the labs replicas and/or dumps: Yes, data in this table is public.

Progress

  • s10 (wikitech)
    • eqiad
    • codfw
  • labstestwiki (clouddb2001-dev.codfw.wmnet)

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 17 2020, 9:36 AM

Renaming an index isn't supported on mariadb 10.1 or 10.4. It has been added to 10.5 (https://mariadb.com/kb/en/mariadb-1052-release-notes/) but we are not there yet. So for now, this schema change will need to be a DROP+CREATE index.

Renaming an index isn't supported on mariadb 10.1 or 10.4. It has been added to 10.5 (https://mariadb.com/kb/en/mariadb-1052-release-notes/) but we are not there yet. So for now, this schema change will need to be a DROP+CREATE index.

The the linked SQL should be good.

Ladsgroup updated the task description. (Show Details)Nov 17 2020, 10:17 AM
Marostegui triaged this task as Medium priority.
Marostegui moved this task from Backlog to In progress on the Blocked-on-schema-change board.
Marostegui added a project: DBA.
Marostegui moved this task from Triage to In progress on the DBA board.

I haven't found anything specifically targeting this index with https://codesearch.wmcloud.org/search/?q=namespace_title&i=nope&files=&repos= (like USE INDEX or FORCE INDEX).

Marostegui updated the task description. (Show Details)EditedNov 24 2020, 9:19 AM

s6 progress

  • dbstore1005
  • db2141
  • db2129
  • db2124
  • db2117
  • db2114
  • db2097
  • db2089
  • db2087
  • db2076
  • db1140
  • db1139
  • db1131
  • db1113
  • db1098
  • db1096
  • db1093
  • db1088
  • db1085
Marostegui updated the task description. (Show Details)Nov 24 2020, 9:20 AM

Mentioned in SAL (#wikimedia-operations) [2020-11-24T09:23:52Z] <marostegui> Deploy schema change on db2114 and db1096:3316 - T268004

For big wikis this alter requires depooling, as while the index isn't present some queries get full scans:

+------+-------------+------------------+--------+---------------+---------+---------+------------------------+----------+-----------------------------------------------------------+
| id   | select_type | table            | type   | possible_keys | key     | key_len | ref                    | rows     | Extra                                                     |
+------+-------------+------------------+--------+---------------+---------+---------+------------------------+----------+-----------------------------------------------------------+
|    1 | SIMPLE      | watchlist        | index  | NULL          | wl_user | 265     | NULL                   | 34709560 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | watchlist_expiry | eq_ref | PRIMARY       | PRIMARY | 4       | frwiki.watchlist.wl_id | 1        | Using where                                               |
+------+-------------+------------------+--------+---------------+---------+---------+------------------------+----------+-----------------------------------------------------------+

Doing everything on the same transaction fixes it: alter table watchlist drop key namespace_title, add key wl_namespace_title (wl_namespace, wl_title);

Marostegui updated the task description. (Show Details)Nov 24 2020, 2:30 PM

s6 is fully done, waiting for db1139 which is down at the moment, but doesn't receive user traffic.
Let's give it 24h to see if we find issues (FORCE INDEX or USE INDEX statements)

Marostegui updated the task description. (Show Details)Nov 24 2020, 3:03 PM

Nothing on logs regarding namespace_title index.

Marostegui added a comment.EditedNov 25 2020, 6:54 AM

s5 progress

  • dbstore1003
  • db1150
  • db1145
  • db1144
  • db1130
  • db1113
  • db1110
  • db1100
  • db1096
  • db1082
Marostegui updated the task description. (Show Details)Nov 25 2020, 6:54 AM
Marostegui updated the task description. (Show Details)Nov 25 2020, 7:26 AM
Marostegui updated the task description. (Show Details)
Marostegui updated the task description. (Show Details)Nov 25 2020, 7:43 AM
Marostegui added a comment.EditedNov 25 2020, 8:07 AM

s2 progress

  • dbstore1004
  • db1146
  • db1129
  • db1122
  • db1105
  • db1095
  • db1090
  • db1076
  • db1074
Marostegui updated the task description. (Show Details)Nov 25 2020, 1:02 PM

Mentioned in SAL (#wikimedia-operations) [2020-11-25T13:14:31Z] <marostegui> Deploy schema change on commonswiki.watchlist on s4 codfw - there will be lag on s4 codfw - T268004

Marostegui updated the task description. (Show Details)Nov 25 2020, 2:02 PM
Marostegui added a comment.EditedNov 25 2020, 2:06 PM

s4 progress

  • dbstore1004
  • db1150
  • db1149
  • db1148
  • db1147
  • db1146
  • db1145
  • db1144
  • db1143
  • db1142
  • db1141
  • db1138
  • db1121
  • db1081
Marostegui updated the task description. (Show Details)Nov 25 2020, 2:07 PM
Marostegui updated the task description. (Show Details)Nov 25 2020, 2:24 PM
Marostegui updated the task description. (Show Details)Nov 26 2020, 8:06 AM

Mentioned in SAL (#wikimedia-operations) [2020-11-26T08:08:27Z] <marostegui> Deploy schema change on s7 codfw - there will be lag on s7 codfw - T268004

Marostegui updated the task description. (Show Details)Nov 26 2020, 8:26 AM
Marostegui added a comment.EditedNov 26 2020, 8:48 AM

s7 eqiad progress

  • dbstore1003
  • db1136
  • db1127
  • db1116
  • db1101
  • db1098
  • db1094
  • db1090
  • db1086
  • db1079
Marostegui updated the task description. (Show Details)Nov 26 2020, 8:53 AM
Marostegui updated the task description. (Show Details)Nov 26 2020, 11:37 AM

Mentioned in SAL (#wikimedia-operations) [2020-11-26T11:40:53Z] <marostegui> Deploy schema change on s8 codfw - there will be lag on s8 codfw - T268004

Marostegui updated the task description. (Show Details)Nov 26 2020, 11:58 AM
Marostegui added a comment.EditedNov 26 2020, 12:41 PM

s8 progress

  • dbstore1005
  • db1126
  • db1116
  • db1114
  • db1111
  • db1109
  • db1104
  • db1101
  • db1099
  • db1092
  • db1087
Marostegui updated the task description. (Show Details)Nov 27 2020, 7:00 AM

Mentioned in SAL (#wikimedia-operations) [2020-11-30T07:11:40Z] <marostegui> Deploy schema change on s1 codfw - T268004

Marostegui updated the task description. (Show Details)Nov 30 2020, 7:31 AM
Marostegui added a comment.EditedNov 30 2020, 7:34 AM

s1 eqiad progress

  • dbstore1003
  • db1140
  • db1133
  • db1139 T268004#6655792
  • db1135
  • db1134
  • db1119
  • db1118
  • db1106
  • db1105
  • db1099
  • db1089
  • db1084
  • db1083

From s1 only db1139 is pending the schema change - as it is now under maintenance due to HW issues.

Marostegui updated the task description. (Show Details)Nov 30 2020, 2:21 PM

Mentioned in SAL (#wikimedia-operations) [2020-11-30T14:23:41Z] <marostegui> Deploy schema change on s3 codfw, lag will show up on s3 codfw T268004

Marostegui updated the task description. (Show Details)Nov 30 2020, 3:16 PM
Marostegui added a comment.EditedDec 1 2020, 6:08 AM

s3 eqiad progress

  • dbstore1004
  • db1123
  • db1112
  • db1095
  • db1078
  • db1075

db1139:3311 done

Marostegui updated the task description. (Show Details)Dec 1 2020, 10:12 AM
Marostegui updated the task description. (Show Details)
Marostegui closed this task as Resolved.Dec 1 2020, 11:23 AM
Marostegui updated the task description. (Show Details)

This is all done