Is raid 5 suitable for a mysql installation?

Question

Is raid 5 suitable for a mysql installation?

Let me explain my application further. My application is a socket programming which will connect with gps device to receive gps string and there after do a further processing. The socket programming will be in another server and db in another server. So during the further processing is where it will query from the db. So here I guess there will lots of i/o rite. Minimally during the further processing there will be minimum of 5 select and the insert will be minimum 1 but at times it can be even minimum 4 or more and also a number of updates. Hope I am clearer now.

Answer 1

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.

InnoDB

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.

MyISAM

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.

Conclusion

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.

Recommendation

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.

Answer 2

For random access RAID 10 will almost certainly be better than RAID 5 (of course more expensive). Whether RAID 5 will be fast enough for your installation? That's going to be pretty tough for anyone else to answer.

Answer 3

RAID 5 has a high write overhead because of the parity calculations. See this and this. So what write load do you expect?

Then, do you have enough RAM to fit the entire database in memory? If so, then you don't need to worry too much about reading data from disk?

Basically, we can't answer this question for you because we don't have enough details...

Answer 4

With current technology, RAID 5 write is as fast as RAID 10 (as parity data are calculated transparently) in general. Only when the application is write intensive, RAID 10 is better.

RAID 5 read is a little slower than RAID 10 because the data are not always in the same disks. But again, it depends on your application that disk/OS buffer might play an important role.

So the question is: it depend. I suppose that if you want to use RAID 5, you need a huge disk size (if not, then go with RAID 10). Then you may want to analyse your MySQL queries pattern to how how it read/write data, how well performs the cache in that case...

Last but not least, RAID 5 is more risky than RAID 10 in disk failure.

Answer 5

But first let's decide what criteria you are using:

• Spinning drive? or SSD?
• Hardware RAID controller? or OS software?
• Battery Backed Write Cache? or not?
• Comparing RAID-5 to no RAID? to RAID-10? To Replication for recovery? To DRBD?
• If comparing -5 to -10, are you comparing the same number of drives? or the same capacity?
• What is your goal? High Availability? Recovery from single-drive failure? Other?
• How much does cost matter?
• Fast writes?

If practical, I would go for multiple machines for recovery from a drive failure or a system failure. (Galera distributed across 3 physical locations is currently the best, in my opinion.)

One machine, and you are worried about a single drive crashing -- RAID-5 and RAID-10 are about the same.

Price per GB with some kind of RAID -- Clearly -5 wins.

Writes -- Both -5 and -10 are likely to do 2 physical writes per logical write. -5 needs to write the block and update the parity (using XOR).

Dispersion of data -- RAID striping (in both -5 and -10) is better than anything to do with manual layout of data vs index, file_per_table, etc.

Read performance of N drives in -5 config is virtually identical to N drives in -10. (Note: the same N, but -5 will have higher capacity.)

A hardware RAID controller with BBWC gives "instantaneous" writes, with no risk of lost data in a power failure. So, regardless of the other factors, I vote for that.

(I used to work for a large Internet company at the time when they had thousands of MySQL RAID-5 servers with hardware BBWC. I never heard of a problem blamed on RAID-5.)