Briefly describe mysql monitoring group replication

Original text: https://dev.mysql.com/doc/refman/8.0/en/group-replication-monitoring.html
Translator: kun
I am currently translating the official MySQL 8.0 documentation and this article is Section 18.3 "Monitoring Group Replication".

1. Monitoring Group Replication

Assuming MySQL has been compiled with the Performance Schema enabled, use the Perfomance Schema tables to monitor Group Replication. Group Replication adds the following tables:

• performance_schema.replication_group_member_stats
• performance_schema.replication_group_members


These existing Performance Schema replication tables also display information about Group Replication:

• performance_schema.replication_connection_status displays information about Group Replication, for example, transactions that have been received from the group and queued in the applier queue (relay log).
• performance_schema.replication_applier_status shows the status of channels and threads related to Group Replication. If there are many different worker threads applying transactions, then this table can also be used to monitor what each worker thread is doing.
  

The replication channels created by the Group Replication plugin are named:

• group_replication_recovery - This channel is used for replication changes related to the distributed recovery phase.
• group_replication_applier - This channel is used for incoming changes from the group. And the channel to which transactions directly from the group are applied.
  

The following sections describe the information available in each table.

2. Group member instance status

Server instances in a group can be in various states. If the servers are communicating normally, all servers will report the same status. However, if there is a network partition, or a group member leaves the group, different information may be reported depending on which server is queried. Note that if a group member has left the group, then it obviously cannot report the latest information about the status of other servers. If a network partition occurs, if the number of servers exceeding the arbitration number are disconnected, the servers will not be able to cooperate with each other. Therefore, they cannot know the status of different server members. Therefore, they will report that some servers are unreachable, rather than guessing about their status.

Server State

Important
Once the instance enters the ERROR state, the super_read_only option will be set to ON. To leave the ERROR state, you must manually configure the instance super_read_only=OFF

It is important to note that Group Replication is not synchronous replication, but it is eventually synchronous. More precisely, transactions are delivered to all group members in the same order, but their execution is asynchronous, meaning that after accepting a transaction to be committed, each member commits at its own pace.

3. replication_group_members table

The performance_schema.replication_group_members table is used to monitor the status of the different server instances that are members of a group. The table replication_group_members is updated whenever the view changes, for example, when the configuration of the group changes dynamically. On this basis, server members exchange some of their metadata to stay synchronized and continue to collaborate. Information is shared between Group Replication members, so information about all group members can be queried from any member. This table can be used to get a high-level view of the state of the replication group, for example by issuing:

SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+| group_replication_applier | 041f26d8-f3f3-11e8-adff-080027337932 | example1 | 3306 | ONLINE | SECONDARY | 8.0.13 || group_replication_applier | f60a3e10-f3f2-11e8-8258-080027337932 | example2 | 3306 | ONLINE | PRIMARY | 8.0.13 || group_replication_applier | fc890014-f3f2-11e8-a9fd-080027337932 | example3 | 3306 | ONLINE | SECONDARY | 8.0.13 |+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+

Based on this result, we can see that the group consists of three members, the host and port number of each member, which the client uses to connect to the member, and the member's server_uuid . The MEMBER_STATE column shows one of the Group Member Instance States, in this case it shows that all three members of the group are ONLINE , and the MEMBER_ROLE column shows that there are two slaves and one master. Therefore, the group must be running in single-primary mode. The MEMBER_VERSION column can be useful when you are upgrading a group and the members in the group are running different MySQL versions.

4. Replication_group_member_stats

Each member in the replication group validates and applies transactions committed by the group. Statistics about validation and application are very useful to understand how the application queue is growing, how many conflicts are triggered, how many transactions are checked, which transactions have been committed by all members, and so on.

The performance_schema.replication_group_member_stats table provides group-level information related to the authentication process, as well as statistics on transactions received and initiated by each member of the replication group. Information is shared between group member instances, so information about all group members can be queried from any member. Note that refreshing of statistics on remote members is controlled by the message period specified in the group_replication_flow_control_period option, so these may differ slightly from the locally collected statistics of the member making the query.

Table replication_group_member_stats

These fields are important for monitoring the performance of members in a group. For example, suppose one of the members of a group is experiencing delays and cannot synchronize with the rest of the group. In this case, you might see a large number of transactions in the queue. Based on this information, you can decide to remove members from the group or to delay transaction processing for other members in the group, thereby reducing the number of queued transactions. This information can also help you decide how to tune the Group Replication plugin's flow control.

The above is a brief description of the details of MySQL monitoring group replication. For more information about MySQL monitoring group replication, please pay attention to other related articles on 123WORDPRESS.COM!