Using multiple disks for large InnoDB database

Question

I am in the process of setting up a large MySQL/InnoDB database on a server that has multiple physical hard drives. My current configuration uses innodb_file_per_table, a setting I would like to keep.

Now, my database consists of hundreds of "small" tables, most a couple of megs to a few gigs in size. All in all however, the database exceeds the size of the individual disks, so I would like to split the .ibd files across multiple drives automatically, preferably with the option to add more disks as needed - I would very much prefer not to be forced to manage these myself.

Are there options in mysql to have it split the files across drives or do I have to use something like LVM/software RAID0 to achieve this?

Answer 1

The best way to use multiple similar drives is to use RAID-0 striping. While you are at it you could add a little bit of security by using RAID-5 - one drive is logically a parity for the others; you can suffer lost of any drive without loss of data.

Once you have RAID, you can increase write performance by having a "Battery Backed Write Cache" on the Raid controller. This gives you near-instantaneous writes without loss of security.

By striping, you get better sharing of the I/O across the drives than you could possibly do manually.

By striping, you get one large (logical) drive, and do not have to manually worry about which ibd goes where.

I would suggest putting the largest 20% of the tables in .ibd files; the rest in ibdata1. This becomes especially important if you have thousands of active tables. Otherwise, the proliferation of .ibd files has other consequences on table caches, etc. (Since this suggestion is not 'automatic', you may not want to do it.)

Unfortunately, you probably have to dump and reload the entire database to add drives to a RAID system. LVM might have a distinct advantage if you want to avoid that. (But it won't have the other advantages of RAID.