Buy Percona ServicesBuy Now!

Percona XtraDB Cluster - Lock wait timeout exceeded

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Percona XtraDB Cluster - Lock wait timeout exceeded

    Hi, i'am doing some tests on my VMs - i installed Percona XtraDB Cluster (3 nodes). Configured it via official manual, cretead database "Percona" and table "mytable".

    Everything is synchronized:

    Code:
    wsrep_local_state                | 4                                                        |
    wsrep_local_state_comment        | Synced
    There is a wsrep config:

    Code:
    [mysqld]
    # Path to Galera library
    wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
    
    # Cluster connection URL contains IPs of nodes
    #If no IP is found, this implies that a new cluster needs to be created,
    #in order to do that you need to bootstrap this node
    wsrep_cluster_address=gcomm://192.168.77.10,192.168.77.20,192.168.77.30
    
    # In order for Galera to work correctly binlog format should be ROW
    binlog_format=ROW
    
    # MyISAM storage engine has only experimental support
    default_storage_engine=InnoDB
    
    # Slave thread to use
    wsrep_slave_threads= 8
    
    wsrep_log_conflicts
    
    # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
    innodb_autoinc_lock_mode=2
    
    # Node IP address
    wsrep_node_address=192.168.77.10
    # Cluster name
    wsrep_cluster_name=pxc-cluster
    
    #If wsrep_node_name is not specified,  then system hostname will be used
    wsrep_node_name=pxc-cluster-node-1
    
    #pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
    pxc_strict_mode=ENFORCING
    
    # SST method
    wsrep_sst_method=xtrabackup-v2
    
    #Authentication for SST method
    wsrep_sst_auth=sstuser:passw0rd
    
    wsrep_sync_wait=1
    innodb_lock_wait_timeout=1000

    Now, what's the problem? I've created a procedure which inserts a 1Milion records into "my table". I'am using DBeaver Client 6.2.0.

    When i call my procedure with AUTO COMMIT OFF it runs about 35 seconds. Then i explicitly run commit. Commit is running about 5 seconds and then on all nodes in the same time i run a command "select count(*) from mytable;" and on 2 nodes it ends with error. On node 1 it shows right number of rows.

    https://i.imgur.com/bAVa8o1.png

    When i run the same commands after 30 seconds, it shows on all nodes same values. Why transactions fail on node 2 and node 3 when parameter innodb_lock_wait_timeout is set really high?

    On Galera Cluster this is working well, without problems.

    Sorry for my bad english, i hope you understand me. Thanks

  • #2
    Hi when you say it ends with error, do you have the error log please? And to save me asking later, if we could have the my.cnf for each node?
    And to confirm, this is a brand new install on these VMs?
    Thanks.

    Comment


    • #3
      Open screen, there is a error message https://i.imgur.com/bAVa8o1.png


      This is mysqld config, it's the same on all nodes. Yea all nodes are fresh installed (Centos 7.6)

      Code:
      [client]
      socket=/var/lib/mysql/mysql.sock
      
      [mysqld]
      server-id=1
      datadir=/var/lib/mysql
      socket=/var/lib/mysql/mysql.sock
      log-error=/var/log/mysqld.log
      pid-file=/var/run/mysqld/mysqld.pid
      log-bin
      log_slave_updates
      expire_logs_days=7
      
      # Disabling symbolic-links is recommended to prevent assorted security risks
      symbolic-links=0
      Attached Files

      Comment


      • #4
        OK, thanks. Unfortunately those errors don't really tell much about what's going on under the hood.

        When the issue happens, what does this command show?

        show status like 'wsrep%';

        You might find that pt-stalk from Percona toolkit (free, open source software) will help you track down what's happening at a system level https://www.percona.com/doc/percona-.../pt-stalk.html and there are a few other blog posts and forum posts that suggest what might be causing the issue if it's in the environment.

        In the meanwhile I will also check if the tech team have anything to add given the config etc you have sent through in case there is something there that is easily identifiable as causing the problem, especially if you are not experiencing the same issue with galera cluster.

        Comment

        Working...
        X