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

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.

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

s6 progress

  • dbstore1005
  • db2141
  • db2129
  • db2124
  • db2117
  • db2114
  • db2097
  • db2089
  • db2087
  • db2076
  • db1140
  • db1139
  • db1131
  • db1113
  • db1098
  • db1096
  • db1093
  • db1088
  • db1085

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);

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)

Nothing on logs regarding namespace_title index.

s5 progress

  • dbstore1003
  • db1150
  • db1145
  • db1144
  • db1130
  • db1113
  • db1110
  • db1100
  • db1096
  • db1082

s2 progress

  • dbstore1004
  • db1146
  • db1129
  • db1122
  • db1105
  • db1095
  • db1090
  • db1076
  • db1074

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

s4 progress

  • dbstore1004
  • db1150
  • db1149
  • db1148
  • db1147
  • db1146
  • db1145
  • db1144
  • db1143
  • db1142
  • db1141
  • db1138
  • db1121
  • db1081

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

s7 eqiad progress

  • dbstore1003
  • db1136
  • db1127
  • db1116
  • db1101
  • db1098
  • db1094
  • db1090
  • db1086
  • db1079

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

s8 progress

  • dbstore1005
  • db1126
  • db1116
  • db1114
  • db1111
  • db1109
  • db1104
  • db1101
  • db1099
  • db1092
  • db1087

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

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.

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

s3 eqiad progress

  • dbstore1004
  • db1123
  • db1112
  • db1095
  • db1078
  • db1075
Marostegui updated the task description. (Show Details)

This is all done