Need 100% seriously help with disklayout and LUNS

Last Post 12 Feb 2007 02:36 PM by janus007. 7 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:26 AM
Hello...

We're using Dell as storage, but the difference between Dell and EMC should be minor - right?

I'm having a very hard time getting the maximum performance and availability from our SQLServer2000.

Our cluster scenario:
2 x Dell PowerEdge 2650 Servers - active/ passive setup
CX3-40 SAN with 45 fiberchannel disks

We need the best disklayout for absolutely highest performance and faulttolerance. We've a proposed setup from Dell, take a look here:

http://www.filebloggers.net/disklayout2.gif

As you can see the Raidgroup 3 and 4 are configured for Primary and secondary indexes. Would it be more wise to combine both into one big raidgroup?

As you can see Raidgroup 2 is configured for datafiles, would it be more wise to combine this Raidgroup with the above combined - so we would end up with 1 big raidgroup for both datafiles and indexfiles?

These question could be very nice to get answered, I would love to hear other solutions based upon these 45 disks.

The last thing... faulttolerance. If bus 0-enclosure 0 goes offline the database goes offline because of the transactionlog placing, the same goes for Bus1-enclosure 0 Would'nt it be much more safe to place the Luns vertically instead of horizontally? Take for instance Raidgroup 0 (transactionlogs) if I place those 10 disks with 4, 3 and 3 disks spanning each box - is it then possible to survive enclosure breakdown? Will it affect performance?

This is a reallife example, all the hardware is bought, just waiting to be configured and taken into test and production.
JHunter
New Member
New Member

--
11 Feb 2007 06:43 AM
Could you supply a bit more information about your environment? ie number of databases (if more than one, which are primary/secondary etc). What type of application - OLTP/DW/DSS etc? Do you have any specific size requirements for data/indexes/logs? How many FC HBAs are in each server, do what is your switching fabric?

I know the CX3-20 and CX300 don't perform well, but you should be able to get some good performance from the CX3-40.

I would say your servers are definately under specified (1 cpu) - given the relatively cheap price of servers (and given you've shelled out for enterprise licensing!) - unless these are web facing as its a per CPU license...

Jamie



janus007
New Member
New Member

--
11 Feb 2007 07:20 AM
I have made a typo... the servers are actually PowerEdge 2950, it's the old setup which are consisting of 2650 - sorry for that.

Anyway.. We've 2 databases which are working together... one database (we can call it primary) is subscriber from one of our backend servers in replication. This primary database has all the raw data etc., the secondary database then uses the raw data and analyse it into smaller aggregated highly enriched data. So you could say, that one database is raw data and another is analysed data. (this is not the best way to do things, I'm aware of that, but it's the scenario right now, we're working on a better design, but for know we need to ensure a stable data service to our customers)

The secondary database is build entirely from the data located on the primary, and our frontend website users are then using the secondary for analysed data and if they wish to do a drilldown they are using the primary raw data. Take into notice that our frontend users uses another setup where all this data is replicated to!! But the setup is the same.

I think there are 2 FC HBA at each server (if necessary, I can find out if it's one or two)
We are using a McDATA® SphereonTM 4400, Fibre Channel-switch - 4 GB

I've made a small piechart of the spacedistribution, look at the percent as an precisely estimated guess
Find it here: http://www.filebloggers.net/spacedistribution.gif
JHunter
New Member
New Member

--
11 Feb 2007 08:10 AM
You don't give any information regarding the actual space requirements for each database, but I'd separate the LUN's for each database. Also, you should configure one HBA as the primary path to the logs (on SP A), and the other HBA as the primary path to the data (on SP B). Paths to the separate index filegroups should also be separated (depending on usage). Failover paths should be created on the other HBA/controllers. Both HBAs should be connected to separate switches. I'd also suggest separating tempdb onto its own disks.

If you want the best performance, separate the data (and indexes) of frequently joined tables onto separate LUNs.

Are your primary and secondary databases in operation at the same time, or does one complete its processing before the second kicks off? If they are at their busiest at the same time, use separate disks for the LUNs, if they are sequential, you can place multiple LUNs over the same disks.

RAID 10 is the optimal for read/write performance and availablility, you should split the mirrors onto separate shelfs. In SQL 2000, loosing any file, the database will become suspect.

Also, be aware of the LUNs you place on the OS disks - I personally wouldn't use them for any other LUNs (except maybe the quorum disk).

Jamie


janus007
New Member
New Member

--
11 Feb 2007 10:40 AM
Hello JHunter, thanks for this quick reply.
The spacerequirements are about 300gb for primary db and 150gb for secondary db, at the moment but we're expectinga 50% growth in the next 6months. My thoughts are just to allocate all available space according to the spacedistribution

As you suggest regarding the tempDB on it's own is a good idea (also best practice)... but I've many theoretically suggestions not to follow that 'best practice' - also why the database and indexes should be placed on the same lun(s)

Let me try to explain my thinkings regarding db and indexes mixed up. This only applies to random reads and no writes (the tempDB has probably another setup, which I don't yet have had time to think through)

Lets concentrate onto datafiles and indexfiles...
If we configure 2 luns each with 4 disks RAID 10, you've around 300 IOPS available at each lun. We then do a query against a table with a clustered index placed at primary and a nonclustered index at an index filegroup, lets call the filegroup FG_INDEX. The query runs and uses the nonclustered index (this index is about 10% of the total tablespace) and reads the data located on primary filegroup. It cannot read any faster than 300IOPS.
But what if we mix both the index and datafiles, would we then get 1.95 x 300 IOPS? Ofcause this is theoretically, but then lets say 20% overhead = 1.75 x 300 IOPS, which should be 75% faster than using dedicated LUNs for datafiles and indexfiles.

The primary goal would allways be to distribute the workload to all disks - right?
And is'nt the whole idea using SAN to let the HW distribute IO's even between the disks?

The issue you mention regarding separate shelfs, is actually something I've been wondering alot about. I took the liberty to create another thread, specifically for this issue (Is 'Best practice' really that old)

Why should the OS disks be dedicated?.. it costs a lot of money to dedicate 5 disks for this, do you know how much the OS requires in space btw?

Kind regards
Janus
JHunter
New Member
New Member

--
11 Feb 2007 11:57 AM
quote:

Originally posted by: rm
Don't know much of Dell server, I always put OS on local raid 1.


The OS to which I was refering is the OS on the SAN (see the diagram)...

Jamie
JHunter
New Member
New Member

--
11 Feb 2007 12:55 PM
quote:

Originally posted by: janus007
Hello JHunter, thanks for this quick reply.
The spacerequirements are about 300gb for primary db and 150gb for secondary db, at the moment but we're expectinga 50% growth in the next 6months. My thoughts are just to allocate all available space according to the spacedistribution

As you suggest regarding the tempDB on it's own is a good idea (also best practice)... but I've many theoretically suggestions not to follow that 'best practice' - also why the database and indexes should be placed on the same lun(s)



You've miss read - data and indexes should be on separate physical disks. This allows the sql engine to parallelise the io operations, and avoids disk contention. Everything about disk configuration comes down to minimising the contention on specific parts of your IO subsystem.

quote:


Let me try to explain my thinkings regarding db and indexes mixed up. This only applies to random reads and no writes (the tempDB has probably another setup, which I don't yet have had time to think through)

Lets concentrate onto datafiles and indexfiles...
If we configure 2 luns each with 4 disks RAID 10, you've around 300 IOPS available at each lun. We then do a query against a table with a clustered index placed at primary and a nonclustered index at an index filegroup, lets call the filegroup FG_INDEX. The query runs and uses the nonclustered index (this index is about 10% of the total tablespace) and reads the data located on primary filegroup. It cannot read any faster than 300IOPS.
But what if we mix both the index and datafiles, would we then get 1.95 x 300 IOPS? Ofcause this is theoretically, but then lets say 20% overhead = 1.75 x 300 IOPS, which should be 75% faster than using dedicated LUNs for datafiles and indexfiles.



Think of a situation with many tables and many indexes in the same join. I'm not sure I follow your logic, but it doesn't sound right :S

quote:


The primary goal would allways be to distribute the workload to all disks - right?
And is'nt the whole idea using SAN to let the HW distribute IO's even between the disks?



Yes, the goal is to spread your data across as many physical disks as possible. And more smaller disks is always preferable to fewer large disks.

But, can you guarantee that the SAN will place the data its optimal location, or will it be completely random? Different tables may require different treatment, different number of disks, different raid levels.
What if you find, there is a hot spot over half a dozen disks, while the rest are idle? How do you configure multiple paths through your SAN to optimise the available bandwidth?

Other factors include internal and external fragmenation... and how about when you start using filegroup backups... And what happens when you modify your clustered index on a really big table?

I'm only suggesting you separate as much as you can points of contention. If after 50% growth, are you certain you'll get the same performance with a 450GB database...? You many even decide that there are only one or two tables which require maximum performance, so you'd want to place these on dedicated disks.

Have you considered how partitioned tables could better utilise hardware as the data becomes more static, and less frequently accessed? I've found in a system, only the most recent (3 months) data is frequently accessed, I therefore place this data over more physical disks than I would +3 - 12 months, and +1 year is accessed even less frequently - so it gets placed on even fewer (but larger) disks.

quote:


The issue you mention regarding separate shelfs, is actually something
janus007
New Member
New Member

--
12 Feb 2007 02:36 PM
Thank you Jamie, you've certainly made a whole bunch of things clear to me.

It's exactly what I needed, I'll think wisely over your last answer here.. you are definitely right, I just needed to read them I wholeheartet agree with you, sometimes I just get a bit confused over my own thoughts

Have a nice day.

Janus (I'll be back )

You are not authorized to post a reply.

Acceptable Use Policy