Page MenuHomePhabricator
Create Task
Maniphest T165246

Add term_full_entity_id column to wb_terms table on testwikidatawiki
Closed, ResolvedPublic
Actions

Description

We need to add the term_full_entity_id column to wb_terms table on test wikidata (testwikidatawiki, which is on s3)

select count(*) from wb_terms;
+----------+
| count(*) |
+----------+
|   192947 |
+----------+

In this case, not sure we can directly apply this change using sql.php script (https://wikitech.wikimedia.org/wiki/How_to_do_a_schema_change#sql.php) which we use for smaller changes or adding tables where replication lag won't be a concern.

maybe can use OSC for this? or whatever the dbas think is best.

Related Objects

Event Timeline

aude created this task.May 14 2017, 4:55 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 14 2017, 4:55 PM
aude mentioned this in T162539: Deploy schema change for adding term_full_entity_id column to wb_terms table.May 14 2017, 4:56 PM
• Marostegui added a project: DBA.May 16 2017, 6:22 AM
• Marostegui added subscribers: jcrespo, • Marostegui.

Hello,

I have tested this change on a codfw slave of s3 and it took 4 seconds.
After doing a select count on the table and after the first ALTER, a second alter took 3 seconds.

We can probably go ahead and do the ALTER directly on the master one day early in the morning. The s3 slowest slaves (64G) might take a couple of seconds more I guess /cc @jcrespo
The table is pretty tiny, only 68M on disk.

jcrespo added a comment.May 16 2017, 7:15 AM

The problem usually is not the alter size, but the metadata locking, which creates way more contention.

• Marostegui added a comment.May 16 2017, 7:21 AM

Ah right! I wasn't expecting a test table to have such issues, but yeah, that could be!

• Marostegui claimed this task.May 16 2017, 10:06 AM

Given that s3 codfw > eqiad replication is reseted for other maintenance - I will execute this on the codfw master, and let ir replicate.
For the eqiad hosts I will do it one by one just to be on the safe side.

Stashbot subscribed.May 16 2017, 10:49 AM

Mentioned in SAL (#wikimedia-operations) [2017-05-16T10:49:22Z] <marostegui> Deploy schema change on testwikidatawiki.wb_terms on s3 codfw master - T165246

• Marostegui added a comment.May 16 2017, 10:55 AM

This has been deployed on codfw master and replicated downstream:

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s3.hosts | grep codfw | awk -F " " '{print $1}'`; do echo $i; mysql -h$i --skip-ssl testwikidatawiki -e "show create table wb_terms\G" | egrep "term_full_entity_id|term_search_full";done
dbstore2001.codfw.wmnet
dbstore2002.codfw.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db2036.codfw.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db2043.codfw.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db2050.codfw.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db2057.codfw.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db2018.codfw.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

dbstore2001 is the delayed slave, and will get it tomorrow.

I am going to run it on eqiad host by host now.

• Marostegui added a comment.May 16 2017, 11:08 AM

Deployed on db1069 (sanitarium) and replicated to labsdb1001 and labsdb1003

root@neodymium:/home/marostegui/git/software/dbtools#  mysql --skip-ssl -hdb1069 -P3313 testwikidatawiki -e "show create table wb_terms\G" |egrep "term_full_entity_id|term_search_full"
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

Deployed on db1095 (sanitarium2) and replicated to labsdb1009, labsdb1010 and labsdb1011.

root@neodymium:/home/marostegui/git/software/dbtools#  mysql --skip-ssl -hdb1095 testwikidatawiki -e "show create table wb_terms\G" |egrep "term_full_entity_id|term_search_full"
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

Deployed on dbstore1002

root@neodymium:/home/marostegui/git/software/dbtools#  mysql --skip-ssl -hdbstore1002 testwikidatawiki -e "show create table wb_terms\G" |egrep "term_full_entity_id|term_search_full"
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

Deployed on dbstore1001

root@neodymium:/home/marostegui/git/software/dbtools#  mysql --skip-ssl -hdbstore1001 testwikidatawiki -e "show create table wb_terms\G" |egrep "term_full_entity_id|term_search_full"
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

Going for production slaves now.

• Marostegui added a comment.May 16 2017, 11:12 AM

db1015:

root@neodymium:/home/marostegui/git/software/dbtools# mysql --skip-ssl -hdb1015 testwikidatawiki -e "show create table wb_terms\G" |egrep "term_full_entity_id|term_search_full"
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

db1044:

root@neodymium:/home/marostegui/git/software/dbtools# mysql --skip-ssl -hdb1044 testwikidatawiki -e "show create table wb_terms\G" |egrep "term_full_entity_id|term_search_full"
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
• Marostegui added a comment.May 16 2017, 11:21 AM

db1078:

root@neodymium:/home/marostegui/git/software/dbtools# mysql --skip-ssl -hdb1078 testwikidatawiki -e "show create table wb_terms\G" |egrep "term_full_entity_id|term_search_full"
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

db1077:

root@neodymium:/home/marostegui/git/software/dbtools# mysql --skip-ssl -hdb1077 testwikidatawiki -e "show create table wb_terms\G" |egrep "term_full_entity_id|term_search_full"
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

db1035:

root@neodymium:/home/marostegui/git/software/dbtools# mysql --skip-ssl -hdb1035 testwikidatawiki -e "show create table wb_terms\G" |egrep "term_full_entity_id|term_search_full"
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

db1038:

root@neodymium:/home/marostegui/git/software/dbtools# mysql --skip-ssl -hdb1038 testwikidatawiki -e "show create table wb_terms\G" |egrep "term_full_entity_id|term_search_full"
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

db1075 (eqiad master):

root@neodymium:/home/marostegui/git/software/dbtools# mysql --skip-ssl -hdb1075 testwikidatawiki -e "show create table wb_terms\G" |egrep "term_full_entity_id|term_search_full"
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
• Marostegui closed this task as Resolved.May 16 2017, 11:29 AM

I believe everything is done now (remember, dbstore2001 will get the alter tomorrow as it is our delayed slave, if you can wait 24 hours until it gets it, that would be nice):

root@neodymium:/home/marostegui/git/software/dbtools# cat s3.hosts | grep -v labs | while read host port; do echo $host; mysql --skip-ssl -h$host -P$port testwikidatawiki -e "show create table wb_terms\G" | egrep "term_full_entity_id|term_search_full";done
dbstore2001.codfw.wmnet
dbstore2002.codfw.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db2036.codfw.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db2043.codfw.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db2050.codfw.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db2057.codfw.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db2018.codfw.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db1069.eqiad.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
dbstore1001.eqiad.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
dbstore1002.eqiad.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db1095.eqiad.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db1015.eqiad.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db1035.eqiad.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db1038.eqiad.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db1044.eqiad.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db1077.eqiad.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db1078.eqiad.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
db1075.eqiad.wmnet
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

And the labs servers:

labsdb1001
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

labsdb1003
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

labsdb1009
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

labsdb1010
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))

labsdb1011
  `term_full_entity_id` varbinary(32) DEFAULT NULL,
  KEY `term_full_entity` (`term_full_entity_id`),
  KEY `term_search_full` (`term_language`,`term_full_entity_id`,`term_type`,`term_search_key`(16))
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL