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.

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.

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?

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.

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.

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?
–
newbie14Feb 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.
–
newbie14Feb 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?
–
newbie14Feb 15 '12 at 17:18

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.

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.
–
newbie14Feb 14 '12 at 22:18

@AaronBertrand I said "as fast as in general", then "write intensive, RAID 10 is better"
–
jcisioFeb 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).
–
jcisioFeb 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?
–
newbie14Feb 15 '12 at 17:17