Page MenuHomePhabricator

Schema change to drop three indexes from wb_changes
Closed, ResolvedPublic

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

LSobanski triaged this task as Medium priority.Sep 29 2020, 4:31 PM
Marostegui added a subscriber: Marostegui.

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

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.

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

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

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

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

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#

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

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

Marostegui changed the task status from Open to Stalled.Oct 5 2020, 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
Marostegui changed the task status from Stalled to Open.Oct 28 2020, 11:21 AM

Mentioned in SAL (#wikimedia-operations) [2020-10-29T09:41:13Z] <marostegui> Deploy schema change on s8 wikidata codfw master (db2079) T264109

Marostegui closed this task as Resolved.EditedOct 29 2020, 9:41 AM

codfw master is done, so this is all finished

# ./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
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