This is part two on a two-part series on the performance implications of in-flight data encryption with MySQL. In the first part, I focused specifically on the impact of using MySQL’s built-in SSL support with some rather surprising results. Certainly it was expected that query throughput would be lower with SSL than without, but I was rather surprised by the magnitude of the performance hit incurred at connection setup time. These results naturally lended themselves to some further investigation; in particular, I wanted to compare performance differences between MySQL’s built-in SSL encryption facilities and external encryption technologies, such as SSH tunneling. I’ll also be using this post to address a couple of questions posed in the comments on my original article. So, without further ado….
Test Environment
The various tests discussed in this post involved a total of four different machines:
- 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 were conducted with MySQL 5.6.13-community, and other tests were conducted with Percona Server 5.6.13.
External Encryption Technology
For this test, I looked at one of the most common ways of establishing a site-to-site link in the absence of a “true” VPN – the good old-fashioned SSH tunnel. I don’t have ready access to sufficient gear to set up a hardware-accelerated VPN, but this is enough to illustrate the point. Recall that the default SSL cipher suite used by MySQL/SSL is DHE-RSA-AES256-SHA; if we unpack this a bit, it tells us that we’re using Diffie-Hellman key exchange with SHA1 as our hashing function, RSA for authentication, and encryption with 256-bit AES (in CBC mode, according to the OpenSSL docs). Although perhaps not immediately obvious, this same cipher suite is pretty easy to emulate with OpenSSH. The SSH version 2 protocol uses DHE/RSA/SHA1 by default, so then all we need to do is explicitly specify the AES256-CBC cipher when we’re setting up our tunnel, and we should be, for all intents and purposes, comparing encrypted apples to encrypted apples. For the sake of curiosity, we also try our SSH tunnel with AES256 in CTR mode, which should theoretically be a bit faster due to its ability to encrypt blocks in parallel, but as it turns out, at least for this test, the difference is marginal.
The machines used for this test were machines C (server) and D (client), which are on the same VLAN of a gigabit Ethernet link, and the test script in use was similar to that from part 1, wherein we attempt to create 100 connections as fast as possible. Each test configuration was run 10 times, with the averages and standard deviations for each test listed in the table below, and the numbers are given in connections per second. Also, note that for this particular test, all keys used are 4096 bits and tests were run against Percona Server 5.6.13 only.
| NO ENCRYPTION | MySQL+SSL | SSH tunnel (AES256-CBC) | SSH tunnel (AES256-CTR) |
|---|---|---|---|
| 1001.33 (59.26) | 22.23 (0.1392) | 476.52 (11.87) | 482.02 (13.42) |
Or, for those of you who like graphics, I think the chart speaks for itself.
Obviously, not using encryption is still the fastest game in town, but connection throughput over an SSH tunnel doesn’t obliterate performance to anywhere near the same level as using MySQL’s native SSL capability. Going from 1000 cps to 22 cps is potentially unusable, but I’d venture a guess that 470-480 cps in a single thread would still provide serviceable results for most people.
Connection Performance over High-Latency Links
Pursuit of data for this test came out of a question left in the comments on my original post; specifically, how the SSL connection penalty is impacted by increasing network latency. We can see from the results above that on a low-latency link, using SSL dramatically impacts performance, but what if we’re doing that connection over a WAN link? It might be the case that if we’re already paying a latency penalty due to simple round-trip time, maybe throwing encryption into the mix won’t hurt that much, even if we do it with MySQL’s built-in SSL support. Thus, for this test, I spun up two different Amazon EC2 instances (machines A and B, described above). Machine C, loacated in Northern California, was used as the client, and this test was conducted both with Community MySQL as well as Percona Server, and with key sizes ranging from zero to 4096 bits. The SSL cipher suite used was the default, and the test script the same as before – run 10 trials, create 100 connections as fast as we can, and report results in connections per second. However, for this test, the raw numbers are somewhat secondary; we’re really just looking to see the impact of network latency on SSL connection 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
And next, 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, obviously the raw numbers are much lower for the transcontinental test than they are when connecting to servers that, at least geographically, are only a few hundred miles away, but as it turns out, with the exception of how Community MySQL behaves, which I’ll talk about in a minute, the percentage decrease in performance actually doesn’t really vary by that much. The table below compares connections from C to B with connections from C to A.
| MySQL 5.6.13 US->EU | MySQL 5.6.13 US->US | PS 5.6.13 US->EU | PS 5.6.13 US->US | PS 5.6.13-static US->EU | PS 5.6.13-static US->US | |
|---|---|---|---|---|---|---|
| 1024-bit | 34.39% | 36.13% | 34.59% | 35.23% | 33.44% | 36.31% |
| 2048-bit | 37.04% | 45.07% | 33.91% | 38.35% | 34.30% | 35.40% |
| 4096-bit | 51.85% | 71.66% | 37.06% | 43.17% | 37.64% | 41.66% |
A few comments on the above. First, at 1024 bits of SSL encryption, it doesn’t make that much difference if you’re 40ms away or 170ms away. Second, as latency decreases, the connection-throughput performance lost due to SSL encryption overhead increases. That makes sense, particularly when we consider that in the base cases (two servers on the same LAN, or connections over a TCP socket to the same server), connection throughput performance is dominated by the use (or not) of SSL. However, the one thing in all of this that doesn’t make sense to me is just how badly community MySQL fares with 4096-bit encryption compared to Percona Server. What’s so special about 4096-bit encryption that tanks the performance of Community MySQL but doesn’t seem to impact Percona Server nearly as much? I don’t have a good answer for this nor even a good hypothesis; if it hadn’t happened on both tests I’d probably have claimed a PEBCAT, but now I’m just not sure. So, if anyone else tries this test, I’d be curious to know if you get the same results.
Parting Thoughts
Regardless of the anomaly with MySQL 5.6.13 and 4096-bit SSL, I think the primary takeaway from both this post and its predecessor is pretty clear: if you need end-to-end encryption of your MySQL traffic and you’re using replication or a connection-pooling sort of workload, MySQL’s built-in SSL support will probably serve you just fine, but if your application is of the type that requires setting up and tearing down large numbers of connections frequently, you might want to look at offloading that encryption work elsewhere, even if “elsewhere” just means running it through an SSH tunnel.
To me it seems like MySQL fails to implement reusable SSL sessions (session caching). It’s fundamental to SSL performance. Also one needs to make sure that client/server entropy pools doesn’t exhaust.
Nice analysis Ernie!
Something else you may want to look at is whether or not the AES-NI instruction set is available and being used by openSSL (openssl engine -c -tt). In some other work/research that I have done there seems to be some situations where AES-NI instructions are available but either an older version of openSSL is present which does not have support for their use or for some reason fails to properly detect their presence. There are also some openssl tests that can be performed to check its base speed ‘openssl speed aes-256-cbc’ for example but I am not sure how useful that may be. You may also want to check to see if both sides of the test (client and server) can use it as well. Use/non-use of AES-NI only really impacts the bulk data transmission and not the connection setup/teardown issues but it is something else to consider.
@George-
Hm, I just checked machines C and D (the only physical boxes that were part of this test) and machine C (Core i7) has AES-NI available for AES-256-CBC (and, I am assuming, in use, based on the output of openssl engine -c -tt) but apparently machine D (Core i3) does not. No idea whether or not EC2 instances will have this or not, but I’ll have to look into it. I would suspect that it depends on instance size and virtualization type, but that’s an interesting question in itself.
Great post.
I think this is somewhat related here:
http://chimera.labs.oreilly.com/books/1230000000545/ch04.html
Ernie,
I see you tested the connection speed only in this case (whenever local or in the cloud)
I would wonder how multiple queries through the same connection as well as “streaming” – think queries with large result set, mysqldump, replication are impacted
My surprise re MySQL SSL performance was what not only connections but also the streaming large data volumes through connections were seriously impacted even though with modern processors one should be able to encrypt the traffic at very high rate.
Is it worth compiling MySQL against OpenSSL and testing with that as some of this could be limitations with yassl
Peter has an interesting point, I wonder how this effects the results of normal and high volume results.
Great test, very interesting. I hope they can overcome these hurdles with a new version
@Simon-
You might be on to something there. Percona Server is compiled against OpenSSL, so that could explain some of the performance differences at higher key lengths. I just did a little research and came across this: http://bugs.mysql.com/bug.php?id=46546 – apparently there’s an alternative version of yassl called cyassl that’s supposed to be faster, but it doesn’t look like there’s been much activity on this bug in quite some time, either.
@Ali, @PeterZ-
I am still not completely convinced of the utility of running a test like this – obviously it’s going to be slower if I run 10000 iterations of “SELECT * FROM table where PK=?” versus 1 iteration “SELECT * FROM table” – and that will be true whether I use SSL or not, simply because of the increased network traffic. But, at the same time, I do take requests, and setting up a couple of tests to measure this shouldn’t be that time-consuming. So, look for a part 3 in this series in the near future. Who knows, the results may be surprising.
Ernie,
My point is as follows. When it comes to SSL I see the overhead in its different flavors can come from 3 different areas
– Establishing connection. SSL handshake is expensive. This is what we see already in your test.
– Overhead of Sending the “packet”. There must be some overhead related to sending even small query returning small result set due to additional handling of SSL by the client library and server.
– The overhead of “extra byte” – sending more data as query result also should be more expensive with SSL due to encryption and possibly other overhead.
My theory is what these overhead should range from higher to lower, as such overhead for establishing connection should be very high while at least in theory streaming data through SSL should come with little overhead as modern CPUs should be able to do AES encryption at speed of hundreds of MB per second.
Hi,
is it not possible for optimization use a dedicated solution like BigIP (F5) and set the encryption SSL on it.
So you client (host) will send through SSL to the F5 , the F5 will send it in clear mode in to MYSQL.
Mysql will send back data in clear mode to F5, will then use SSL to respond to client . So we use the F5 features like hardware compression, encryption , check for SQL injection …
@Eric–
For many applications, this is a sufficient level of security, and you’ve offloaded the encryption to the F5 so that MySQL doesn’t have to do it, so you would get some performance boost, but you won’t have end-to-end encryption. How much do you trust the link between your F5 and your database server? For some applications, maybe it doesn’t matter, but for very high security requirements, I wouldn’t want unencrypted data anywhere on the wire.
Very interesting test. I wonder how this effects normal/high vol results.