SQL Server Performance Forum – Threads Archive

Log file on a san

Hey folks, I have a theoretical question about log files. We recently purchased a high end storage system that has 240 drives, 40G of cache, and cost north of $500,000. This is my playground right now, I’m trying to figure out how to get the most out of our new toy. In particular something that’s interested me is I have a development project that is log stalled when I run it on our new storage system (IE: It could go faster but I cant write the logs fast enough). Looking at the statistics reported by the storage system, the log voluem is doing about 1600 writes/sec, 512 bytes each, and the write completion time is consistently .2ms. When I put the log on local disk the performance is much better. When I put it on a ramdisk the performance is better yet. I’ve been analyzing this problem a few minutes at a time for a while now, and I ran across this very detailed articlehttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx This article says that sql server depends on the log writes being applied in the order they are sent by the server. Combining that with what I’ve seen, I have come to the conclusion that sql server does not queue its log writes. It sends one command, then waits for a response, the sends another. I’ve noticed a similar thing with data files. If I create one datafile on one virtual disk, I get X performance. If I split that among two virtual disks I get twice the performance, etc until I run into the limit of the fiber channels (the most I’ve been able to pull out of one system so far is 390MB/s, which was limited by the number of fiber channels I’d attached). But my point is you sort of have to force sql server to parallize it’s requests. It’s as if it was designed to work with a short queue depth because it makes the assumption that all disks (whether they are a floppy or a 1500 spindle storage system) have the same speed characteristics. With the data files this is not big deal. I just split the data among as many virtual drives as it takes to make something else the bottleneck. But my question is how to I make the log file parallize? SQL Server seems adamant about issuing only one log write at a time, no matter how many log files there are or how many disks you put them on. I have found that splitting the data into multiple databases helps. Then sql will do one log write per database at a time. What I’d like to do is make it parallize the log writes to an extreme degree. Any suggestions on how to do this would be appreciated. Any comments on my observations in general are also welcome. Thanks, Steve Sadler

$500,000 would be cheap for that type of system. What type of fiber cards do you have? How are your RAID groups setup on this SAN? When you setup across multiple "virtual drives" (this is a LUN btw), are they spanning more physical spindles? Try setting up a dedicated RAID 10 raid group on the SAN with dedicated spindles. See what your log file performance looks like after that. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

I know from your past posts that you are an intelligent person who typically has good input into questions of this type so I dont want to insult you. However, I put a lot of research and detail into this (a discovery that could help the community) and your reply seems to assume I am an idiot. I can only conclude that you didnt actually read my post. In that light, please dont take offense at the following:
>$500,000 would be cheap for that type of system. You are misinformed in this instance. At first they try to high ball you, but its just a matter of persistence and negotiation to get them to come to earth. In the end they all want the market share and will dive deep into their profit margins in order to achieve it. If you have a different impression then either your information is outdated (IE: 2 years ago such a unit would have cost more), or your negotiating team was not persistent enough. We negotiated with four vendors for five weeks before making a purchase.
> When you setup across multiple "virtual drives" (this is a LUN btw) This was correct 10 years ago, but not true in modern environments. The reason I chose to call it a virtual drive is a drive can exist on more than one lun at a time via multipathing drivers (IE, microsoft’s MPIO, QLogic’s QLDirect, or Emulex’s MultiPulse). For example, on one one of our dev servers I run four FC connections (two dual port cards) and a multipathing driver, so each drive that windows sees actually consists of four luns. Requests for data off any one drive are load balanced among the four luns that consistute the virtual drive. The fact that these four separate luns appear to be one is an illusion that is a result of the storage system assigning the four separate luns the same numeric value. In reality, the address of the drive from the OS’s perspective is more than just the lun. It is the concatenation of the Port, Bus, Target ID, and lun. IE, one of the virtual drives on the system I am describing consists of these four luns: Port 2, bus 0, target 0, lun 14.
Port 2, bus 0, target 0, lun 14.
Port 3, bus 0, target 1, lun 14.
Port 3, bus 0, target 1, lun 14. As you can see, even though they are all "lun 14", they are actually 4 differnent luns. The fact that the storage system assigns the value "14" to them all (rather than different numbers) is only to make things convienient for humans; it would be entirely possible to assign four different lun numbers to the four luns attached to a virtual drive on those four different ports. My point is that they are actually separate luns and as such, in a modern environment luns are data paths to drives rather than the drives themsleves. I recognize that in the past a lun was tied to one virtual drive, and for that reason I’m willing to call it a lun for the purpose of our conversation, but since you saw fit to correct me when I was not mistaken, I thought I’d set the record straight. Dont get me started on the word "SAN" (It’s not a san, it’s a storage system. The san is the network between the host and the storage system…) Hopefully you see now that you underestimated the level of thought you’d have to put into this question, which I understand since most people ask stupid questions without thinking. Would you care to reread my post and provide your best input? I will summarize my above post: Observation:
My observation is that sql server artificially maintains a queue depth of 1 (0) on log writes in order to achieve write ahead logging (see above url). Am I correct? Conclusion:
If so, there is an upper performance limit based on the round trip time for a packet that will come nowhere close loading the storage system. How can we encora Question:
How can we encourage sql server to parallize log write operations in order to work around the above? Steve

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> >$500,000 would be cheap for that type of system. You are misinformed in this instance. At first they try to high ball you, but its just a matter of persistence and negotiation to get them to come to earth. In the end they all want the market share and will dive deep into their profit margins in order to achieve it. If you have a different impression then either your information is outdated (IE: 2 years ago such a unit would have cost more), or your negotiating team was not persistent enough. We negotiated with four vendors for five weeks before making a purchase. <hr height="1" noshade id="quote"></blockquote id="quote"> I’m well aware of what the prices for current SANs are (yes, that’s what they’re called). $500,000 for a SAN from any of the current vendors is a great price. My information is not outdated. I have series of quotes from all kinds of vendors sitting on my desk. I said it was cheap. I didn’t say you were lying or an idiot. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> > When you setup across multiple "virtual drives" (this is a LUN btw) This was correct 10 years ago, but not true in modern environments. The reason I chose to call it a virtual drive is a drive can exist on more than one lun at a time via multipathing drivers (IE, microsoft’s MPIO, QLogic’s QLDirect, or Emulex’s MultiPulse). For example, on one one of our dev servers I run four FC connections (two dual port cards) and a multipathing driver, so each drive that windows sees actually consists of four luns. Requests for data off any one drive are load balanced among the four luns that consistute the virtual drive. The fact that these four separate luns appear to be one is an illusion that is a result of the storage system assigning the four separate luns the same numeric value. In reality, the address of the drive from the OS’s perspective is more than just the lun. It is the concatenation of the Port, Bus, Target ID, and lun. IE, one of the virtual drives on the system I am describing consists of these four luns: Port 2, bus 0, target 0, lun 14. Port 2, bus 0, target 0, lun 14. Port 3, bus 0, target 1, lun 14. Port 3, bus 0, target 1, lun 14. As you can see, even though they are all "lun 14", they are actually 4 differnent luns. The fact that the storage system assigns the value "14" to them all (rather than different numbers) is only to make things convienient for humans; it would be entirely possible to assign four different lun numbers to the four luns attached to a virtual drive on those four different ports. My point is that they are actually separate luns and as such, in a modern environment luns are data paths to drives rather than the drives themsleves. I recognize that in the past a lun was tied to one virtual drive, and for that reason I’m willing to call it a lun for the purpose of our conversation, but since you saw fit to correct me when I was not mistaken, I thought I’d set the record straight. <hr height="1" noshade id="quote"></blockquote id="quote"> I wasn’t trying to correct you. I was clarifying. Don’t show your ignorance by reading into what people are saying. A LUN is nothing more than a logical unit presented to a host. You have physical drives grouped into RAID groups. You can make LUNs from single or multiple parts of RAID groups. The pathing has nothing to do with what a LUN is or isn’t. The idea that a multi-pathed connection to the SAN allows presentations in the manner you describe is ridiculous. If you didn’t have the multi-path softare, it would show each LUN multiple times instead of as the one LUN it actually is. If you are going to pontificate, get your facts straight. You haven’t shared any "knowledge" with people. You might have read a good book on SAN technology. Your comments on LUNs, multipathing, and SAN technology in general are dead wrong though. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> Dont get me started on the word "SAN" (It’s not a san, it’s a storage system. The san is the network between the host and the storage system…) <hr height="1" noshade id="quote"></blockquote id="quote"> SAN = storage area network The SAN is the network that provides integration of hosts and storage systems. It’s tight integration makes it a SAN as opposed to a NAS which can ……. ohh, nevermind. Get a book and start reading. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> Hopefully you see now that you underestimated the level of thought you’d have to put into this question, which I understand since most people ask stupid questions without thinking. <hr height="1" noshade id="quote"></blockquote id="quote"> No, I really don’t think I did. I don’t agree that most people on these forums as stupid questions without thinking. There are some who do [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />], but the majority of people come to forums because they DO think. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> Would you care to reread my post and provide your best input? <hr height="1" noshade id="quote"></blockquote id="quote"> My original questions and comments were accurate. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> Question: How can we encourage sql server to parallize log write operations in order to work around the above? <hr height="1" noshade id="quote"></blockquote id="quote"> Log writes are sequential. Period. There is no parallization [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] of a single log file. If I’m wrong here, I would love to be proven wrong with information from MS. What you can do is provide dedicated spindles (yes, that’s correct) that are optimized for sequential writes. If you can afford to provide this to each individual log file per database, then you will be able to even further optimize your throughput. Think before you reply to the above paragraph. I’m not trying to slam you here. I’m always open to being wrong. People like Joe Chang have sometimes proven me wrong, and I appreciate it. I don’t appreciate being taken out of context and "corrected" with information that’s 100% wrong though. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

Oh, log writes are sequential? Thanks, I’m glad I came here and got your help.

In a manner of speaking yes. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_3t2d.asp It’s actually a little bit more complicated than that. You can read about the entire architecture if you follow all the up/down links on that article. Even on extremely busy systems, the log files are seldom ever a bottleneck if they live on RAID 1 or RAID 10 disk arrays unless you have a severe memory issue and multiple databases with log files all on the same array. SQL Server basically assigns an LSN to the transactions as they occur. It then sequentially writes them down to the file(s). You can either create multiple log files spread across multiple disks, or you can have more physical disks on the array the single log file sits on to allow more IOPS. I’m not trying to make you mad Slart. I hope you don’t take this wrong. If I am wrong on something, show me the articles. I’m always happy to learn something new. BTW, what kind of a SAN is this? FC, iSCSI? EMC, Hitachi, etc? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

]]>