Can MySQL use multiple data directories on different physical storage devices

Question

I am running MySQL with its data dir on a 128Gb SSD. I am dealing with large datasets (~20Gb) that are loaded and processed weekly, each stored in a separate DB for the purposes of time point comparisons. Putting all the data into a single database in unfeasible because the performance on such large databases is already a problem. However, I cannot keep more than 6 datasets on the SSD at a time. Right now I am manually dumping the oldest to much larger 2Tb spinning disk every week, and dropping the database to make space for the new one. But if I need one of the 'archived' databases (a semi regular occurrence) I have to drop a current one (after dumping), reload it, do what I need to, then reverse the results.

Is there a way to configure MySQL to use multiple data directories, say one on the SSD and one on the 2Tb spinning disk, and 'merge' them transparently? If I could do this, then archiving would no longer mean "moved out of the database entirely", but instead would mean "moved onto the slow physical device". The time taken to do my queries on a spinning disk would be less than that taken to completely dump, drop, load, drop, reload two entire databases, so this is a win.

I thought of using something like unionfs but I can't think of a way to control which database gets stored on which physical drive, because it works by merging on a directory level (from what I understand) so I'm still stuck with using multiple directories.

Any help appreciated, thanks in advance

Solution

First I think we should consider that for every database db_name, it will store a folder inside it's datadir (e.g. /var/lib/mysql). So, in theory, you could just have symlinks to the folders on different disks. However, this has another problem: if you use the InnoDB storage engine, it does not store its data inside the folder. Instead, it uses a single logfile, ibdata*.

I think this is important: as you have pointed out unionfs is great if all you want is a straightforward union of two filesystems, but it does not understand (or appreciate) the underlying semantics of the backing store when writing data.

From the top off my head, I can't tell you there is a filesystem that will do exactly what you want - I also think it might be too niche.

However, I think there might be a few things that you could look into to get where you want to be:

• Running two copies of MySQL, with their own separate data directories. You can then use a FEDERATED table type in order to 'simulate' the effect of having all the data inside a single MySQL instance.
• A bit simplier: symlinks, using MyISAM table types (this will require some extensive testing when it comes to dropping the tables, perhaps you could only truncate them to prevent the link being lost)

Comments

[Martin C.]

You can still use InnoDB, you just have to set the innodb-file-per-table, which I personally use always as I don't like the single huge file.

[sirlark]

First off, Thanks. The symlink solution is, in retrospect, blindingly obvious. It's not a huge issue, but the InnoDB log file can't be split that way, although implementing @Martin C's innodb-file-per-table setting will alleviate a lot of the issues. I'll definitely look into the FEDERATED table thing though.

[Caltor]

@MartinC. Can you elaborate on "innodb-file-per-table" please.

[Martin C.]

@Caltor Per default, MySQL uses global data/log-files for all InnoDB tables. With innodb-file-per-table in my.cnf you can configure it to create separate files for every new table. As you now have more files, you can move them around more easily and symlink them however you want. I am still a fan of moving whole databases, though, not individual tables, I don't know when the files get re-created.