Friday, September 22, 2006 6:51 PM tonyrogerson

EMC CX300 SAN performance - 12MBytes/sec and 80% SAN utilisation on writing for single INSERT's...!

This thing has been doing mine head in so I’m going to vent; some of you who know me know my opinion on SAN's - overpriced fads where DASD performs better, is cheaper etc....

Anyway, get this; SQL 2005 on x64 Windows 2003 R2, running a simple INSERT that inserts 8KB of data into a table (see script below).

Running the script where the database is on the SAN I’m only getting 12MB/sec write performance, SAN utilisation 80% and that’s the only thing running on the SAN!!

Running it against a database that is on local DASD SCSI disks – a mirrored pair through a PERC controller with battery backed cache I’m getting 30MB/sec.

The checkpoint process is fine, so its just the SAN not handing the 8KB writes that the log is doing.

Trying to find any support information on EMC’s site is laughable; perhaps they just don’t get it – is this the great information lock in? When you have a problem with their kit you have to pay somebody to help you; I thought those days in IT where long gone….

Based on the information that you have provided and the information available in our records, we have not been able to identify your company as having the necessary relationship with EMC to provide you with full access to Powerlink. However, you will be able to retain a limited-access Powerlink account.”

The limited-access is so limited it hasn’t got any options apart from asking you to upgrade to another account.

Anyway, we are going to rebuild the SAN using guidance from the supplier so I’ll report back in a couple of weeks and let you know how it went…

Out of interest, perhaps people could run the script below and report how many MB/sec write they are getting and whether its DASD or SAN….

create database testio

    on primary ( name=testio, filename='g:\testio.mdf', size=10gb )

    log on ( name=testio_log, filename='g:\testio.ldf', size=10mb,

    filegrowth=10mb )

go

 

use testio

go

 

create table testio (

 blah char(8000) not null

 )

go

 

declare @i int

set @i = 1

set nocount on

while @i <= 100000

begin

    insert testio values( cast( getdate() as char(8000) ) )

    set @i = @i + 1

 

end

23 Oct 2006 -> Check out a more recent entry on Server Commissioning (Transaction Log) for a more detailed explanation and tests and results as to whats going on.

Filed under:

Comments

# re: EMC CX300 SAN performance - 12MBytes/sec and 80% SAN utilisation on writing for single INSERT's...!

Friday, September 22, 2006 10:36 PM by ipfeifer

Interesting observations, Tony, but I disagree with your testing methodology for a few reasons:

1) A log size of 10 mb will require auto-grows during the loop, skewing the overall throughput numbers.
2) Replacing getdate() with 1 in the loop speeds up the process on my servers, indicating that the disk is not the bottleneck.
3) The loop itself presents significant overhead, reducing the the throughput to disk.  This is demonstrated by the fact that the following code is much faster than the initial loop:

create table testio2 (
blah char(8000) not null
)
go

insert into testio2
select * from testio

Some results from my servers:
Your test, DASD (8 10k SCSI disks): 4.39 MB/s
Your test, SAN (CX700, 14 10k FC disks): 4.41 MB/s

No loop, DASD: 12.70 MB/s
No loop, SAN: 34.78 MB/s

Then I took it a step further and changed it to a bulk-logged operation:

select * into testio3
from testio

Bulk-logged, DASD: 34.78 MB/s
Bulk-logged, SAN: 160 MB/s

This is on our heavily-loaded Test environment SAN.  The Production SAN (CX3-80, 40 disks) was about twice as fast on each test.  

I'm interested to hear whether the auto-grows and getdate() overhead were intentional and meant to be controlled pieces of the process, since I may be making invalid assumptions here.  But according to my testing, my SANs are capable of more throughput than is evident through the original test and more throughput than the DASD I'm using, although I know there are higher-perfoming storage attachments out there.

# re: EMC CX300 SAN performance - 12MBytes/sec and 80% SAN utilisation on writing for single INSERT's...!

Saturday, September 23, 2006 1:00 AM by tonyrogerson

How do you explain given the same starting point and using the loop - remember, I'm testing single INSERT's; bulk inserts and multiple INSERT's in a single transaction is fine; given that - how do you explain the DASD is double the performance of the SAN?

Compare like for like; what are your results from the loop test starting from the same database etc... DASD v SCSI?

Size your log at 5GB and I see the same results - 12MB/sec write from SAN and 30MB/sec from DASD; change the loop to an INSERT and TRUNCATE TABLE so the log isn't in the picture; just a single log write - same again, 12MB/sec from SAN; 30MB/sec from DASD.

This is on a SAN and DASD that is doing nothing; literally nothing else because this is stuff suppose to go to production soon....

Again, from scratch; do a DROP DATABASE testio and a) post me the results from the CREATE DATABASE and then the loop for DASD; then b) post me the same from the SAN; don't alter the test.

Tony.

# re: EMC CX300 SAN performance - 12MBytes/sec and 80% SAN utilisation on writing for single INSERT's...!

Saturday, September 23, 2006 1:07 AM by tonyrogerson

Actually, seeing your results:

Your test, DASD (8 10k SCSI disks): 4.39 MB/s
Your test, SAN (CX700, 14 10k FC disks): 4.41 MB/s

I'd have to see what load but that performance is shocking too!

Database transactions in OLTP are predominately single INSERT's / UPDATE; basically single page (8KB) writes to the log....

# re: EMC CX300 SAN performance - 12MBytes/sec and 80% SAN utilisation on writing for single INSERT's...!

Monday, September 25, 2006 10:45 AM by GrumpyOldDBA

I will mention my experiences when tasked with obtaining SAN information with regard to a consolidation project white paper I was asked to review.
1 x SAN manufacturer - unable to speak to anyone in person, only able to leave voice mail. Messages produced zero repsonse, emails unanswered.
1 x SAN manuafacturer/provider - tricky to speak to anyone as company policy dictates that no job titles are held by switchboard - was given phone numbers of "partners". These would not speak to me as "they don't deal with users"
1 x SAN manufacturer ( Hitachi Data Systems ) was put through to correct person first time who not only knew storage but understood the DBA perspective.
I'm minded to remember handling a BI tendering process where the BI providers were asked to show their wares as a working demo. One of the vendors, having been rejected, complained to the MD that "they were used to making their presentation to Boards of Directors not technical managers and staff". They were also very abusive to a work colleague.
My feeling is that SAN technology is big money for vendors and is sold in the manner of the BI vendor I encountered, personally I've found SAN's to be pretty crap and a major performance bottleneck, with the exception of HDS I've found a level of arrogance I detest - hence I'm off on the SNIA certification - if nothing else it will hopefully give me a valid point of reference to work from.
I'll post what I can later this week.

# re: EMC CX300 SAN performance - 12MBytes/sec and 80% SAN utilisation on writing for single INSERT's...!

Monday, September 25, 2006 1:06 PM by Colin Leversuch-Roberts

I just did your test on my desktop, not sure if this counts as it's sql2k, but I got 16mb/sec by my calcs , that sound right for a single disk on a P4 + HT ? perfmon indicated 300 i/o sec.

# re: EMC CX300 SAN performance - 12MBytes/sec and 80% SAN utilisation on writing for single INSERT's...!

Monday, September 25, 2006 2:04 PM by dong

I did spend some time with Dell storage people trying to get a CX500 last year. I believe a CX300 can do far more better than these numbers. It must be the setting up of the SAN.

Normally an IT manager who also manage a SAN may just RAID-5 across all the hard drives he got. Then happily ask you: What's the size you want for your database? In the end, you probably get a LUN for data files, another LUN for log files. But in fact, they are on the same spindles!

Worst, if cache wasn't turnned on for the SAN or the LUNs, RAID-5 will run as fast as your USBKey. I'll blog about this topic later. Anyone interested?

# re: EMC CX300 SAN performance - 12MBytes/sec and 80% SAN utilisation on writing for single INSERT's...!

Monday, September 25, 2006 3:48 PM by Colin Leversuch-Roberts

I'd be interested in any refs to internally setting up a SAN. I'm not so much interested in luns on physical disks etc. but how the internals are configured - especially the mapping of the ports onto the physical disks.

# re: EMC CX300 SAN performance - 12MBytes/sec and 80% SAN utilisation on writing for single INSERT's...!

Monday, October 16, 2006 7:22 PM by a115ebfd

I run a number of bencmarks with sqlio and sqliostress and they all show that performance of my CX300 is on average 50% worse then of DAS.

Here's the average numbers:

DAS  15 MB/s, 7660 IOPS
SAN  10 MB/s, 5510 IOPS

Maybe this is why Dell calls it 'Entry Level' SAN...
I am so feed up with it that I am going back to directly attached storage for my SQL servers.

# re: EMC CX300 SAN performance - 12MBytes/sec and 80% SAN utilisation on writing for single INSERT's...!

Monday, October 16, 2006 9:50 PM by tonyrogerson

Yep - if I'd forked out £33K on that kit I'd be disappointed to say the least, in fact it would be boxed back up and sent back.

Anyone looking at SAN's should ALWAYS get an eval kit in first and see what benchmarks you can get out of it with relation to the IO patterns of the system you wish it to support.

We found that using IOMeter increasing outstanding IO's improved the situation, but transaction logging relies on not having outstanding IO's.

Lessons learn't....

# re: EMC CX300 SAN performance - 12MBytes/sec and 80% SAN utilisation on writing for single INSERT's...!

Wednesday, January 10, 2007 8:36 PM by bfrasca

First of all, thanks.  I love your blog and the info has served me well.  However, I have to speak up about this issue.

I know I'm a little late to this party but I think that you're comparison of a SAN to DASD is an apples to oranges comparison.  This is primarily because you left out some critical configuration parameters with regard to the SAN.

1.  Was it 1 gigabit, 2 gigabit or 4 gigabit fibre channel?

2.  How many HBA's were there, what was the bus speed, and were they single, dual, or quad port controllers?

3.  How were the LUN's carved and how many spindles were used and what was the capacity of each spindle?

4.  What RAID level was used for the LUN's associated with the database versus the DASD?  (All LUN's are not created equal.  You could have data files on a RAID 5 LUN and log files on a RAID 10 LUN for example.)  There's a certain performance tradeoff for redundancy and reliability.

5.  Were your files appropriately distributed across the SAN for optimal performance? (See #4 above.)

6.  How many other networks can see your DASD array versus the SAN?

7.  How was the fibre channel switch configured?  If that isn't done properly then performance suffers.

8.  What kind of hot swapping capabilities do you have for your DASD versus your SAN?

9.  What happens to the performance of your DASD during database backups versus the SAN?  SAN's are GREAT places to perform disk-to-disk backups.

10.  What kind of applications are going to be accessing the databases?  SAN's are, in my opinion anyway, more suited to data warehouse and BI implementations than OLTP where speed is more critical.

That's just a few of the issues.  There are others.

To imply that SAN's are "overpriced fads" based on a fairly elementary test is a little unfair I think.  In fact, it's difficult to create a fair test environment because a SAN is a network unto itself and requires a high level of competence to configure and deploy.  I'm not surprised that it's slower.  The gains in redundancy and reliability do have a tradeoff with regard to network latency but the flexibility of the SAN overall is far greater than some SCSI array.