Understanding I/O Performance for SQL Server on Amazon EC2 / AWS.

Having run SQL Server on EC2, be advised EC2 is a very stable platform, however you are required to pay for the performance you need. I have done several repeated tests, and my experience is that Amazon gives you exactly what you pay for.

If you are having a performance problem, you need to look at your entire infrastructure and ensure you have not over provisioned one aspect of the system, while under-provisioning another.

The AWS blog tells the Dedicated network throughput for each instance size. I have chosen to expand on this to show you the best choices you have for EBS volume configuration for these instance types. If your only objective is maxing out I/O performance the table below should be sufficient. If however you need large amounts of space, you may choose to increase your drive count, and reduce your Provisioned IOPs per volume.

 

There are several key things, I have learned and wanted to state for you:

1) Your instance size determines Guaranteed Network Throughput

2) Everything that goes off your server traverses that single NIC ( Disk I/O, Network I/O, everything)

3) 1000 IOPS is the approximately 16 MB per second on AWS, the block size is 16K.

4)  If you are running SQL with Terabytes of Data you most likely need an instance size is rated as High for Network Performance and potentially one of the newer instances which promise 10Gigabit throughput.

5) Please do configure your server to be EBS optimized.

6) EBS volumes are currently limited to 1TB in size, so to create larger disks use Software RAID in your Operating System. (p.s. Amazon will deliver the IOPS of all volumes in the RAID Array — just remember you cannot exceed the Dedicated Throughput of your NIC).

7) If your backups are running terribly long and you can’t seem to figure out why… you most likely have an I/O bottleneck related to your server configuration. Amazon is NOT the problem.

Below is the table, hope it works for you..

 

Any questions / comments that may assist in improving this post are appreciated.

 

 

Instance Type Dedicated Throughput Dedicated Through Put (MB/second)  Max IOPs Through Put for EBS Purchasing   Most Optimized Purchase Size   Min Drive Size (GB) @ MAX IOPS   Min Drive Count to Get Maximum IOPS 
m1.large 500 Mbps 62.5                3,906.25                    4,000                             400                   1
 m1.xlarge 1000 Mbps 125                7,812.50                    8,000                             800                   2
 m2.2xlarge (new) 500 Mbps 62.5                3,906.25                    4,000                             400                   1
 m2.4xlarge 1000 Mbps 125                7,812.50                    8,000                             800                   2
 m3.xlarge (new) 500 Mbps 62.5                3,906.25                    4,000                             400                   1
 m3.2xlarge (new) 1000 Mbps 125                7,812.50                    8,000                             800                   2
 c1.xlarge (new) 1000 Mbps 125                7,812.50                    8,000                             800                   2
Instance Type Dedicated Throughput Dedicated Through Put (MB/second)  Max IOPs Through Put for EBS Purchasing   Most Optimized Purchase Size   Min Drive Size (GB) @ MAX IOPS   Min Drive Count to Get Maximum IOPS 
m1.large 500 Mbps 62.5                3,906.25                    4,000                             400                   1
 m1.xlarge 1000 Mbps 125                7,812.50                    8,000                             800                   2
 m2.2xlarge (new) 500 Mbps 62.5                3,906.25                    4,000                             400                   1
 m2.4xlarge 1000 Mbps 125                7,812.50                    8,000                             800                   2
 m3.xlarge (new) 500 Mbps 62.5                3,906.25                    4,000                             400                   1
 m3.2xlarge (new) 1000 Mbps 125                7,812.50                    8,000                             800                   2
 c1.xlarge (new) 1000 Mbps 125                7,812.50                    8,000                             800                   2
Advertisements

7 thoughts on “Understanding I/O Performance for SQL Server on Amazon EC2 / AWS.

  1. Would you typically store the SQL databases on a separate volume from the OS (i.e. D:)? And if so, would you do provisioned IOPS on both volumes or just the data volume?

    1. Yes, I put data files and log files on a separate volume, and I prefer provisioned IOPS for all drives. I separate the files unto separate drives following MS Best practices. In reality, you can get away with not doing that, but the choice is really dependent on what your tolerances are, how many customers you have, and how responsive you need the system to be.

  2. Some significant changes have recently been announced for General Purpose SSD EBS volumes that allow for IO requests of up to 256k to be counted as a single IO as well as doubling the maximum throughput per volume:
    http://aws.amazon.com/blogs/aws/enhanced-ebs-throughput/

    Should be good for SQL Server and significantly reduce the cost/improve the throughput vs the comparison tables above…

    AFAIK, still bottlenecked to 2000Mbps (250MB/s) total throughput on EBS Optimised instances (although this is dedicated and separate to other network traffic)

  3. Hi, great article that seems to be feeling the pain of our current AWS experiements. You mention in #7, “you most likely have a IO bottleneck related to server configuration…” can you clarify what you mean? Are you suggesting we need to choose another instance (say from i2.xlarge to i2.2xlarge), or are you suggesting there are other configurations to be considered aside from upgrading to higher instance?

    Thanks!

    1. As with everything it depends. Try to run something to test max disk throughput. Then also take a look at the top waits and queues on your server. Grab the tuning whitepaper from microsoft web site

    2. Yes. I recommend reading aws blog / testing to determine max throughput for your instance and moving to an instance that supports your demands… Easier said than done 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s