| Bug #89963 | Slowdown in creating new SSL connection | ||
|---|---|---|---|
| Submitted: | 8 Mar 2018 13:45 | Modified: | 24 Nov 2023 12:41 |
| Reporter: | Rene' Cannao' | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Connection Handling | Severity: | S5 (Performance) |
| Version: | 8.0.4 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | performance ssl connction | ||
[8 Mar 2018 14:09]
Georgi Kodinov
That's comparing apples to oranges unfortunately. The 5.7 callstack shows that this is a yaSSL binary. And the 8.0 seems to be an openssl one. Can you please compare openssl to openssl ?
[8 Mar 2018 14:11]
Georgi Kodinov
Also your client seems to be running with --default-plugin=caching_sha2 against a user with native_authentication.
[8 Mar 2018 14:25]
Rene' Cannao'
Hi Georgi,
good catch on client running with --default-plugin=caching_sha2 !
Although, changing it (using mysqlslap from 5.7.21) doesn't make a difference, there is always a 17ms extra latency per connection.
$ time ~/opt/mysql/5.7.21/bin/mysqlslap --create-schema=information_schema --ssl-mode=required -u msandbox -pmsandbox -h 127.0.0.1 -P17822 -q "select @@version_comment limit 1" -i 300
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.013 seconds
Minimum number of seconds to run all queries: 0.012 seconds
Maximum number of seconds to run all queries: 0.016 seconds
Number of clients running queries: 1
Average number of queries per client: 1
real 0m4.220s
user 0m0.816s
sys 0m0.084s
$ time ~/opt/mysql/5.7.21/bin/mysqlslap --create-schema=information_schema --ssl-mode=required -u msandbox -pmsandbox -h 127.0.0.1 -P8004 -q "select @@version_comment limit 1" -i 300
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.030 seconds
Minimum number of seconds to run all queries: 0.028 seconds
Maximum number of seconds to run all queries: 0.046 seconds
Number of clients running queries: 1
Average number of queries per client: 1
real 0m9.357s
user 0m0.792s
sys 0m0.116s
About comparing apples to oranges: I agree with you.
5.7 uses yaSSL, while 8.0 uses OpenSSL .
In fact, it is likely that this performance issue isn't due to 5.7 vs 8.0 , but yaSSL vs OpenSSL (maybe I should change the title of this bug report).
Can I compare openssl to openssl ?
Sure, where do I find a version of 5.7.21 compiled against openssl?
I am using the tarballs from https://dev.mysql.com/downloads/mysql/ , where 5.7.21 is compiled against yaSSL , while 8.0.4 is compiled against OpenSSL .
From a user prospective, MySQL 8.0.4 provided by Oracle is slower compared to MySQL 5.7.21 provided by Oracle.
[8 Mar 2018 14:37]
Rene' Cannao'
From manual: https://dev.mysql.com/doc/refman/5.7/en/openssl-versus-yassl.html * MySQL Community Edition binary distributions are compiled using yaSSL. https://dev.mysql.com/doc/refman/8.0/en/openssl-versus-yassl.html * MySQL Community Edition binary distributions are compiled using OpenSSL. (Prior to MySQL 8.0.4, MySQL Community Edition binary distributions are compiled using yaSSL.) So likely the regression happened from 8.0.3 to 8.0.4 : I will shortly test that. Also, since "MySQL Enterprise Edition binary distributions are compiled using OpenSSL" in both 5.7 and 8.0 , if this performance issue is indeed related to OpenSSL, maybe it is affecting all Enterprise Edition binaries (I cannot verify that).
[8 Mar 2018 15:03]
Rene' Cannao'
I tested 8.0.3 (yassl):
$ ~/opt/mysql/8.0.4/bin/mysql -u msandbox -pmsandbox -h 127.0.0.1 -P8003 -e "SELECT @@version, @@version_comment, @@default_authentication_plugin; SHOW CREATE USER msandbox@'127.%'\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+------------------------------+---------------------------------+
| @@version | @@version_comment | @@default_authentication_plugin |
+--------------+------------------------------+---------------------------------+
| 8.0.3-rc-log | MySQL Community Server (GPL) | mysql_native_password |
+--------------+------------------------------+---------------------------------+
*************************** 1. row ***************************
CREATE USER for msandbox@127.%: CREATE USER 'msandbox'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' DEFAULT ROLE `R_DO_IT_ALL`@`%` REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT
$ time ~/opt/mysql/5.7.21/bin/mysqlslap --create-schema=information_schema --ssl-mode=required -u msandbox -pmsandbox -h 127.0.0.1 -P8003 -q "select @@version_comment limit 1" -i 300
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.018 seconds
Minimum number of seconds to run all queries: 0.016 seconds
Maximum number of seconds to run all queries: 0.024 seconds
Number of clients running queries: 1
Average number of queries per client: 1
real 0m5.634s
user 0m0.812s
sys 0m0.104s
I tried multiple times, and the conclusion is that average connection time with SSL is:
5.7.21 (yassl) : 13ms
8.0.3 (yassl) : 18ms
8.0.4 (openssl) : 30ms
[12 Apr 2018 6:11]
Harin Vadodaria
Hi René Summarizing our discussion on slack: - I observed performance difference between ProxySQL and MySQL 8.0 when establishing SSL conenction. I used your benchmark script from github : https://github.com/renecannao/benchmark-tools/blob/master/connect_speed.cpp - Investigating further, cause for performance difference appeared to be differences in TLS protocol version, key size of X509 certificate and ciphersuite being used by ProxySQL and MySQL. If i configure MySQL to use values similar ProxySQL, performance was comparable. - Perf data shows that maximum time is spent in session key generation: in generate_key() and compute_key() function of OpenSSL. These are called as part of key exchange while establishing TLS connection. So it is no suprise that when a non-DHE ciphersuite is used, connection speed is faster. - Latest patch for ProxySQL 2.0 lab release changed TLS protocol to 1.2 and key size to 2048. With that, performance of MySQL 8.0 is similar to that of ProxySQL. - ProxySQL, when compiled using default instructions, links against system SSL. Hence, for above mentioned experiment, i used EL7 tarball that links against system SSL. Best Regards, - Harin
[13 May 2018 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Creating new SSL connections in MySQL 8.0.4 seems very slow compared to 5.7.21 . On a 3.2GHz CPU , 8.0.4 adds an extra latency of around 17ms per connection. For this simple benchmark, both 5.7.21 and 8.0.4 where configured with default_authentication_plugin = mysql_native_password , and users where created using mysql_native_password as authentication plugin Test against 5.7.21: $ ~/opt/mysql/8.0.4/bin/mysql -u msandbox -pmsandbox -h 127.0.0.1 -P17822 -e "SELECT @@version, @@version_comment, @@default_authentication_plugin; SHOW CREATE USER msandbox@'127.%'\G" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+------------------------------+---------------------------------+ | @@version | @@version_comment | @@default_authentication_plugin | +------------+------------------------------+---------------------------------+ | 5.7.21-log | MySQL Community Server (GPL) | mysql_native_password | +------------+------------------------------+---------------------------------+ *************************** 1. row *************************** CREATE USER for msandbox@127.%: CREATE USER 'msandbox'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK $ time ~/opt/mysql/8.0.4/bin/mysqlslap --create-schema=information_schema --ssl-mode=required -u msandbox -pmsandbox -h 127.0.0.1 -P17822 -q "select @@version_comment limit 1" -i 300 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 0.014 seconds Minimum number of seconds to run all queries: 0.014 seconds Maximum number of seconds to run all queries: 0.017 seconds Number of clients running queries: 1 Average number of queries per client: 1 real 0m4.642s user 0m1.536s sys 0m0.060s === Test against 8.0.4 : $ ~/opt/mysql/8.0.4/bin/mysql -u msandbox -pmsandbox -h 127.0.0.1 -P8004 -e "SELECT @@version, @@version_comment, @@default_authentication_plugin; SHOW CREATE USER msandbox@'127.%'\G" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------+------------------------------+---------------------------------+ | @@version | @@version_comment | @@default_authentication_plugin | +--------------+------------------------------+---------------------------------+ | 8.0.4-rc-log | MySQL Community Server (GPL) | mysql_native_password | +--------------+------------------------------+---------------------------------+ *************************** 1. row *************************** CREATE USER for msandbox@127.%: CREATE USER 'msandbox'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' DEFAULT ROLE `R_DO_IT_ALL`@`%` REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT $ time ~/opt/mysql/8.0.4/bin/mysqlslap --create-schema=information_schema --ssl-mode=required -u msandbox -pmsandbox -h 127.0.0.1 -P8004 -q "select @@version_comment limit 1" -i 300 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 0.031 seconds Minimum number of seconds to run all queries: 0.030 seconds Maximum number of seconds to run all queries: 0.044 seconds Number of clients running queries: 1 Average number of queries per client: 1 real 0m9.755s user 0m1.532s sys 0m0.132s ==== Output of perf for 5.7.21: 49.44% mysqld mysqld [.] TaoCrypt::Portable::Multiply8 17.45% mysqld mysqld [.] TaoCrypt::Portable::Add 8.17% mysqld mysqld [.] TaoCrypt::Portable::Subtract 5.45% mysqld mysqld [.] TaoCrypt::Portable::Multiply8Bottom 3.54% mysqld mysqld [.] TaoCrypt::Portable::Square4 3.32% mysqld mysqld [.] TaoCrypt::RecursiveMultiply 2.74% mysqld mysqld [.] TaoCrypt::Portable::Multiply4 1.50% mysqld mysqld [.] TaoCrypt::RecursiveSquare 0.82% mysqld mysqld [.] TaoCrypt::RecursiveMultiplyTop 0.65% mysqld mysqld [.] TaoCrypt::RecursiveMultiplyBottom 0.40% mysqld mysqld [.] yaSSL::Sessions::lookup 0.34% mysqld mysqld [.] TaoCrypt::SHA::Transform 0.29% mysqld mysqld [.] TaoCrypt::MontgomeryReduce 0.29% mysqld mysqld [.] TaoCrypt::Integer::operator= 0.26% mysqld mysqld [.] TaoCrypt::MD5::Transform 0.26% mysqld mysqld [.] TaoCrypt::AbstractGroup::SimultaneousMultiply === Output of perf for 8.0.4: 52.03% mysqld libcrypto.so.1.0.0 [.] bn_mul_add_words 18.59% mysqld libcrypto.so.1.0.0 [.] bn_sqr_comba8 6.68% mysqld libcrypto.so.1.0.0 [.] bn_mul_comba8 5.42% mysqld libcrypto.so.1.0.0 [.] bn_add_words 4.14% mysqld libcrypto.so.1.0.0 [.] MOD_EXP_CTIME_COPY_FROM_PREBUF 3.48% mysqld libcrypto.so.1.0.0 [.] bn_sub_words 3.33% mysqld libcrypto.so.1.0.0 [.] BN_from_montgomery_word 1.46% mysqld libcrypto.so.1.0.0 [.] bn_sqr_recursive 0.41% mysqld libcrypto.so.1.0.0 [.] bn_mul_recursive 0.24% mysqld libcrypto.so.1.0.0 [.] BN_CTX_get 0.22% mysqld libcrypto.so.1.0.0 [.] bn_cmp_words 0.21% mysqld libcrypto.so.1.0.0 [.] BN_CTX_end 0.18% mysqld libcrypto.so.1.0.0 [.] BN_sqr 0.18% mysqld libcrypto.so.1.0.0 [.] sha256_block_data_order 0.17% mysqld libcrypto.so.1.0.0 [.] BN_set_word 0.16% mysqld libcrypto.so.1.0.0 [.] BN_CTX_start 0.13% mysqld libcrypto.so.1.0.0 [.] BN_mod_mul_montgomery 0.09% mysqld libcrypto.so.1.0.0 [.] bn_sub_part_words 0.08% mysqld libcrypto.so.1.0.0 [.] BN_mod_exp_mont_consttime 0.07% mysqld libcrypto.so.1.0.0 [.] BN_mul How to repeat: Install MySQL 5.7.21 and MySQL 8.0.4 , both using default_authentication_plugin = mysql_native_password , and create a user using mysql_native_password as authentication plugin . Test connection time performance Suggested fix: Not sure