Storing InnoDB Tables on Multiple Directories and Disks

InnoDB by default stores all data in one large file, typically referred to as the InnoDB tablespace. Without customisation, A file named ibdata1 at the root of your MySQL data directory will contain all your data and indexes.

One problem that has been noted with this setup is that data cannot be reclaimed when you delete data from your tables, so in the long run the data file can grow to an awkward (and redundant size).

However there is the option to have each InnoDB table as a separate file. For this you must use the innodb_file_per_table option in your my.cnf file.

MyISAM tables have 3 files per table, a table.FRM for table format, table.MYD for data, and table.MYI for indexes. InnoDB also has the .frm file but stores both data and indexes in table.ibd. This setup has the slight advantage of having less files open to access your tables, which can become important particularly for partitioned tables or setups with a low open_files_limit setting.

By using separate files per table, the opportunity arises to split your data up across different directories and disks. This can be done easily with MyISAM tables by specifying a DATA DIRECTORY and INDEX DIRECTORY in your CREATE TABLE syntax, allowing you spread your tables across directories and disks with ease. The problem is that InnoDB ignores these specifications…

I encountered this problem whilst trying to take advantage of a 2xHDD and 2xSSD (solid state disk) setup, with the intention of putting regularly accessed tables on the SSD to speed things up. InnoDB default behaviour seemed to prevent me from doing this, however there is a workaround. The solution is to create a separate database and create a symbolic link of the folder containing your new database, which is an eloquent or messy solution depending on whether you have an existing or new project.

Consider the following example where your default MySQL data directory is /var/lib/mysql and you have another folder/partition/disk you wish to use at /home/mysql.

Login to MySQL to create the following database (just for testing)

Exit MySQL into the command line to create the symbolic link. The following command is for Linux systems:

…and back into MySQL

Now you should have two databases, and if you check /var/lib/mysql, the db1 folder should be there with its data, alongside a symbolic link to db2, which now resides on /home/mysql.

And that is how it is done! The drawback is that if you have existing scripts referencing your database, you’re going to have to update your queries to reference db2 for all the tables that now reside in your symlinked database. This is a hassle, but I’m sure some release of MySQL in the near future will harness the DATA and INDEX DIRECTORY syntax that works so well for MyISAM tables.

A word of caution: to save you having to re-write and reference db2, you could of course use symbolic links for all the tables in a pre-existing table you wish to have in a separate directory. ALTER TABLE queries will break this setup, so beware if you decide to go down that route.

All in all, it is a bit of an unusual situation to what shouldn’t really be a problem, given the versatility of MySQL, but for the meantime is a handy workaround.

When InnoDB is Slow in phpMyAdmin

You may have recently switched over from MyISAM tables to InnoDB, or in fact used InnoDB for a long time. phpMyAdmin has been a mainstay tool for quick viewing and editing of databases, but unfortunately seems to grind to a halt after clicking to view a particular database.

This is mainly due to the way that SELECT COUNT(*) FROM TABLE is calculated. MyISAM keeps that kind of metadata at hand so can instantly calculate the value while InnoDB does not. phpMyAdmin doesn’t recognise this major difference; which means that if you have big InnoDB tables or a number of medium sized ones, loading up a database’s details can take a number of seconds, even minutes. It also is sucking up resources while it intensely tries to calcuate some statistics about your tables.

If you are not too interested in the general stats of a database, and more interested in viewing and manipulating the tables, there is a small hack you can make to one of phpMyAdmin’s PHP files that will load tables up instantly, namely libraries/database_interace.lib.php.

At around line 290 there is a variable $sql declared (it is declared a number of times in the script but we’re interested in this instance), edit the $sql command to this:

The small downside is that you can’t see row counts and some other general metadata, but it’s a small price to pay to continue using phpMyAdmin as a quick GUI reference to your database. I can verify that a 600M row database (10 tables) originally took about 6 seconds to load, but loads instantly after this fix. A smaller 150M row database (40 tables, lots of table partitions) would take up to 30 seconds to load and hang my browser… now only takes a second to spark up.

Credit goes to Richard Dale in the Source Forge forum, who created this workaround. I thought I would dedicated a post to this as the issue does not seem to be too prominent. With MySQL soon to use InnoDB as its default database engine, no doubt this issue will come ot the fore, and phpMyAdmin will implement a more permanent workaround.