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

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

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

Share this post

Comments (12)

  • Rick Pizzi Reply

    Nice one!!! Thank you pt-osc team!

    November 4, 2016 at 4:51 pm
  • Arnoldas Reply

    Nice work, it provides some flexibility in extra cases

    November 5, 2016 at 4:23 am
  • William P Santana Reply

    Awesome!!! Thanks Percona Team, always helping the community.

    November 5, 2016 at 12:17 pm
  • Jo Valerio Reply

    Thanks for sharing! Keep it up Percona Team!

    November 6, 2016 at 9:26 pm
  • vidyadhar Reply

    adding new feature in the tool to support customer requirement is awesome.. Good customer support from Percona..

    November 8, 2016 at 2:03 am
  • robert rost Reply

    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?

    November 8, 2016 at 3:52 pm
    • robert rost Reply

      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.

      November 8, 2016 at 3:56 pm
      • robert rost Reply

        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.

        November 9, 2016 at 3:19 pm
  • Johnson Reply

    Does pt-osc support create temporary table with other table engine, like myisam ?

    December 22, 2016 at 4:23 am
    • Daniel Reply

      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.

      December 23, 2016 at 7:42 am
  • Farhana Mohammed Reply

    Thankyou for the post. It’s really very helpful and understanding.

    December 10, 2017 at 5:27 am

Leave a Reply