MySQL encryption performance test

This is the second article in the series. In the first article, I used MySQL's built-in SSL support for stress testing, which produced some surprising results. Of course, it is expected that the throughput performance of queries using SSL is lower than that of queries without SSL, But I am surprised that the main performance bottleneck is the time spent establishing connections. This result naturally leads to further research. In particular, I want to make one-time comparison between MySQL's built-in SSL encryption and external encryption technologies, such as the SSH channel. I will also use this article to clarify some questions raised in the comments of my previous article. Let's go straight to the topic ..

Test environment:

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

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

Some tests use MySQL5.6.13-community, while others use Percona Server5.6.13.

External encryption technology

In this test, without a real vpn, I used the most common method to create a site-site connection-that is, the old SSH channel of Baodao. I didn't find enough devices to build a hardware-accelerated VPN, but this is enough to explain the problem. The default SSL encryption component used by MySQL/SSL is the DHE-RSA-AES256-SHA. For a slight explanation, this meaning is to use the SHA1 algorithm as our hash function, RSA as the identity authentication, 256-bit AES (in CBC mode, according to the OpenSSL document) encryption for Diffie-Hellman Key Exchange. Although it may not be obvious, it is easy to imitate the same cipher suite through OpenSSL. SSH version2 uses DHE/RSA/SHA1 by default, so all we need is to specify the AES256-CBC encryptor when creating our channel, and we will compare the encryption results out of all the intentions and guesses. Out of curiosity, we will also try to use AES256 In the CTR mode of the SSH channel, because it can encrypt the block, so it will be a little faster theoretically, but the final result, at least in this test, this difference is minimal.

The machines used for this test are C machines (servers) and D machines (clients). The two machines are in the same Gigabit Ethernet VLAN chain. The test script is similar to the first part of the script, the goal is to create 100 connections as quickly as possible. Each test configuration is run 10 times. The following table lists the average and standard deviation, and the number is the number of connections created per second. At the same time, we should also note that in this special test, all keys are 4096 bits long and all tests are run on Percona Server 5.6.13.

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, if you like charts, the following is the chart method.

Obviously, no encryption is the fastest, but the method of creating a connection through the SSH tunnel does not suffer much performance loss compared with the MySQL local SSL method. Whether it is 100 cps or 22 cps, it is unrealistic, but I bet that for most people, the number of 470-480 cps produced by each independent thread can still provide services.

Connection Performance of High-latency links

The test data will be provided after 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. What will happen on the Wan? In either case, the latency of a simple round-trip network is considered. With the built-in SSL support of MySQL, hybrid encryption will not affect the performance. Therefore, in this test, I split two different Amazon EC2 instances (device A and Device B ). The device C is located in northern California as the client. This test is performed under the MySQL cluster and Percona server. The key size ranges from 0 to 4096. The SSL password component uses the default settings. The test script needs to run 10 times as before, quickly create 100 links, and refresh the connection results per second. Of course, in the test, the raw data is secondary. We just want 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 bytes ed, 0% packet loss, time 49228 ms
Rtt min/avg/max/mdev = 167.851/170.126/177.433/2.289 MS

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 bytes ed, 0% packet loss, time 49108 ms
Rtt min/avg/max/mdev = 42.543/44.648/59.994/3.194 MS

As we expected, it is obvious that the test data is much lower than the number of servers connected across a mainland China. At least there is a distance of several hundred meters from the geographical location, but it turns out that, excluding the response from the MySQL cluster, we can see that the performance will not actually drop so much. The following table compares the connections from C to B and from C to ..

	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%

The above are some comments. First, if your server is 40 ms or 1024 Ms away, the-bit SSL encryption will not significantly affect the performance. Second, as the latency increases, the loss of connections will be affected due to the increase in SSL encryption overhead. This makes sense, especially in a common situation (the server is in the same Intranet or connected to the same server through TCP ), the connection throughput performance is mainly affected by the absence of SSL. Of course, the price of 4096-bit encryption for MySQL clusters is no longer meaningful than that for Percona servers. There are some special measures to improve the 4096-bit encryption performance of the MySQL cluster, but it does not seem to have much impact on the Percona server. I'm not sure this is a good assumption. In both tests, I may say it is a PEBCAT. So if others are also testing, I'm curious to know if you get the same structure as well.

Last thought

Regardless of MySQL 5.6.13 and 4096-bit SSL, I think what I want to express in this article is also very clear (I do not understand the meaning of my predecessor ): if you need to encrypt your MySQL transmission end-to-end, the built-in SSL of MySQL supports the workload of using replication or connection pool classes, which may also meet your needs, however, your application requires frequent creation and destruction of a large number of connections. You can only use SSH tunnels to reduce the encryption load.