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流量控制

相关信息：

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集群事务相关信息

事务简介：事务是访问数据库的一个操作序列，数据库应用系统通过事务集来完成对数据库的存取。