Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14781

regression in 10.2 but where ?

Details

    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

          Ascending order - Click to sort in descending order
          wlad Vladislav Vaintroub added a comment -

          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.

          wlad Vladislav Vaintroub added a comment - 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.
          stephane@skysql.com VAROQUI Stephane added a comment -

          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 VAROQUI Stephane added a comment - 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
          wlad Vladislav Vaintroub added a comment -

          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.

          wlad Vladislav Vaintroub added a comment - 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.

          People

            wlad Vladislav Vaintroub
            stephane@skysql.com VAROQUI Stephane
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.