Bug #89963 Slowdown in creating new SSL connection
Submitted: 8 Mar 13:45 Modified: 12 May 13:15
Reporter: Rene' Cannao' Email Updates:
Status: No Feedback Impact on me:
None 
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 13:45] Rene' Cannao'
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
[8 Mar 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 14:11] Georgi Kodinov
Also your client seems to be running with --default-plugin=caching_sha2 against a user with native_authentication.
[8 Mar 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 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 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 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 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".