Page MenuHomePhabricator

Schema change for renaming page_timestamp index on revision table to rev_page_timestamp
Closed, ResolvedPublic

Description

Part of T270033: Fix and enforce table prefix usage in columns and indexes in core and T230428: Migrate tables.sql to abstract schema

  1. ALTERs to run: https://gerrit.wikimedia.org/r/c/mediawiki/core/+/674138/6/maintenance/archives/patch-revision-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: public, replicated to the cloud

Progress

  • s2
    • eqiad
    • codfw
  • s3
    • eqiad
    • codfw
  • s4
    • eqiad
    • codfw
  • s5
    • eqiad
    • codfw
  • s6
    • eqiad
    • codfw
  • s7
    • eqiad
    • codfw
  • s8
    • eqiad
    • codfw
  • labswiki
  • labtestwiki

Event Timeline

LSobanski triaged this task as Medium priority.
LSobanski moved this task from Triage to Pending comment on the DBA board.
LSobanski added a subscriber: LSobanski.

Assigning to Stevie to confirm if this can go into Ready.

Kormat moved this task from Pending comment to Ready on the DBA board.
Kormat added a subscriber: Kormat.

Mentioned in SAL (#wikimedia-operations) [2021-06-21T11:51:44Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1135 T283499', diff saved to https://phabricator.wikimedia.org/P16662 and previous config saved to /var/cache/conftool/dbconfig/20210621-115143-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2021-06-21T11:54:42Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1099:3311 T283499', diff saved to https://phabricator.wikimedia.org/P16664 and previous config saved to /var/cache/conftool/dbconfig/20210621-115441-marostegui.json

Altered db1099:3311 and db1135 on enwiki, let's see if we find some queries forcing that index.

root@cumin1001:/home/marostegui/T283499# for i in db1135 db1099:3311; do echo $i; mysql.py -h$i enwiki -e "show create table revision\G" | grep page_timestamp
> done
db1135
  KEY `rev_page_timestamp` (`rev_page`,`rev_timestamp`)
db1099:3311
  KEY `rev_page_timestamp` (`rev_page`,`rev_timestamp`)

Mentioned in SAL (#wikimedia-operations) [2021-06-22T07:28:29Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1169 T283499', diff saved to https://phabricator.wikimedia.org/P16678 and previous config saved to /var/cache/conftool/dbconfig/20210622-072828-marostegui.json

So far I haven't seen any errors coming from queries forcing this specific index, so I have change it on db1169 as well. If nothing happens in the next 24h, we can proceed with this task:

root@db1169.eqiad.wmnet[enwiki]> DROP  INDEX page_timestamp ON  /*_*/revision;
Query OK, 0 rows affected (0.194 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1169.eqiad.wmnet[enwiki]> CREATE INDEX rev_page_timestamp ON  /*_*/revision (rev_page, rev_timestamp);
Stage: 1 of 1 'Altering table'    100% of stage donee


Query OK, 0 rows affected (1 hour 9.347 sec)
Records: 0  Duplicates: 0  Warnings: 0

s1 eqiad

  • dbstore1003
  • db1184
  • db1169
  • db1164
  • db1163
  • db1154
  • db1140
  • db1139
  • db1135
  • db1134
  • db1133
  • db1119
  • db1118
  • db1106
  • db1105
  • db1099
  • clouddb1021
  • clouddb1017
  • clouddb1013
  • db2146
  • db2145
  • db2141
  • db2130
  • db2116
  • db2112
  • db2103
  • db2102
  • db2097
  • db2094
  • db2092
  • db2088
  • db2085
  • db2072
  • db2071

eqiad is done, waiting for the switch back to complete codfw.

Marostegui changed the task status from Open to Stalled.Jul 8 2021, 10:49 AM
Marostegui moved this task from In progress to Blocked on the DBA board.
Marostegui changed the task status from Stalled to Open.Sep 28 2021, 5:28 AM

Mentioned in SAL (#wikimedia-operations) [2021-09-28T09:23:57Z] <marostegui> Deploy schema change on s2 codfw (lag will show up) T283499

Mentioned in SAL (#wikimedia-operations) [2021-09-28T09:26:39Z] <marostegui> Deploy schema change on s4 codfw (lag will show up) T283499

Mentioned in SAL (#wikimedia-operations) [2021-09-28T10:00:55Z] <marostegui> Deploy schema change on s7 codfw (lag will show up) T283499

Mentioned in SAL (#wikimedia-operations) [2021-09-28T10:01:42Z] <marostegui> Deploy schema change on s5 codfw (lag will show up) T283499

Mentioned in SAL (#wikimedia-operations) [2021-09-28T11:25:47Z] <marostegui> Deploy schema change on s6 codfw (lag will show up) T283499

Mentioned in SAL (#wikimedia-operations) [2021-09-28T11:29:57Z] <marostegui> Deploy schema change on s3 codfw (lag will show up) T283499

Mentioned in SAL (#wikimedia-operations) [2021-09-29T06:15:12Z] <marostegui> Deploy schema change on s8 codfw (lag will show up) T283499

Marostegui updated the task description. (Show Details)

All done