EmergencyEMERGENCY? Get 24/7 Help Now!

Changing the Tablespace Directory with pt-online-schema-change

and  | November 4, 2016 |  Posted In: InnoDB, MySQL, Percona Toolkit

PREVIOUS POST
NEXT POST

Tablespace DirectoryIn 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:

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

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

DATA DIRECTORY='/opt/datadir/' is in the right place! 🙂

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

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

And the contents seems fine:

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.

PREVIOUS POST
NEXT POST
Daniel Guzmán Burgos

Daniel studied Electronic Engineering, but quickly becomes interested in all data things. He has worked as a DBA since 2007 for several companies including one of the major social gaming company at that time in Latin America. Daniel lives in Bogotá, Colombia and in his free time he loves to spend time with his family and go out to eat at any kind of restaurant. Working for Percona since 2014, he is part of the Consulting team.

Carlos Salguero

Carlos, a Web back-end Go developer, has been writing computer programs since 1984. Prior to joining Percona, he was a developer at Onapsis Inc., Edrans/Zappos.com, Tupperware. Carlos lives in Rosario with his wife and her two daughters.

11 Comments

  • 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

    • 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.

Leave a Reply