January 24, 2008 at 8:13 am
Guys,
I can't get an answer to his question - so hoping I will from my SQL colleagues. If you have any thoughts please let me know.
I am just about to build 2 database servers in a windows cluster and connect them to a high available HP MSA500 - it's DAS storage.
The servers will host a number of sql2005 databases.
My question is...
(i) Do I configure the 14 disks in the disk enclosure as one lun and then configure seperate logical partitions for each database function (i.e. user databases, tempdb, logs, etc.).
or
(ii) Do I configure the physical and logical partitions of the disk enclosure for each database function. So my database logs would be hosted on a 3 disk lun with 1 partition spanning these 3 disks - and no other physical disk or partition involved.
I've heard that option (i) is better for performance but is this a recommended practice for database servers.
Also does option (i) allow failover of the seperate logical drives? Or is failover of the user database volume only possible if the partition is configured physically.
I hope this is understandable to you.
Joe
February 2, 2011 at 9:53 am
Just curious if you ever received any feedback or direction on your particular question?
We have an opportunity to upgrade and reconfigure an MSA 500 with newer disks - my team's original proposal was to configure the layout much like your option (ii). When presented, our sys engineers came back and suggested option (i) - all 14 disks in RAID 1+0 and then carving up the LUN into separate logical partitions. (Side note: The MSA that is being replaced is also setup in a similar fashion - they've done it this way before).
This recommendation struck our team as a bit odd...as we assumed the old prinicple of separate physical disks for each functional area of the db was the right way to go. It's certainly possible we're missing something here, though...
February 9, 2011 at 3:03 am
i have been struggling with this topic for a while....i have a slightly different issue where i am limited by spindles....but the theory is the same
one thing i have found is that having 1 big LUN and seperating it into logical partitions really affects performance when it comes to tempdb and transaction logs due to the latency time for the disks....the problem with 1 big lun is there is no way to alter the logical partiton so you cannot seperate random i/o from sequential i/o
i would at least create a physical RAID1 of 2 or 4 (RAID10) disks for the transaction logs depending on size, 2 disks for tempdb if it will be heavily used, and the rest in RAID10 for the datafiles
if you have enough space consider creating 2 RAID10 partitions for the data....one for primary datafile and tables and the second for non-clustered indexes or some other table partitioning or filegroup setup based on size or usage
as far as failover is concerned the more raid groups you create the more spare disks are needed as you can safely lose 1 from each RAID1 and 1 from each side on the RAID10 but will need to be able to replace all very quickly....if this is for a remote hosted environment you may want to consider this aspect
February 10, 2011 at 6:10 am
Ideally you should separate the data, log and tempDB on different disks.
Depending on the size of the disks, I would do something like this
R: drive (data) = 10 drives (disks) RAID 1+0
L: drive (log) = 2 drives (disks) RAID 5
T: drive (TempDB) = 2 drives (disks) RAID 5
Steve
EDIT: I meant disks, not drives in the breakdown.
February 10, 2011 at 6:47 am
NEVER put logs onto a RAID5 array....logs only use sequential write activity and RAID5 gives over a 40% drop in performance for writes....RAID1 or RAID10 is the only type to use for transaction logs
you also cannot create a RAID5 array using 2 disks....3 minimum
*annoying you cannot see the original post when replying!!
also do not put tempdb onto RAID5.....the data should be on RAID1 or RAID10 and the logs the same....but on DIFFERENT disk groups so no data files sit on disks that hold log files
February 10, 2011 at 7:00 am
Agreed, it would be nice to see the post during the reply.
Yes, RAID 1 for 2 disks, I was mistaken.
I would still keep the physical disks separate.
10 disks RAID 1+0 (data)
2 disks RAID 1 (log)
2 disk RAID 1 (tempdb)
February 10, 2011 at 7:15 am
definately....data and logs need to be seperated if you have the choice
most people do not know the difference between a logical and physical disk....this is the cause of most i/o problems!!
if in doubt speak to your storage or server engineer who will be able to ensure the correct disk groups are configured on the SAN before you install the server
February 10, 2011 at 7:24 am
Depending on the storage requirements and actual disk size he might be able to get away with
8 disks RAID 1+0 (data)
4 disks RAID 1+0 (log)
2 disk RAID 1 (tempdb)
I've had SAN vendors push IOPS when in the SQL world that doesn't seem to be the best method for measuring performance.
February 10, 2011 at 7:33 am
lol....i have the same problem....hardware engineers look at the total throughout of the SAN saying we rarely reach 5%.....they dont check the actual disk activity where i have noticed 100% disk time
it can handle massive amounts of data in theory....it cant handle the large disk latency times and OLTP database request levels!!
see my post on spindle limited oltp database for more info 😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply