正文
PXC集群信息查询
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
目录
-
PXC集群信息查询
- show status like "%wsrep%";
- show status like "%queue%";
-
pxc流量控制
- 相关信息：
- PXC节点状态
- PXC集群状态
- 节点与集群的相关信息
-
PXC集群事务相关信息
- 事务简介：事务是访问数据库的一个操作序列，数据库应用系统通过事务集来完成对数据库的存取。
PXC集群信息查询
show status like "%wsrep%";
+------------------------------+--------------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------------+
| wsrep_local_state_uuid | 7365e025-6d28-11e8-b6d1-93c7cedfed5a |
| wsrep_protocol_version | 8 |
| wsrep_last_committed | 25866212 |
| wsrep_replicated | 27447 #向集群中发送数据的次数 |
| wsrep_replicated_bytes | 12196872 |
| wsrep_repl_keys | 109813 |
| wsrep_repl_keys_bytes | 1537248 |
| wsrep_repl_data_bytes | 8847207 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 11990314 #从集群中接收数据的次数 |
| wsrep_received_bytes | 48153690617 |
| wsrep_local_commits | 27439 #提交事务的次数 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 4971 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 1.048084 |
| wsrep_local_cached_downto | 23818171 |
| wsrep_flow_control_paused_ns | 656848338309 |
| wsrep_flow_control_paused | 0.000119 |
| wsrep_flow_control_sent | 24 |
| wsrep_flow_control_recv | 35 |
| wsrep_cert_deps_distance | 68.511372 |
| wsrep_apply_oooe | 0.095710 |
| wsrep_apply_oool | 0.000063 |
| wsrep_apply_window | 2.189377 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.952548 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 336 |
| wsrep_cert_bucket_count | 256290 |
| wsrep_gcache_pool_size | 8589935136 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.212581 |
| wsrep_incoming_addresses | db503:4503,db501:4501,db502:4502 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | ddfbf0be-6d3c-11e8-87c9-b680c62f3f0c |
| wsrep_cluster_conf_id | 10 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 7365e025-6d28-11e8-b6d1-93c7cedfed5a |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| 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)
show status like "%queue%";
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| wsrep_local_send_queue | 0 | #发送数据
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 | #平均发送长度，如果send_queue同时也很大，这说明发生了阻塞，需检查网络或线程数
| wsrep_local_recv_queue | 0 | #接收数据
| wsrep_local_recv_queue_max | 4971 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 1.048111 |#平均接收数和接收长度数都大，则说明接收线程不足，需增加线程
+----------------------------+----------+
pxc流量控制
相关信息：
+------------------------------+--------------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------------+
| wsrep_local_state_uuid | 7365e025-6d28-11e8-b6d1-93c7cedfed5a |
| wsrep_protocol_version | 8 |
| wsrep_last_committed | 25866212 |
| wsrep_replicated | 27447 #向集群中发送数据的次数 |
| wsrep_replicated_bytes | 12196872 |
| wsrep_repl_keys | 109813 |
| wsrep_repl_keys_bytes | 1537248 |
| wsrep_repl_data_bytes | 8847207 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 11990314 #从集群中接收数据的次数 |
| wsrep_received_bytes | 48153690617 |
| wsrep_local_commits | 27439 #提交事务的次数 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 4971 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 1.048084 |
| wsrep_local_cached_downto | 23818171 |
| wsrep_flow_control_paused_ns | 656848338309 |
| wsrep_flow_control_paused | 0.000119 |
| wsrep_flow_control_sent | 24 |
| wsrep_flow_control_recv | 35 |
| wsrep_cert_deps_distance | 68.511372 |
| wsrep_apply_oooe | 0.095710 |
| wsrep_apply_oool | 0.000063 |
| wsrep_apply_window | 2.189377 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.952548 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 336 |
| wsrep_cert_bucket_count | 256290 |
| wsrep_gcache_pool_size | 8589935136 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.212581 |
| wsrep_incoming_addresses | db503:4503,db501:4501,db502:4502 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | ddfbf0be-6d3c-11e8-87c9-b680c62f3f0c |
| wsrep_cluster_conf_id | 10 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 7365e025-6d28-11e8-b6d1-93c7cedfed5a |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| 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)
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| wsrep_local_send_queue | 0 | #发送数据
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 | #平均发送长度，如果send_queue同时也很大，这说明发生了阻塞，需检查网络或线程数
| wsrep_local_recv_queue | 0 | #接收数据
| wsrep_local_recv_queue_max | 4971 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 1.048111 |#平均接收数和接收长度数都大，则说明接收线程不足，需增加线程
+----------------------------+----------+
mysql> show status like "%flow%"; 查看流控相关信息
+------------------------------+--------------+
| Variable_name | Value |
+------------------------------+--------------+
| Ssl_session_cache_overflows | 0 |
| Table_open_cache_overflows | 0 |
| wsrep_flow_control_paused_ns | 656848338309 |
| wsrep_flow_control_paused | 0.000119 |
| wsrep_flow_control_sent | 24 |
| wsrep_flow_control_recv | 35 |
+------------------------------+--------------+
#如果进程了流控，可以通过/etc/my.cnf 中的wsrep_slave_threads="xxx" 进行线程数调节，“xxx”一般为cpu线程数的1-1.5倍。
#查看cpu线程数 grep 'processor' /proc/cpuinfo | sort -u | wc -l
PXC节点状态
open ：节点mysql启动成功
primary： 加入集群成功
joiner：与其他节点同步数据
joined：数据同步完成
synced：可以对外提供服务了
doner：有其他节点与本节点进行全量同步时，本节点为doner状态
PXC集群状态
primary：集群正常状态
non_primary：集群出现脑裂时的状态
disconnected：节点宕机状态
节点与集群的相关信息
PXC集群事务相关信息
事务简介：事务是访问数据库的一个操作序列，数据库应用系统通过事务集来完成对数据库的存取。
