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

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.

Share this post

Comments (7)

  • Chris Tomlinson

    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.

    May 28, 2014 at 9:08 am
  • Morgan Tocker

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

    May 28, 2014 at 11:57 am
  • Bill Karwin

    “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. 🙂

    May 28, 2014 at 1:10 pm
  • Khurram


    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

    November 5, 2014 at 12:23 pm
  • Tanz

    DATA Directory is not working, giving this error while trying to do that, [Err] 1030 – Got error -1 from storage engine, please help

    November 7, 2016 at 12:04 am
    • Valerie Parham-Thompson

      Hi Tanz. Check your error log for more detailed error messages. You may not have file permissions to the directory you’re using.

      November 29, 2016 at 11:50 am
  • Venkat

    Hello Frederic Descamps,

    You mentioned “XtraBackup is also aware of these tablespaces on different locations and is able to deal with them”. I have a table with DAT DIRECTORY, outside the default datadir. When I run xtrabackup, only the .frm and .isl files bet coped but not the /ibd file in the non default daatdir. How to run xtrabackup so that gets picked up as well?

    April 22, 2017 at 2:29 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.