Bechmark of MySQL with SSL on AWS RDS

Ran a very specific benchmark of MySQL – the native SSL connection performance penalty on AWS RDS.

When establishing a MySQL connection there is a way to tell the server to use SSL to encrypt the communication between the client and the database. When activated the database is doing all of its normal work plus the extra work of encrypting the data. Some overhead is expected. Advances in encryption technology and CPU power have come a long way since their original introduction so the actual impact might not be much.

The procedure was to setup an AWS RDS MySQL 5.6.13 m1.medium instance. A test database was filled with 10M rows to make it ~2.2GB in size. The load tests were performed using SysBench from an EC2 host in the same availability zone with and without the SSL option enabled.

Ran into some problems with SysBench and SSL options on RDS, which I will explain below, but first to the RESULTS!

Results of SSL vs non-SSL:

MySQL with and without SSL, 25 threads
With SSL Without SSL % Penalty (SSL)
Transactions/sec 36,535 43,561 -16.1% fewer transactions
Read/write requests/sec 3,885 4,597 -15.5% fewer requests
Avg (ms) 123 103 +19.4% (slower)
95th Percentile (ms) 172 126 +36.5% (slower)

 

rds_ssl1

rds_ssl2

Yeesh, MySQL’s native SSL hurts!

  • Add 20% to the response time, sometimes way more.
  • Cut throughput by ~16%. 

Personally – I was hoping for something like 3%…  I’m surprised at how high the performance penalty is and would look at other options first like a VPN. Response time and throughput may or may not be as critical as security (which can’t be compromised), but this is not an easy tradeoff.

Other sources of information on the topic of MySQL SSL performance:

In 2011 yaSSL did a similar benchmark test and noted a 15 -40% performance penalty, with an average penalty of ~17%. Their study was not specifically targeted at RDS and was ran locally on a Macbook Pro.

The MySQL Connector/J 5.0 driver had the following stat“The performance penalty for enabling SSL is an increase in query processing time between 35% and 50%, depending on the size of the query, and the amount of data it returns.”

These results match the yaSSL results pretty closely, but it did not come close to penalties like the Java JDBC driver saw (though we didn’t test with Java in this case).

Limitations and Additional Testing Called For:

MySQL RDS instances always have SSL compiled in and enabled. This test compares the performance penalty of connecting with SSL vs a standard unencrypted connection. This test does not look at how the performance would change if SSL was disabled or excluded from the MySQL binary.

SysBench was not cooperating with MySQL SSL on RDS!

First of all, SysBench provides no documentation for the –mysql-ssl=on option, leaving you to rely on a series of error messages as your only clue. This SO answer was helpful. Ended up studying the source code and the mysql_ssl_set() function documentation. Not quite RTFM, but in this case RTFC – RTF Code!

As of Sysbench 0.4.12, when using the –mysql-ssl=on option, it requires the server’s CA certificate, the client key and the client cert.  That effectively forces you into X509 mode. This is hard coded into /sysbench/drivers/mysql/drv_mysql.c.

  if (args.use_ssl)
  {
    ssl_key= "client-key.pem";
    ssl_cert= "client-cert.pem";
    ssl_ca= "cacert.pem";

    DEBUG("mysql_ssl_set(%p,\"%s\", \"%s\", \"%s\", NULL, NULL)", con, ssl_key,
          ssl_cert, ssl_ca);
    mysql_ssl_set(con, ssl_key, ssl_cert, ssl_ca, NULL, NULL);
  }

As it turns out SSL options –mysql-key and –-mysql-cert are optional for SSL mode and only need to be used if you want to connect with client X509 cert.

The problem with this is AWS RDS provides a cert file only but not the private key needed to generate X509 client certs! This was VERY annoying but I was able to roll up my sleeves, recompile SysBench (an adventure in itself), and got it to work with just the ssl_ca option.

# change sysbench/drivers/mysql/drv_mysql.c
# line 398 to
mysql_ssl_set(con, NULL, NULL, ssl_ca, NULL, NULL);

#then recompile sysbench

At first I tried to create my own client cert by skipping the private key option but that did not work. MySQL is smart about enforcing certificate authenticity.  It also makes sense that AWS keeps their RDS private key private. The side effect of that is MySQL ‘GRANT…. …REQUIRE X509’ does not work with AWS RDS. Amazon needs to allow users to install their own CA certs into their RDS instances. It is a little disconcerting to me that every single RDS MySQL instance is sharing the exact same CA cert!

How I ran the tests:

Prepare a 2.2GB database with ~10M rows.

sysbench --test=oltp --mysql-host=host --mysql-user=user --mysql-password=*** --mysql-table-engine=innodb --oltp-table-size=10000000 --max-time=180 --max-requests=0 prepare

Run the test with SSL:

sysbench --num-threads=25 --max-requests=100000 --test=oltp --mysql-host=host --mysql-user=user--mysql-password=**** --mysql-table-engine=innodb --oltp-table-size=1000000 --max-time=180 --max-requests=0 run --mysql-ssl=on

Run the test without SSL:

sysbench --num-threads=25 --max-requests=100000 --test=oltp --mysql-host=host --mysql-user=user --mysql-password=**** --mysql-table-engine=innodb --oltp-table-size=1000000 --max-time=180 --max-requests=0 run

 

Other MySQL Benchmarks:

I have benchmarked MySQL on RDS in a previous post, and discussed the pros and cons of running RDS vs an EC2 instance locally. This test cost under $0.50 on AWS. I love it! It was more expensive this time around because of the troubleshooting with SysBench.

I encourage readers to play around with load test experiments. Getting past assumptions by conducting experiments and analyzing the data is a lot of fun!

This entry was posted in Data, Sys Admin and tagged , , , , . Bookmark the permalink.

One Response to Bechmark of MySQL with SSL on AWS RDS

  1. Pingback: Using MySQL with Encrypted SSL Connections | Laurence Gellert's Blog