November 25, 2014

Location for InnoDB tablespace in MySQL 5.6.6

There is one new feature in MySQL 5.6 that didn’t get the attention it deserved (at least from me ;-) ) : “DATA DIRECTORY” for InnoDB tables.

This is implemented since MySQL 5.6.6 and can be used only at the creation of the table. It’s not possible to change the DATA DIRECTORY with an ALTER for a normal table (but it’s in some case with partitioned ones as you will see below). If you do so, the option will be just ignored:

You can read more information in the MySQL Manual: Specifying the Location of a Tablespace.

So it’s now possible if for example you use SSD or FusionIO disks to have the large log or archived table to cheaper disks as you won’t require fast random access for those table and then save some expensive diskspace.

The syntax is very simple:

And in fact if we check on the filesystem:

# ls -lh /var/lib/mysql/fred/
total 20K
-rw-r--r-- 1 mysql mysql 65 May 23 22:30 db.opt
-rw-r--r-- 1 mysql mysql 8.5K May 23 22:30 sales_figures.frm
-rw-r--r-- 1 mysql mysql 31 May 23 22:30 sales_figures.isl

Not the new file .isl (referred as a link to the RemoteDatafile in the source code) that contains the location of the tablespace:

[root@imac2 tmp]# cat /var/lib/mysql/fred/sales_figures.isl
/tmp/tb1/fred/sales_figures.ibd

And indeed the tablespace is there:

[root@imac2 tmp]# ls -lh /tmp/tb1/fred/
total 96K
-rw-r--r-- 1 mysql mysql 96K May 23 22:30 sales_figures.ibd

This is really great ! And something even nicer, it finally works with partitioning too (before that was only possible for MyISAM tables):


[root@imac2 mysql]# ls -l /tmp/tb1/fred/sales_figures#P#US_DATA.ibd
-rw-rw---- 1 mysql mysql 98304 May 23 16:19 /tmp/tb1/fred/sales_figures#P#US_DATA.ibd

[root@imac2 mysql]# ls -l /tmp/tb2/fred/sales_figures#P#EU_DATA.ibd
-rw-rw—- 1 mysql mysql 98304 May 23 16:19 /tmp/tb2/fred/sales_figures#P#EU_DATA.ibd

So now you can have some partitions on fast disks and some on slower disks. This is great for historical partitioning.

For example you have a table orders partitioned by years as follow:

Only the partition handling the orders for the current year is on SSD.
At the end of the year, you can recreate a new partition and move all the data for 2014 on slower disks:

Notice that XtraBackup is also aware of these tablespaces on different locations and is able to deal with them.

There is currently only one issue is that with –copy-back, you need to have the full path created for the tablespaces not in the MySQL data directory.

So in the example above I had to create /tmp/tb1/fred and /tmp/tb2/fred before being able to run innobackupex –copy-back
(see bug 1322658).

I hope now that this important feature got some more visibility as it deserves it.

About Frederic Descamps

Frédéric joined Percona in June 2011, he is an experienced Open Source consultant with expertise in infrastructure projects as well in development tracks and database administration.

Frédéric is a believer of devops culture.

Comments

  1. How does this compare to replacing the data directory for a specific directory with a symlink to a different filesystem?

    I see there is still a file on the default disk so I wonder whether there is any difference in how frequently that file needs to be read from disk compared to a symlink on the main disk.

  2. @Chris – symlinks will work at the datadir or schema level, but at the individual table level they can get destroyed by ALTER TABLE commands. This feature allows for fine grained control at the table (or partition) level.

  3. “Destroyed” sounds scary, like you lose your data or something. Don’t worry!

    What happens is that an ALTER TABLE or OPTIMIZE TABLE that performs a table restructure creates a new file under the datadir, copies all your data from the old file into the new file, and then drops the old file. But these DDL commands don’t know to create the new file with a symlink to the location used by the old file.

    So I would say a table-level symlink is “undone” by some DDL commands. :-)

  4. Khurram says:

    Hi,

    tablespace is not proper word to use while talking to Innodb engine , since tablespace use in cluster database, Innodb not support tablespace , instead use data directory. Am I right ?

    Giving warning 1478 “Table storage engine ‘InnoDB’ does not support the create option ‘TABLESPACE or LOGFILE GROUP'” in Mysql 5.5.24

    Thank you

Speak Your Mind

*