you're reading...

Founders BLOG

MySQL on S3: performance with storage located across the continent

Can OLTP database workloads use Amazon S3 as primary storage? Now they can, thanks to the Cloud Storage Engine (ClouSE), but the question is: how fast?


To answer the question about performance, we decided to run db_STRESS benchmark on a MySQL database in Amazon EC2.  We compared 3 configurations:

  • “Across the street storage”: ClouSE with data stored in S3 in the same region (US Standard)
  • “Across the continent storage”: ClouSE with data stored in S3 in another region (US West-2)
  • “Local storage”: InnoDB (the default MySQL storage engine)  with data on an EBS volume

Each configuration was tested with 2, 8, and 64 concurrent users.  Each run was 9 minutes long with 1 minute sleep in between runs.  The transaction mix was biased towards writes, which put the I/O subsystem to test. For data collection and reporting we used Zabbix 2.0.

ClouSE on “across the street” vs. “across the continent” cloud storage

The following graph shows the number of MySQL operations that the database can process per second.  The db_STRESS benchmark sets autocommit on, so each operation runs in its own transaction.  For each 2 select queries db_STRESS executes 1 insert + 1 delete + 1 update, so there are 3 modifying transactions for each 2 read-only transactions.

Graph 1: “across the street” ops on MySQL w/ClouSE.

The data on graph 1 corresponds to MySQL with ClouSE that stored data in Amazon S3 in the US Standard region, located in N. Virginia.  The Amazon EC2 instance ran in the US Standard region as well, so the storage was “across the street”.

Now, here is graph 2 that depicts the processing rate for MySQL with ClouSE that stored data in Amazon S3 in the US West-2 region, located in Oregon.  The Amazon EC2 instance ran in the US Standard region, so the storage was located across the continent.

Graph 2: “across the continent” ops on MySQL w/ClouSE.

The data shows that moving storage away didn’t slow the database server down.  ClouSE was indeed able to hide network latency when working with remote storage.  Designed and optimized for cloud storage from the ground up, ClouSE was able to process the same amount of data with cloud storage across the street as with cloud storage across the continent.

Why is it interesting?  With ClouSE, cloud storage becomes a true utility that can be used from anywhere, not just inside the cloud.   ClouSE makes cloud storage a viable drop-in replacement for good old SAN.  Moreover, it does so in a fully secure manner: ClouSE encrypts the data and customers are in charge of encryption key management, so they fully control who has access to their data, if anyone.

ClouSE vs InnoDB

We ran the same benchmark with InnoDB as well.  InnoDB is the default storage engine for MySQL 5.5 that uses local disk.

Graph 3: local storage ops on MySQL w/InnoDB.

On graph 3, it easy to see that ClouSE with storage across the continent works just as well as InnoDB with local disk.

ClouSE benefits

The benchmark showed that the I/O performance on local vs cloud storage is a close match. This test demonstrates how ClouSE makes the cloud storage that is on the other side of the continent as fast as a local disk for database workloads.

You can now use cloud storage with your existing MySQL applications and tools – cloud vs. local storage becomes a simple deployment choice. Note that the data can be stored in the cloud regardless of where the application runs: on premise, private cloud or EC2.

Without changing a line of code MySQL databases can enjoy all the benefits of cloud storage – scaling cost with usage, high storage availability and reliability, quick and easy disaster recovery. Using cloud storage has never been this easy – the app stays operational during migration, the adoption can be both gradual and partial, and you can just as easily go back to the local storage.

 

The full performance report with detailed configuration, statistics, and side-by-size performance comparisons is available for download.

Ready to check out the new Cloud Storage Engine for MySQL? ClouSE 1.0b.1.2 Beta is available for FREE download from http://www.oblaksoft.com/downloads/.

Can we assist you in migrating your database to cloud? We’d love to.

See also:

WordPress on S3: run it anywhere.

Making the Cloud Work for You.

Discussion

4 Responses to “MySQL on S3: performance with storage located across the continent”

  1. Hi,

    great to see you’ve adopted db_STRESS workload within your tests!

    however I’m surprised by your low QPS numbers obtained with InnoDB using “local disk”.. – for me it should be at least x10 times(!) higher on a “normal box with local storage” — my latest results on MySQL 5.5 @db_STRESS are here: http://dimitrik.free.fr/blog/archives/2011/12/mysql-performance-564-dbstress-benchmark.html

    So, what is the main bottleneck within your configuration?.. As well db_STRESS is reporting response times on every type of queries during test execution — did you try to catch it?..

    Rgds,
    -Dimitri

    Posted by Dimitri | July 10, 2012, 5:57 am
    • Hi Dimitri,

      Thank you for sharing the db_STRESS benchmark and the MySQL 5.x run results!

      I see a couple differences in the configuration: we’ve got innodb_flush_method=O_DIRECT and innodb_flush_log_at_trx_commit=1 – to put the “D” in ACID each transaction goes to durable storage before it’s considered committed (that’s what ClouSE does as well). Besides, your “normal box” seems to be a bit bigger than a “large box” in Amazon EC2 :-). The details on MySQL configuration for our run are available in the full performance report.

      As far as the bottleneck is concerned, InnoDB did a fair amount of disk IO (you can see it in the full performance report). ClouSE seems to have handled IO pretty well (that’s been the main perf focus, given the nature of the project :-)) and has other areas for improvement that are a part of planned performance work for the release.

      We haven’t done a formal data collection of response times. At a glance there were no unexpected results.

      Posted by Artem Livshits | July 10, 2012, 5:23 pm
  2. Hi Artem,

    from the last testing I’ve done, the regression when “innodb_flush_log_at_trx_commit” is switched from 2 to 1 is less 10%.. — all you need is just to not mix REDO and data I/O. As well, not sure how O_DIRECT is managed within EC2, you should try without and compare (and compare results with innodb_flush_log_at_trx_commit=1 and 2 will be a good idea too, just to be sure about its impact within your testing environment)..

    Regarding the test box — did not expect that 12cores is something big ;-) but well, even on 4cores the result will be still x3 times better comparing to yours, so there is somewhere a problem for sure..

    Also, I don’t like when asked to register on site just to be able to read the full performance report, that’s why I did not read it :-)

    Rgds,
    -Dimitri

    Posted by Dimitri | July 11, 2012, 12:34 am
    • Thanks for the suggestions; we’ll try those tweaks when we do the next round of runs. Or if you’re curious enough, you can easily try it yourself: spin up a m1.large EC2 instance with Amazon Linux, get MySQL binaries from http://www.mysql.com and you’ve got “our” testing environment :-).

      In any case, the main goal here is to compare relative performance of different configurations in the same environment, which the results represent very well. It is also important to note, that the environment is very practical and broadly applicable: anyone who decides to use a large EC2 instance (or a large RDS instance, for that matter) is going to get a virtual machine with similar capabilities.

      Posted by Artem Livshits | July 11, 2012, 11:46 am

Post a Comment

Categories

Twitting ...