Not the answer you need?
Register and ask your own question!

cluster query execute hang at wsrep in pre-commit stage

alex@fuelx.com[email protected] EntrantInactive User Role Beginner
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


wsrep_local_state_uuid
7afb8427-cb98-11e5-986d-33dae198bfb5


wsrep_protocol_version
4


wsrep_last_committed
1854809


wsrep_replicated
15954


wsrep_replicated_bytes
6972263


wsrep_received
14761


wsrep_received_bytes
6405341


wsrep_local_commits
15413


wsrep_local_cert_failures
541


wsrep_local_replays
1


wsrep_local_send_queue
0


wsrep_local_send_queue_avg
0.074181


wsrep_local_recv_queue
0


wsrep_local_recv_queue_avg
57.142034


wsrep_flow_control_paused
0.000000


wsrep_flow_control_sent
0


wsrep_flow_control_recv
0


wsrep_cert_deps_distance
5.171123


wsrep_apply_oooe
0.696407


wsrep_apply_oool
0.000319


wsrep_apply_window
7.473739


wsrep_commit_oooe
0.000000


wsrep_commit_oool
0.000319


wsrep_commit_window
6.108963


wsrep_local_state
4


wsrep_local_state_comment
Synced


wsrep_cert_index_size
907


wsrep_causal_reads
0


wsrep_incoming_addresses
10.0.2.8:3306,10.0.2.5:3306,10.0.2.4:3306


wsrep_cluster_conf_id
3


wsrep_cluster_size
3


wsrep_cluster_state_uuid
7afb8427-cb98-11e5-986d-33dae198bfb5


wsrep_cluster_status
Primary


wsrep_connected
ON


wsrep_local_bf_aborts
0


wsrep_local_index
2


wsrep_provider_name
Galera


wsrep_provider_vendor
Codership Oy <[email protected]>


wsrep_provider_version
2.12(r318911d)


wsrep_ready
ON


wsrep_thread_count
9

Comments

  • vadimtkvadimtk Contributor Percona Staff Role
    I would strongly recommend to try the latest version.
    Latest Percona XtraDB Cluster 5.6 comes with

    | wsrep_provider_version | 3.14(r53b88eb)|
  • Fernando MatteraFernando Mattera Entrant Current User Role Beginner
    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 <[email protected]> |
    | wsrep_provider_version | 3.14(r53b88eb) |
    | wsrep_ready | ON |
    +
    +
    +

    any ideas?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.