Remove partitions from metawiki.pagelinks in s7
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Marostegui
	Dec 15 2016, 10:50 AM

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

Details

Subject	Repo	Branch	Lines +/-
db-eqiad.php: Remove old comments	operations/mediawiki-config	master	+1 -1
db-eqiad.php: Repool db1028	operations/mediawiki-config	master	+4 -4
db-eqiad.php: Depool db1028	operations/mediawiki-config	master	+4 -4
db-codfw.php: Depool db2061	operations/mediawiki-config	master	+3 -3
db-codfw.php Depool db2054	operations/mediawiki-config	master	+4 -4
db-codfw.php: Depool db2047	operations/mediawiki-config	master	+1 -1

Customize query in gerrit

Related Objects
Search...

Status	Assigned	Task
Resolved	Joe	T154658 Prepare and improve the datacenter switchover procedure
Resolved	None	T155099 Database maintenance scheduled while eqiad datacenter is non primary (after the DC switchover)
Resolved	Marostegui	T153300 Remove partitions from metawiki.pagelinks in s7

Event Timeline

Marostegui created this task.Dec 15 2016, 10:50 AM

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

gerritbot added a project: Patch-For-Review.Jan 19 2017, 2:39 PM

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.

Marostegui removed a project: Patch-For-Review.Jan 20 2017, 8:45 AM

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

gerritbot added a project: Patch-For-Review.Jan 24 2017, 9:16 AM

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)

Marostegui added a parent task: T155099: Database maintenance scheduled while eqiad datacenter is non primary (after the DC switchover).Mar 31 2017, 3:54 PM

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.Apr 27 2017, 2:18 PM

Marostegui claimed this task.

Remove partitions from metawiki.pagelinks in s7Closed, ResolvedPublicActions

Description

Details

Related ObjectsSearch...

Event Timeline

Remove partitions from metawiki.pagelinks in s7
Closed, ResolvedPublic
Actions

Related Objects
Search...