Location for InnoDB tablespace in MySQL 5.6.6_MySQL

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 sinceMySQL 5.6.6and 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 justignored:

mysql> CREATE TABLE `sales_figures` (-> `region_id` int(11) DEFAULT NULL,-> `sales_date` date DEFAULT NULL,-> `amount` int(11) DEFAULT NULL-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1-> DATA DIRECTORY = '/tb1/';Query OK, 0 rows affected (0.11 sec)mysql> alter table sales_figures engine=innodb data directory='/tb2/';Query OK, 0 rows affected, 1 warning (0.21 sec)Records: 0Duplicates: 0Warnings: 1mysql> show warnings;+---------+------+---------------------------------+| Level | Code | Message |+---------+------+---------------------------------+| Warning | 1618 |option ignored |+---------+------+---------------------------------+

mysql>CREATETABLE`sales_figures`(

    ->  `region_id`int(11)DEFAULTNULL,

    ->  `sales_date`dateDEFAULTNULL,

    ->  `amount`int(11)DEFAULTNULL

    ->)ENGINE=InnoDBDEFAULTCHARSET=latin1

    ->DATADIRECTORY='/tb1/';

QueryOK,0rowsaffected(0.11sec)

mysql>altertablesales_figuresengine=innodbdatadirectory='/tb2/';

QueryOK,0rowsaffected,1warning(0.21sec)

Records:0  Duplicates:0  Warnings:1

mysql>showwarnings;

+---------+------+---------------------------------+

|Level  |Code|Message                        |

+---------+------+---------------------------------+

|Warning|1618|  optionignored|

+---------+------+---------------------------------+

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:

mysql> CREATE TABLE `sales_figures` (`region_id` int(11) DEFAULT NULL,`sales_date` date DEFAULT NULL,`amount` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/tmp/tb1/'mysql> select @@datadir;+-----------------+| @@datadir |+-----------------+| /var/lib/mysql/ |+-----------------+

mysql>CREATETABLE`sales_figures`(

  `region_id`int(11)DEFAULTNULL,

  `sales_date`dateDEFAULTNULL,

  `amount`int(11)DEFAULTNULL

)ENGINE=InnoDBDEFAULTCHARSET=latin1DATADIRECTORY='/tmp/tb1/'

mysql>select@@datadir;

+-----------------+

|@@datadir      |

+-----------------+

|/var/lib/mysql/|

+-----------------+

And in fact if we check on the filesystem:
<br> # ls -lh /var/lib/mysql/fred/<br> total 20K<br> -rw-r--r-- 1 mysql mysql 65 May 23 22:30 db.opt<br> -rw-r--r-- 1 mysql mysql 8.5K May 23 22:30 sales_figures.frm<br> -rw-r--r-- 1 mysql mysql 31 May 23 22:30 sales_figures.isl<br>
Not the new file.isl(referred as a link to the RemoteDatafile in the source code)that contains the location of the tablespace:
<br> [root@imac2 tmp]# cat /var/lib/mysql/fred/sales_figures.isl<br> /tmp/tb1/fred/sales_figures.ibd<br>
And indeed the tablespace is there:
<br> [root@imac2 tmp]# ls -lh /tmp/tb1/fred/<br> total 96K<br> -rw-r--r-- 1 mysql mysql 96K May 23 22:30 sales_figures.ibd<br>

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

mysql> CREATE TABLE sales_figures (region_id INT, sales_date DATE, amount INT)PARTITION BY LIST (region_id) ( PARTITION US_DATA VALUES IN(100,200,300) DATA DIRECTORY = '/tmp/tb1', PARTITION EU_DATA VALUES IN(400,500) DATA DIRECTORY = '/tmp/tb2/');

mysql>CREATETABLEsales_figures(region_idINT,sales_dateDATE,amountINT)

PARTITIONBYLIST(region_id)(    

  PARTITIONUS_DATAVALUESIN(100,200,300)DATADIRECTORY='/tmp/tb1',    

  PARTITIONEU_DATAVALUESIN(400,500)DATADIRECTORY='/tmp/tb2/'

);

<br> [root@imac2 mysql]# ls -l /tmp/tb1/fred/sales_figures#P#US_DATA.ibd<br> -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 tableorderspartitioned by years as follow:

create table orders (id int, purchased DATE)partition by range (YEAR(purchased)) ( partition pre2012 values less than (2012) DATA DIRECTORY '/hdd/', partition pre2013 values less than (2013) DATA DIRECTORY '/hdd/', partition pre2014 values less than (2014) DATA DIRECTORY '/hdd/', partition current values less than MAXVALUE DATA DIRECTORY '/ssd/');

createtableorders(idint,purchasedDATE)

  partitionbyrange(YEAR(purchased))(

    partitionpre2012valueslessthan(2012)DATADIRECTORY'/hdd/',

    partitionpre2013valueslessthan(2013)DATADIRECTORY'/hdd/',

    partitionpre2014valueslessthan(2014)DATADIRECTORY'/hdd/',

    partitioncurrentvalueslessthanMAXVALUEDATADIRECTORY'/ssd/'

  );

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:

mysql> ALTER TABLE orders REORGANIZE PARTITION `current` INTO ( partition pre2015 values less than (2015) DATA DIRECTORY '/hdd/', partition current values less than MAXVALUE DATA DIRECTORY '/ssd');

mysql>ALTERTABLEordersREORGANIZEPARTITION`current`INTO(

      partitionpre2015valueslessthan(2015)DATADIRECTORY'/hdd/',

      partitioncurrentvalueslessthanMAXVALUEDATADIRECTORY'/ssd');

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 runinnobackupex –copy-back
(seebug 1322658).

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