MySQL Encryption Performance Testing

This is the second in a two-part series on MySQL encryption performance testing. In the first part, I stress-tested MySQL’s built-in SSL support exclusively and got some surprising results. Naturally, query throughput with SSL was lower than without it, but I was quite surprised that the major performance bottleneck was the time spent establishing connections. This result naturally led to further investigation. I especially wanted to do a performance comparison between MySQL’s built-in SSL encryption and external encryption techniques—such as SSH tunneling. I will also clarify some issues raised in the comments of my previous article through this post. So, let’s get straight to the point.

Test Environment:

The test environment involved in this article consists of four machines total:

• Machine A: m1.xlarge EC2 instance (4 vCPU/15GB RAM/Amazon Linux) in US-West-2/Oregon
• Machine B: m1.xlarge EC2 instance (4 vCPU/15GB RAM/Amazon Linux) in EU-West/Ireland
• Machine C: Intel Core i7-2600K 3.4GHz (8 HT cores/32GB RAM/CentOS 6.4)
• Machine D: Intel Core i3-550 3.2GHz (4 HT cores/16GB RAM/CentOS 6.4)

Some tests used MySQL 5.6.13-community, and the other part used Percona Server 5.6.13.

External Encryption Techniques

In this test, without a real VPN, I used the most common way to create a site-to-site connection—the tried-and-true SSH tunnel. I didn’t have enough equipment to set up a hardware-accelerated VPN, but these are sufficient to illustrate the point. The default SSL cipher suite used by MySQL/SSL is DHE-RSA-AES256-SHA; let’s break that down briefly: it uses SHA1 as the hash function, RSA for authentication, and 256-bit AES (in CBC mode, according to OpenSSL documentation) encryption for Diffie-Hellman key exchange. Although perhaps not obvious, it’s very easy to mimic the same cipher suite via OpenSSL. The SSH version 2 protocol uses DHE/RSA/SHA1 by default, so all we need is to specify the AES256-CBC cipher when establishing our tunnel, and for all intents and purposes, we will be comparing equivalent encryption. Out of curiosity, we will also try AES256 in CTR mode over the SSH tunnel, because it encrypts blocks, so it should theoretically be slightly faster, but the end result, at least in this test, showed the difference was negligible.

The machines used for this test were Machine C (server) and Machine D (client), both located in the same Gigabit Ethernet VLAN segment. The test script was similar to the one in Part 1, with the goal of creating 100 connections as fast as possible. Each test configuration ran 10 times. The table below lists the averages and standard deviations; the numbers shown are connections created per second. Also, note for this particular test, all keys were 4096 bits long, and all tests were run on Percona Server 5.6.13.

Or, if you prefer a chart, here it is in chart form.

Clearly, no encryption is the fastest, but creating connections through an SSH tunnel hardly loses any performance compared to MySQL’s native SSL approach. Neither 100 cps nor 22 cps is realistic, but I’d wager that for most people, the figure of 470-480 cps per individual thread is still serviceable.

Connection Performance Over High-Latency Links

The test data will be presented later in this article. In fact, the stability of an SSL connection is affected by network latency. From the results above, we can see that on a low-latency link, using SSL has a significant impact on performance. So what happens over a WAN? It’s possible that in a scenario where the network’s simple round-trip time delay is factored in, using MySQL’s built-in SSL support means the encryption mix won’t affect performance as much. Therefore, for this test, I split it across two different Amazon EC2 instances (the aforementioned Machine A and Machine B). Machine C, located in Northern California, was used as the client. This test was run under both MySQL Community and Percona Server, with key sizes ranging from 0 to 4096 bits. The SSL cipher suite used was the default setting. The test script, as before, ran 10 times, rapidly creating 100 connections each time and reporting the connections per second. Of course, in this test, these raw numbers are secondary; we just wanted to see the impact of network latency on SSL performance.

First, from C to B (Northern California to Ireland):

— ec2-54-220-212-210.eu-west-1.compute.amazonaws.com ping statistics —
50 packets transmitted, 50 received, 0% packet loss, time 49228ms
rtt min/avg/max/mdev = 167.851/170.126/177.433/2.289 ms

Then, from C to A (Northern California to Oregon):

— ec2-54-212-134-221.us-west-2.compute.amazonaws.com ping statistics —

50 packets transmitted, 50 received, 0% packet loss, time 49108ms

rtt min/avg/max/mdev = 42.543/44.648/59.994/3.194 ms

As expected, the test values are clearly much lower than connecting to a server across a continent—at least geographically a few hundred miles away—but it turns out that, setting aside the MySQL Community reaction, we see that performance doesn’t actually drop that much. The table below compares connections from C to B and from C to A.

A few observations from the above. First, if your server is 40ms or 170ms away, 1024-bit SSL encryption doesn’t have much impact on performance. Second, as latency increases, the percentage of lost connections increases due to the SSL encryption overhead. This makes perfect sense, especially in a common scenario (servers on the same intranet, or connecting to the same server via TCP), where connection throughput is mainly affected by whether SSL is used or not. Of course, comparing the cost of MySQL Community 4096-bit encryption with Percona Server, the above doesn’t really mean anything. There seem to be some specific tricks used to boost MySQL Community 4096-bit encryption performance, but they don’t appear to have affected Percona Server much. I’m not sure if this is a good assumption—it could be a PEBKAT on my part in both tests. So, if others are also testing, I’d be curious to know if you get the same results.

Original link: http://www.mysqlperformanceblog.com/2013/11/18/mysql-encryption-performance-revisited/