cancel
Showing results for 
Search instead for 
Did you mean: 

Best Practise installing (SQL) DB files across multiple LUNs

ken_halvorsen2
Active Participant
0 Kudos

We have SAP R\3 Enterprize running on Windows \ SQL Server 2000, with about 1 TB of data, with 9 datafiles each being 110 GB in size all on 1 LUN.

We are running into servere IO problems. The SAN Specialist is suggesting that moving the datafiles across multiple LUNs to the SAN should correct.

I understand this may have been an SAP suggestion many years ago, but can't seem to find any "Best Practise" info on it presently.

Does anyone out there have any experience with this with greater than a 1 TB db's?

Accepted Solutions (1)

Accepted Solutions (1)

markus_doehr2
Active Contributor
0 Kudos

If that one LUN is pointing the same SAN as the several LUNs would do, I'm not sure, if that would give you the necessary performance gain.

How did he come to that conclusion?

Markus

ken_halvorsen2
Active Participant
0 Kudos

Thanks

Some Dell diagnositics have suggested that there is a "Hot Spot" on the specific LUN (which only has the datafiles on it).

It was found that even though the disk spindle showed little active the bottleneck was data transfer.

(personally I believe it was a CYA suggestion, but I do need to check it out)

markus_doehr2
Active Contributor
0 Kudos

Ok - but I'm still not sure why a "LUN" can have a bottleneck. A LUN is nothing more than a logical assignment, an addresse so to speak for a hardware device.

Transferring this statement to a network would mean, that if one "IP addresse" has a bottleneck, you can work around that, by adding another IP addresse to the same device and do loadbalancing.

What I could imagine would be, that you add a separate HBA into your box and use then a new LUN.

Did you check, if the alignment on the SAN is correctly configured? See

Markus

Former Member
0 Kudos

Moving to several LUNS should prevent (or at least drastically diminish) any hot spots you might get on the disk. Basically its like making a big grid where you have the data files going down and the LUNs across. Its sort of hard to visualize but if your system is accessing data that is all on one datafile and its stored on the same physical disk in the LUN then you get contention. Spread the 9 files across multiple LUNs might prevent that. You should be able to track any of these hot spots with your SAN software though, your vendor should be able to point you in that direction.

A second HBA would also increase your throughput and would be helpful as well, especially with a high volume of data transfer.

markus_doehr2
Active Contributor
0 Kudos

> Moving to several LUNS should prevent (or at least drastically diminish) any hot spots you might get on the disk.

Well, we don't know what's "behind" that LUN - but if it's a SAN (be it Fibrechannel or iSCSI), it's not a disk but many of them.

> Basically its like making a big grid where you have the data files going down and the LUNs across. Its sort of hard to visualize but if your system is accessing data that is all on one datafile and its stored on the same physical disk in the LUN then you get contention.

Yes - but LUN != Disk!

Markus

ken_halvorsen2
Active Participant
0 Kudos

Thanks for your comments guys.

This is the answer I received from the SAN tech in regards to multiple LUN's:

"The reason I made this suggestion is that the system is requesting more I/O from the single LUN than the array can provide, thus the LUN sitting at 100% utilization almost constantly. My theory (and Dell's) was simply to spread the I/O load over 2 or more LUNs to improve performance."

Also, yes the LUN is on an HBA connected SAN, using fibre with multiple disks, that has had several additions of allocated space to the LUN.

So is there an SAP best Practise on this?

markus_doehr2
Active Contributor
0 Kudos

"The reason I made this suggestion is that the system is requesting more I/O from the single LUN than the array can provide, thus the LUN sitting at 100% utilization almost constantly. My theory (and Dell's) was simply to spread the I/O load over 2 or more LUNs to improve performance."

Well - I could go on discussing here

From http://en.wikipedia.org/wiki/Logical_Unit_Number:


In computer storage, a logical unit number or LUN is simply the number assigned to a logical unit. A logical unit is a SCSI protocol entity, the only one which may be addressed by the actual input/output (I/O) operations. 

So it´s understandable like having one "C" drive and on the other side a partitioned "C" and "D" drive. Having two partitions on the same disk (which is the same as ONE SAN in your case) doesn´t make throughput faster because it is, as by definition, just a logical assignment.

So taking that statement from the SAN tech would mean: If you create two partitions "C" and "D" on a single disk, the throughput will be faster - which is nonsense since one disk can provide only a specific number of I/O no matter how many logical layer request that.

The best practice recommendation is (as far as I remember) to have the same number of volumes (in your case files) than number of disks. So if your raid has 24 disks I would spread the database onto 24 files in optimum case. This will enable the max. throughput because there is not too less requests where one has to wait for the other and not too many so they won´t queue up. The number of LUNs is irrelevant, this can be one, two or even 24.

Markus

Answers (0)