August 21, 2010 at 3:01 pm
Hi,
I am tuning a disk sub system for our OLTP datebase server these
days. This is DAS disks. I now came into a question.
The server has 8 148GB SAS drives. To have fault tolerance, we want to
have RAID mirroring, so RAID 1 or 10. That leaves us half of the
whole capacity, which is 4 * 148GB. As this box is only used for
database, we want to have partitions for OS, data file, log file and
temp db. Now I there is a dilemma:
I know for best practise, temp db should on its own physical disk. If I set up four partition(logical drives), one for OS, one for data
files, one for logs and one for temp db, then I can only have RAID 1
on each of the disk array. But the advantage of this is temp db is
only its own disk.
Or the second choice I can have is to partition the disks into three
array (logical drives), namely OS, data files and logs. In this case I
can have RAID 1+0 (striping and mirroring) on data file partition and
RAID 1 on OS and log.
Could you give me some idea, which one is better? Any
disscussions are welcome. Thank you very much.
August 23, 2010 at 2:04 pm
There's no simple or right answer here, as the answers are based on your environment and requirements.
4 things need to be taken into account when considering best practice and the real world.
1) Redundancy
2) Performance
3) Recoverability
4) Management
In a perfect world I would want each of the OS, Data, Log and TempDB files on their own physical drives, but with only 8 X 148GB drives to play with, and the resulting waist if TempDB never goes above 20GB this isn't realistic. I'm assuming you have decided to go RAID 10 and not RAID 5 at all.
You'll need to consider how critical performance is in your environment and how much will it be affected by having everything on one set of RAID 10 disks - This will give you the best use of the space, but may result in IO contention.
What about having 2 X 148 as RAID 1 for OS and TempDB, since the OS is low IO, you won't get IO contention. Also losing the OS and TempDB presents the lowest risk to your environment. Then the other 6 disks as RAID 10 for Data and Log, or 4 disks RAID 10 (Data) & 2 disks RAID 1 (Log).
Alternately you could put the Logs on the OS Drives and TempDB on it's own drive.
With theses options you will need to limit the size of TempDB or the logs to make sure they don't fill the OS drive (or create a separate partition) otherwise you could kill the OS by filling up the drive.
No straight answers, but a few ideas.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 24, 2010 at 10:13 am
I'm not a big fan of RAID 10. You loose half of your disks to the RAID overhead and, while you can sustain two disk losses, they have to be one on each side of the mirror - it can't be two random disks.
RAID 6 on the other had can lose *any* two disks, while at the same time you only lose two disks to the RAID system overhead. Compared to RAID 10 you have both more efficient space usage and better recovery ability. Performance characteristics are similar to RAID 5.
While it would be nice to separate the OS, Page file, Data, Log, Indexes, and TempDB onto separate physical drives, I don't think that's going to be practical given the disks that you have. You'll probably want to take a look at your usage pattern and perhaps do some modelling.
Generally, I consider the OS and the database executables to be perhaps the easiest of all of these to recover. At worst you can do a reinstall and be ready to go - there's little that is specific to your application in these files. Since TempDB is rebuilt every time the server is booted there's little to recover, so I'd recommend using RAID for TempDB only if you feel can gain some performance. Recovering your data is another story. You'll want to make sure you have as much protection and performance for your data as is possible.
If you want to start simple, I'd go with a striped pair for the OS and Paging file, a single physical drive for TempDB, and put the remainder in a RAID 6 array, giving you a total drive space of 3x148Gb for your database. If you want more space for the database you could use a single physical drive for the OS and move one more disk into the RAID 6 array giving you 4x148 for your data, logs and indexes. As you better understand your performance needs you might want to move the indexes, logs, etc. off to other physical drives or arrays.
Basically, start simple, then test. You'll be a lot better off if your needs can be met with a simple configuration instead of burdening yourself with a needlessly complex disk configuration that doesn't really buy you anything.
Good luck!
Steve G.
August 24, 2010 at 3:24 pm
We need to have a much deeper discussion on data access patterns, etc to give GOOD advice here. But I would try RAID 1 for OS and tempdb, RAID 1 for ALL tlogs, 4 disk RAID 10 for all data files. Make sure you set max memory for sql server to avoid paging, which will affect OS LUN. You can play around with tempdb and tlogs potentially depending on usage patterns.
Again, you really should get a professional on board for a detailed analysis of what you REALLY need to get an optimal IO config. There are soooo many ways you can go wrong here it isn't even funny. And if you screw it up, you sometimes have to go back to bare metal to get it done right.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 24, 2010 at 4:43 pm
with only 8 drives available, you are extremely limited!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 24, 2010 at 9:48 pm
I'd agree with other posters that 8 drives is limiting.
Without having experienced RAID 6 I can't really comment on that.
I would go for a mixed setup.
Take 2 drives in RAID 1 for OS / Executables / other stuff (low write-I/O)
Use 1 drive on its own for tempdb - no mirrors, nothing. What's to lose?
Use 2 drives in RAID 1 for logs.
Use 2 drives in RAID 1 for data.
Use 1 drive as a Hot Spare if that is supported. That way any single failure can be absorbed and managed
August 25, 2010 at 1:50 am
Personally I would issue the following configuration
C: RAID1 (OS & SQL Logs)
D: RAID10 (SQL Data)
E: NORAID (Backup drive)
F: NORAID (Tempdb)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 25, 2010 at 5:42 am
Due to budget constraints etc we've had to set up new database servers with 8 disk's (although I argued for more). I set them up as follows:
2 Disks on RAID1 (OS, SQL Server exe's etc, TempDB)
4 Disks on RAID5 (User Databases, Backups)
2 Disks on RAID1 (Log Files)
It all depends on your system and usage etc. Our application is basically a content management system which is more READ intensive than WRITE, so we wanted the mdf files on a disk array that offers good READ performance such as RAID5
August 25, 2010 at 10:02 am
I tend to agree with the Raid 1, Raid 10, Raid 1 setup. I am actually using this setup and it performs much better than the original 5 disk Raid 5 the original dba had configured. I will caution however that though it seems statistically unlikely, I've personally had 3 disks fail in that Raid 10 at the same time.
August 25, 2010 at 10:21 am
Actually, the statistical likelihood of multiple disk failures in RAID arrays has been widely understated. The mathematical models that failure rates were based on did not accurately reflect the reality of what happens during RAID rebuild. Multiple disk failures are actually fairly common and RAID 5 is especially vulnerable.
This is one of the reasons that I prefer RAID 6 over 5 or 10; RAID 6 allows for any two random disks to fail, where RAID 5 allows only one disk failure and RAID 10 will survive two disk failures only if they're in different sides of the mirroring.
Moral of this story: Back up your data. No system in "invulnerable".
Steve G.
August 25, 2010 at 5:56 pm
RAID 6 will survive only 2 disks failing
RAID 10 will survive up to 50% of the disks in the array failing providing that none of them are opposite sides of the mirror set.
This is where hot spares come in! 😀
August 25, 2010 at 5:58 pm
BaddaBing (8/25/2010)
Due to budget constraints etc we've had to set up new database servers with 8 disk's (although I argued for more). I set them up as follows:2 Disks on RAID1 (OS, SQL Server exe's etc, TempDB)
4 Disks on RAID5 (User Databases, Backups)
2 Disks on RAID1 (Log Files)
It all depends on your system and usage etc. Our application is basically a content management system which is more READ intensive than WRITE, so we wanted the mdf files on a disk array that offers good READ performance such as RAID5
Seems like a valid setup, though if you can carve off the space I would put the backups on a different drive to the databases...!
August 25, 2010 at 5:59 pm
... And that's why I like RAID 6 over RAID 5. 😀
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply