Is 'Best practice' really that old

Last Post 11 Feb 2007 02:50 PM by JHunter. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
janus007
New Member
New Member

--
11 Feb 2007 12:19 PM
Hello...

This is actually an extent to my other topic 'Need 100% seriously help with disklayout and LUNS' just to keep things separated

However, I've been wondering a bit upon all the topics for best practice to separate indexes and datafiles and also the placement for a mirrored diskset, as JHunter mention the best way is to place a mirror at each shelf in a SAN. For instance, if our scenario consists of a SAN with 4 shelfs each with 15 disks then the layout should be done like this. I've chosen to create a LUN with 12 disks...

(|shelf-disknumber|)

LUN 1:
|0-0|0-1|0-2|0-3|0-4|0-5|
|1-0|1-1|1-2|1-3|1-4|1-5|

The above ascii shows one LUN where the mirror is placed at shelf 1

Now we have one LUN, now I will create another so we then have two LUNS
LUN 2:
|2-0|2-1|2-2|2-3|2-4|2-5|
|3-0|3-1|3-2|3-3|3-4|3-5|


What you see now is a normal 'best practice' for a SQLServer setup at a SAN - right?. Normally we would place an index at shelf 2 and 3 and datafiles at 0 and 1. In our database properties we would then create a primary filegroup pointing at LUN 1 and a filegroup index pointing at LUN 2.... and everybody is happy

However I think this approach is old and dusty, why are we still trying to take control over the LUNS and their disks? Why not let the HW do what it's best at!

Why not create vertically LUNS? Like this..
LUN 1:
|0-0|
|1-0|
|2-0|
|3-0|

LUN 2:
|0-1|
|1-1|
|2-1|
|3-1|

LUN 3:
|0-2|
|1-2|
|2-2|
|3-2|

LUN 4:
|0-3|
|1-3|
|2-3|
|3-3|

Is'nt this much more fault tolerant and also using the bandwidth much more distributed?
The database then needs 2 primary, one pointing to LUN1 and another to LUN2, the same goes for the indexfiles.
And what if we then take my other approach into considerations, you know regarding using the same LUNS for both datafiles and indexfiles.... If we use my approach all readings would be evenly distributed between LUN1-2-3-4 and because of the high fault tolerance we can loose a shelf and still retrieve our data at 75% speed.

I would love to hear comments as I think this is very interesting.

Kind regards
Janus
JHunter
New Member
New Member

--
11 Feb 2007 02:50 PM
Firstly, the HW is best at storing and retrieving data - but that's it I'm afaid. What data is stored (ie billing for Q1 2006), how much data is to be read/written with each operation, how the access patterns for groups of data look, how often data is accessed etc is not known.

If you have a large database, in which all data is distributed and accessed evenly, letting the hardware get on with it may work (this is very unlikely, it is much more common to see hotspots of activity). A DBA should understand the data and its access patterns, and should be able to tune the underlying hardware to be as optimal as possible.

You should also bring into your equation the transaction log and tempdb. TempDB is the work horse of all databases in an instance, and can easily become a bottleneck if over looked. The transaction logs are even more critical, logs are write intensive, and sequential (mostly) in nature.

Get some real performance figures from Dell; IO performance for the HBA (Random and sequential), SAN fabric, a physical disk. Then look at where the bottlenecks would be given the volumes of data your applications read and write...

"Best practice" are just guidelines, you must do what is right for your system. Only testing can give you the really answers as each implementation of an SQL Server database has its own distinct IO signature.

Not everyone can throw shelfs and RAID 10 at their problems. There are usually restrictions in place...

Enjoy,
Jamie
janus007
New Member
New Member

--
12 Feb 2007 02:13 AM
Hi Jamie

Yes okay, I understand, but if we then only take the non system databases and a dedicated LUN to the transactionlogs into considerations - would my suggestion then be any good?

I'm worried about the 'best practice' saying 'keep datafiles and indexfiles separated' Both accesstypes are random or have I misunderstood something?
And if both files (data and index) are random, then why try to create luns as if they were sequential reads? I guess the mainpoint in having separated LUNS is (apart from writes), that for instance a join would be faster? - but doesn't that only apply to sequential reads?.
What I'm trying to say here, is that random is random and therefore it would be more optimum to use all available disks.

Ofcause I can establish some logging and get some real numbers, but this topic is theoretical

I'm neither capable of just throwing shelfs and raid10 at my problems, I've a setup consisting of 45 disks and 3 shelfs, but I'm trying to get the best out of it.

If we could discuss my first proposal:
LUN 1:
|0-0|
|1-0|
|2-0|
|3-0|

LUN 2:
|0-1|
|1-1|
|2-1|
|3-1|

LUN 3:
|0-2|
|1-2|
|2-2|
|3-2|

LUN 4:
|0-3|
|1-3|
|2-3|
|3-3|

Could you see any pitfalls in doing so?

Im very happy to discuss this issue with you, hopefully I'm not taking too much of you sparetime.

Kind regards
Janus
JHunter
New Member
New Member

--
14 Feb 2007 03:01 AM
I'd say having only 4 disks per LUN will limit your performance. And you seem to have gained an extra shelf!

Here's what I'd do, create a LUN for each user databases transaction logs, one for tempdb (maybe one for its logs too - depends on how your tempdb is used). 4 LUNs for each of your user databases data (so you'll have 1 filegroup with two files for data and the same for indexes - there maybe more optimal configurations for your system). Bear in mind that a filegroup with more than one file will almost always perform better than a single file on a multiple CPU server.

If you have TEXT or IMAGE data it maybe worth split that too. The distribution system database could also become a bottleneck if you have one on this server.

I'd ignore the SAN OS disks - just place you quorum there...

Lastly, make sure you have sufficient hot spares, minimum of 2 on each shelf.

There was something I recall regarding a registry tweak on 2000 for systems with more than 8 LUNs - dunno if its the same with 2003.

Enjoy,
Jamie
janus007
New Member
New Member

--
14 Feb 2007 01:54 PM
Thanks Jamie...
I will continue to test your proposal.

Cheers
Janus
You are not authorized to post a reply.

Acceptable Use Policy