Page MenuHomePhabricator

codfw: Fix S4 commonswiki.templatelinks partitions
Closed, ResolvedPublic

Description

We have found that all the slaves have commonswiki. templatelinks table partitioned (including those servers which are not part of the rc service)

This is db2065

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
/*!50100 PARTITION BY RANGE (tl_namespace)
(PARTITION p_9 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p_10 VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION p_827 VALUES LESS THAN (828) ENGINE = InnoDB,
 PARTITION p_828 VALUES LESS THAN (829) ENGINE = InnoDB,
 PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

After having a chat with @jcrespo looks like this is not intended and could be a mistake.

The slaves that need fixing are:

db2058
db2051
db2065
db2019 (master)

Details

Related Gerrit Patches:
operations/mediawiki-config : masterdb-eqiad.php: Depool db1064 for maintenance
operations/mediawiki-config : masterdb-eqiad.php: Depool db1068 for maintenance
operations/mediawiki-config : masterdb-eqiad.php: Repool db1059
operations/mediawiki-config : masterdb-eqiad.php: Depool db1059 for maintenance

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 25 2016, 9:38 AM
Marostegui renamed this task from codfw: Fix S4 commonswiki.template links partitions to codfw: Fix S4 commonswiki.templatelinks partitions.Oct 25 2016, 9:38 AM

Mentioned in SAL (#wikimedia-operations) [2016-10-25T09:43:07Z] <marostegui> Deploying ALTER table s4 commonswiki.templatelinks - T149079 (db2058 only)

This is now running - db2058:

./software/dbtools/osc_host.sh --host=db2058.codfw.wmnet --port=3306 --db=commonswiki --table=templatelinks --method=ddl --no-replicate "remove PARTITIONING"
Marostegui moved this task from Triage to In progress on the DBA board.Oct 25 2016, 9:46 AM

db2058 finished correctly

       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

root@db2058:/srv/sqldata/commonswiki# ls -lh templatelinks.*
-rw-rw---- 1 mysql mysql 2.3K Oct 25 10:52 templatelinks.frm
-rw-rw---- 1 mysql mysql 247G Oct 26 05:42 templatelinks.ibd

Mentioned in SAL (#wikimedia-operations) [2016-10-26T07:14:33Z] <marostegui> Deploying ALTER table s4 commonswiki.templatelinks - db2051 - T149079

db2051 is now running the ALTER

db2051 finished:

root@neodymium:/home/marostegui/git# mysql -hdb2051.codfw.wmnet commonswiki -e "show create table templatelinks\G"
*************************** 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) [2016-10-27T07:47:12Z] <marostegui> Deploying ALTER table s4 commonswiki.templatelinks - https://phabricator.wikimedia.org/T149079 (db2065 only)

db2065 is now running.

I believe we'd also need to fix the following eqiad hosts:

db1059 (api)
db1064 (vslow, dump)
db1068 (api)

db2065 is now done

MariaDB MARIADB db2065.codfw.wmnet commonswiki > show create table templatelinks;
+---------------+----------------------------------------------------------------------------------------------------------------------
| Table         | Create Table
+---------------+----------------------------------------------------------------------------------------------------------------------
| templatelinks | 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
Marostegui added a comment.EditedOct 27 2016, 5:20 PM

The only pending one in codfw is the master (db2019) and then the other three servers in eqiad

Looks like removing partition isn't a online ddl operation.
Jaime and myself have agreed on not altering the master until we've done a failover to another host.

I will continue unifying the hosts in eqiad then

db1059 (api)
db1064 (vslow, dump)
db1068 (api)

Change 320346 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1059 for maintenance

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

Change 320346 merged by jenkins-bot:
db-eqiad.php: Depool db1059 for maintenance

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

Mentioned in SAL (#wikimedia-operations) [2016-11-08T10:08:04Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1059 - T149079 T147305 (duration: 00m 57s)

ALTER running on db1059.

db1059 is now finished

root@neodymium:~# mysql -hdb1059 -A commonswiki -e "nopager;show create table templatelinks\G"
PAGER set to stdout
*************************** 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 320728 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Repool db1059

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

Change 320728 merged by jenkins-bot:
db-eqiad.php: Repool db1059

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

Mentioned in SAL (#wikimedia-operations) [2016-11-10T09:38:27Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: wmf-config/db-codfw.php Depool db1059 - T149079. Repool db2048 T150334 (duration: 00m 50s)

Change 320773 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1068 for maintenance

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

Change 320773 merged by jenkins-bot:
db-eqiad.php: Depool db1068 for maintenance

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

Mentioned in SAL (#wikimedia-operations) [2016-11-10T14:04:25Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1068 - T149079 (duration: 00m 48s)

Mentioned in SAL (#wikimedia-operations) [2016-11-10T15:09:28Z] <marostegui> Deploy schema change s4 commonswiki.template links (db1068) - https://phabricator.wikimedia.org/T149079

This is now running on db1068

db1068 is finished

MariaDB PRODUCTION s4 localhost commonswiki > show create table templatelinks\G
*************************** 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) [2016-11-11T07:33:08Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1068 - T149079 (duration: 00m 48s)

Change 321354 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1064 for maintenance

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

Change 321354 merged by jenkins-bot:
db-eqiad.php: Depool db1064 for maintenance

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

Mentioned in SAL (#wikimedia-operations) [2016-11-14T08:58:44Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1064 - T149079 (duration: 03m 07s)

Mentioned in SAL (#wikimedia-operations) [2016-11-14T09:11:36Z] <marostegui> Deploy schema change s4 commonswiki templatelinks db1064 - T149079

This is now running in db1064, the last server to fix.

db1064 is finished:

root@neodymium:~# mysql -hdb1064 -A commonswiki -e "show create table templatelinks\G"
*************************** 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

All the hosts are done, the only one pending is db2019 which is the master and will not be done as this operation isn't online as stated here: https://phabricator.wikimedia.org/T149079#2776840

Mentioned in SAL (#wikimedia-operations) [2016-11-15T09:43:34Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1064 - T149079 (duration: 00m 51s)

Marostegui removed Marostegui as the assignee of this task.Mar 3 2017, 3:58 PM
Marostegui moved this task from In progress to Next on the DBA board.

Will leave this alter table running on db2019 (codfw master) on Monday, it will generate lag on codfw.

This was done before the switchover

root@db2019.codfw.wmnet[commonswiki]> show create table templatelinks;
+---------------+----------------------------------------------------------------------------------------------------------------------
| Table         | Create Table
+---------------+----------------------------------------------------------------------------------------------------------------------
| templatelinks | 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 |
+---------------+----------------------------------------------------------------------------------------------------------------------
1 row in set (0.03 sec)
Marostegui closed this task as Resolved.Apr 21 2017, 1:02 PM
Marostegui claimed this task.