Remove partitions from enwiktionary.templatelinks in s2
Open, NormalPublic

Description

The hosts in codfw have the table enwiktionary.templatelinks partitioned whereas eqiad hosts don't.
That also isn't what it is specified on: https://phabricator.wikimedia.org/diffusion/MW/browse/master/maintenance/tables.sql

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s2.hosts  | awk -F " " '{print $1}' | egrep -v "db1069|db2035|db1036|dbstore2002|labs*|db1095"`; do echo $i; mysql --skip-ssl -h$i enwiktionary -e "show create table templatelinks\G" | grep PARTITION| wc -l;done
dbstore2001.codfw.wmnet
5
db2041.codfw.wmnet
5
db2049.codfw.wmnet
5
db2056.codfw.wmnet
5
db2063.codfw.wmnet
5
db2064.codfw.wmnet
5
db2017.codfw.wmnet
5
dbstore1002.eqiad.wmnet
0
dbstore1001.eqiad.wmnet
0
db1021.eqiad.wmnet
0
db1024.eqiad.wmnet
0
db1047.eqiad.wmnet
0
db1054.eqiad.wmnet
5
db1060.eqiad.wmnet
0
db1063.eqiad.wmnet
0
db1067.eqiad.wmnet
0
db1074.eqiad.wmnet
0
db1076.eqiad.wmnet
0
db1090.eqiad.wmnet
0
db1018.eqiad.wmnet
0
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 24 2016, 10:28 PM
Marostegui triaged this task as "Normal" priority.Dec 24 2016, 10:28 PM
Marostegui claimed this task.
Marostegui moved this task from Triage to In progress on the DBA board.

@Anomie we were wondering if you'd have some thoughts about these partitions on this particular wiki.
We were considering deleting them because they look like a mistake, and they are only on codfw (and on rc/api servers), but we were wondering if these partitions are actually intended o just a mistake and that is why they are on all the hosts on codfw?

Do you have some thoughts on this?

Anomie added a comment.Jan 3 2017, 5:27 PM

As far as I know, MediaWiki doesn't specify partitions at all, so it would be something a DBA did at some point. Note the origin of the partitioning probably predates our current DBAs.

In your list I note that db1054.eqiad.wmnet also shows 5 rather than 0, and rOMWC817780f64df7: depool s2 db1054, s3 db1027, s4 db1056, s5 db1037 for codfw cloning seems to indicate that s2 in codfw was cloned from db1054. I have no idea why templatelinks might be partitioned on that replica and not elsewhere.

Thanks for your answer and from the clue about codfw cloning, that might explain why codfw has partitions!! Very nice catch!
Maybe db1054 was serving recentchanges at some point before serving API and that is why it has the partitions. The server might have been moved to API without getting rid of the partitions and that's why they remain.

Anomie added a comment.Jan 4 2017, 2:05 PM

The only change to wmf-config/db-eqiad.php in git touching db1054 before the "depool for codfw cloning" was when it was first added in rOMWCd67da49efc12: pool db1054 in s2. It may be that it was itself cloned from some other server that had partitioning.

https://wikitech.wikimedia.org/wiki/Server_admin_log/Archive_25#August_26 seems to indicate it was cloned from db1036. At the time db1036 wasn't in any groups, but earlier that month it filled in for 'vslow, dump' for 10 days. It was apparently the s2 master from 2013-10-18 to 2014-02-10. I see it was also used to clone db1067 on 2014-05-09.

The only change to wmf-config/db-eqiad.php in git touching db1054 before the "depool for codfw cloning" was when it was first added in rOMWCd67da49efc12: pool db1054 in s2. It may be that it was itself cloned from some other server that had partitioning.

Wow, thanks a lot for your help. Very useful!!!. Looks like it is just a chain of cloning from the wrong servers and then in the end cloning a whole DC from that server.

I think I am going to proceed and start removing partitions in codfw from those hosts not serving the "rc" service.

Mentioned in SAL (#wikimedia-operations) [2017-01-17T07:50:47Z] <marostegui> Remove partitions from enwiktionary.templatelinks on dbstore2001 - T154097

dbstore2001:

root@neodymium:/home/marostegui/git# mysql -hdbstore2001.codfw.wmnet enwiktionary -e "show create table templatelinks\G" --skip-ssl
*************************** 1. row ***************************
       Table: templatelinks
Create Table: CREATE TABLE `templatelinks` (
  `tl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `tl_namespace` int(11) NOT NULL DEFAULT '0',
  `tl_title` varbinary(255) NOT NULL DEFAULT '',
  `tl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `tl_from` (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

Change 332441 had a related patch set uploaded (by Marostegui):
db-codfw.php: Depool db2041

https://gerrit.wikimedia.org/r/332441

Change 332441 merged by jenkins-bot:
db-codfw.php: Depool db2041

https://gerrit.wikimedia.org/r/332441

Mentioned in SAL (#wikimedia-operations) [2017-01-17T09:59:43Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2041 - T154097 (duration: 00m 38s)

Mentioned in SAL (#wikimedia-operations) [2017-01-17T10:02:02Z] <marostegui> Remove partitions from enwiktionary.templatelinks on db2041 - T154097

db2041:

root@neodymium:/home/marostegui/git# mysql -hdb2041.codfw.wmnet enwiktionary -e "show create table templatelinks\G" --skip-ssl
*************************** 1. row ***************************
       Table: templatelinks
Create Table: CREATE TABLE `templatelinks` (
  `tl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `tl_namespace` int(11) NOT NULL DEFAULT '0',
  `tl_title` varbinary(255) NOT NULL DEFAULT '',
  `tl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `tl_from` (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2017-01-17T10:58:26Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2041 - T154097 (duration: 00m 38s)

Change 332445 had a related patch set uploaded (by Marostegui):
db-codfw.php: Depool db2049

https://gerrit.wikimedia.org/r/332445

Change 332445 merged by jenkins-bot:
db-codfw.php: Depool db2049

https://gerrit.wikimedia.org/r/332445

Mentioned in SAL (#wikimedia-operations) [2017-01-17T11:13:42Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2049 - T154097 (duration: 00m 38s)

Mentioned in SAL (#wikimedia-operations) [2017-01-17T11:15:01Z] <marostegui> Remove partitions from enwiktionary.templatelinks on db2049 - T154097

db2049:

root@neodymium:/home/marostegui/git# mysql -hdb2049.codfw.wmnet enwiktionary -e "show create table templatelinks\G" --skip-ssl
*************************** 1. row ***************************
       Table: templatelinks
Create Table: CREATE TABLE `templatelinks` (
  `tl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `tl_namespace` int(11) NOT NULL DEFAULT '0',
  `tl_title` varbinary(255) NOT NULL DEFAULT '',
  `tl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `tl_from` (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2017-01-17T12:16:08Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2049 - T154097 (duration: 00m 47s)

Change 332451 had a related patch set uploaded (by Marostegui):
db-codfw.php: Depool db2056

https://gerrit.wikimedia.org/r/332451

Change 332451 merged by jenkins-bot:
db-codfw.php: Depool db2056

https://gerrit.wikimedia.org/r/332451

Mentioned in SAL (#wikimedia-operations) [2017-01-17T12:27:54Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2056 - T154097 (duration: 00m 42s)

Mentioned in SAL (#wikimedia-operations) [2017-01-17T12:29:05Z] <marostegui> Remove partitions from enwiktionary.templatelinks on db2056 - T154097

db2056:

root@neodymium:/home/marostegui/git# mysql -hdb2056.codfw.wmnet enwiktionary -e "show create table templatelinks\G" --skip-ssl
*************************** 1. row ***************************
       Table: templatelinks
Create Table: CREATE TABLE `templatelinks` (
  `tl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `tl_namespace` int(11) NOT NULL DEFAULT '0',
  `tl_title` varbinary(255) NOT NULL DEFAULT '',
  `tl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `tl_from` (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2017-01-17T16:29:31Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2056 - T154097 (duration: 00m 48s)

Change 332744 had a related patch set uploaded (by Marostegui):
db-codfw.php: Depool db2063

https://gerrit.wikimedia.org/r/332744

Change 332744 merged by jenkins-bot:
db-codfw.php: Depool db2063

https://gerrit.wikimedia.org/r/332744

Mentioned in SAL (#wikimedia-operations) [2017-01-18T08:50:44Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2063 - T154097 (duration: 00m 39s)

Mentioned in SAL (#wikimedia-operations) [2017-01-18T08:51:12Z] <marostegui> Remove partitions from enwiktionary.templatelinks on db2063 - T154097

db2063:

root@neodymium:/home/marostegui/git# mysql -hdb2063.codfw.wmnet enwiktionary -e "show create table templatelinks\G" --skip-ssl
*************************** 1. row ***************************
       Table: templatelinks
Create Table: CREATE TABLE `templatelinks` (
  `tl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `tl_namespace` int(11) NOT NULL DEFAULT '0',
  `tl_title` varbinary(255) NOT NULL DEFAULT '',
  `tl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `tl_from` (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2017-01-18T09:56:57Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2063 - T154097 (duration: 00m 48s)

Change 332751 had a related patch set uploaded (by Marostegui):
db-codfw.php: Depool db2064

https://gerrit.wikimedia.org/r/332751

Change 332751 merged by jenkins-bot:
db-codfw.php: Depool db2064

https://gerrit.wikimedia.org/r/332751

Mentioned in SAL (#wikimedia-operations) [2017-01-18T10:05:13Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2064 - T154097 (duration: 00m 45s)

Mentioned in SAL (#wikimedia-operations) [2017-01-18T10:05:28Z] <marostegui> Remove partitions from enwiktionary.templatelinks on db2064 - T154097

db2064:

root@neodymium:/home/marostegui/git# mysql -hdb2064.codfw.wmnet enwiktionary -e "show create table templatelinks\G" --skip-ssl
*************************** 1. row ***************************
       Table: templatelinks
Create Table: CREATE TABLE `templatelinks` (
  `tl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `tl_namespace` int(11) NOT NULL DEFAULT '0',
  `tl_title` varbinary(255) NOT NULL DEFAULT '',
  `tl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `tl_from` (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2017-01-18T11:17:28Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2064 - T154097 (duration: 00m 39s)

Only pending the master in codfw. This is a non-online operation, so once I decide do it on the master the slaves will get lagged, since it is codfw it should be fine.
The ALTER takes around 45 minutes to complete.