Blog

This week I presented at the SSWUG Virtual Conference on how to benchmark your storage performance with Microsoft’s SQLIO utility.  Last week I talked about running SQL Server in the cloud on Amazon EC2.  Take those two things together, and we’ve got storage benchmarking on Amazon EC2 servers:

  • Maximum write speed: 68 MBs/sec
  • Maximum read speed: 8 MBs/sec

I find this too horrifying to comprehend.  I can’t imagine running a database server using a first-generation USB thumb drive for storage, but that’s essentially what this is.

If anybody out there is using Windows on Amazon EC2, I’d be really curious to hear what your SQL Server performance is like relative to your physical in-house servers.  If you’ve got the time, running SQLIO on your EC2 instances would be even better, but it takes several hours to run through my full battery of SQLIO tests.  Running any storage testing utility against an EC2 hard drive would be interesting.

This was not a one-time result – this was a continuous overnight test against an m1.large instance with nothing installed on it.  Every single read test maxed out at around 8 MBs/sec.  Unbelievable.

I’d love to spend more time digging into this, but I’m signing off the computer tonight to start our move up to Michigan.  (I have a linkpost scheduled for Friday already.)  When I get to Michigan, I’ll try a couple of other storage performance test utilities against EC2 instances to see if I’m missing something obvious, and I certainly hope I am.  I’ve never seen performance this bad on a desktop, let alone a server.

Update from Amazon EC2 Support

There’s a thread about slow Amazon EC2 performance for storage on the Amazon forums.  The official answer from Amazon is yes, it’s that slow but they believe it’s normal for SAN storage.  They’re way off base there – I routinely get faster performance out of my home lab gear – but at least they’re watching the forums.

↑ Back to top
  1. amazon instance storage drive c:

    ????????????????????????????????????????????????????????????????????????????????
    ? ?
    ? SQL70IOStress Version 1.00.069 ?
    ? ?
    ????????????????????????????????????????????????????????????????????????????????
    ? ?
    ? File: c:\temp\Stress.mdf ?
    ? Iteration 5 of 5. Start: 15:56:05 ?
    ? ?
    ? Successful completion – Please review SQL70IOStress.Errors.txt ?
    ? ?
    ? Total Elapsed time for cycle: 5 seconds ?
    ? Average writes per second: 6400.000000 ?
    ? Average MB per second: 53.125000 ?
    ? ?
    ? ?
    ????????????????????????????????????????????????????????????????????????????????

    C:\>
    standard pc sata

    ????????????????????????????????????????????????????????????????????????????????
    ? ?
    ? SQL70IOStress Version 1.00.069 ?
    ? ?
    ????????????????????????????????????????????????????????????????????????????????
    ? ?
    ? File: c:\temp\Stress.mdf ?
    ? Iteration 5 of 5. Start: 12:47:23 ?
    ? ?
    ? Successful completion – Please review SQL70IOStress.Errors.txt ?
    ? ?
    ? Total Elapsed time for cycle: 20 seconds ?
    ? Average writes per second: 1600.000000 ?
    ? Average MB per second: 13.281250 ?
    ? ?
    ? ?
    ????????????????????????????????????????????????????????????????????????????????

    e:\
    3 Ebs drives Striped together with win os software

    ????????????????????????????????????????????????????????????????????????????????
    ? ?
    ? SQL70IOStress Version 1.00.069 ?
    ? ?
    ????????????????????????????????????????????????????????????????????????????????
    ? ?
    ? File: E:\temp\Stress.mdf ?
    ? Iteration 5 of 5. Start: 16:12:22 ?
    ? ?
    ? Successful completion – Please review SQL70IOStress.Errors.txt ?
    ? ?
    ? Total Elapsed time for cycle: 15 seconds ?
    ? Average writes per second: 2133.333252 ?
    ? Average MB per second: 17.708333 ?
    ? ?
    ? ?
    ????????????????????????????????????????????????????????????????????????????????

    e:\
    1 ebs no stripe

    ????????????????????????????????????????????????????????????????????????????????
    ? ?
    ? SQL70IOStress Version 1.00.069 ?
    ? ?
    ????????????????????????????????????????????????????????????????????????????????
    ? ?
    ? File: e:\temp\Stress.mdf ?
    ? Iteration 5 of 5. Start: 16:20:26 ?
    ? ?
    ? Successful completion – Please review SQL70IOStress.Errors.txt ?
    ? ?
    ? Total Elapsed time for cycle: 25 seconds ?
    ? Average writes per second: 1280.000000 ?
    ? Average MB per second: 10.625000 ?
    ? ?
    ? ?
    ????????????????????????????????????????????????????????????????????????????????

    E:\>

    Not looking good, for speed

  2. I tried to download the sqlio tool from various places after reading your post including from Microsoft and softpedia but I get an error about an invalid digital signature. I would like to run the tool against an EC2 machine that I am using. Any suggestions?

    Thanks

    • Robert – that’s odd, I’m not sure what to tell you there. I’ve downloaded it dozens of times in the last couple of months and never seen an error like that. I don’t think you’re downloading the right app, maybe – try downloading it on your own local drive and see if you get that same error. Or you may be working in an environment with aggressive security settings that don’t allow you to download non-signed code, perhaps.

  3. Pingback: How can I speed up SQL Server queries on Amazon EC2? - Admins Goodies

  4. Pingback: Amazon EC2 Sql Performance | Technology & Programming

  5. Pingback: Amazon EC2 Sql Performance | Ask Programming & Technology

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php