Personal blog of Yzmir Ramirez

In this blog, we’ll discuss changing the tablespace directory using pt-online-schema-change.

One of the most annoying situations in the life of a DBA is realizing that the disk where the datadir resides is running out of space. If you’re lucky enough to run over an LVM volume or a RAID (depending on the level, though), it is easy to add disk space. But what if you are not that lucky, and your datadir is running on a single disk? Not so funny!

That is the exact situation we recently faced with a customer, for both the master and slave server. When trying to figure out a solution we saw that:

• There was enough space on a different partition within the same server.
• The tables have their own tablespace (innodb_file_per_table = on)
• The MySQL version was 5.6.

We proceed to move some of the tables to the other partition to make room in the datadir, by using the tablespace placing feature: http://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html.

One note before we continue: if you are using a version equal or lower than 5.6.29, and

innodb_flush_method = O_DIRECT

CREATE TABLE....DATA DIRECTORY = '/another/directory/'

 won’t work. See: https://bugs.mysql.com/bug.php?id=79200. This was fixed on 5.6.30.

In the slave, we were able to stop the replication and move the tables. A problem occurred when we wanted to do the same on the master, since no downtime was allowed.

This is where

pt-online-schema-change

 came to the rescue!

We could use

pt-osc

 to do the table placing without downtime, but there’s a catch:

pt-osc

 only works when what you want to do is possible by using an ALTER TABLE statement, and in order to use the

CREATE TABLE....DATA DIRECTORY = '/another/directory'

  you need to use a CREATE TABLE statement.

What to do, then? Add a new feature to

pt-online-schema-change

:

--data-dir="/new/directory"

With the help of the main developer of the Percona Toolkit, Carlos Salguero, adding this new feature was possible in record time. Now moving the tablespace to another place without downtime is possible.

The new feature will be available with version 2.2.20 of Percona Toolkit, but until the release the code is available at the GitHub repository: https://raw.githubusercontent.com/percona/percona-toolkit/2.2/bin/pt-online-schema-change

Moving the table is just a matter of executing

pt-online-schema-change  --data-dir="/new/datadir" --execute

Let’s see an example. The following table resides in the default datadir:

mysql> show create table sbtest5;
*************************** 1. row ***************************
Table: sbtest5
Create Table: CREATE TABLE `sbtest5` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_5` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000
1 row in set (0.00 sec)

Now, let’s move it to the directory /opt/datadir, which owner is the MySQL user:

[root@ps56-1 percona]# pt-online-schema-change  --data-dir="/opt/datadir" --execute D=percona,t=sbtest5
No slaves found.  See --recursion-method if host ps56-1 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `percona`.`sbtest5`...
Creating new table...
Created new table percona._sbtest5_new OK.
2016-11-01T19:22:27 Creating triggers...
2016-11-01T19:22:27 Created triggers OK.
2016-11-01T19:22:27 Copying approximately 1000 rows...
2016-11-01T19:22:27 Copied rows OK.
2016-11-01T19:22:27 Analyzing new table...
2016-11-01T19:22:27 Swapping tables...
2016-11-01T19:22:28 Swapped original and new tables OK.
2016-11-01T19:22:28 Dropping old table...
2016-11-01T19:22:28 Dropped old table `percona`.`_sbtest5_old` OK.
2016-11-01T19:22:28 Dropping triggers...
2016-11-01T19:22:28 Dropped triggers OK.
Successfully altered `percona`.`sbtest5`.

Okay, all good. Let’s see the new table definition:

mysql> show create table sbtest5;
*************************** 1. row ***************************
       Table: sbtest5
Create Table: CREATE TABLE `sbtest5` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_5` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000 DATA DIRECTORY='/opt/datadir/'

DATA DIRECTORY='/opt/datadir/'

 is in the right place!

And from the filesystem, the *.ibd file is in the new directory:

[root@ps56-1 opt]# ls -l /opt/datadir/percona/ | grep sbtest5
-rw-rw---- 1 mysql mysql 344064 Nov  1 19:22 sbtest5.ibd

And in the datadir, we can see the *isl file:

[root@ps56-1 opt]# ls -l /var/lib/mysql/percona/ | grep sbtest5
-rw-rw---- 1 mysql mysql   8632 Nov  1 19:22 sbtest5.frm
-rw-rw---- 1 mysql mysql     32 Nov  1 19:22 sbtest5.isl

And the contents seems fine:

[root@ps56-1 opt]# cat /var/lib/mysql/percona/sbtest5.isl
/opt/datadir/percona/sbtest5.ibd

So, in conclusion, if you need to move a table to another directory without downtime,

pt-online-schema-change

 can do that for you now.