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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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: 0 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+---------------------------------+ | Level | Code | Message | +---------+------+---------------------------------+ | Warning | 1618 | option 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:
1 2 3 4 5 6 7 8 9 10 11 12 | 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/ | +-----------------+ |
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):
1 2 3 4 5 | 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/' ); |
[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:
1 2 3 4 5 6 7 | 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/' ); |
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:
1 2 3 | 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'); |
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.
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.
@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.
“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. 🙂
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
DATA Directory is not working, giving this error while trying to do that, [Err] 1030 – Got error -1 from storage engine, please help
Hi Tanz. Check your error log for more detailed error messages. You may not have file permissions to the directory you’re using.
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?