Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

separate disks for data / logs / tempdb / system Expand / Collapse
Author
Message
Posted Monday, October 7, 2013 9:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:00 AM
Points: 74, Visits: 319
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?




Post #1502199
Posted Monday, October 7, 2013 9:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:13 PM
Points: 33,100, Visits: 15,210
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







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1502236
Posted Monday, October 7, 2013 11:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:00 AM
Points: 74, Visits: 319
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.
Post #1502273
Posted Monday, October 7, 2013 12:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:13 PM
Points: 33,100, Visits: 15,210
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1502303
Posted Monday, October 7, 2013 1:18 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:00 AM
Points: 74, Visits: 319
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..

Post #1502326
Posted Monday, October 7, 2013 5:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:13 PM
Points: 33,100, Visits: 15,210
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1502428
Posted Monday, October 7, 2013 7:23 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:00 AM
Points: 74, Visits: 319
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?




Post #1502445
Posted Tuesday, October 8, 2013 4:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:20 PM
Points: 112, Visits: 802
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
Post #1502520
Posted Tuesday, October 8, 2013 4:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:00 AM
Points: 74, Visits: 319
sqlsurfing (10/8/2013)
[quote]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!
Post #1502539
Posted Thursday, October 10, 2013 2:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:20 PM
Points: 112, Visits: 802
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/




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
Post #1503420
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse