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

Connection stuck in Unlocking tables state

    XMLWordPrintable

    Details

      Description

      After upgrade to MariaDB from 10.1 to 10.2.8.
      We are using wsrep with 3 nodes cluster.
      One node for Write and two nodes for read data.

      sometimes I see several connections that stuck in Unlocking tables in Write node.
      Even I kill them, they will be alive.
      I don't know when it happen, but I know it maybe happen on different databases and not related to specific database or table.

      my.cnf setting(this is same in all nodes)

      #
      # This group is read both both by the client and the server
      # use it for options that affect everything
      #
      [client-server]
       
      #
      # include all files from the config directory
      #
      !includedir /etc/my.cnf.d
       
      [mysqld]
      skip-name-resolve
      datadir         = /home/mysql/
      tmpdir          = /home/mysqltmp/
      port            = 3306
      socket          = /home/mysql/mysql.sock
       
      # General #
      log_error                      = /home/mysql/node1.err
      default-storage-engine         = innodb
      query_cache_limit              = 0
      query_cache_size               = 0
      query_cache_type               = 0
      bind-address                   = 0.0.0.0
      max-connections                = 500
      max-allowed-packet             = 15M
      table_open_cache               = 60000
      tmp_table_size                 = 512M
       
      #FTS
      innodb_ft_result_cache_limit=256M
       
       
      # Binlog #
      binlog_format                  = ROW
      sync_binlog                    = 0
       
      # Galera #
      wsrep_on=on
      wsrep_provider=/usr/lib64/galera/libgalera_smm.so
      wsrep_cluster_address="gcomm://xx.xx.xx.xx,xx.xx.xx.xx,xx.xx.xx.xx"
      wsrep_cluster_name='galera_cluster'
      wsrep_node_address='xx.xx.xx.xx'
      wsrep_node_name='node1'
      wsrep_provider_options="gcache.size=25G;gcs.fc_limit=4000;gcache.page_size=512M;gcs.fc_master_slave=YES;gcs.fc_factor=1.0;"
      wsrep_sst_method=xtrabackup-v2
      wsrep_sst_auth=xtrabackup:1111111
       
       
      #Replication
      gtid_domain_id=1
      server_id=1
      log_slave_updates=1
      log_bin=binlog
      expire_logs_days=2
       
      # Innodb #
      innodb_file_format             = BARRACUDA
      innodb-flush-method            = O_DIRECT
      innodb_autoinc_lock_mode       = 2
      innodb_log_file_size           = 1G
      innodb_log_buffer_size         = 512M
      innodb_file_per_table          = 1
      innodb_flush_log_at_trx_commit = 0
      innodb_buffer_pool_size        = 100G
      innodb_buffer_pool_instances   = 20
      innodb_write_io_threads        = 16
      innodb_read_io_threads         = 16
      innodb_change_buffering        = all
      transaction-isolation          = READ-COMMITTED
       
      [sst]
      compressor="pigz"
      decompressor="pigz -d"
      

        Attachments

        1. 2.png
          2.png
          10 kB
        2. df.log
          0.4 kB
        3. mariadb.jpg
          mariadb.jpg
          32 kB
        4. response.sql
          136 kB
        5. top.log
          31 kB

          Issue Links

            Activity

              People

              Assignee:
              jplindst Jan Lindström
              Reporter:
              HamoonDBA Hamoon Mohammadian Pour
              Votes:
              3 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration