[MDEV-14781] regression in 10.2 but where ? Created: 2017-12-27  Updated: 2018-11-07  Resolved: 2018-11-07

Status: Closed
Project: MariaDB Server
Component/s: SSL
Affects Version/s: 10.2.11
Fix Version/s: 10.2.19

Type: Bug Priority: Major
Reporter: VAROQUI Stephane Assignee: Vladislav Vaintroub
Resolution: Fixed Votes: 0
Labels: ODBC, SSL, Thread, ThreadPool
Environment:

MariaDB-10.2.11+maria~jessie-log


 Description   

When connecting with SSL from ODBC via WLAN , we can connect once and than closing ODBC , but after doing so reconnection get impossible.

We observe some pending connection in login state 

 847 | unauthenticated user | 192.168.100.50:58464 | NULL  | Connect     | 4388 | login                                                                 | NULL             |    0.000 |
 
|  878 | unauthenticated user | 192.168.100.50:58934 | NULL  | Connect     | 4334 | login                                                                 | NULL             |    0.000 |
 
|  914 | unauthenticated user | 192.168.100.50:59452 | NULL  | Connect     | 4277 | login                                                                 | NULL             |    0.000 |
 
|  920 | unauthenticated user | 192.168.100.50:59644 | NULL  | Connect     | 4253 | login                                                                 | NULL             |    0.000 |
 
|  925 | unauthenticated user | 192.168.100.50:60098 | NULL  | Connect     | 4204 | login                                                                 | NULL             |    0.000 |
 
|  926 | unauthenticated user | 192.168.100.50:60162 | NULL  | Connect     | 4196 | login                                                                 | NULL             |    0.000 |
 
|  945 | unauthenticated user | 192.168.100.50:33364 | NULL  | Connect     | 4035 | login                                                                 | NULL             |    0.000 |
 
|  967 | unauthenticated user | 192.168.100.50:35166 | NULL  | Connect     | 3835 | login                                                                 | NULL             |    0.000 |
 
|  972 | unauthenticated user | 192.168.100.50:35490 | NULL  | Connect     | 3799 | login

And database SQL become very slow for all operations, i guess that's an effect of thread pool.

Note VM has only one core, so thread_pool_size is 1

Killing those unauthenticated treads make the database performance back to normal .

So far downgrading to last 10.1 looks a valid workaround

Test was done using
Windows ODBC MySQL 3.51.30 32 bits
Windows ODBC MariaDB 3.0.2 32 bits

Connections to user that don't have USING_SSL never trigger this issue

custom variables 

[mariadb]
 
 
 
max_connections = 500
 
max_user_connections = 450
 
thread_cache_size = 32
 
back_log = 128 
skip_name_resolve = 1
 
max_allowed_packet = 512M
 
 
 
# Disabling symbolic-links is recommended to prevent assorted security risks
 
# symbolic-links = 0
 
sysdate_is_now = 1
 
connect_timeout = 10
 
bind-address = 192.168.100.71
 
 
 
local_infile = 0
 
max_connect_errors = 1000000
 
# pam_use_cleartext_plugin = ON 
# secure_auth = 1
 
skip_show_database = 1
 
 
 
thread_handling = pool-of-threads
 
extra_port = 3307
 
extra_max_connections = 10
 
 
 
ssl
 
ssl-ca=/etc/mysql/ssl/ca-cert.pem
 
ssl-cert=/etc/mysql/ssl/server-cert.pem
 
ssl-key=/etc/mysql/ssl/server-key.pem 
 
 
plugin_load_add ="semisync_master.so"
 
plugin_load_add ="semisync_slave.so"
 
 
 
loose_rpl_semi_sync_master = ON
 
loose_rpl_semi_sync_slave = ON
 
loose_rpl_semi_sync_master_enabled = ON
 
loose_rpl_semi_sync_slave_enabled = ON
 
 
 
sql_mode=''
 
 
 
open_files_limit = 65000
 
tmp_table_size = 256M
 
max_heap_table_size = 256M
 
key_buffer_size = 1000M
 
table_open_cache = 1024
 
table_definition_cache = 1024
 
max_allowed_packet = 512M
 
sort_buffer_size = 1M
 
read_buffer_size = 256K
 
join_buffer_size = 128M
 
 
 
innodb_buffer_pool_populate=0  
innodb_force_primary_key = 1
 
innodb_default_row_format=dynamic
 
innodb_defragment = 1
 
 
 
 
 
transaction-isolation=READ-COMMITTED
 
default_storage_engine = InnoDB
 
innodb_adaptive_hash_index = 0
 
innodb_buffer_pool_size = 256M
 
innodb_buffer_pool_instances = 1
 
innodb_flush_log_at_trx_commit = 1
 
innodb_file_per_table=1
 
innodb_read_io_threads = 1
 
innodb_write_io_threads = 1
 
innodb_io_capacity = 150
 
innodb_io_capacity_max = 300
 
innodb_max_dirty_pages_pct = 10
 
innodb_flush_neighbors = 1
 
innodb_doublewrite = 1
 
innodb_log_file_size = 1024M
 
innodb_log_buffer_size = 256M
 
innodb_log_files_in_group = 4
 
innodb_thread_concurrency = 0
 
innodb_file_format = barracuda
 
innodb_flush_method = O_DIRECT
 
 
 
#innodb_undo_tablespaces = 4
 
innodb_autoinc_lock_mode = 2
 
innodb_stats_on_metadata = 0
 
innodb_buffer_pool_dump_at_shutdown = 1
 
innodb_buffer_pool_load_at_startup = 1
 
innodb_open_files = 16384
 
innodb_purge_threads = 8
 
innodb_change_buffering=none
 
innodb_concurrency_tickets =5000



 Comments   
Comment by Vladislav Vaintroub [ 2018-01-04 ]

stephane@skysql.com, can you provide all threads stacks if this situation occurs?
"thread apply all bt" . we should count idle thread as idle, but perhaps it is not happening on some reason.

Comment by VAROQUI Stephane [ 2018-01-04 ]

Hi Vlad,

Did not mention it but this was using the docker MariaDB images ! There is no debug build of MariaDB docker, as well i have no access to windows ODBC myself but was exposing those services over WAN! Can you expose docker images with debug mode.!

/stéphane

Comment by Vladislav Vaintroub [ 2018-01-04 ]

that's not a question to me , Stephane. I'm fully oblivious to the docker, but our normal build always generates debug info in the images. I'll try to artificially add some sleeps into client library to emulate slow connections. In the meantime, you can try to decrease thread-pool-stall-limit, to something small like 50ms, or even less. Alternatively increase thread-pool-size slightly.

Generated at Thu Feb 08 08:16:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.