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
          10 kB
          Hamoon Mohammadian Pour
        2. df.log
          0.4 kB
          Hamoon Mohammadian Pour
        3. mariadb.jpg
          32 kB
          Hamoon Mohammadian Pour
        4. response.sql
          136 kB
          Hamoon Mohammadian Pour
        5. top.log
          31 kB
          Hamoon Mohammadian Pour

        Issue Links

          Activity

            People

              jplindst Jan Lindström (Inactive)
              HamoonDBA Hamoon Mohammadian Pour
              Votes:
              3 Vote for this issue
              Watchers:
              7 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.