Sunday, May 7, 2017

MySQL and SSL/TLS Performance

In conversations about SSL/TLS people often say that they either don't need TLS because they trust their network or they say it is too slow to be used in production.

With TLS the client and server has to do additional work, so some overhead is expected. But the price of this overhead also gives you something in return: more secure communication and more authentication options (client certificates).

SSL and TLS have existed for quite a long time. First they were only used for online banking and during authentication on web sites. But slowly many websites went to full-on SSL/TLS. And with the introduction of Let's encrypt many small websites are now using SSL/TLS. And many non-HTTP protocols either add encryption or move to a HTTP based protocol.

So TLS performance is very important for day-to-day usage. Many people and companies have put a lot of effort into improving TLS performance. This includes browser vendors, hardware vendors and much more.

But instead of just hoping for good performance: Let's try to measure it with a simple benchmark.

There are multiple pieces of a database connection we have to benchmark:
  1. New connections
  2. Reconnecting
  3. Bulk transfer
 And for all of these there are multiple things we can measure:
  1. Connect and/or transfer time (performance)
  2. CPU usage (efficiency)
  3. Concurrency 
The benchmark code can be found here:

Let's look at connection performance. In this test I connect a number of times to MySQL  and do a "DO 1". This is on a localhost TCP connection, so it should be fast.

This is the connection time in ms for a single connection.
With 5.6.33 Community Edition, which is YaSSL based we see a very noticable overhead. And with 5.7.17 Community Edition this overhead is much smaller, but still very noticable.

Then MySQL 5.7 with OpenSSL (compiled on Fedora 25) shows another very noticable improvement over YaSSL. This can be explained because in this case the AVX2 and AES-NI CPU features can be used.

Also OpenSSL supports TLS tickets and YaSSL doesn't. This is why the yellow bar is much shorter that the orange bar. This is not yet supported in libmysqlclient, see Bug #76921 for details.

So SSL/TLS can be slow, but doesn't have to be slow.

Note that TLS needs multiple roundtrips. When testing this with netem on Linux I see this with MySQL 5.7.18 (YaSSL) and a 5ms delay:
No TLS goes from 0.5ms to 52ms
TLS goes from 8ms to 85ms

The second thing to measure is bulk performance. This is for large result sets including mysqldump.

With mysqldump from MySQL 5.7 it is easy to do:

$ time mysqldump --ssl-mode=disabled -A > /dev/null

real 0m0.145s
user 0m0.021s
sys 0m0.005s
$ time mysqldump --ssl-mode=required --ssl-cipher=AES128-SHA -A > /dev/null

real 0m0.120s
user 0m0.039s
sys 0m0.007s 
If you do this with multiple ciphers and put some data in the database you'll see something like this:
TLS Default
 This is with MySQL 5.6.33 with YaSSL. Note that this is without using modern CPU features etc.

To conclude, there are some steps you can take to improve SSL/TLS performance:
  1. Upgrade to 5.7
  2. Compile MySQL with OpenSSL
  3. Use TLS tickets
  4. Use persistent connections
  5. Try different cipher suits for mysqldump and other places where you transfer larger amounts of data.