Remove partitions from metawiki.pagelinks in s7
Closed, ResolvedPublic

Description

Some hosts (apart from rc services one) have the table pagelinks partioned which looks like a mistake:

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s7.hosts| egrep -v "db1069|db2040|db1034|db1062"| awk -F " " '{print $1}'`; do echo $i; mysql -h$i -A metawiki -e "show create table pagelinks\G" | grep PARTITI | wc -l; done
dbstore2001.codfw.wmnet
0
db2047.codfw.wmnet
5
db2054.codfw.wmnet
5
db2061.codfw.wmnet
5
db2068.codfw.wmnet
0
db2029.codfw.wmnet
5
dbstore1001.eqiad.wmnet
0
dbstore1002.eqiad.wmnet
5
labsdb1001.eqiad.wmnet
0
labsdb1003.eqiad.wmnet
0
db1028.eqiad.wmnet
5
db1033.eqiad.wmnet
0
db1039.eqiad.wmnet
5
db1079.eqiad.wmnet
0
db1086.eqiad.wmnet
0
db1094.eqiad.wmnet
0
db1041.eqiad.wmnet
5

This should get unified:

alter table pagelinks remove partitioning

./software/dbtools/osc_host.sh --host=xxxxxx --port=3306 --db=metawiki --table=pagelinks --method=ddl --no-replicate "remove partitioning"

They have been removed already from db2068 as it was needed for maintenance: T153194
Moving tablespaces with partitions isn't supported on MariaDB right now and it is marked to be supported in 10.3: https://jira.mariadb.org/browse/MDEV-10568

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 15 2016, 10:50 AM
Marostegui triaged this task as "Low" priority.Dec 15 2016, 10:50 AM
Marostegui moved this task from Triage to Backlog on the DBA board.

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

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

Change 332997 merged by jenkins-bot:
db-codfw.php: Depool db2047

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

Mentioned in SAL (#wikimedia-operations) [2017-01-20T07:38:37Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2047 - T153300 (duration: 00m 48s)

Mentioned in SAL (#wikimedia-operations) [2017-01-20T08:41:18Z] <marostegui> Remove partitions on metawiki.pagelinks db2047 - T153300

I have started altering db2047 to see how long it takes.

Mentioned in SAL (#wikimedia-operations) [2017-01-20T13:30:36Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2047 - T153300 (duration: 00m 39s)

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

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

Change 333859 merged by jenkins-bot:
db-codfw.php Depool db2054

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

Mentioned in SAL (#wikimedia-operations) [2017-01-24T09:21:06Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2054 - T153300 (duration: 00m 39s)

Mentioned in SAL (#wikimedia-operations) [2017-01-24T09:21:50Z] <marostegui> Alter table db2054 metawiki.pagelinks - T153300

db2054:

root@neodymium:/home/marostegui/git# mysql --skip-ssl -hdb2054.codfw.wmnet metawiki -e "show create table pagelinks\G"
*************************** 1. row ***************************
       Table: pagelinks
Create Table: CREATE TABLE `pagelinks` (
  `pl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `pl_namespace` int(11) NOT NULL DEFAULT '0',
  `pl_title` varbinary(255) NOT NULL DEFAULT '',
  `pl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`) KEY_BLOCK_SIZE=4,
  KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`) KEY_BLOCK_SIZE=4,
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

db2047:

root@neodymium:/home/marostegui/git# mysql --skip-ssl -hdb2047.codfw.wmnet metawiki -e "show create table pagelinks\G"
*************************** 1. row ***************************
       Table: pagelinks
Create Table: CREATE TABLE `pagelinks` (
  `pl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `pl_namespace` int(11) NOT NULL DEFAULT '0',
  `pl_title` varbinary(255) NOT NULL DEFAULT '',
  `pl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`) KEY_BLOCK_SIZE=4,
  KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`) KEY_BLOCK_SIZE=4,
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

Mentioned in SAL (#wikimedia-operations) [2017-01-25T08:02:09Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2054 - T153300 (duration: 00m 51s)

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

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

Change 335404 merged by jenkins-bot:
db-codfw.php: Depool db2061

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

Mentioned in SAL (#wikimedia-operations) [2017-02-01T07:52:00Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2061 - T153300 (duration: 00m 53s)

Mentioned in SAL (#wikimedia-operations) [2017-02-01T07:53:25Z] <marostegui> Deploy alter table metawiki.pagelinks db2061 - T153300

db2061:

root@neodymium:/home/marostegui/git# mysql --skip-ssl -hdb2061.codfw.wmnet metawiki -e "show create table pagelinks\G"
*************************** 1. row ***************************
       Table: pagelinks
Create Table: CREATE TABLE `pagelinks` (
  `pl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `pl_namespace` int(11) NOT NULL DEFAULT '0',
  `pl_title` varbinary(255) NOT NULL DEFAULT '',
  `pl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`) KEY_BLOCK_SIZE=4,
  KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`) KEY_BLOCK_SIZE=4,
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

Mentioned in SAL (#wikimedia-operations) [2017-02-01T11:52:45Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2061 - T153300 (duration: 00m 40s)

Mentioned in SAL (#wikimedia-operations) [2017-02-01T13:39:07Z] <marostegui> Deploy alter table dbstore1002 metawiki.pagelinks - T153300

dbstore1002:

root@neodymium:/home/marostegui/git# mysql --skip-ssl -hdbstore1002.eqiad.wmnet metawiki -e "show create table pagelinks\G"
*************************** 1. row ***************************
       Table: pagelinks
Create Table: CREATE TABLE `pagelinks` (
  `pl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `pl_namespace` int(11) NOT NULL DEFAULT '0',
  `pl_title` varbinary(255) NOT NULL DEFAULT '',
  `pl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`),
  KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`),
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2017-02-03T11:01:56Z] <marostegui> Alter table metawiki.pagelinks on db1039 (depooled) - T153300

db1039:

root@PRODUCTION s7[metawiki]> show create table pagelinks\G
*************************** 1. row ***************************
       Table: pagelinks
Create Table: CREATE TABLE `pagelinks` (
  `pl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `pl_namespace` int(11) NOT NULL DEFAULT '0',
  `pl_title` varbinary(255) NOT NULL DEFAULT '',
  `pl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`),
  KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`),
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

Mentioned in SAL (#wikimedia-operations) [2017-02-06T07:03:25Z] <marostegui> Upgrade mariadb+packages db1039 - T153300

Change 336198 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1028

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

Change 336198 merged by jenkins-bot:
db-eqiad.php: Depool db1028

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

Mentioned in SAL (#wikimedia-operations) [2017-02-06T09:25:50Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1028 - T153300 (duration: 00m 42s)

Mentioned in SAL (#wikimedia-operations) [2017-02-06T09:26:59Z] <marostegui> Deploy ALTER table db1028 metawiki.pagelinks - T153300

db1028:

root@neodymium:/home/marostegui/git# mysql --skip-ssl -hdb1028.eqiad.wmnet metawiki -e "show create table pagelinks\G"
*************************** 1. row ***************************
       Table: pagelinks
Create Table: CREATE TABLE `pagelinks` (
  `pl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `pl_namespace` int(11) NOT NULL DEFAULT '0',
  `pl_title` varbinary(255) NOT NULL DEFAULT '',
  `pl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`),
  KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`),
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

Pending hosts:

db2029 - codfw master (can be done if we downtime all the slaves in codfw)
db1045 - eqiad master (cannot be done until we switch DCs)

Change 336775 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Repool db1028

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

Change 336775 merged by jenkins-bot:
db-eqiad.php: Repool db1028

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

Mentioned in SAL (#wikimedia-operations) [2017-02-09T13:34:19Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1028 - T153300 (duration: 00m 41s)

Change 338337 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Remove old comments

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

Change 338337 merged by jenkins-bot:
db-eqiad.php: Remove old comments

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

Mentioned in SAL (#wikimedia-operations) [2017-02-17T11:54:19Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Clean up db1028 old comments - T153300 (duration: 00m 41s)

Mentioned in SAL (#wikimedia-operations) [2017-04-03T06:12:24Z] <marostegui> Remove partitions from metawiki.pagelinks (s7) on codfw master (db2029) this will generate lag on codfw - T153300

Cleaning up codfw master now, so once the DC switchover is done, we can clean up eqiad and close this ticket.

db2029 is done:

root@db2029.codfw.wmnet[metawiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| db2029     |
+------------+
1 row in set (0.03 sec)

root@db2029.codfw.wmnet[metawiki]> show create table pagelinks\G
*************************** 1. row ***************************
       Table: pagelinks
Create Table: CREATE TABLE `pagelinks` (
  `pl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `pl_namespace` int(11) NOT NULL DEFAULT '0',
  `pl_title` varbinary(255) NOT NULL DEFAULT '',
  `pl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`) KEY_BLOCK_SIZE=4,
  KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`) KEY_BLOCK_SIZE=4,
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.04 sec)

Only pending primary master - db1041

Probably worth waiting for the master switchover to be done (T162133 )to get this done on this host while it is not the master, as it will generate lag on the dc

db1041 is no longer a master- this can be done at any time now, and doesn't have to be done during the switchover.

Mentioned in SAL (#wikimedia-operations) [2017-04-27T06:17:27Z] <marostegui> Deploy schema change on s7 metawiki.pagelinks to remove partitioning on db1041 - T153300

db1041 is now clean of partitions:

root@neodymium:/home/marostegui/git/software/dbtools# mysql --skip-ssl -e "show create table metawiki.pagelinks\G" -hdb1041
*************************** 1. row ***************************
       Table: pagelinks
Create Table: CREATE TABLE `pagelinks` (
  `pl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `pl_namespace` int(11) NOT NULL DEFAULT '0',
  `pl_title` varbinary(255) NOT NULL DEFAULT '',
  `pl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`),
  KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`),
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
Marostegui closed this task as "Resolved".Thu, Apr 27, 2:18 PM
Marostegui claimed this task.