July 9, 2011 at 2:26 pm
I have web based application with very large database. I have Server machine with 8 hard drive. Now I want to configure the RAID so that it is better available and better performance. Could you advise me in this regard? like which RAID level for operating system, Logfile, data.... and any advise.
Thank you very much
July 9, 2011 at 2:47 pm
8 drives is not a lot. Plus, performance depends on how your data will be accessed (heavier writes, reads or a split). Course, it's always a split, but by how much.
Recoverability first:
Drives 1 & 2: You could use two drives in a simple R1 set and partition it. C for the OS and paging file, D for backups (if there is enough room).
Drives 3 & 4: Another R1 set dedicated to the Tlog. Separation also helps with performance, but really, this is a recoverability decision.
Drives 5..8: R10 set where the mdf/ndf's live. The stripe can increase the size of this set to fit the large db, depending on how big it is.
The key here is to separate the backups from the log from the data. Best for protecting data. Also helps performance. If you loose a single drive set, you have better odds of zero data loss. Well, don't loose the tlog set though!
You may find that a R1 set doesn't give you the write throughput that you need, so you may choose to use R10 for the log and R1 for the data. Really, a lot depends on many factors. Also, I foresee potential backup disk space problems unless you add more drives. Again, depends on the drive sizes and the what you mean by 'a big database'.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
July 9, 2011 at 3:49 pm
Thank U Very much,
it is very nice explanation. For your information the database growth rate ( approximately) is around 10,000,000 records and contains text and image data. It is expected to be accessed my 10,000 people at the same time ( max).
For this Enterprise server purchase is under process. But, still that we decided to host the application to small/medium size server (HP Proliant DL380 G7 server) so that data can be encoded. After a month , we will migrate to another powerful server with 16 hard disk. Is there any issue to be considered ahead that will be very important during migration from current server to new Server( RAID level for TL , TempDB, Data.....)?. what is your advise about TempDB? Is it better to place with Tlog or Separate array?
Thank U
July 10, 2011 at 3:48 am
assefam (7/9/2011)
After a month , we will migrate to another powerful server with 16 hard disk.
why not just wait until you migrate to the larger server?
disk configs for 16 disks would provide a lot more options. What disk sizes do you intend on purchasing?
exactly how large currently is your database?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2011 at 8:13 am
I agree with Jim Murphy, but with relation to disks 5-8, i'm prefer raid 5 or 6, because with raid 10 I have more performance on writes on disk, but depending of disk that to fail, i can lost everything! and now, raid 5 or 6, i can lost one or two disk (respectively) that I have High Availability.
and more performance on reads of disk!!!
July 11, 2011 at 8:26 am
rfr.ferrari (7/11/2011)
I agree with Jim Murphy, but with relation to disks 5-8, i'm prefer raid 5 or 6, because with raid 10 I have more performance on writes on disk, but depending of disk that to fail, i can lost everything! and now, raid 5 or 6, i can lost one or two disk (respectively) that I have High Availability.and more performance on reads of disk!!!
1) Your knowledge is limited here rfr. You can lose HALF of all the drives in a raid 10 if they all fall on the same side of the stripe.
2) OP - you have no hope of serving up 10K users on your database unless that database fits in memory on just 8 drives, and most likely not on 16 drives either, especially given 10M inserts/day if I read that correctly.
3) You REALLY need to engage a qualified professional to help you determine what your IO needs are and then test various configurations to help get the best chance for success here. A forum thread will NOT get you optimized for this system!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 11, 2011 at 8:27 am
rfr.ferrari,
Your comments are not true. For raid 10, in worst scenario you can affrod having 3 disks failure (two mirrorred disks + 1) since raid 10 is mirrorred striped array.
Pei
July 11, 2011 at 9:30 am
I have nothing to say! only to thank, because every day I learn more! and have long enough humility to recognize that I am wrong!
Jim Murphy, Pei Zhu-415513, TheSQLGuru, my apologies!! you're right!!!
assefam, forget my reply!!!
July 11, 2011 at 9:50 am
Pei Zhu-415513 (7/11/2011)
rfr.ferrari,Your comments are not true. For raid 10, in worst scenario you can affrod having 3 disks failure (two mirrorred disks + 1) since raid 10 is mirrorred striped array.
Pei
This is not correct. In R10, you can easily loose 1 any of the 4 disks. Or, you could loose 2 of them (in a four disk set, this is 1/2 of the set), DEPENDING on which two, as Kevin said. And you definitely can not loose 3 out of 4 no matter what (in a 4 disk set).
In your example, loosing the mirrored pair is fine, but if you loose one of the two that make up a stripe, then you just lost 1/2 of the physical non-redundant data - meaning it is all gone. Striping is not 'really' RAID because it is not redundant.
Edit: fixed grammar. Clarified 3 out of 4, not 2 out of 3.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
July 11, 2011 at 10:20 am
Jim,
Say raid 10 like below
1 1 1 1
1 1 1 1
you can have
0 0 1 1
0 1 1 1
(0 indicates failure drive)
That was what I meant 'in worst scenario).
I believe I am correct.
Pei
July 11, 2011 at 10:55 am
Pei Zhu-415513 (7/11/2011)
Jim,Say raid 10 like below
1 1 1 1
1 1 1 1
you can have
0 0 1 1
0 1 1 1
(0 indicates failure drive)
That was what I meant 'in worst scenario).
I believe I am correct.
Pei
Your using an 8-drive set in your example to try to prove an incorrect claim about a 4-drive set. In a 4-drive set R10, you can not loose 3 out of 4 drives. You can only loose up to 1/2 the number of drives (2 out of 4 in a 4-drive set), and only a 50% loss is tolerated in some cases. Just like what Kevin said.
In an 8-drive set, you can loose up to 4 drives, depending on which fail.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
July 11, 2011 at 11:11 am
Jim,
I was assuming raid 10 with 8 disk thru the topic title which was not true. In my opinion, raid 10 with 4 disks or raid 5 with 2 disks does not make any sense.
Pei
July 11, 2011 at 11:49 am
Pei Zhu-415513 (7/11/2011)
... raid 5 with 2 disks does not make any sense
R5 with 2 disks is impossible. A minimum of 3 disks are required for RAID 5.
R10 may sometimes be required with only 4 disks. More disks is always better, but there are only 8 total on the Orig Posters system and this was my opinion of what might work best for his setup.
If you have a differing opinion about how his 8 drives should divided, please post what you think. Education will help him decide which is best for his environment.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
July 11, 2011 at 12:16 pm
Jim,
Yes. Traditionally raid 5 needs at least 3 disks. But I clearly remembered our windows admin configure raid 5 with 2 disks when he installed the brand new servers(HP server) since it suprised me a lot. Not sure if it was a virtual raid 5 or what. I could not figure out how parity could work on raid 5 with 2 disks if it is doable.
8 disks would be hard to play around. What about just a raid 10 with 8 disk for all since the physical separations with limited spindles might not benefit you with any i/o performance gain.
Pei
July 11, 2011 at 12:47 pm
Pei Zhu-415513 (7/11/2011)
Jim,Yes. Traditionally raid 5 needs at least 3 disks. But I clearly remembered our windows admin configure raid 5 with 2 disks when he installed the brand new servers(HP server) since it suprised me a lot. Not sure if it was a virtual raid 5 or what. I could not figure out how parity could work on raid 5 with 2 disks if it is doable.
8 disks would be hard to play around. What about just a raid 10 with 8 disk for all since the physical separations with limited spindles might not benefit you with any i/o performance gain.
Pei
It's not a traditional vs modern requirement. You can learn about the various RAID levels here: http://en.wikipedia.org/wiki/RAID, including the minimum number of disks for each setup. R5 has always needed at least 3 disks, and will continue to need at least 3 disks.
2 Disks Mirrored is called R1, and 2 disks Striped is called R0 (which isn't redundant).
The orig poster could do as you suggest by making one 8-disk R10 volume and he'd probably get pretty good throughput generally. However, the lack of separation of the log onto its own set could hurt performance over a separate R1 set because the disk heads will constantly be moved out of alignment (among other performance implications).
However, the biggest deal-breaker, is that the backup files would end up being on the same physical disks as the data files as well as the transaction log. This is very bad.
It is never a better recommendation to advise someone to put performance ahead of recoverability. Minimizing data loss exposure in the event of a failure is the #1 priority of a DBA. After the data is protected, THEN performance is next most important.
Therefore, I'm going to advise the OP to not follow your opinion, because although it would technically function, it places his data at very high risk of loss, as well as him loosing granularity to tune performance from a disk IO perspective.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply