December 6, 2007 at 9:19 am
Nice post Colin.
Yes, the DBA needs input on SAN setup/configuration if you desire optimum performance. However, the DBA needs to be knowledgeable to be able to provide appropriate inputs. As Colin states, there is a WIDE array of variables and options that come into play when you are placing SQL Server files on a SAN. And there is an INCREDIBLE range of performance that will come out of the system depending on how things are configured.
Oh, and yes, I just did tell a new client (who is having awful performance issues) that there spiffy new SAN was so poorly configured that it was no better than JBOD internal storage. 🙂 But I gave them benchmarks to prove it and they had no leg to stand on. As someone else suggested SAN 'administrators' are often not TRULY knowledgeable yet very protective of their turf - bad combination.
A quick list of items to consider:
number of spindles per RAID set
type of RAID for type of SQL Server file
RAID stripe size
usage patterns of the data
HBA configuration: cache ratios (btw, always pay for max cache for each card), queue depth
number of HBAs and how allocated to RAID sets
number of LUNs per RAID set
SAN I/O cache ratios
NTFS allocation size
partition sector alignment (MANDATORY)
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 8, 2007 at 4:43 am
And here lies the problem - some vendors tell customers that san's outperform DAS, disk for disk this is untrue a disk still only has x i/o per sec and x spin speed, and a raid is a raid, just beacuse it's in a san doesn't make the disks spin faster!
The upshot is that customers beleive SAN's will always outperform DAS, what a SAN brings is management and consolidation not performance.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 8, 2007 at 8:03 am
Two points you are missing in the performance equation Colin. With a SAN it isn't just about the disk I/O capability. SANs usually come with large (sometimes many GB) memory caches that can provide tremendous throughput increases for a wide variety of I/O loads. They can also provide multiple paths to the storage which can improve performance over DASD. So they really can offer improved I/O capabilities despite the truism that the disks don't spin faster.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 10, 2007 at 10:24 am
actually cache is another myth put forward by the vendors - in the early days of tcp benchmarks, back in sql 6.5 and sql 7 days it used to be recommended to set cache to 100% write ( or disabled ), and in fact I did prove this with DAS whereby setting read cache on the data drives degraded performance.
as for SAN cache, you'll find even 128Gb of cache split read/write between 512 LUNs is actually only 256mb per lun , assuming all cache is actually available. without going into what the cache is intended to do you'll find san cache does very little. When we allocate 64gb of ram to a sql server then 256mb isn't actually significant. As for multiple paths - same thing, if your network is rubbish or overloaded then no difference - If you run multiple raid cards to multiple DAS you can get the same throughput - I ran a server with 4 dual channel scsi 3 cards back in 6.5/7.0 days. Don't forget you can DAS with 4gb fibre too. In actual fact the bandwidth is rarely an issue, most sql servers are i/o bound - you won't find cache having too much effect there.
Sorry to disagree - but this is why i went on the SNIA courses as I needed to understand why a SAN might not perform as one might expect.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 10, 2007 at 10:35 am
TheSQLGuru (12/8/2007)
Two points you are missing in the performance equation Colin. With a SAN it isn't just about the disk I/O capability. SANs usually come with large (sometimes many GB) memory caches that can provide tremendous throughput increases for a wide variety of I/O loads. They can also provide multiple paths to the storage which can improve performance over DASD. So they really can offer improved I/O capabilities despite the truism that the disks don't spin faster.
..unless your activity is such that your activity is so high consistently that the cache can't be flushed fast enough to disk. Again - a matter of appropriately sizing and configuring said SAN.
This can also happen if you just have a hotspot that just can't keep up.
The Cache was one that used to mystify me - you can add all sorts of bells and whistles to your multi-million-dollar storage solution, but just try to double the cache on the storage controller? nah - why would you ever need to do that? (vaguely reminiscent of that infamous quote from an IBM Tech: "why would you ever need more that 640k of RAM?")....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 10, 2007 at 12:33 pm
My comment was directed at an optimally sized/configured SAN. I don't consider a SAN with 512 LUNs to be optimally configured for database throughput. DAS with that many splits will suck *** too. 🙂 And if you didn't size it correctly to begin with - well, tough noogies!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 10, 2007 at 12:41 pm
TheSQLGuru (12/10/2007)
My comment was directed at an optimally sized/configured SAN. I don't consider a SAN with 512 LUNs to be optimally configured for database throughput. DAS with that many splits will suck *** too. 🙂 And if you didn't size it correctly to begin with - well, tough noogies!
No doubt - it's just amazing how badly a SAN can suffer at the hands of a single, badly configured hotspot....
And like Colin mentioned before - nothing like walking into the CTO's office with the old "I know you just spent 2M on this thing, BUT....." :sick: Can make for a rough day....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 10, 2007 at 1:36 pm
That is just one of the many advantages to being a consultant - CTOs actually PAY me to tell them their stuff is poorly configured, improperly designed, sized, maintained, etc, etc, yada-yada-yada. 😎 Now, having my tires slashed by the group that sourced and setup said equipment is another matter entirely. Gotta take the bad with the good I suppose. LOL
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 10, 2007 at 4:26 pm
I tend to work in fairly large organisations, so with maybe a couple of hundred servers attached to 30 terabytes of storage I don't see 512 luns being excessive - in fact I recently interviewed for a role where there were 400 sql server instances, even if you give them 2 luns each ??
I quite agree that this doesn't work well for databases and you need dedicated spindles and zones for your production servers, but as I've said this soon makes a mockery of the vendor's 85%-95% utilisation so then you're trying to tell "the management" you know better than the vendor - tricky!
just out of interest I think you'll find you can probably define a very high number of luns on a DAS.
I was just trying to say that with DAS where I'd have maybe 48 spindles in 4 storage boxes partitioned as maybe 6 ( raid 10 ) arrays, in a san they'd set a couple of 15 or 30 disk raid 5's and carve the 6 drives out of the disks ( and probably more ) claiming that the cache and faster fibre would more then outperform your old DAS ( not )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 10, 2007 at 7:49 pm
YES!
Every dba should know the hw drive configuration. Although SAN technology has improved, there are still plenty of bottlenecks and they are always on the drives. I am assuming your connecting to Fibre LUNS. When you get up about 5 SQL servers, do an SQLIO test. You can get the tool free from MS. Now, once you have your 5 servers up, configure the SQLIO to test against a 2GB file with 8 threads on a random i/o for 3hrs. Now create 10 to 15 instances of the test on each server. This will stress the living hell out of the SAN and give you a good idea of what your SQL server will have to work with. If the storage goons get upset because the SQL servers are degrading their SAN performance, tell them that's their problem not yours and to fix the bottleneck, the SAN. If they give you any more hassle, run 20 instances of SQLIO for 6 hrs and say these are just SQL transactions.
December 11, 2007 at 1:12 am
Hi,
DBA's and storage team's must now work very closely together, as the SAN now plays a fundamental role in the foundation of high performing SQL Server implementations. As already mentioned throughout thread, it is an absolute must for DBA's to at least understand SAN technology and how to best utilize for any file based application to ensure best possible storage throughput and protect these assets. This is not to say, DBA's must do the job of the SAN team, but a closer working relationship will ensure the business get the most out of both its SQL Server and SAN investment and applications will perform robustly.
As highlighted earlier in thread, testing is paramount to ensure the SAN technology is configured optimally so that SQL Server can take advantage of this investment and once again, close working with storage team is so, so important. In most organizations I work with, I work hard to bring these two teams together, as most would agree, they are normally treated as two distinct teams with different objectives and a host of forms and service SLA's implemented to provision service request for simple storage presentation, but without enough detail as to how to best present the storage to the DBA team.
Overall, this issue will not resolve itself in the short term, but DBA's will need to take the initiative and gain as much SAN technology knowledge to boost their knowledge and allow better dialogue with storage team via direct talks or when completing the change control or service request documentation. Most SAN vendors provide loads of free documentation to support this knowledge building effort, so take advantage to boost the knowledge.
Thanks,
Phillip Cox
December 11, 2007 at 3:10 am
my experience has been that SAN vendors are very reluctant to provide any type of help or documentation unless you are within a specified channel - that doesn't mean you can't purchase training of course.
BUT I will make a point that in my many years of battling with SAN performance one company stands out from the crowd in providing information and having people you can talk to, and that is Hitachi Data Systems ( HDS ) sadly I've yet to work on a site that uses their hardware.
You'll probably guess by now that I'm not a great fan of most SAN vendors/partners - what I have found is that as a DBA when you challenge SAN performance you tend to be ridiculed, ignored, told you don't know what you're talking about and just generally made to feel very unpopular.
So I'll give a second plug http://www.snia.org get on their training course(s), get an understanding of the architecture of a SAN , the actual storage is still storage after all, with the certifications at least you have a point of reference. It may help , or not!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 11, 2007 at 8:00 am
For you own knowledge, I would say yes. But, if you would like to avoid the argument with them all together, find a way to track I/O bandwidth. Really this is what you care about. Then you can simple say I need this many MB per second throughput, who cares how they do it. BTW, my experience on SAN (EMC and IBM DS4800 sitting under SVC) suggests that anything less than 100 MB/sec is pushing the envelope on high end systems. You really want to see 120 - 140.
December 11, 2007 at 9:02 am
Colin,
You mentioned how telling the truth on SAN performance for DB servers tends to be going a bit against the grain and I'll agree. I've battled a bit with SAN storage 'experts' at our client sites that were experiencing performance problems with our application. My diagnosis was that these problems were not application problems but instead problems with storage configuration or hardware. The client's SAN guys had their senior management convinced that the SAN was fine and that our application was buggy, but after a year's time I was able to get them to see that the bottleneck was in the SAN's controller. I then later found that MSA 1000s are not recommended by the vendor for database storage.
You've obvously been in several shops where you've had to go to the mat with SAN storage advocates. How do you typically go about convincing them that their SAN may be causing them problems?
December 11, 2007 at 9:12 am
In case you haven't heard enough, I am currently in this same situation. Our SAN administraters believe if they provide us with the amount of space requested then they have done thier job. When they did this our performance went down the drain and we spent several months educating them on how SQL Server works and getting the extra spindles needed to boost performance.
Keep fighting for what is right for your user's sake.
Viewing 15 posts - 16 through 30 (of 55 total)
You must be logged in to reply to this topic. Login to reply