Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
6  
Is 42 a suitable answer for a question? –  Leigh Riffel Feb 14 '12 at 20:09
    
Depends on your needs. As Author of this post dbasquare.com/2012/04/02/… stated, it doesn't fit heavily loaded production load. For other purposes it shoudl be fine. –  user7879 Apr 4 '12 at 0:40
add comment

4 Answers

up vote 7 down vote accepted

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.

share|improve this answer
2  
+1 nice thorough answer –  Aaron Bertrand Feb 14 '12 at 19:32
1  
@Rolando I have edited my question by explaining the situation even better now. –  newbie14 Feb 14 '12 at 22:17
    
+1 really a good answer... –  Abdul Manaf Feb 15 '12 at 5:51
add comment

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.

share|improve this answer
    
some claim not to use RAID 5 for mysql during it will be slowing in writing process is that true as of now with more advance technology? –  newbie14 Feb 14 '12 at 6:44
1  
RAID 5 is still RAID 5. Write speeds on spinning platters has not advanced at all. If you're using SSDs it's a different story, but my goal would still be RAID 10. –  Aaron Bertrand Feb 14 '12 at 12:19
    
I have edited my question by explaining the situation even better now. –  newbie14 Feb 14 '12 at 22:18
2  
Do you expect the answer to change? Nobody is going to be able to tell you whether RAID 5 is satisfactory. I think we are all telling you that RAID 10 is faster and safer. Only you can test your application on RAID 5 and determine whether or not the performance meets your "suitable" bar and/or if it is "good enough" given the price difference and your tolerance for failures/downtime. –  Aaron Bertrand Feb 14 '12 at 22:21
    
I saw the price of raid 10 is expensive so some suggested to go with raid 1 and have another server to keep the replicated data what is your idea about this? –  newbie14 Feb 15 '12 at 17:18
show 1 more comment

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...

share|improve this answer
    
I have edited my question by explaining the situation even better now. –  newbie14 Feb 14 '12 at 22:18
add comment

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.

share|improve this answer
    
I'm confused, you seem to be contradicting yourself: "RAID 5 write is as fast as RAID 10" but then you say "when application is write intensive, RAID 10 is better"? The only situation where RAID 5 makes sense is if you're on a budget and the $/GB trumps both performance and stability. –  Aaron Bertrand Feb 14 '12 at 19:23
    
@Jcisio I have edited my question by explaining the situation even better now. –  newbie14 Feb 14 '12 at 22:18
    
@AaronBertrand I said "as fast as in general", then "write intensive, RAID 10 is better" –  jcisio Feb 14 '12 at 23:35
    
@newbie14 Usually RAID 10 is better (but more expensive). However, if you are using RAID 5, the question would be: "Is RAID 5 good enough for this application?". Then it depend on how much disk I/O you use. It looks like you use mostly simply MySQL queries. Then RAID 5 would be okey with something like a few thousands of qps (queries per second). –  jcisio Feb 14 '12 at 23:41
    
@jcisio yes I check raid 10 is really expensive. Some guys suggest to me to have just raid 1 with another server to have the replicated db. What is your idea on that? –  newbie14 Feb 15 '12 at 17:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.