November 17, 2014 at 8:45 am
I understand the differences between the two and the write penalty incurred by RAID 5. I am aware that in general, RAID-10 is considered best for your MDBs
In regards to the read to write ratio, at what point would you consider just taking the RAID 5 write penalty in order to improve read performance and maximize available disk space.
I'm bring a new disk array online, a Dell MD3220 with 24 146GB 15K SAS drives and obviously want to set it up optimally.
Our application consist of over 1700 tables and is used mainly for OLTP, but also has data warehousing characteristics. My read to write ratio runs about 45 to 1 during the day when online performance in most important. During my nightly batch runs when the heavy updating take place from 6:00pm to about 8:00pm-9:00pm, the read to write ration is more like 4 to 1. After that we're into extract and reporting mode and the read to write ration is back to 45-50 to 1.
My initial thought is to use RAID-10, but I'm wondering that in this case RAID-5 might make more sense.
I've got a day or two to work with the array and would welcome any advise on what SQLIO parameters I could use to determine what would be the best course of action.
TIA
D
November 17, 2014 at 9:30 am
Most modern RAID10 controllers will push very good read performance purely because all the disks in the array are usually used.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 17, 2014 at 9:38 am
Have you considered going for multiple RAID 5 partitions and essentially "striping" your database across them?
For example you could have 4x RAID5 partitions (6 disks each). That way you only lose 1 disk per partition so you still have a partition size of 5x146GB = 730GB each.
You then create three extra data files for your database and rebuild Clustered Indexes so they spread out across the partitions evenly. If you have any HEAPs you can add and then remove a Clustered Index in order to spread them out across the disks. There is also a special way to do this for BLOB data if you have any tables that contain this.
It may not be as great as RAID 10 but the IO improvement would be a sure sight better than 1x RAID 5 partition and may even be better than 1x RAID10. I have done this for many large databases with significant performance gains. Unfortunately SQLIO will not be able to provide you with a good benchmark of this scenario as its a straightforward IO tool. If you could set up the "four-filer" database and test\replay your Application against it before going LIVE that would be the best bet.
hope this helps
P.S the assumption here is that your Log File (s) will be on a separate partition. If I am adding an external storage array to a server then I usually carve this up for data and use my internal disks as RAID1 or RAID10 for Logs.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply