search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
6.03k stars 981 forks source link

Connecting to frontend with SSL consumes 100% CPU #2244

Open clawster opened 5 years ago

clawster commented 5 years ago

I have run in to a problem that as soon as a client connects to the frontend with SSL the proxysql process consumes 100% cpu.

But when the SSL based connection is terminated, the CPU drops down to 1-2%.

I have tried with the following binary distributions

Some notes...

Backend is AWS Aurora (using ssl). But that part seems to be working fine.

It does not seem to be related to hardware (tried t3a.nano and c5.large without any success).

The issue can reproduced with the following simple setup.

$ mysql -h proxysql.local -u user -p calcDb --ssl-ca=proxysql-ca.pem
--------------
mysql  Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:          311
Current database:       calcDb
Current user:           user@10.5.2.4
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MySQL
Server version:         5.5.30 (ProxySQL)
Protocol version:       10
Connection:             proxysql.local via TCP/IP
Server characterset:    latin1
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Uptime:                 2 min 0 sec

Threads: 1  Questions: 60  Slow queries: 0
$ mysql -h proxysql.local -u user -p calcDb --skip-ssl
--------------
mysql  Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:          735
Current database:       calcDb
Current user:           user@10.5.2.4
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MySQL
Server version:         5.5.30 (ProxySQL)
Protocol version:       10
Connection:             proxysql.local via TCP/IP
Server characterset:    latin1
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Uptime:                 4 min 41 sec

Threads: 2  Questions: 136  Slow queries: 0
--------------

PROXYSQL ADMIN

+-----------+-----------+-----------+-----------+---------+---------+------+
| SessionID | user      | db        | hostgroup | command | time_ms | info |
+-----------+-----------+-----------+-----------+---------+---------+------+
| 311       | user      | calcDb    | 10        | Sleep   | 0       | NULL |
| 735       | user      | calcDb    | 10        | Sleep   | 101448  | NULL |
+-----------+-----------+-----------+-----------+---------+---------+------+

+-----------------------------------------------------+-------------------------------------------+
| Variable_name                                       | Value                                     |
+-----------------------------------------------------+-------------------------------------------+
| admin-stats_mysql_connections                       | 60                                        |
| admin-stats_mysql_connection_pool                   | 60                                        |
| admin-stats_mysql_query_cache                       | 60                                        |
| admin-stats_system_cpu                              | 60                                        |
| admin-stats_system_memory                           | 60                                        |
| admin-telnet_admin_ifaces                           | (null)                                    |
| admin-telnet_stats_ifaces                           | (null)                                    |
| admin-refresh_interval                              | 2000                                      |
| admin-read_only                                     | false                                     |
| admin-hash_passwords                                | true                                      |
| admin-vacuum_stats                                  | true                                      |
| admin-cluster_check_interval_ms                     | 1000                                      |
| admin-cluster_check_status_frequency                | 10                                        |
| admin-cluster_mysql_query_rules_diffs_before_sync   | 3                                         |
| admin-cluster_mysql_servers_diffs_before_sync       | 3                                         |
| admin-cluster_mysql_users_diffs_before_sync         | 3                                         |
| admin-cluster_proxysql_servers_diffs_before_sync    | 3                                         |
| admin-cluster_mysql_query_rules_save_to_disk        | true                                      |
| admin-cluster_mysql_servers_save_to_disk            | true                                      |
| admin-cluster_mysql_users_save_to_disk              | true                                      |
| admin-cluster_proxysql_servers_save_to_disk         | true                                      |
| admin-checksum_mysql_query_rules                    | true                                      |
| admin-checksum_mysql_servers                        | true                                      |
| admin-checksum_mysql_users                          | true                                      |
| admin-web_enabled                                   | false                                     |
| admin-web_port                                      | 6080                                      |
| admin-mysql_ifaces                                  | 0.0.0.0:6032                              |
| mysql-shun_on_failures                              | 5                                         |
| mysql-shun_recovery_time_sec                        | 10                                        |
| mysql-query_retries_on_failure                      | 1                                         |
| mysql-client_multi_statements                       | true                                      |
| mysql-connect_retries_delay                         | 1                                         |
| mysql-connection_delay_multiplex_ms                 | 0                                         |
| mysql-connection_max_age_ms                         | 0                                         |
| mysql-connect_timeout_server_max                    | 10000                                     |
| mysql-eventslog_filename                            | queries.log                               |
| mysql-eventslog_filesize                            | 104857600                                 |
| mysql-eventslog_default_log                         | 1                                         |
| mysql-eventslog_format                              | 1                                         |
| mysql-auditlog_filesize                             | 104857600                                 |
| mysql-default_charset                               | utf8                                      |
| mysql-free_connections_pct                          | 10                                        |
| mysql-session_idle_ms                               | 1000                                      |
| mysql-have_ssl                                      | true                                      |
| mysql-client_found_rows                             | true                                      |
| mysql-monitor_enabled                               | true                                      |
| mysql-monitor_connect_timeout                       | 1000                                      |
| mysql-monitor_ping_max_failures                     | 3                                         |
| mysql-monitor_ping_timeout                          | 500                                       |
| mysql-monitor_read_only_max_timeout_count           | 3                                         |
| mysql-monitor_replication_lag_interval              | 10000                                     |
| mysql-monitor_replication_lag_timeout               | 1000                                      |
| mysql-monitor_groupreplication_healthcheck_interval | 5000                                      |
| mysql-monitor_groupreplication_healthcheck_timeout  | 800                                       |
| mysql-monitor_galera_healthcheck_interval           | 5000                                      |
| mysql-monitor_galera_healthcheck_timeout            | 800                                       |
| mysql-monitor_galera_healthcheck_max_timeout_count  | 3                                         |
| mysql-monitor_query_interval                        | 60000                                     |
| mysql-monitor_query_timeout                         | 100                                       |
| mysql-monitor_slave_lag_when_null                   | 60                                        |
| mysql-monitor_threads_min                           | 8                                         |
| mysql-monitor_threads_max                           | 128                                       |
| mysql-monitor_threads_queue_maxsize                 | 128                                       |
| mysql-monitor_wait_timeout                          | true                                      |
| mysql-monitor_writer_is_also_reader                 | true                                      |
| mysql-max_allowed_packet                            | 4194304                                   |
| mysql-throttle_connections_per_sec_to_hostgroup     | 1000000                                   |
| mysql-max_transaction_time                          | 14400000                                  |
| mysql-multiplexing                                  | true                                      |
| mysql-forward_autocommit                            | false                                     |
| mysql-enforce_autocommit_on_reads                   | false                                     |
| mysql-autocommit_false_not_reusable                 | false                                     |
| mysql-autocommit_false_is_transaction               | false                                     |
| mysql-verbose_query_error                           | false                                     |
| mysql-hostgroup_manager_verbose                     | 1                                         |
| mysql-binlog_reader_connect_retry_msec              | 3000                                      |
| mysql-threshold_query_length                        | 524288                                    |
| mysql-threshold_resultset_size                      | 4194304                                   |
| mysql-query_digests_max_digest_length               | 2048                                      |
| mysql-query_digests_max_query_length                | 65000                                     |
| mysql-wait_timeout                                  | 28800000                                  |
| mysql-throttle_max_bytes_per_second_to_client       | 0                                         |
| mysql-throttle_ratio_server_to_client               | 0                                         |
| mysql-max_stmts_per_connection                      | 20                                        |
| mysql-max_stmts_cache                               | 10000                                     |
| mysql-mirror_max_concurrency                        | 16                                        |
| mysql-mirror_max_queue_length                       | 32000                                     |
| mysql-default_max_latency_ms                        | 1000                                      |
| mysql-query_processor_iterations                    | 0                                         |
| mysql-query_processor_regex                         | 1                                         |
| mysql-set_query_lock_on_hostgroup                   | 1                                         |
| mysql-reset_connection_algorithm                    | 2                                         |
| mysql-auto_increment_delay_multiplex                | 5                                         |
| mysql-long_query_time                               | 1000                                      |
| mysql-query_cache_size_MB                           | 256                                       |
| mysql-poll_timeout_on_failure                       | 100                                       |
| mysql-keep_multiplexing_variables                   | tx_isolation,version                      |
| mysql-kill_backend_connection_when_disconnect       | true                                      |
| mysql-client_session_track_gtid                     | true                                      |
| mysql-session_idle_show_processlist                 | true                                      |
| mysql-show_processlist_extended                     | 0                                         |
| mysql-query_digests                                 | true                                      |
| mysql-query_digests_lowercase                       | false                                     |
| mysql-query_digests_replace_null                    | false                                     |
| mysql-query_digests_normalize_digest_text           | false                                     |
| mysql-query_digests_track_hostname                  | false                                     |
| mysql-servers_stats                                 | true                                      |
| mysql-default_reconnect                             | true                                      |
| mysql-ssl_p2s_ca                                    | /etc/ssl/certs/rds-combined-ca-bundle.pem |
| mysql-default_time_zone                             | SYSTEM                                    |
| mysql-connpoll_reset_queue_length                   | 50                                        |
| mysql-min_num_servers_lantency_awareness            | 1000                                      |
| mysql-stats_time_backend_query                      | false                                     |
| mysql-stats_time_query_processor                    | false                                     |
| mysql-query_cache_stores_empty_result               | true                                      |
| mysql-threads                                       | 4                                         |
| mysql-max_connections                               | 2048                                      |
| mysql-default_query_delay                           | 0                                         |
| mysql-default_query_timeout                         | 36000000                                  |
| mysql-have_compress                                 | true                                      |
| mysql-poll_timeout                                  | 2000                                      |
| mysql-interfaces                                    | 0.0.0.0:6033                              |
| mysql-default_schema                                | information_schema                        |
| mysql-stacksize                                     | 1048576                                   |
| mysql-server_version                                | 5.5.30                                    |
| mysql-connect_timeout_server                        | 3000                                      |
| mysql-monitor_history                               | 600000                                    |
| mysql-monitor_connect_interval                      | 2000                                      |
| mysql-monitor_ping_interval                         | 2000                                      |
| mysql-monitor_read_only_interval                    | 2000                                      |
| mysql-monitor_read_only_timeout                     | 500                                       |
| mysql-ping_interval_server_msec                     | 120000                                    |
| mysql-ping_timeout_server                           | 500                                       |
| mysql-commands_stats                                | true                                      |
| mysql-sessions_sort                                 | true                                      |
| mysql-connect_retries_on_failure                    | 10                                        |
| mysql-server_capabilities                           | 571947                                    |
| mysql-session_debug                                 | true                                      |
| admin-debug                                         | false                                     |
| admin-version                                       | 2.0.6-73-gc746bf7f_DEBUG                  |
+-----------------------------------------------------+-------------------------------------------+
clawster commented 5 years ago 
# /var/lib/proxysql.log contents
Main phase3 : GloAdmin initialized in 0.016581 secs.
Standard Query Processor rev. 2.0.6.0805_DEBUG -- Query_Processor.cpp -- Tue Aug 13 13:32:45 2019
Main phase3 : Query Processor initialized in 0.001024 secs.
Main phase3 : MySQL Threads Handler initialized in 0.000189 secs.
In memory Standard Query Cache (SQC) rev. 1.2.0905_DEBUG -- Query_Cache.cpp -- Tue Aug 13 13:32:45 2019
Main phase3 : Query Cache initialized in 0.000454 secs.
2019-09-09 06:54:46 MySQL_Logger.cpp:518:events_open_log_unlocked(): [INFO] Starting new mysql event log file /var/lib/proxysql/queries.log.00000005
Main phase3 : MySQL Threads Handler listeners started in 0.000197 secs.
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226_DEBUG -- MySQL_Monitor.cpp -- Tue Aug 13 13:32:45 2019
Main phase3 : MySQL Monitor initialized in 0.000247 secs.
Main init phase3 completed in 0.03962 secs.
2019-09-09 06:55:10 ProxySQL_GloVars.cpp:9:term_handler(): [WARNING] Received TERM signal: shutdown in progress...
2019-09-09 06:55:10 main.cpp:1675:main(): [INFO] Starting shutdown...
GloMTH joined in 0.010018 secs.
GloMyMon joined in 0.16201 secs.
GloQC joined in 0.181502 secs.
All threads joined in 0.35359 secs.
GloMyMon shutdown in 0.000441 secs.
GloQC shutdown in 0.000101 secs.
GloQPro shutdown in 0.000118 secs.
GloMyAuth shutdown in 8.2e-05 secs.
GloMTH shutdown in 8.2e-05 secs.
GloMyLogger shutdown in 1.1e-05 secs.
GloAdmin shutdown in 0.005596 secs.
GloHGM shutdown in 0.004471 secs.
Main init phase4 shutdown completed in 0.364596 secs.
2019-09-09 06:55:10 main.cpp:1679:main(): [INFO] Shutdown completed!
2019-09-09 06:55:10 main.cpp:1692:main(): [INFO] Exiting...
2019-09-09 06:55:10 main.cpp:1382:ProxySQL_daemonize_phase3(): [INFO] Shutdown angel process

# journalctl -u proxysql
Sep 09 06:54:45 proxysql systemd[1]: Stopping High Performance Advanced Proxy for MySQL...
Sep 09 06:54:45 proxysql systemd[1]: proxysql.service: Succeeded.
Sep 09 06:54:45 proxysql systemd[1]: Stopped High Performance Advanced Proxy for MySQL.
Sep 09 06:54:45 proxysql systemd[1]: Starting High Performance Advanced Proxy for MySQL...
Sep 09 06:54:45 proxysql proxysql[26975]: 2019-09-09 06:54:45 main.cpp:703:ProxySQL_Main_process_global_variables(): [INFO] Using config file /etc/proxysql.cnf
Sep 09 06:54:45 proxysql proxysql[26975]: Main init global variables completed in 0.000657 secs.
Sep 09 06:54:45 proxysql proxysql[26975]: List of cipher avaiable:
Sep 09 06:54:45 proxysql proxysql[26975]: TLS_AES_256_GCM_SHA384:  TLS_AES_256_GCM_SHA384  TLSv1.3 Kx=any      Au=any  Enc=AESGCM(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: TLS_CHACHA20_POLY1305_SHA256:  TLS_CHACHA20_POLY1305_SHA256 TLSv1.3 Kx=any      Au=any  Enc=CHACHA20/POLY1305(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: TLS_AES_128_GCM_SHA256:  TLS_AES_128_GCM_SHA256  TLSv1.3 Kx=any      Au=any  Enc=AESGCM(128) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-ECDSA-AES256-GCM-SHA384:  ECDHE-ECDSA-AES256-GCM-SHA384 TLSv1.2 Kx=ECDH     Au=ECDSA Enc=AESGCM(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-RSA-AES256-GCM-SHA384:  ECDHE-RSA-AES256-GCM-SHA384 TLSv1.2 Kx=ECDH     Au=RSA  Enc=AESGCM(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-RSA-AES256-GCM-SHA384:  DHE-RSA-AES256-GCM-SHA384 TLSv1.2 Kx=DH       Au=RSA  Enc=AESGCM(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-ECDSA-CHACHA20-POLY1305:  ECDHE-ECDSA-CHACHA20-POLY1305 TLSv1.2 Kx=ECDH     Au=ECDSA Enc=CHACHA20/POLY1305(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-RSA-CHACHA20-POLY1305:  ECDHE-RSA-CHACHA20-POLY1305 TLSv1.2 Kx=ECDH     Au=RSA  Enc=CHACHA20/POLY1305(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-RSA-CHACHA20-POLY1305:  DHE-RSA-CHACHA20-POLY1305 TLSv1.2 Kx=DH       Au=RSA  Enc=CHACHA20/POLY1305(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-ECDSA-AES128-GCM-SHA256:  ECDHE-ECDSA-AES128-GCM-SHA256 TLSv1.2 Kx=ECDH     Au=ECDSA Enc=AESGCM(128) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-RSA-AES128-GCM-SHA256:  ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2 Kx=ECDH     Au=RSA  Enc=AESGCM(128) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-RSA-AES128-GCM-SHA256:  DHE-RSA-AES128-GCM-SHA256 TLSv1.2 Kx=DH       Au=RSA  Enc=AESGCM(128) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-ECDSA-AES256-SHA384:  ECDHE-ECDSA-AES256-SHA384 TLSv1.2 Kx=ECDH     Au=ECDSA Enc=AES(256)  Mac=SHA384
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-RSA-AES256-SHA384:  ECDHE-RSA-AES256-SHA384 TLSv1.2 Kx=ECDH     Au=RSA  Enc=AES(256)  Mac=SHA384
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-RSA-AES256-SHA256:  DHE-RSA-AES256-SHA256   TLSv1.2 Kx=DH       Au=RSA  Enc=AES(256)  Mac=SHA256
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-ECDSA-AES128-SHA256:  ECDHE-ECDSA-AES128-SHA256 TLSv1.2 Kx=ECDH     Au=ECDSA Enc=AES(128)  Mac=SHA256
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-RSA-AES128-SHA256:  ECDHE-RSA-AES128-SHA256 TLSv1.2 Kx=ECDH     Au=RSA  Enc=AES(128)  Mac=SHA256
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-RSA-AES128-SHA256:  DHE-RSA-AES128-SHA256   TLSv1.2 Kx=DH       Au=RSA  Enc=AES(128)  Mac=SHA256
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-ECDSA-AES256-SHA:  ECDHE-ECDSA-AES256-SHA  TLSv1 Kx=ECDH     Au=ECDSA Enc=AES(256)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-RSA-AES256-SHA:  ECDHE-RSA-AES256-SHA    TLSv1 Kx=ECDH     Au=RSA  Enc=AES(256)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-RSA-AES256-SHA:  DHE-RSA-AES256-SHA      SSLv3 Kx=DH       Au=RSA  Enc=AES(256)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-ECDSA-AES128-SHA:  ECDHE-ECDSA-AES128-SHA  TLSv1 Kx=ECDH     Au=ECDSA Enc=AES(128)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-RSA-AES128-SHA:  ECDHE-RSA-AES128-SHA    TLSv1 Kx=ECDH     Au=RSA  Enc=AES(128)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-RSA-AES128-SHA:  DHE-RSA-AES128-SHA      SSLv3 Kx=DH       Au=RSA  Enc=AES(128)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: RSA-PSK-AES256-GCM-SHA384:  RSA-PSK-AES256-GCM-SHA384 TLSv1.2 Kx=RSAPSK   Au=RSA  Enc=AESGCM(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-PSK-AES256-GCM-SHA384:  DHE-PSK-AES256-GCM-SHA384 TLSv1.2 Kx=DHEPSK   Au=PSK  Enc=AESGCM(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: RSA-PSK-CHACHA20-POLY1305:  RSA-PSK-CHACHA20-POLY1305 TLSv1.2 Kx=RSAPSK   Au=RSA  Enc=CHACHA20/POLY1305(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-PSK-CHACHA20-POLY1305:  DHE-PSK-CHACHA20-POLY1305 TLSv1.2 Kx=DHEPSK   Au=PSK  Enc=CHACHA20/POLY1305(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-PSK-CHACHA20-POLY1305:  ECDHE-PSK-CHACHA20-POLY1305 TLSv1.2 Kx=ECDHEPSK Au=PSK  Enc=CHACHA20/POLY1305(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: AES256-GCM-SHA384:  AES256-GCM-SHA384       TLSv1.2 Kx=RSA      Au=RSA  Enc=AESGCM(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: PSK-AES256-GCM-SHA384:  PSK-AES256-GCM-SHA384   TLSv1.2 Kx=PSK      Au=PSK  Enc=AESGCM(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: PSK-CHACHA20-POLY1305:  PSK-CHACHA20-POLY1305   TLSv1.2 Kx=PSK      Au=PSK  Enc=CHACHA20/POLY1305(256) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: RSA-PSK-AES128-GCM-SHA256:  RSA-PSK-AES128-GCM-SHA256 TLSv1.2 Kx=RSAPSK   Au=RSA  Enc=AESGCM(128) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-PSK-AES128-GCM-SHA256:  DHE-PSK-AES128-GCM-SHA256 TLSv1.2 Kx=DHEPSK   Au=PSK  Enc=AESGCM(128) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: AES128-GCM-SHA256:  AES128-GCM-SHA256       TLSv1.2 Kx=RSA      Au=RSA  Enc=AESGCM(128) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: PSK-AES128-GCM-SHA256:  PSK-AES128-GCM-SHA256   TLSv1.2 Kx=PSK      Au=PSK  Enc=AESGCM(128) Mac=AEAD
Sep 09 06:54:45 proxysql proxysql[26975]: AES256-SHA256:  AES256-SHA256           TLSv1.2 Kx=RSA      Au=RSA  Enc=AES(256)  Mac=SHA256
Sep 09 06:54:45 proxysql proxysql[26975]: AES128-SHA256:  AES128-SHA256           TLSv1.2 Kx=RSA      Au=RSA  Enc=AES(128)  Mac=SHA256
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-PSK-AES256-CBC-SHA384:  ECDHE-PSK-AES256-CBC-SHA384 TLSv1 Kx=ECDHEPSK Au=PSK  Enc=AES(256)  Mac=SHA384
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-PSK-AES256-CBC-SHA:  ECDHE-PSK-AES256-CBC-SHA TLSv1 Kx=ECDHEPSK Au=PSK  Enc=AES(256)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: SRP-RSA-AES-256-CBC-SHA:  SRP-RSA-AES-256-CBC-SHA SSLv3 Kx=SRP      Au=RSA  Enc=AES(256)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: SRP-AES-256-CBC-SHA:  SRP-AES-256-CBC-SHA     SSLv3 Kx=SRP      Au=SRP  Enc=AES(256)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: RSA-PSK-AES256-CBC-SHA384:  RSA-PSK-AES256-CBC-SHA384 TLSv1 Kx=RSAPSK   Au=RSA  Enc=AES(256)  Mac=SHA384
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-PSK-AES256-CBC-SHA384:  DHE-PSK-AES256-CBC-SHA384 TLSv1 Kx=DHEPSK   Au=PSK  Enc=AES(256)  Mac=SHA384
Sep 09 06:54:45 proxysql proxysql[26975]: RSA-PSK-AES256-CBC-SHA:  RSA-PSK-AES256-CBC-SHA  SSLv3 Kx=RSAPSK   Au=RSA  Enc=AES(256)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-PSK-AES256-CBC-SHA:  DHE-PSK-AES256-CBC-SHA  SSLv3 Kx=DHEPSK   Au=PSK  Enc=AES(256)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: AES256-SHA:  AES256-SHA              SSLv3 Kx=RSA      Au=RSA  Enc=AES(256)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: PSK-AES256-CBC-SHA384:  PSK-AES256-CBC-SHA384   TLSv1 Kx=PSK      Au=PSK  Enc=AES(256)  Mac=SHA384
Sep 09 06:54:45 proxysql proxysql[26975]: PSK-AES256-CBC-SHA:  PSK-AES256-CBC-SHA      SSLv3 Kx=PSK      Au=PSK  Enc=AES(256)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-PSK-AES128-CBC-SHA256:  ECDHE-PSK-AES128-CBC-SHA256 TLSv1 Kx=ECDHEPSK Au=PSK  Enc=AES(128)  Mac=SHA256
Sep 09 06:54:45 proxysql proxysql[26975]: ECDHE-PSK-AES128-CBC-SHA:  ECDHE-PSK-AES128-CBC-SHA TLSv1 Kx=ECDHEPSK Au=PSK  Enc=AES(128)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: SRP-RSA-AES-128-CBC-SHA:  SRP-RSA-AES-128-CBC-SHA SSLv3 Kx=SRP      Au=RSA  Enc=AES(128)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: SRP-AES-128-CBC-SHA:  SRP-AES-128-CBC-SHA     SSLv3 Kx=SRP      Au=SRP  Enc=AES(128)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: RSA-PSK-AES128-CBC-SHA256:  RSA-PSK-AES128-CBC-SHA256 TLSv1 Kx=RSAPSK   Au=RSA  Enc=AES(128)  Mac=SHA256
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-PSK-AES128-CBC-SHA256:  DHE-PSK-AES128-CBC-SHA256 TLSv1 Kx=DHEPSK   Au=PSK  Enc=AES(128)  Mac=SHA256
Sep 09 06:54:45 proxysql proxysql[26975]: RSA-PSK-AES128-CBC-SHA:  RSA-PSK-AES128-CBC-SHA  SSLv3 Kx=RSAPSK   Au=RSA  Enc=AES(128)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: DHE-PSK-AES128-CBC-SHA:  DHE-PSK-AES128-CBC-SHA  SSLv3 Kx=DHEPSK   Au=PSK  Enc=AES(128)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: AES128-SHA:  AES128-SHA              SSLv3 Kx=RSA      Au=RSA  Enc=AES(128)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: PSK-AES128-CBC-SHA256:  PSK-AES128-CBC-SHA256   TLSv1 Kx=PSK      Au=PSK  Enc=AES(128)  Mac=SHA256
Sep 09 06:54:45 proxysql proxysql[26975]: PSK-AES128-CBC-SHA:  PSK-AES128-CBC-SHA      SSLv3 Kx=PSK      Au=PSK  Enc=AES(128)  Mac=SHA1
Sep 09 06:54:45 proxysql proxysql[26975]: 2019-09-09 06:54:45 main.cpp:447:ssl_mkit(): [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.
Sep 09 06:54:45 proxysql proxysql[26975]: Main SSL init variables completed in 0.00417 secs.
Sep 09 06:54:45 proxysql proxysql[26975]: SHA1 generated in 0.031291 secs.
Sep 09 06:54:45 proxysql proxysql[26975]: Main daemonize phase1 completed in 7.8e-05 secs.
Sep 09 06:54:45 proxysql systemd[1]: Started High Performance Advanced Proxy for MySQL.
Sep 09 06:55:10 proxysql systemd[1]: Stopping High Performance Advanced Proxy for MySQL...
Sep 09 06:55:10 proxysql systemd[1]: proxysql.service: Succeeded.
Sep 09 06:55:10 proxysql systemd[1]: Stopped High Performance Advanced Proxy for MySQL.
renecannao commented 5 years ago

Hi. Does this happen with a regular mysql cli client? Or some other client? We need to reproduce it

renecannao commented 5 years ago

I can easily reproduce it. Thank you for the report.

renecannao commented 5 years ago

ProxySQL 2.0.3 , using openssl-1.1.0h , it is not affected. So it is likely that the issues is related to upgrade to openssl-1.1.1b in ProxySQL 2.0.4 . Investigating

renecannao commented 5 years ago

When there is no data queued to be sent:

renecannao commented 5 years ago

Implementation of BIO_read() in OpenSSL has changed, causing this bug

renecannao commented 5 years ago

We opted to downgrade openssl back to 1.1.0h .

@clawster : thank you again for the report!

@pondix : our automated tests do not show this sort of regression because they run benchmark and do not evaluate CPU usage in idle or mostly idle conditions. We need to improve that. Let's leave this issue open until we are also able to test this.

clawster commented 5 years ago

@renecannao : Thanks for the quick response! Glad I could contribute. Keep up the good work :+1:

renecannao commented 5 years ago

Closing this as solve. @clawster : thank you again!

bertho-zero commented 4 years ago

I still have this problem in v2.0.12, with an environment without SSL everything is fine, as soon as there is SSL the ProxySQL servers go up to 200% and it becomes impossible to see the processlists.

The other problem I encounter when enabling SSL is requests that return ECONNRESET after fifteen minutes of inactivity.

renecannao commented 4 years ago

Reopening, as "ECONNRESET after fifteen minutes of inactivity" hints some further debugging