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:
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, there’s a bug that the 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:
|
1 |
mysql> show create table sbtest5;<br>*************************** 1. row ***************************<br>Table: sbtest5<br>Create Table: CREATE TABLE `sbtest5` (<br>`id` int(10) unsigned NOT NULL AUTO_INCREMENT,<br>`k` int(10) unsigned NOT NULL DEFAULT '0',<br>`c` char(120) NOT NULL DEFAULT '',<br>`pad` char(60) NOT NULL DEFAULT '',<br>PRIMARY KEY (`id`),<br>KEY `k_5` (`k`)<br>) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000<br>1 row in set (0.00 sec)<br> |
Now, let’s move it to the directory /opt/datadir, which owner is the MySQL user:
|
1 |
[root@ps56-1 percona]# pt-online-schema-change --data-dir="/opt/datadir" --execute D=percona,t=sbtest5<br>No slaves found. See --recursion-method if host ps56-1 has slaves.<br>Not checking slave lag because no slaves were found and --check-slave-lag was not specified.<br>Operation, tries, wait:<br> analyze_table, 10, 1<br> copy_rows, 10, 0.25<br> create_triggers, 10, 1<br> drop_triggers, 10, 1<br> swap_tables, 10, 1<br> update_foreign_keys, 10, 1<br>Altering `percona`.`sbtest5`...<br>Creating new table...<br>Created new table percona._sbtest5_new OK.<br>2016-11-01T19:22:27 Creating triggers...<br>2016-11-01T19:22:27 Created triggers OK.<br>2016-11-01T19:22:27 Copying approximately 1000 rows...<br>2016-11-01T19:22:27 Copied rows OK.<br>2016-11-01T19:22:27 Analyzing new table...<br>2016-11-01T19:22:27 Swapping tables...<br>2016-11-01T19:22:28 Swapped original and new tables OK.<br>2016-11-01T19:22:28 Dropping old table...<br>2016-11-01T19:22:28 Dropped old table `percona`.`_sbtest5_old` OK.<br>2016-11-01T19:22:28 Dropping triggers...<br>2016-11-01T19:22:28 Dropped triggers OK.<br>Successfully altered `percona`.`sbtest5`.<br> |
Okay, all good. Let’s see the new table definition:
|
1 |
mysql> show create table sbtest5;<br>*************************** 1. row ***************************<br> Table: sbtest5<br>Create Table: CREATE TABLE `sbtest5` (<br> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,<br> `k` int(10) unsigned NOT NULL DEFAULT '0',<br> `c` char(120) NOT NULL DEFAULT '',<br> `pad` char(60) NOT NULL DEFAULT '',<br> PRIMARY KEY (`id`),<br> KEY `k_5` (`k`)<br>) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000 DATA DIRECTORY='/opt/datadir/'<br> |
DATA DIRECTORY='/opt/datadir/' is in the right place! 🙂
And from the filesystem, the *.ibd file is in the new directory:
|
1 |
[root@ps56-1 opt]# ls -l /opt/datadir/percona/ | grep sbtest5<br>-rw-rw---- 1 mysql mysql 344064 Nov 1 19:22 sbtest5.ibd<br> |
And in the datadir, we can see the *isl file:
|
1 |
[root@ps56-1 opt]# ls -l /var/lib/mysql/percona/ | grep sbtest5<br>-rw-rw---- 1 mysql mysql 8632 Nov 1 19:22 sbtest5.frm<br>-rw-rw---- 1 mysql mysql 32 Nov 1 19:22 sbtest5.isl<br> |
And the contents seems fine:
|
1 |
[root@ps56-1 opt]# cat /var/lib/mysql/percona/sbtest5.isl<br>/opt/datadir/percona/sbtest5.ibd<br> |
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.
Resources
RELATED POSTS
