August 3, 2010 at 3:36 am
I am creating a new OLTP database cluster. I need help on deciding which disk option I should take for best performance. Please elaborate on your reasoning.
Option 1
1 RAID 10 group with 4 disks
LUN1 cluster quorum
LUN2 temp log
LUN3 user db log
-or-
Option 2
2 RAID 1 groups with 2 disks
Group 1
LUN1 cluster quorum
LUN2 temp log
Group 2
LUN3 user log
Here's an example diagram:
Thank you,
Robert
August 4, 2010 at 10:05 am
Sounds like you are asking should you install raid 1 or 10 for DB files?
August 4, 2010 at 12:00 pm
for an OLTP system shared storage is a bad idea!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 4, 2010 at 1:03 pm
Correct. Which is better for performance?
August 4, 2010 at 1:05 pm
Perry Whittle (8/4/2010)
for an OLTP system shared storage is a bad idea!
Why Perry? Could you please elaborate? What if I had separate physical disks dedicated to the OLTP SQL server? Isn't the industry going towards a shared environment SAN/NAS?
August 4, 2010 at 1:27 pm
Saying shared storage is bad idea, can be correct in certain situations. most likely not!
You are correct, This is a SAN not DAC this can change things up a bit
...it all Depends just like most things in SQL.
There are lots of ? is ask...just a few without going in to all of them
1. Are these Db 90% Reads/ 10% writes then you may get fine performance out of Raid 5
2. Then you must ask yourself what I am sharing these LUNS with....Depends if you San guy spun up a few other Luns on this array that are busying doing Writes...this could change things a Bunch.
I have shared many DB's on 1 LUN and they can be just FINE...I have gotten very fast performance then again I had long Page life expectancy somewhere in the 10,000 area meaning almost everything was in RAM.
There many things to considered! here....
Rule of thumb when i set up is this....If they are write intensive keep them on Raid 10 and read then raid5..this may or may not be ok...
After you set them up go back and check the following
1. Page life expectancy > 1000 to be safe 300 at the least if its below 1000, add more ram..max sure you set min. max server mem
2. Check Avg. Disk Sec/Read and Write if its below .020 mil you should be fine....005 mil would be ideal
If you still not sure then Figure out the IOP on the array and who many you need my guess you are running 15k disk which should give you about 180 iops per sec.
there are lots of threads out there that go into this....at the end of the day my guess is this is not a busy server.
You have set everything else correct so my guess is you should be ok......Good LUCK
August 4, 2010 at 1:43 pm
OK, taking RAID group2 from your diagram link above you have 1 RAID 10 group carved up to service
TEMPDB data
user DB data
Backup data
Sharing storage amongst 3 LUNs will likely not provide best performance. If you're going to place all files on the same physical array anyway, why bother splitting the files out onto separate LUNs? Dedicated disks offer better performance and allow you to configure the arrays independently (stripe size, RAID, etc)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 4, 2010 at 2:04 pm
Perry Whittle (8/4/2010)
OK, taking RAID group2 from your diagram link above you have 1 RAID 10 group carved up to serviceTEMPDB data
user DB data
Backup data
Sharing storage amongst 3 LUNs will likely not provide best performance. If you're going to place all files on the same physical array anyway, why bother splitting the files out onto separate LUNs? Dedicated disks offer better performance and allow you to configure the arrays independently (stripe size, RAID, etc)
So option 2 would be your recommendation?
Group 1 (physical disk 0 & 1)
-LUN1 cluster quorum
-LUN2 temp log
Group 2 (physical disk 2 & 3)
-LUN3 user log
Diagram again - http://xenmaster.com/ISCSISAN2NodesSQLCluster.aspx
August 4, 2010 at 2:21 pm
neither, in my opinion RAID group 2 is the weak point. I would opt for multiple smaller arrays!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 4, 2010 at 2:42 pm
Take out the guess work use the following link...http://www.wmarow.com/strcalc/
with raid 10, 4 disks, 8 pages and 64k extents, 10% read/90% writes,15 k RPM = 185 random iops so your numbers are going to higher then random due the logs....
total workload average random IOPS == about 400 iops
total workload average throughput (MiB/s) == 25
This takes guess work out of the mix
your only question is what do you need? as far as iops or mib/s goes
set everything up then run SQLIO ==this will tell you what it is capable of...this Great info to have anyhow ---
http://sqlserverpedia.com/blog/sql-server-performance-tuning/sqlio-tutorial/
August 4, 2010 at 3:09 pm
That's an excellent link. IMHO anybody who hooks a SQL Server system up to a storage system without completing IO tests needs their brain looked at 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 4, 2010 at 3:20 pm
456789psw (8/4/2010)
with raid 10, 4 disks, 8 pages and 64k extents, 10% read/90% writes,15 k RPM = 185 random iops so your numbers are going to higher then random due the logs....
total workload average random IOPS == about 400 iops
total workload average throughput (MiB/s) == 25
This takes guess work out of the mix
your only question is what do you need? as far as iops or mib/s goes
set everything up then run SQLIO ==this will tell you what it is capable of...this Great info to have anyhow ---
http://sqlserverpedia.com/blog/sql-server-performance-tuning/sqlio-tutorial/%5B/quote
Useful link indeed. Thanks 456789psw. What do you suggest to gather the percentage of read and write once SQL is in production?
August 4, 2010 at 3:28 pm
you can run perfmon to get the actual after you up and running, just make sure you select the drive and not Total
Phyical disk %disk write time
Phyical disk %disk read time
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply