Page MenuHomePhabricator

Schema change to drop three indexes from wb_changes
Open, Stalled, MediumPublic

Description

This is the follow up to T262856: Investigate indexes of wb_changes and T264084: Drop unused indexes of wb_changes

  1. ALTERs to run: https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/630224/2/repo/sql/mysql/archives/patch-wb_changes-drop-change_type_index.sql
  2. Where to run those changes: wikibaserepo.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.

Notes:

  • The table is empty on commons (and very likely testcommons), I assume it could be done directly
  • The table on wikidata has a pretty high write.

Progress:

  • s4 (testcommonswiki, commonswiki)
  • s3 (testwikidatawiki)
  • s8 (wikidatawiki) T264109#6503957

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptTue, Sep 29, 4:25 PM
LSobanski triaged this task as Medium priority.Tue, Sep 29, 4:31 PM
Marostegui added a subscriber: Marostegui.

Thanks for this Amir.
I will get it done before the switch back!

Marostegui moved this task from Triage to Next on the DBA board.Tue, Sep 29, 4:39 PM
Marostegui added a comment.EditedWed, Sep 30, 6:03 AM

Progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db2100
  • db2094
  • db2091
  • db2086
  • db2085
  • db2084
  • db2083
  • db2082
  • db2081
  • db2080
  • db2079
  • db1126
  • db1124
  • db1116
  • db1114
  • db1111
  • db1109
  • db1104
  • db1101
  • db1099
  • db1092
  • db1087

@Ladsgroup I am going to deploy this first to a few hosts in codfw (as it is active now) to make sure we are totally fine, performance wise.

Marostegui moved this task from Next to In progress on the DBA board.Wed, Sep 30, 6:25 AM
Marostegui moved this task from Backlog to In progress on the Blocked-on-schema-change board.

Thank you so much <3

No major regressions observed on the altered hosts on codfw in s8. I am going to proceed with some more

Marostegui updated the task description. (Show Details)Wed, Sep 30, 12:33 PM

As the table was empty on testcommonswiki, I have deployed it there.

Marostegui updated the task description. (Show Details)Wed, Sep 30, 12:35 PM

Mentioned in SAL (#wikimedia-operations) [2020-09-30T12:39:00Z] <marostegui> Deploy schema change on db2080, db2081 T264109

Ladsgroup added a comment.EditedWed, Sep 30, 12:42 PM

As the table was empty on testcommonswiki, I have deployed it there.

commonswiki (s4) and testwikidatawiki (s3) should have it too. The former is empty and latter is tiny.

Yep, deployed also on commons.
Thanks for pointing to s3 testwikidatawiki, I forgot that one existed too :)

Marostegui updated the task description. (Show Details)Wed, Sep 30, 12:46 PM

s4 is fully done:

# ./section s4 | grep -v "labs" | while read host port ; do   mysql.py -h$host:$port -A testcommonswiki -e "show create table wb_changes\G" | egrep -i "wb_changes_change_type|wb_changes_change_object_id|wb_changes_change_user_id";done
# ./section s4 | grep -v "labs" | while read host port ; do   mysql.py -h$host:$port -A commonswiki -e "show create table wb_changes\G" | egrep -i "wb_changes_change_type|wb_changes_change_object_id|wb_changes_change_user_id";done
root@cumin1001:/home/marostegui#
Marostegui updated the task description. (Show Details)Wed, Sep 30, 12:56 PM

Mentioned in SAL (#wikimedia-operations) [2020-10-01T05:29:28Z] <marostegui> Deploy schema change on s3 (testwikidatawiki) T264109

Marostegui updated the task description. (Show Details)Thu, Oct 1, 5:29 AM

@Ladsgroup this change is deployed to codfw. The master is impossible to alter online, so it will need to get done once we are back in eqiad.
I am going to monitor the change during the next few hours, and if nothing shows up, I will deploy it to eqiad as well.

Thanks! It makes sense.

Marostegui changed the task status from Open to Stalled.Mon, Oct 5, 5:36 AM
Marostegui moved this task from In progress to Blocked external/Not db team on the DBA board.

All done except codfw master (db2079) as it is the active one.
I will complete that one once we are back in eqiad. Stalling until then.

root@cumin1001:/home/marostegui# ./section s8  | while read host port ; do echo "$host:$port" ;   mysql.py -h$host:$port -A wikidatawiki -e "show create table wb_changes\G" | egrep -i "wb_changes_change_type|wb_changes_change_object_id|wb_changes_change_user_id";done
labsdb1012.eqiad.wmnet:3306
labsdb1011.eqiad.wmnet:3306
labsdb1010.eqiad.wmnet:3306
labsdb1009.eqiad.wmnet:3306
dbstore1005.eqiad.wmnet:3318
db2100.codfw.wmnet:3318
db2094.codfw.wmnet:3318
db2091.codfw.wmnet:3306
db2086.codfw.wmnet:3318
db2085.codfw.wmnet:3318
db2084.codfw.wmnet:3306
db2083.codfw.wmnet:3306
db2082.codfw.wmnet:3306
db2081.codfw.wmnet:3306
db2080.codfw.wmnet:3306
db2079.codfw.wmnet:3306
  KEY `wb_changes_change_type` (`change_type`),
  KEY `wb_changes_change_object_id` (`change_object_id`),
  KEY `wb_changes_change_user_id` (`change_user_id`),
db1126.eqiad.wmnet:3306
db1124.eqiad.wmnet:3318
db1116.eqiad.wmnet:3318
db1114.eqiad.wmnet:3306
db1111.eqiad.wmnet:3306
db1109.eqiad.wmnet:3306
db1104.eqiad.wmnet:3306
db1101.eqiad.wmnet:3318
db1099.eqiad.wmnet:3318
db1092.eqiad.wmnet:3306
db1087.eqiad.wmnet:3306