SQL Server Performance Forum – Threads Archive
Raid configuration for 6 hard disks
Hi, We are using SQL W2k standard with sp4. We have a transactional database server dedicate for housing 8 SQL databases. It is a IBM e-server which has 1 controller, two cpu and 2 Gbytes of Ram. Currently the server only has 4 disks and we are going to put in 2 more to fill all the available slots in the server. We have two Raid 1 set up for it righ now. C drive consists of OS and log, D drive has all the data. This server is also involving in replication as the publisher with push method so there is some IO traffic from there. The daily transaction maybe about Out of the 8 databases in the server, there is only one very active and the rest is low uaage. The active database is 13 Gtyes in size and has about 60 (write)/40 (read) split activity ratio. 80 % of the time, the avg disk write queue length and avg read queue length are under 4 but from time to time it would jump to between 6 and 250. The situation of IO >100 doesn’t happen often though, maybe once every few hours. So from what I have gathered performance statistics IO wise, I can safely say that the server can handle daily read/write load 80 to 85 percentage of the time currently. To us, it is acceptable. Since we are going to put in two more hard drives, I am wondering what is the best raid configuration for it. One thing for you to be aware of is that we are doing log shipping every 20 minutes to a standby server so I am not worry about data of log recovery if there are hardware issues that may bring down our transactinal server. I am just worried about performance. With that in mind, I am thinking the following Raid configuration options. 1. 3 sets of Raid 1. OS and tempdb in one, log in another one, and data in the third one.2. 1 Raid 1 and 1 raid 5 (4 disks). OS and log in Raid 1, and tempdb and data in Raid 5.
3. 1 raid 1 and 1 raid 10 (4 disks). OS and log in raid 1, and tempdb and data in raid 10.
4. 1 set of raid 5 (6 disks)
5. 1 set of raid 10 (6 disks) Here are my questions. 1)what is the difference between partitioning hard drive using the raid utility from the vendor compared to windows disk management tool. The Raid tool from the vendor will configure the raid and also partition the raid before we even install Windows 2K OS. Does using this kind of Raid tool have any advantage doing the partition at this level to enhance performance compared to doing the partition in W2k disk management tool. Let take option 4 above for example, I will use the IBM Raid utility to set up one phyical raid 10 and partition it to three logical raid 10 volumes. Will this mean it would allocate three dediated areas from the physical Raid 10 for these three volumns? I like to know this because option 2,3, 4, and 6 will require some form of partition. 2) Snce the server only have one SCSI controller, will it matter at all for any options mentioned above since all IO will go through this single controller anyway? So why not just choose option 4 or 5 to enhance the read/write across the maximum spindles we have. 3) Am I correct that option 4 will have better Read performance and option 5 will have better write performance? 4) I hope by my facts listed and my questions someone will have some ideas what I need. So which option would you suggest for my situation? I am leaning to option 3 but I haven’t considered others yet because I don’t know answer of #1 yet. I am not an expert in hardware and raid behavior so I like to hear some expert advice. Thanks in advance for any help regarding this. Wingman
i think we discussed this matter fairly recently in this very section, why don’t you look for posts in the last couple of months, then reply to that for any additional questions
quote:Originally posted by joechang
i think we discussed this matter fairly recently in this very section, why don’t you look for posts in the last couple of months, then reply to that for any additional questions
Yes, I did read some related posts and get some general ideas but they didn’t have an answer for #1. Plus since we only have six slots for hard disks in our server and it is a slight difference compared to what are posted out there. I thought there may be some better suggestion for it. Do you think the option 3 is the best and how about question #1? If I miss some posting, please give me some links if it is not too much work for you. Wing
#3 is probably good, the OS should be a noload if you configure your system properly, ie, when SQL Server does not have to release memory to other apps, which just means not to run too many 3rd party monitoring apps (preferably none at all, since they tend to cause more problems than they solve). well actually i am not convince they solve any problems except for soaking up unused budget, and i have seen them cause problems. as for disk counters, monitor Avg Disk sec/read, Avg Disk Queue Length, Read/sec, Read Bytes/sec and the same for writes,
do not waste time with %Disk time, it is a worthless counter in server systems with SCSI disks
Yes, I am with you. I don’t have any third party software in our SQL server so our SQL server is very pristine. So the OS should be very quiet. Do you think OS paging will affect tran log that is on the same drive as OS? On the other hand, I am still interested in your insight on my question #1. If the partition method using Raid utility from the vendor is better than the w2k disk management tool IO performance wise, I will use that instead. Please advice. Wing
quote:Originally posted by joechang
#3 is probably good, the OS should be a noload if you configure your system properly, ie, when SQL Server does not have to release memory to other apps, which just means not to run too many 3rd party monitoring apps (preferably none at all, since they tend to cause more problems than they solve). well actually i am not convince they solve any problems except for soaking up unused budget, and i have seen them cause problems.
as for disk counters, monitor Avg Disk sec/read, Avg Disk Queue Length, Read/sec, Read Bytes/sec and the same for writes,
do not waste time with %Disk time, it is a worthless counter in server systems with SCSI disks
do not waste time with %Disk time, it is a worthless counter in server systems with SCSI disks
i have always used the hardware controller,
the os raid requires dynamic disks, i am not sure of the current situation, but sql server uses some very specialized api calls, including one that were put into the os specifically for sql server,
early on, i had heard that dynamic disk may or may not support some of these calls, but i am not sure now, i do know that none of the performance benchmarks use os raid, so they would not have reported any performance problems with os raid, so it probably would not be a priority there should be zero activity to the page file if the system is configured correctly,
just monitor the avg sec /write on the log drive, it should be 0, or occasionally 1ms,
except when you backup the t-log, which causes much disruption than minor page file activity
quote:Originally posted by joechang
i have always used the hardware controller,
the os raid requires dynamic disks, i am not sure of the current situation, but sql server uses some very specialized api calls, including one that were put into the os specifically for sql server,
early on, i had heard that dynamic disk may or may not support some of these calls, but i am not sure now, i do know that none of the performance benchmarks use os raid, so they would not have reported any performance problems with os raid, so it probably would not be a priority there should be zero activity to the page file if the system is configured correctly,
just monitor the avg sec /write on the log drive, it should be 0, or occasionally 1ms,
except when you backup the t-log, which causes much disruption than minor page file activity
the os raid requires dynamic disks, i am not sure of the current situation, but sql server uses some very specialized api calls, including one that were put into the os specifically for sql server,
early on, i had heard that dynamic disk may or may not support some of these calls, but i am not sure now, i do know that none of the performance benchmarks use os raid, so they would not have reported any performance problems with os raid, so it probably would not be a priority there should be zero activity to the page file if the system is configured correctly,
just monitor the avg sec /write on the log drive, it should be 0, or occasionally 1ms,
except when you backup the t-log, which causes much disruption than minor page file activity
Ok, sounds good. Just want to clarify my statements/questions in my previous emails. I always use hardware Raid to configure our Raid array. While configuring the hardware raid using the hardware vendor tool, the tool also allows me to partition the raid at the same time. In my example, say, I want to have two vitrual partiion of raid 10 on my data drive. My question in my previous emails was referring to whether I should do the partition right there or defer it later to let W2k disk management tool do the partition. Is there any performance gain if I do the partition during the hardware raid configuation? Based on your reply, it seems I should do the partition during the Raid configuration and don’t use W2K disk management tool to do it. Am I correct? It seems my configuration is quite clear now. On my Raid 1 disks, I will have two virutal partition, one for OS and one for tran log. On my Raid 10 disks (a total of four physical disks), I will also have two virtual partitions, one for the data and one for the tempdb. Do you think it is necessary to create a virtual raid 10 for tempdb? Our tempdb is used by application quite often due to the sorting and temp tables. Wing
i am not aware of a problem using the OS for partitioning, as opposed to raid, the only time it is necessary to use the hw raid controller is if you wanted different raid levels for one set of disks
example: 4 physical disks, 1 raid 10 part for data, 1 raid 5 for backup
upto you on the tempdb, were you thinking raid 0?, is it ok for the server to restart should a disk fail while the data is protected?
]]>