This could depend a great deal on the storage engine.
For MyISAM, I think it would be a great idea because you can make data as contiguous as a you want. That would benefit queries involving bulk operations and large range scans. The toolset for compressing and repairing MyISAM to improve the table format can also make access a little better. IMHO concurrent INSERTs could thrive in such a disk environment.
InnoDB is a completely different story.
If you have innodb_file_per_table disabled, data can be scattered throughout ibdata1 and thus scattered amongst the multiple disks. You could run OPTIMIZE TABLE against an InnoDB table and make the data and index pages contiguous, but that could cause two problems:
- Makes the ibdata1 grow
- Deteriorate performance over time
Don't forget the four types of data that reside in ibdata1:
- Table MetaData
- Table Data Pages
- Index Data Pages
- MVCC Data
There is constant writing of MVCC Data to protect data integrity. There is also constant interaction with ib_logfile0 and ib_logfile1 (redo logs). There is constant data flushing every 10 seconds from the InnoDB Buffer Pool back to the . Overall, it is safe to say that the spread of data across multiple disks and the I/O performance can show marginal-to-modest performance improvement until there is enough fragmentation within ibdata1.
Now, if innodb_file_per_table is enabled, things could be a little messier. Here is why:
Once you cleanup ibdata1 and separate all InnoDB tables from ibdata1, you will have a smaller ibdata1. However, each access to an InnoDB table requires navigating its use through ibdata1 ALL THE TIME via Table Metadata. You literally cannot semantically separate the table metadata (ibdata1) from the table (.ibd). Wherever ibdata1 lives, disk I/O must always pass through ibdata1.
Overall effect: Table fragmentation is limited to .ibd files and can be reduced and reorganized with OPTIMIZE TABLE, but no additional I/O performance enhancement is really achieved.
What about putting all .ibd files into a different set of disks apart from ibdata1 and using symlinks? According to Percona, that creates more mayhem.
When it comes to using multiple drives over a single drive, I highly favor MyISAM over InnoDB.