Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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)
share|improve this answer
    
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. –  Martin C. Oct 28 '12 at 23:42
    
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. –  sirlark Oct 29 '12 at 7:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.