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, 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 2 3 4 5 6 7 8 9 10 11 12 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | [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:
1 2 3 4 5 6 7 8 9 10 11 | 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:
1 2 | [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:
1 2 3 | [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:
1 2 | [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.
Nice one!!! Thank you pt-osc team!
Nice work, it provides some flexibility in extra cases
Awesome!!! Thanks Percona Team, always helping the community.
Thanks for sharing! Keep it up Percona Team!
adding new feature in the tool to support customer requirement is awesome.. Good customer support from Percona..
I have a similar project that i need to complete but was unable to get the above to work. Any idea on a release date for PT2.2.20?
For those interested in the failure… the pt-online-schema-change command works with the –data-dir=’/new/datadir’ option but it simply does not do whats it’s suppose to do (table copy is still in $datadir as defined by my.cnf). I am using PT 2.2.19 and copied the code from repo listed above.
Quick follow-up.. We found that the table partitioning was causing -data-dir option to be ignored. Will submit a bug report once we do some testing.
Robert, from the doc: (http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html)
DATA DIRECTORY and INDEX DIRECTORY are subject to the following restrictions when used with partitioned tables:
Table-level DATA DIRECTORY and INDEX DIRECTORY options are ignored (see Bug #32091).
And pt-osc will only do table-level DATA DIECTORY.
Is not a bug, is just not supported in this version 🙂
Take in account that partitions with data directory faces bugs, like http://bugs.mysql.com/bug.php?id=73084
Does pt-osc support create temporary table with other table engine, like myisam ?
Johnson, if you mean tables created with the “temporary” keyword the answer is no. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed.
Thankyou for the post. It’s really very helpful and understanding.