cluster query execute hang at wsrep in pre-commit stage

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi, we setup 3 nodes cluster to handle max connection of 500 application, when point the traffic to cluster, all the query hang in at wsrep pre-commit stage, most query take more than 3 seconds to finish, we run same query in a single db, it only take 0.063 s in average, so it has something to do this commit process, it only happened when multiple connections happened,

machines:3x ubuntu with 4 core 15G memory in SSD 500G max IO 2300 latest version of cluster, cpu memory usage looks fine

any suggestions?

here are wsrep status
[TABLE]
[TR]
[TD]wsrep_local_state_uuid[/TD]
[TD]7afb8427-cb98-11e5-986d-33dae198bfb5[/TD]
[/TR]
[TR]
[TD]wsrep_protocol_version[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]wsrep_last_committed[/TD]
[TD]1854809[/TD]
[/TR]
[TR]
[TD]wsrep_replicated[/TD]
[TD]15954[/TD]
[/TR]
[TR]
[TD]wsrep_replicated_bytes[/TD]
[TD]6972263[/TD]
[/TR]
[TR]
[TD]wsrep_received[/TD]
[TD]14761[/TD]
[/TR]
[TR]
[TD]wsrep_received_bytes[/TD]
[TD]6405341[/TD]
[/TR]
[TR]
[TD]wsrep_local_commits[/TD]
[TD]15413[/TD]
[/TR]
[TR]
[TD]wsrep_local_cert_failures[/TD]
[TD]541[/TD]
[/TR]
[TR]
[TD]wsrep_local_replays[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]wsrep_local_send_queue[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]wsrep_local_send_queue_avg[/TD]
[TD]0.074181[/TD]
[/TR]
[TR]
[TD]wsrep_local_recv_queue[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]wsrep_local_recv_queue_avg[/TD]
[TD]57.142034[/TD]
[/TR]
[TR]
[TD]wsrep_flow_control_paused[/TD]
[TD]0.000000[/TD]
[/TR]
[TR]
[TD]wsrep_flow_control_sent[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]wsrep_flow_control_recv[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]wsrep_cert_deps_distance[/TD]
[TD]5.171123[/TD]
[/TR]
[TR]
[TD]wsrep_apply_oooe[/TD]
[TD]0.696407[/TD]
[/TR]
[TR]
[TD]wsrep_apply_oool[/TD]
[TD]0.000319[/TD]
[/TR]
[TR]
[TD]wsrep_apply_window[/TD]
[TD]7.473739[/TD]
[/TR]
[TR]
[TD]wsrep_commit_oooe[/TD]
[TD]0.000000[/TD]
[/TR]
[TR]
[TD]wsrep_commit_oool[/TD]
[TD]0.000319[/TD]
[/TR]
[TR]
[TD]wsrep_commit_window[/TD]
[TD]6.108963[/TD]
[/TR]
[TR]
[TD]wsrep_local_state[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]wsrep_local_state_comment[/TD]
[TD]Synced[/TD]
[/TR]
[TR]
[TD]wsrep_cert_index_size[/TD]
[TD]907[/TD]
[/TR]
[TR]
[TD]wsrep_causal_reads[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]wsrep_incoming_addresses[/TD]
[TD]10.0.2.8:3306,10.0.2.5:3306,10.0.2.4:3306[/TD]
[/TR]
[TR]
[TD]wsrep_cluster_conf_id[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]wsrep_cluster_size[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]wsrep_cluster_state_uuid[/TD]
[TD]7afb8427-cb98-11e5-986d-33dae198bfb5[/TD]
[/TR]
[TR]
[TD]wsrep_cluster_status[/TD]
[TD]Primary[/TD]
[/TR]
[TR]
[TD]wsrep_connected[/TD]
[TD]ON[/TD]
[/TR]
[TR]
[TD]wsrep_local_bf_aborts[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]wsrep_local_index[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]wsrep_provider_name[/TD]
[TD]Galera[/TD]
[/TR]
[TR]
[TD]wsrep_provider_vendor[/TD]
[TD]Codership Oy <info@codership.com>[/TD]
[/TR]
[TR]
[TD]wsrep_provider_version[/TD]
[TD]2.12(r318911d)[/TD]
[/TR]
[TR]
[TD]wsrep_ready[/TD]
[TD]ON[/TD]
[/TR]
[TR]
[TD]wsrep_thread_count[/TD]
[TD]9[/TD]
[/TR]
[/TABLE]

2

I would strongly recommend to try the latest version.
Latest Percona XtraDB Cluster 5.6 comes with

| wsrep_provider_version | 3.14(r53b88eb)|

3

Hi, I have the same problem, 5 nodes, and always we got to boostrap the cluster

±-----------------------------±-------------------------------------------------------------------------------------+
| Variable_name | Value |
±-----------------------------±-------------------------------------------------------------------------------------+
| wsrep_local_state_uuid | f7dc1bbb-f8b8-11e4-8172-3f0c0cf99383 |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 87000552 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 14948 |
| wsrep_received_bytes | 8605960 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 2 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.008696 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 192 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 1.353225 |
| wsrep_local_cached_downto | 86986123 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 62.633056 |
| wsrep_apply_oooe | 0.285734 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.726738 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.444086 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 120 |
| wsrep_cert_bucket_count | 420 |
| wsrep_gcache_pool_size | 9180175 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.832779 |
| wsrep_incoming_addresses | 10.1.3.224:64213,10.1.3.225:64213,10.1.3.223:64213,10.1.3.222:64213,10.1.3.221:64213 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0.000815784/0.000815784/0.000815784/0/1 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | db835eb7-fb55-11e5-9184-facc0beb80b0 |
| wsrep_cluster_conf_id | 5 |
| wsrep_cluster_size | 5 |
| wsrep_cluster_state_uuid | f7dc1bbb-f8b8-11e4-8172-3f0c0cf99383 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 4 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.14(r53b88eb) |
| wsrep_ready | ON |
±-----------------------------±-------------------------------------------------------------------------------------+

any ideas?