Performance test for MySQL encryption

This is the second of two article series on the encryption performance test for MySQL. In the first article, I specifically used MySQL's built-in support for SSL to do stress testing, which produced some surprising results.

AD:WOT2015 Internet operations and developers Conference selling tickets

This is the second of two article series on the encryption performance test for MySQL. In the first article, I specifically used MySQL's built-in support for SSL to do stress testing, which produced some surprising results. Of course, the throughput performance of using SSL queries is less than the performance of non-SSL, which is expected, but I am quite surprised that the main performance bottleneck is the time spent in connection setup. This result naturally leads to further research. In particular, I want to do a performance comparison between MySQL's built-in SSL encryption and external encryption techniques such as SSH channels. I will also use this article to clarify some of the questions I raised in the comments in my last article. So, go straight to the subject.

Test environment:

The test environment involved in this article is altogether four machines:

• Machine A:m1.xlarge EC2 Instance (4-core CPU/15GB Ram/amazon Linux) in Us-west-2/oregon
• Machine B:m1.xlarge EC2 Instance (4 VCPU/15GB Ram/amazon Linux) I 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 use mysql5.6.13-community and the other part uses Percona Server5.6.13.

External encryption technology

In this test, I created a station-to-station connection in the most common way without a real VPN-that is, blades an old SSH channel. I didn't find enough equipment to build a hardware-accelerated VPN, but that was enough to explain the problem. The default SSL encryption component used by MYSQL/SSL is Dhe-rsa-aes256-sha; let's just explain that this means using the SHA1 algorithm as our hash function, RSA as identity authentication, 256-bit AES (in CBC mode, Based on the OpenSSL document) encryption for Diffie-hellman key exchange. While it may not be obvious that through OpenSSL it is easy to mimic the same cryptographic suite. The SSH Version2 protocol uses DHE/RSA/SHA1 by default, so all we need is to specify the AES256-CBC cipher when we set up our channel, and we'll compare the encryption results for all intents and guesses. Out of curiosity, we will also try to use AES256 in the CTR mode of the SSH channel because it encrypts blocks, so it will theoretically be a little faster, but the end result, at least in this test, is very different.

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)

The machine in this test is the C machine (server) and the D machine (client), both of which are in a Gigabit Ethernet VLAN chain, and the test script is similar to the first part of the script, with the goal of creating 100 connections as quickly as possible. Each test configuration runs 10 times, and the table below lists the average and standard deviations, and the numbers listed are the number of connections created per second. Also, note that this particular test, all keys are 4096 specific , and all tests are run on Percona Server 5.6.13.

Or, if you like charts, here's how the chart works.

Obviously, no encryption is the quickest, but the way to create a connection over an SSH tunnel does not cost much performance compared to MySQL native SSL. Either the CPS or the CPS are unrealistic, but I'll bet that for most people, the number of 470-480 cps that each individual thread produces is still available for service.

Connection performance for high-latency links

The test data is given behind my article. In fact, the stability of SSL connections is affected by network latency. From the above results we can see that the use of SSL on low-latency links has a significant impact on performance, so what happens on the WAN? It is possible to consider the delay of a simple round-trip time for the network, which uses MySQL's built-in SSL support, which does not affect too much performance. So, in this test, I split up two different instances of Amazon EC2 (the above device A and device B). Device C is used as a client in Northern California, and this test was tested under MySQL cluster and Percona server, with a key size range from 0 to 4096 bits. The SSL cipher component uses the default settings, and the test script needs to run 10 times as before, quickly create 100 links, and refresh the connection results every second. Of course, in the test, these raw data are secondary, we just want to look at the network latency is the impact of SSL performance.

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

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

Next, from C to A (Northern California to Oregon State):

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

Packets transmitted, received, 0% packet loss, time 49108ms

RTT Min/avg/max/mdev = 42.543/44.648/59.994/3.194 ms

As we expected, it is clear that the test data is much lower than the number of connected servers across a continent, at least hundreds of meters in geographical location, but it turns out that the response to the MySQL cluster, we see that in fact, the performance does not really fall so much. The following table compares the connections from C to B, from C to a.

	MySQL 5.6.13 U S->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%	3 6.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%

The above is a few comments. First, if your server is 40ms or 170ms away, 1024-bit SSL encryption does not have much effect on performance. Second, as the delay increases, the loss of connectivity is affected by the increase in SSL encryption overhead. This makes sense, especially in a common case where the server is in the same intranet or through TCP to the same server, the throughput performance of the connection is primarily affected by the use of no SSL. Of course, the MySQL cluster 4096-bit the price of encryption compared to the Percona server, the above does not make any sense at all. There are some special means to improve the 4096-bit encryption performance of MySQL cluster, but it does not seem to affect the Percona server much. I'm not sure this is a good hypothesis, and I could have said it was a pebcat in these two Tests. So, if other people are also testing, I'm curious to know if you also get the same structure.

Last thought

Regardless of MySQL 5.6.13 and 4096-bit SSL issues, I think this article chase to express and its predecessor is also very clear (do not understand the meaning of the predecessor): If you need to end-to-end encryption of your MySQL transmission, MySQL's built-in SSL allows you to use the workload of replication or connection pooling classes, and may also meet your needs, but your application is required to frequently create and destroy a large number of connections, only through SSH tunneling you can reduce the load of encryption.

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

Link: http://www.oschina.net/translate/mysql-encryption-performance-revisited

Performance test for MySQL encryption