October 7, 2013 at 9:08 am
I have 16 drives and one RAID controller. Performance is one big consideration, but storage space is another.
The databases will be updated daily (sometime hourly) with data loads form other databses. I want to optimize for writes.
I'm planning on using 2 disks in a RAID 1 for the OS (actually, the server came that way). I'm also planning on configuring the other 14 disks as RAID10.
I don't need to recover to a point in time, so I'm going to use simple recovery model.
I don't see an advantage to allocating disks specifically for logs and tempdb files by configuring a 10 disk RAID 10 for data, a 2 disk RAID 1 for logs and a 2 disk RAID 1 for tempdb. The I/O would still be written across the same number of drives.
Is the 14 disk RAID 10 the best way to allocate the drives?
October 7, 2013 at 9:58 am
Personally I'd make sure I had separate disks for backups. You could put logs there or tempdb if you want, but if you lose data drives for some reason, make sure backups are elsewhere.
I might do either of these, depending on space.
- 2 disk R1 for OS
- 2-4 disk R1/R10 for backups (maybe logs/tempdb, depending on space)
- 10-12 disk R10 for data
or
2-6 disk R10 for OS/backups
14-10 disk R10 for data/logs/tempdb
October 7, 2013 at 11:14 am
Thanks - backups will be saved to a file share on a NAS.
My question is whether there is a performance gain that might be realized by separating the physical disks from:
14 drive RAID 10 holding data / logs and tempdb
to:
10 drive RAID 10 holding data
2 drive RAID 1 holding logs
2 drive RAID 1 for tempdb
The recommendations that I've seen suggest that separating them is advantageous.
It seems to me that the 14 drive RAID 10 would give me the best write IOPS, and if logs or tempdb were at all write intensive that a 2 drive RAID 1 might be a bottleneck.
October 7, 2013 at 12:29 pm
You'd have to test it for your workloads. It might, or might not matter.
Be careful of NAS. If there are any issues sending large backup files, the backups fail.
October 7, 2013 at 1:18 pm
Steve Jones - SSC Editor (10/7/2013)
You'd have to test it for your workloads. It might, or might not matter.
Kind of hard to test it. I'm building a new server and I'd like to get it right from the start.
I was hoping to hear from someone who can shed some insight on the question..
October 7, 2013 at 5:48 pm
There are testing utilities, like SQLIO to measure raw performance. In terms of what your workload does, no one can tell you that. The workload on my disks doesn't apply to yours. That's one of the problems with database sizing.
You can get a trace from your existing system and run that. Otherwise you'll have to make some guesses on reads v writes, sequential v random IO.
October 7, 2013 at 7:23 pm
Steve Jones - SSC Editor (10/7/2013)
There are testing utilities, like SQLIO to measure raw performance.
With fourteen 15k RPM SAS drives in a RAID 10 array, at a generous 200 IOP per disk, I should have (at most) 1400 write IOPS and 2800 read IOPS.
SQLIO reports 6000 - 8200 IOPS.
Isn't the raw performance dictated by the physical specs of the drives in the array? What does SQLIO bring to the table? How can I be getting the 6000 - 8200 IOPS it is reporting???
That's why I have my doubts about the value of SQLIO...
Steve Jones - SSC Editor (10/7/2013)
In terms of what your workload does, no one can tell you that. The workload on my disks doesn't apply to yours. That's one of the problems with database sizing.You can get a trace from your existing system and run that. Otherwise you'll have to make some guesses on reads v writes, sequential v random IO.
But my question was specific to whether I can gain an advantage by splitting off tempdb and/or logs from the data disks, given that I have 14 disks to work with. To me, the advantage is in aggregating the IO with one large array.
What advantage can be gained by segregating tempdb files and/or a log files on arrays with less IO capability?
October 8, 2013 at 4:01 am
inevercheckthis2002 (10/7/2013)
Steve Jones - SSC Editor (10/7/2013)
There are testing utilities, like SQLIO to measure raw performance.With fourteen 15k RPM SAS drives in a RAID 10 array, at a generous 200 IOP per disk, I should have (at most) 1400 write IOPS and 2800 read IOPS.
SQLIO reports 6000 - 8200 IOPS.
Isn't the raw performance dictated by the physical specs of the drives in the array? What does SQLIO bring to the table? How can I be getting the 6000 - 8200 IOPS it is reporting???
That's why I have my doubts about the value of SQLIO...
Steve Jones - SSC Editor (10/7/2013)
In terms of what your workload does, no one can tell you that. The workload on my disks doesn't apply to yours. That's one of the problems with database sizing.You can get a trace from your existing system and run that. Otherwise you'll have to make some guesses on reads v writes, sequential v random IO.
But my question was specific to whether I can gain an advantage by splitting off tempdb and/or logs from the data disks, given that I have 14 disks to work with. To me, the advantage is in aggregating the IO with one large array.
What advantage can be gained by segregating tempdb files and/or a log files on arrays with less IO capability?
I was curious for the SQLIO test was the test on random and sequential read/writes? Were they all same values or different in SQL IO?
In regards to your question splitting tempdb and/or logs from data disks.
It's considered best practice to split tempdb and logs. By combining temp and data disks there will be greater disk contention since tempdb is likely highly utilized. Like wise similar issue can arise combining log and data when the data is written to the log there can be disk contention. In addition from a database recovery standpoint, separating logs from Data provides a potential safety measure where if the data files are corrupted we can recover from a full back and the database logs w/assumption we are in full recovery and have appropriate logs available.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
October 8, 2013 at 4:34 am
sqlsurfing (10/8/2013)
I was curious for the SQLIO test was the test on random and sequential read/writes? Were they all same values or different in SQL IO?
8 threads writing for 120 secs to file D:\TestFile.dat
using 8KB random IOs
throughput metrics:
IOs/sec: 6027.19
MBs/sec: 47.08
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 10
Max_Latency(ms): 263
8 threads reading for 120 secs from file D:\TestFile.dat
using 8KB random IOs
throughput metrics:
IOs/sec: 7349.60
MBs/sec: 57.41
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 8
Max_Latency(ms): 233
8 threads writing for 120 secs to file D:\TestFile.dat
using 64KB sequential IOs
throughput metrics:
IOs/sec: 8265.63
MBs/sec: 516.60
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 7
Max_Latency(ms): 228
8 threads reading for 120 secs from file D:\TestFile.dat
using 64KB sequential IOs
throughput metrics:
IOs/sec: 17319.85
MBs/sec: 1082.49
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 3
Max_Latency(ms): 99
The results from SQLIO are greater than what I calculate the array can provide because I was using 8 threads, I presume?
Steve Jones - SSC Editor (10/7/2013)
You can get a trace from your existing system and run that.
What do I use to get a 'trace' and will it tell me how much tempdb is utilized?
...and thanks for your help with this!
October 10, 2013 at 2:57 am
The results from SQLIO are greater than what I calculate the array can provide because I was using 8 threads, I presume?
Wonder what IOMeter would give?
http://www.sqlservercentral.com/blogs/sqlmanofmystery/2011/06/29/understanding-benchmarks/[/url]
Steve Jones - SSC Editor (10/7/2013)
You can get a trace from your existing system and run that.
What do I use to get a 'trace' and will it tell me how much tempdb is utilized?
...and thanks for your help with this!
I'm not sure but maybe Steve means a Profiler Trace? I would run a server side trace and filter as many things out as possible only tempdb? But not sure if that's what he meant...or if he meant something tracing w/extended events maybe?
Another idea comes to mind is you can search for a query on the Virtual io file stats DMV. This will show every read/write IOs that was written to the SQL Server for each data and log file since reboot. Although it won't show in size how large each SQL statement/transaction to TEMPDB it can give you an idea the number of reads/writes compared to your "date files"
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
October 10, 2013 at 4:15 pm
you can use a server side trace to get your workload. Then replay that against your new disk system and see what results you get. Change disks, do this again. This can help you understand how your workload runs against the different configs.
I believe SQLIO uses the separate threads and aggregates, which would explain your results. You might read through these to help:
http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply