In a read-heavy, low-write environment on a RAID5, I would just leave that to one's budget, tolerance, and blood pressure.
In a write-heavy, low-read or write-heavy, read-heavy environment, RAID5 is simply out of the question. This is especially true for InnoDB. Think of an InnoDB's table interaction.
If you do not use innodb_file_per_table, OMG all the activity would be centered around just one file, ibdata1. What is contained in ibdata1?
- Table Data Pages
- Table Index Pages
- Table Metadata for Managing TableSpace IDs
- MVCC Data (for ACID Compliance and Transaction Isolation)
Even reads in InnoDB tend to shroud rows with MVCC protection to allow repeatable reads and permit transactions to hit the same rows being read. Thus, reads as well as writes produce disk I/O in ibdata1.
Using innodb_file_per_table may relieve some of the disk I/O by separating Table Data and Index pages from ibdata1 into
.ibd files. Yet, I would expect a somwehat noticeable performance improvement only for a limited time in a RAID5 environment. The table interaction is still somewhat the same. Every access to a
.ibd file is always preceded by reference checks against ibdata1.
While the separation can bring significant performance changes, RAID5 would be what they call in the chemistry world, a limiting reagent. Any benefits expected from InnoDB layout changes would be neutralized by outside factors, such as RAID5. The presence of extra tablespace files due to innodb_file_per_table buys you nothing over time but just the presence of extra tablespace files.
When it comes to MyISAM, RAID5 is OK in a read-heavy, low-write environment provided you map all temp tables (using tmpdir) to another disk, separate from the RAID5.
Please remember that table data pages live in
.MYD files and its corresponding index pages live in
.MYI files. A write-heavy environment (INSERTs, UPDATEs, DELETEs) will obligate RAID5 to slow things down. Given MyISAM's locking behavior (full table lock with each INSERT, UPDATE, and DELETE) in a write-heavy environment, a steady stream of DML will keep RAID5 rather busy and have DB users enter a brief-but-annoying time warp waiting for DML to complete.
Under the hood, RAID5 has the following characteristics for writing with parity
- Read the old data block
- Read the old parity block
- Compare the old data block with the write request. For each bit that has flipped (changed from 0 to 1, or from 1 to 0) in the data block, flip the corresponding bit in the parity block
- Write the new data block
- Write the new parity block
Should any of these steps see the slightest intermittency, the RAID5 set enters a brief-but-annoying time warp. Multiply that by a huge number of writes and you will feel it in the database performance. Each of these steps could be a point of failure. Why?
According to Wikipedia
In the event of a system failure while there are active writes, the
parity of a stripe may become inconsistent with the data. If this is
not detected and repaired before a disk or block fails, data loss may
ensue as incorrect parity will be used to reconstruct the missing
block in that stripe. This potential vulnerability is sometimes known
as the write hole. Battery-backed cache and similar techniques are
commonly used to reduce the window of opportunity for this to occur.
RAID10 not only provides stability but allows some leeway in disk maintenance without taking mysql down in most cases. When data is mirrored, you know where the data is going and you know from where the data is being read.
UPDATE 2012-02-14 17:55 EDT
After reading your question update, I would say go with RAID10. Unless you do not mind long periods of downtime, you cannot afford to do RAID5 disk maintenance in lieu disk syncing. In fact, the smaller the disks you stripe in RAID10, the faster the sync time would be after a RAID 10 disk maintenance.
Other things to consider
- Tune your queries
- Remove redundant indexes
- Cache as much of the data as you can
- Use covering indexes wisely
Questions of this nature can be posted in StackOverflow. You may post such questions in the DBA StackExchange as well.