Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.11
-
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
|
Attachments
Activity
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
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.
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.