weird sync issue locks up all 3 nodes

  • Filter
  • Time
  • Show
Clear All
new posts

  • weird sync issue locks up all 3 nodes

    I'm getting errors on the "mysql" database syncing error and it affects all three nodes which causes them to lock up.

    Here's node1 error log:

    Here's node2 error log:

    And Here's node3 error log:

    Here's is the mysql configuration
    port   = 3306
    socket = /var/run/mysqld/mysqld.sock
    port                    = 3306
    datadir                 = /var/lib/mysql
    tmpdir                  = /tmp
    socket                  = /var/run/mysqld/mysqld.sock
    max_connections         = 90
    transaction-isolation   = 'READ-COMMITTED'
    # caching and optimisations
    join_buffer_size   = 4M
    table_cache        = 4M
    table_open_cache   = 4M
    thread_cache_size  = 128 # see http://cl.ly/142s250i1S0o
    thread_stack       = 1M
    tmp_table_size     = 384M
    max_allowed_packet = 500M
    read_buffer_size   = 256K
    slow-query-log-file = /var/log/mysql/slow-queries.log # file must first be created and owned by the MySQL user - see http://cl.ly/142T211c0Z35
    long_query_time     = 3
    log_slow_queries    = /var/log/mysql/log-slow-queries.log
    # InnoDB #
    innodb_flush_method                 = ALL_O_DIRECT # see http://cl.ly/040b1B1H1I2C
    innodb_buffer_pool_size             = 1024M # for 8GB RAM - it should be ~70-80% of the available RAM.
    innodb_log_buffer_size              = 24M
    innodb_log_file_size                = 100M # DO see http://cl.ly/3A3l2y170J29 and http://cl.ly/133W402C050v for more details on how to change this setting safely
    innodb_support_xa                   = 0 # OK with Galera synchronous replication
    innodb_import_table_from_xtrabackup = 1 # allows restoring a single table - see http://cl.ly/2F230t0D340A
    innodb_flush_log_at_trx_commit      = 0 # speeds things up, can lose 1 second worth of transactions if MySQL crashes. This is fine for us at the moment.
    innodb_doublewrite                  = 1 # ensures an incremental state transfer will be tried if possible - see http://cl.ly/1E0w1v0z1m0n
    # Galera synchronous replication #
    binlog_format                  = row
    binlog_cache_size              = 128K
    innodb_locks_unsafe_for_binlog = 1
    server-id                      = 1 # change on each node! - not strictly required for Galera, but it's safer to have a unique id on each node in case normal replication is also used
    innodb_autoinc_lock_mode       = 2
    query_cache_type               = 0
    query_cache_size               = 0
    default_storage_engine         = InnoDB
    wsrep_cluster_name             = "xxxxxxxx"
    wsrep_node_name                = "node1" # change this on each node!
    wsrep_provider                 = /usr/lib/libgalera_smm.so
    wsrep_cluster_address          = "gcomm://node2,node3" #"gcomm://db1:4567"  set to "gcom://"" to reinitialise (reset) a node; otherwise ensure it is pointing to a valid node
    wsrep_slave_threads            = 8 # recommended: 4 slave thread per core - see http://cl.ly/0z0l0K3v3Y0j
    wsrep_retry_autocommit         = 2 # how many times to retry deadlocked autocommits
    wsrep_convert_LOCK_to_trx      = 0 # convert locking sessions into transactions; see http://cl.ly/2c0N3B0f1v41
    wsrep_certify_nonPK            = 1 # Generate fake primary keys for non-PK tables (required for multi-master and parallel applying operation)
    # rsync or xtrabackup; remember to create the user 'mysql'@'localhost' on each node, with all privileges for the xtrabackup-based SST to work
    # see http://cl.ly/153r0A2k0G1N
    wsrep_sst_method               = xtrabackup 
    #Authentication for SST method
    max_allowed_packet = 128M
    # Remove the next comment character if you are not familiar with SQL
    key_buffer_size  = 20M
    sort_buffer_size = 20M
    read_buffer      = 2M
    write_buffer     = 2M
    target_dir = /var/lib/mysql
    It has occurred twice lately and I'm not so sure how to fix this. I've only been restarting the nodes but I have no idea how to debug this.
    Thanks for any help.

  • #2
    These error messages are coming from mysql_upgrade script. Looks like you probably don't have the PXC packages configured. Is this Debian/Ubuntu maybe?
    What is the output of "dpkg -l|grep -i percona" ?


    • #3
      I'm running Ubuntu 12.10

      Here is the output:
      dpkg -l|grep -i percona
      ii  libmysqlclient18                  1:5.5.33-rel31.1-566.quantal           amd64        Percona Server database client library
      ii  percona-xtrabackup                2.1.5-680-1.quantal                    amd64        Open source backup tool for InnoDB and XtraDB
      ii  percona-xtradb-cluster-client-5.5 5.5.33-23.7.6-496.quantal              amd64        Percona Server database client binaries
      ii  percona-xtradb-cluster-common-5.5 5.5.33-23.7.6-496.quantal              all          Percona Server database common files (e.g. /etc/mysql/my.cnf)
      ii  percona-xtradb-cluster-galera-2.x 157.quantal                            amd64        Galera components of Percona XtraDB Cluster
      ii  percona-xtradb-cluster-server-5.5 5.5.33-23.7.6-496.quantal              amd64        Percona Server database server binaries