October 9, 2013 at 11:59 am
inevercheckthis2002 (10/9/2013)
Kurt W. Zimmerman (10/9/2013)
4) System, Data, Logs & TempDB should be on their separate LUNs.With sincere apologies the thread hijacking... I'm trying my best to gain insight to the best use of the hardware at hand. It will be in use next week and I'm trying to get it right from the start. I'd follow best practices and recommedations whenever I am able, but sometimes there are limited resources.
I have 16 disks - direct attached storage, one RAID controller. The OS is already on a 2 disk RAID1.
Which is best?
A.) One RAID 10 array across 14 disks, giving me the best I/O from an IOPS perspective. Install data, logs, tempdb - could be separate partitions, if that matters.
B.)One RAID 10 array across 12 disks for Data and Logs (could be separate partitions). One RAID1 (2 disks) for tempdb files, sized as you describe above.
C.) One RAID 10 array across 10 disks for Data. One RAID 1 (2 disks) for logs (simple recovery model). One RAID1 (2 disks) for tempdb files.
It's the same controller and the same disks - wouldn't option A be best?
I would favor C if I had to choose.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 9, 2013 at 12:20 pm
I would suggest you find out your percentage of reads to writes before deciding this. The limited hardware you have listed here isn't going to support a very busy database but I have no idea what your dealing with in that area or even your disk speeds. . You could make better use of it if you discover you have 85%reads to writes for instance by going with raid 5. Look what you are suggesting for TempDB and Log. That is going to get you at the most 160-200IOPS with 15K SAS drive. A generic answer of separation may actually hurt you more in such a situation.
I don't always test my SQL scripts, but when I do, I test in Production.
October 9, 2013 at 2:06 pm
KTD (10/9/2013)
I would suggest you find out your percentage of reads to writes before deciding this. The limited hardware you have listed here isn't going to support a very busy database but I have no idea what your dealing with in that area or even your disk speeds. . You could make better use of it if you discover you have 85%reads to writes for instance by going with raid 5.
The disks are 15K RPM SAS drives. The databases are used for analytics and have daily and hourly data loads from another database. It needs to be optimized for writes. So, RAID10.
KTD (10/9/2013)
Look what you are suggesting for TempDB and Log. That is going to get you at the most 160-200IOPS with 15K SAS drive.
Exactly!
But, as an aside, are you familiar with SQLIO? Here's my results on a two drive RAID1. I was expecting 200 IOPS max and I'm wondering why it reports what it does. I would have a tempdb with 8 files on the two drive RAID 1.
using system counter for latency timings, 2212939 counts per second
8 threads writing for 120 secs to file F:\TestFile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 24576 MB for file: F:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 2583.37
MBs/sec: 161.46
KTD (10/9/2013)
A generic answer of separation may actually hurt you more in such a situation.
That was my thinking, but while researching this I almost always see the recommendation to separate them. Thus, my queries here to get some further insight.
It seems that you would support option A - aggregate the I/O over 7 disks mirrored to 7 disks and let the chips fall where they may? That was my original plan, but I started researching this some more to make sure it was the best plan, given the resources.
Is there any way I could test performance of option A over option C, in the next couple of days? Any tools I should try?
Thank you all very much for your comments!
October 9, 2013 at 7:44 pm
I am fairly confident your not getting over 2000 IOPS from 2 disk.
I have a similar situation as you for my UAT server that is a Dell 510 with a MD1220 DAS and I had to make compromises for this due the budget the organization was willing to spend for UAT. In this case I was able to put in 4 NL storage disk inside the server for space and 6 15K disk for tempdb all Raid10. With 20 disk in the DAS Raid5. Not many writes. Not sure if you have that option to add any disk to the server.
Not knowing your application. It sounds like you could put this DB into Bulk Logged and save a great deal of logging IO. I would think from what you say there might be heavy usage of tempdb. I tend to agree with you on the first option if you cannot get any more investment except that i would split it into two sets of 8 disk. I would be sure your managers know the risk. Sometimes you just have to do what you can with what you are given to get the job done. If you do have time to test these options that would be the best.
I don't always test my SQL scripts, but when I do, I test in Production.
October 10, 2013 at 5:34 am
KTD (10/9/2013)
It sounds like you could put this DB into Bulk Logged and save a great deal of logging IO.
We're in Simple Recovery Model.
October 10, 2013 at 6:03 am
Operations that can be minimally logged are minimally logged in both simple and bulk-logged recovery so I think your good to go. I just realized you have this posted in it's own posting. I'll move over there. to read.
I don't always test my SQL scripts, but when I do, I test in Production.
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply