MySQL tables/databases into different disks

Question

I am aware that there is a possibility to have a single database server with many tables/databases, and you can separate some of those tables/databases into separate disks. Lets say you have 5 tables/databases, and 5 drives. So you attach each table/database for a different drive. Thus increasing IO and everything else.

What is that technique called ?

I think if you try separating databases, you just mount different drive to different folder for different database, but what if you want to separate tables into separate drives (each table in different disk)

Answer 1

In terms of MyISAM, I have seen it work to the advantage of many since there does exists syntax to create a MyISAM table where the .MYD exists on one disk and the .MYI file exists on another. This can reduce disk latency accessing data and index pages for any given MyISAM table. However, you can achieve at least 90% of this effect by just making judicious use of covering indexes.

When it comes do InnoDB, it is a completely different story. Why?

The interaction between the table metadata within ibdata1 and the InnoDB data and index pages is impossible to separate. I pointed this out in an earlier post. In addition, I stated that Percona has recommended against pushing .ibd files into separate disks.

StanleyJohns recommended MySQL Partitioning, which logically makes the most sense for MyISAM. atxdba mentions how to support the use of data and index separation of MyISAM in the OS. Even though InnoDB has the capability for it, please don't overthink partitioning InnoDB. When it comes to performing maintenance, it is more trouble than it is worth.

Answer 2

I'm not aware of a specific term for this practice but you can specify different data and/or index dirs using the create table syntax.

This only works for MyISAM tables and your OS must support symlinks.

In my practice though, we generally just segment sets databases into their own LV. These are generally a segment per functional application. Having a single logical volume with all the tables actually in there it's easier to keep track of an manage disk space for each DB.

Those LVs though, are generally in a RAID 10 config to help balance IO.

Answer 3

I think the technique you are looking for is called partitioning. You can read more about MySQL partitioning here.