4

On my old server, I separated them on to different RAID arrays to improve performance. Using a SAN is new to me, so I'm not sure of the optimal configuration.

SAN: 48 drives. 10K 600GB SAS. RAID 50. 4 Gbps connection.

Updated info:

All 48 drives are part of the same RAID array. I would like to know if there is any benefit to having data and logs in different volumes. I know that if I split my SAN up there can be some benefit, depending on the circumstances. The Dell representative that sold us the SAN said the best performance can be obtained by keeping all 48 disks in one array, and use seperate volumes ("LUNS"?). Sorry if my question isn't clear.

Improve this question
6
  • More specifically, two volumes on the same array.
    – Keith Walton
    Sep 29, 2011 at 17:23
  • 1
    This changes my answer. There's very little benefit in that case.
    – Basil
    Sep 29, 2011 at 17:32
  • 1
    Not necessarily, two volumes likely gives you double the queue depth from the host side.
    – MikeyB
    Sep 29, 2011 at 17:44
  • 1
    @JimB, it can't be a Compellent, they don't do RAID 50.
    – Jeremy
    Oct 2, 2011 at 14:54
  • 1
    @Jeremy-great point I hadn't picked up on that
    – Jim B
    Oct 4, 2011 at 12:44

4 Answers 4

Reset to default
9

Putting your log file on a separate volume, even if it's on the same raid, should be better performance than having the data and logs in the same volume. Each volume can only be access through one path at a time on most storage systems. This means that your logs and data would share the same controller port, controller cache, queue depth, and FC switch resources. Any of these could become a bottleneck, and putting your logs into another volume that shares as few of them as possible would likely result in a performance increase.

That said, there is both a performance and reliability reason to have logs and data live on separate disks, assuming you're not on one of those newfangled "wide striping" machines that use all the spindles in the array simultaneously. Each time you do a database write, it's logged, and each log takes a write (on top of the actual modification).

  • If you lose an entire raid (which is rare, but happens) that hosts both your logs and data, you risk real data loss unless your logs were replicated. Log shipping is not always supported with all databases.
  • During normal operation, if you have your logs and data living on the same small set of spindles, you can risk throttling actual database production with disk level performance bottlenecks.

You are on a raid 50. That's generally a sign that you're striping your data across a lot more than 8 or 16 disks. If this is the case, it's also generally true that you'll set up at least two raid-50s per disk type (of which you have one). The best solution for your situation is to put your data on the fastest (ie biggest) raid you have, and your logs on the other one.

Improve this answer
6
  • I am saturating the link, both for backups and my largest query - which populates my warehouse table in a nightly job. I'm going to see what I can get out of a 10 Gbps connection.
    – Keith Walton
    Sep 29, 2011 at 17:30
  • 1
    "There is both a performance and reliability reason to have logs and data live on separate disks. Each time you do an operation, it's logged, and each log takes a write (on top of the actual modification). " There is a growing trend from most of the big manufacturers to make this statement untrue. HP EVA, Dell Compellent, and use of an IBM SVC device, are all examples of where this scheme would really hurt performance of either the overall san or the log spindles.
    – Jim B
    Sep 29, 2011 at 17:34
  • 1
    If you're using a broad striping machine, all this goes out the window. That said, I spoke to that in the section about the number of raid 50s he has. If this were a 3par, XIV, SVC, or EVA, then absolutely- you never want to specify spindles. If it's literally a pair of 24 drive raid 50s, though, you probably do.
    – Basil
    Sep 29, 2011 at 18:07
  • Also, @KeithWalton, how many front end connections do you have?
    – Basil
    Sep 29, 2011 at 18:08
  • @Basil - I'm not sure what you mean by front end connection. There are 2 database servers connected to the SAN. Each one has 4 1 Gbps iSCSI connections.
    – Keith Walton
    Sep 29, 2011 at 19:23
1

sorry about the link bomb

http://www.brentozar.com/sql/sql-server-san-best-practices/

Improve this answer
4
  • Very useful. I can see where it may be a benefit to split the SAN into multiple arrays in some cases. I am also curious if there is any performance difference if the volumes are on the same array.
    – Keith Walton
    Sep 29, 2011 at 17:20
  • 1
    If the data and log volumes are on the same array, you are not going to realize the performance and data-recovery benefits of separate volumes. It would be mostly pointless.
    – Skyhawk
    Sep 29, 2011 at 17:23
  • 1
    @KeithWalton, its the same as on the sever, you want to reduce IO contention, so you would want to have different physical spindles for each type of IO (eg tmpdb on 1 lun, log files on 1 lun, data files on 1 lun). additionally (for folks with lots and lots of disks) you can also split up the datafiles themselves on to different arrays
    – Jim B
    Sep 29, 2011 at 17:41
  • @Jim B, So does each lun use different spindles on the same array?
    – Keith Walton
    Sep 29, 2011 at 17:45
1

Generally speaking, to minimize write latency for a demanding database you'd want to split the logs and data to different volumes on different RAID groups, so there isn't resource contention between log and data for the RAID group's time.

Sounds like you've already got the drives configured into RAID groups, so you'll probably just want to look at provisioning the different volumes from different groups and shelves.

Improve this answer
0

It really depends on the brand. Compellent SANs can be configured to auto optimize san reads and writes. HP EVA sans will actually take an overall performance hit if you start dedicating spindles to certain applications. Can you dedicate cache to certain Worldwide names? Whether SQL is ona san or on local storage the same rules apply, so if you are familiar with Optimizing IO on the server, its just a matter of learning about the capabilities of the san you have and seeing how best to utilize them.

Improve this answer
5
  • You might want to explain what you mean by "optimize" :)
    – Basil
    Sep 29, 2011 at 17:24
  • in the english languange optimize means "to make as perfect, effective, or functional as possible " according to mirriam- webster.com
    – Jim B
    Sep 29, 2011 at 17:35
  • 1
    So I could rephrase your point as "Compellent SANs can be configured to automatically make san reads and writes as perfect, effective, or functional as possible"? I asked you what you meant, not what the word means.
    – Basil
    Sep 29, 2011 at 18:16
  • @basil - correct, compellent treats all data at the block level rather than at the file or volume level, so in a nutshell you could have a file on a raid 50 volume having data live on sata, FC, and SSD disks at the same time and that data will automigrate to the lower tiers of storage based on inactivity (or activity) levels that you choose.
    – Jim B
    Sep 30, 2011 at 15:00
  • 1
    You pretty clearly have an EQL box and not a Compellent. I don't know much about EQL but from the Compellent side, it doesn't matter from a disk performance aspect whether you put them on different SAN volumes or not. A properly-configured Compellent writes to RAID10 on its fastest tier of disk and reads from RAID5 (or RAID 6) from the most appropriate tier based on how hot the block is.
    – Jeremy
    Oct 2, 2011 at 14:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .