Percona XtraDB Cluster 5.6 commit slow

I’m running Percona-XtraDB-Cluster-server-56 with Version : 5.6.39 Release : 26.25.1.el6. with 2 node PXC + 1 Garbd monitor.

Sometime commit command is slow, it affect to all commit command at that time.

When commit command stuck, i found state off all that commit command is wsrep in pre-commit stage. After 10-20 second, all commit done together.

commit-stuck

Following is my.cnf

[client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 # CLIENT # port = 3306 socket = /u01/mysql-data/mysql/mysql.sock [mysqld] character-set-client-handshake = TRUE init-connect='SET NAMES utf8mb4' character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci  lower_case_table_names=1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0  # Path to Galera library wsrep_provider=/usr/lib64/libgalera_smm.so  # Cluster connection URL contains IPs of node#1, node#2 wsrep_cluster_address=gcomm://192.168.5.50,192.168.5.123,192.168.5.124 # 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 # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2 # Node #6 address wsrep_node_address=192.168.5.124 # Cluster name wsrep_cluster_name=my_centos_cluster # SST method wsrep_sst_method=xtrabackup-v2 #Authentication for SST method wsrep_sst_auth="sstuser:secret" # GENERAL # user = mysql default-storage-engine = InnoDB socket = /u01/mysql-data/mysql/mysql.sock pid-file = /u01/mysql-data/mysql/mysql.pid # MyISAM # key-buffer-size = 32M myisam-recover-options = FORCE,BACKUP # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 sql-mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE # DATA STORAGE # datadir = /u01/mysql-data/mysql/ # BINARY LOGGING # log-bin = /u01/mysql-data/mysql/mysql-bin expire-logs-days = 14 sync-binlog = 1 # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 1000 thread-cache-size = 200 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 4096 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 512M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 42G # LOGGING # log-error = /u01/mysql-data/log/mysql-error.log log-queries-not-using-indexes = 1 slow-query-log = 0 slow-query-log-file = /u01/mysql-data/log/mysql-slow.log tmpdir = /u01/mysql-data/tmp-dir 

A “show status like ws%” shows the table below.

+------------------------------+----------------------------------------------------+ | Variable_name                | Value                                              | +------------------------------+----------------------------------------------------+ | wsrep_local_state_uuid       | b54b3415-32e2-11e6-a332-2301ad2b6985               | | wsrep_protocol_version       | 8                                                  | | wsrep_last_committed         | 1336624250                                         | | wsrep_replicated             | 1195063                                            | | wsrep_replicated_bytes       | 1010104072                                         | | wsrep_repl_keys              | 3845443                                            | | wsrep_repl_keys_bytes        | 59445080                                           | | wsrep_repl_data_bytes        | 869723981                                          | | wsrep_repl_other_bytes       | 0                                                  | | wsrep_received               | 116337                                             | | wsrep_received_bytes         | 87279556                                           | | wsrep_local_commits          | 1194550                                            | | wsrep_local_cert_failures    | 513                                                | | wsrep_local_replays          | 0                                                  | | wsrep_local_send_queue       | 14                                                 | | wsrep_local_send_queue_max   | 228                                                | | wsrep_local_send_queue_min   | 0                                                  | | wsrep_local_send_queue_avg   | 2.192429                                           | | wsrep_local_recv_queue       | 0                                                  | | wsrep_local_recv_queue_max   | 46                                                 | | wsrep_local_recv_queue_min   | 0                                                  | | wsrep_local_recv_queue_avg   | 0.576670                                           | | wsrep_local_cached_downto    | 1336472838                                         | | wsrep_flow_control_paused_ns | 1582906149386                                      | | wsrep_flow_control_paused    | 0.039390                                           | | wsrep_flow_control_sent      | 179                                                | | wsrep_flow_control_recv      | 2454                                               | | wsrep_cert_deps_distance     | 6406.050311                                        | | wsrep_apply_oooe             | 0.298637                                           | | wsrep_apply_oool             | 0.000297                                           | | wsrep_apply_window           | 3.859035                                           | | wsrep_commit_oooe            | 0.000000                                           | | wsrep_commit_oool            | 0.000295                                           | | wsrep_commit_window          | 3.563781                                           | | wsrep_local_state            | 4                                                  | | wsrep_local_state_comment    | Synced                                             | | wsrep_cert_index_size        | 9611                                               | | wsrep_cert_bucket_count      | 15184                                              | | wsrep_gcache_pool_size       | 134219032                                          | | wsrep_causal_reads           | 0                                                  | | wsrep_cert_interval          | 5.691751                                           | | wsrep_incoming_addresses     | ,192.168.5.123:3306,192.168.5.124:3306             | | wsrep_desync_count           | 0                                                  | | wsrep_evs_delayed            |                                                    | | wsrep_evs_evict_list         |                                                    | | wsrep_evs_repl_latency       | 0.000270953/0.000414583/0.0033408/9.85005e-05/3825 | | wsrep_evs_state              | OPERATIONAL                                        | | wsrep_gcomm_uuid             | a096306e-edb1-11e8-a5cb-d6133bf912e6               | | wsrep_cluster_conf_id        | 7                                                  | | wsrep_cluster_size           | 3                                                  | | wsrep_cluster_state_uuid     | b54b3415-32e2-11e6-a332-2301ad2b6985               | | wsrep_cluster_status         | Primary                                            | | wsrep_connected              | ON                                                 | | wsrep_local_bf_aborts        | 38                                                 | | wsrep_local_index            | 1                                                  | | wsrep_provider_name          | Galera                                             | | wsrep_provider_vendor        | Codership Oy <info@codership.com>                  | | wsrep_provider_version       | 3.25(rac090bc)                                     | | wsrep_ready                  | ON                                                 | +------------------------------+----------------------------------------------------+ 59 rows in set (0.00 sec) 

What happen with my cluster?