June 9, 2011 at 6:37 am
Hi all,
We have some nice new shiney disks for our sql cluster. Its running 3 instances and we have 16 disks.
My idea was to put:
8 disks RAID 10 DATA
4 disks RAID 10 LOG
4 disks RAID 10 TempDB
Ive been given no real time to test and play with the SAN before i set it up and put it out live.
What i know is:
we used to have our all our data and logs on a raid 5 using 16 disks which was shared with a file server. a quick test on it showed it was pretty fast with crystalDiskMark. 200 Seq Reads 170 writes
When we decided to move i set up a temp space of Data on RAID 5 on 3 disks and Logs on a RAID1
the crystaldiskMark scores where a lot lower which im guessing is due to the lack of spindles compared to before?
So with that im mind, im a bit worried that yes RAID10 is great. and having Tempdb, Data and logs all seperate is awesome. but with only 4 disks is the performance worth it? compared to maybe 8 disks raid 10 DATA, 6 disks RAID 10 Log RAID1 Tempdb?
Im not sure how to check if Tempdb is used a lot which would proof it better seperated that stuck with data/log.
Also read a recent Danny Cherry blog post saying he would properly jsuty default it to split 8 data / 8 log type thing. If you can go though and work it all out.
Any advice?
June 9, 2011 at 11:52 am
I would suggest carve out your partition and do some IO test.
Data would require combination of write and read geared more towards reads depending on your database.
Log would require testing out writes.
TempDB would be a combination of read and writes.
Then see how much combination of using RAIO 10 with x number of disk versus RAID1 makes a difference.
-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer
June 9, 2011 at 12:22 pm
Not to make it more complicated, but where are backup files going? I'd make sure you have space somewhere separate from the data files.
Do you use tempdb a lot? If it's not really heavy activity, I might stick it with the data and add more spindles to the data partition.
The other thing to be sure is that you have spare spindles in case of failures R10 is very tolerant, but if two disks in the same mirror set fail, it's down. I might make sure that I have disks ready for rebuild.
June 9, 2011 at 1:06 pm
Hi Guys,
Ive basically been told i have no time to do any tests on SAN when it gets here. so much as i would love to, its not an option..
Steve for Tempdb usage. I dont know how you would find out if Tempdb is used a lot. 🙁
Back ups are being done by DPM so it back ups to the DPM's DAS. And we have 2 hot spares on the jbod
I have just seen some comparisons of RAID10 SAS 15k (What we are about to get!) with 4-6 disks and RAID1(2 disks). And the difference is crazy how much better 6 is, compared to 4! never mind RAID1!
Never really thought about how much of a performance boost more spindles would add...
So i guess it comes down. Is there a quick way to find out if Tempdb is being used a lot. and if not . go straight for 2 8 disk raid 10s?
June 9, 2011 at 1:21 pm
I'd look at virtual file stats for the tempdb data file.
Quite a few posts here as well: http://blogs.msdn.com/b/sqlserverstorageengine/archive/tags/tempdb/
Ultimately it comes down to measuring how much the file is being used (I/Os, bytes read/writen, etc) over some time and trying to determine if it's a lot compared to the usage on your other files. If it's not a significant percentage, combine it with the other files.
June 10, 2011 at 7:11 am
What SAN are you using?
What are the size / speed of the drives you are using?
What Win/SQL platform are you building this for?
What is business need of the database(s) OLAP, OLTP, Reporting, CRM, Web Portal, etc?
John Zacharkan
June 10, 2011 at 9:24 am
zach_john:
What SAN are you using?
LSI (Model im unsure off im afriad)
What are the size / speed of the drives you are using?
SAS 15k 150gb (Space will never be an issue, larges DB is 25gb and only half of the is data)
What Win/SQL platform are you building this for?
2008 r2 ent for both
What is business need of the database(s) OLAP, OLTP, Reporting, CRM, Web Portal, etc?
I guess it would be defined at OLTP
Steve:
thanks, not had a chance to read it, but shall take a look over the weekend!
S
June 11, 2011 at 4:41 pm
stebennettsjb (6/9/2011)
Hi all,We have some nice new shiney disks for our sql cluster. Its running 3 instances and we have 16 disks.
My idea was to put:
8 disks RAID 10 DATA
4 disks RAID 10 LOG
4 disks RAID 10 TempDB
Ive been given no real time to test and play with the SAN before i set it up and put it out live.
What i know is:
we used to have our all our data and logs on a raid 5 using 16 disks which was shared with a file server. a quick test on it showed it was pretty fast with crystalDiskMark. 200 Seq Reads 170 writes
When we decided to move i set up a temp space of Data on RAID 5 on 3 disks and Logs on a RAID1
the crystaldiskMark scores where a lot lower which im guessing is due to the lack of spindles compared to before?
So with that im mind, im a bit worried that yes RAID10 is great. and having Tempdb, Data and logs all seperate is awesome. but with only 4 disks is the performance worth it? compared to maybe 8 disks raid 10 DATA, 6 disks RAID 10 Log RAID1 Tempdb?
Im not sure how to check if Tempdb is used a lot which would proof it better seperated that stuck with data/log.
Also read a recent Danny Cherry blog post saying he would properly jsuty default it to split 8 data / 8 log type thing. If you can go though and work it all out.
Any advice?
I suggest Raid 1 for TempDB.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply