Shulou(Shulou.com)05/31 Report--

MySQL monitoring group replication is what, in view of this question, this article introduces in detail the corresponding analysis and solutions, hoping to help more partners who want to solve this problem to find a more simple and easy way.

1. Monitor group replication

Assuming that MySQL has been compiled with performance mode enabled, the Perfomance Schema table is used to monitor group replication. Group replication adds the following table:

Performance_schema.replication_group_member_stats

Performance_schema.replication_group_members

These existing Perfomance Schema replication tables also display information about group replication:

Performance_schema.replication_connection_status displays information about group replication, such as transactions (relay logs) that have been received from the group and queued in the application queue.

Performance_schema.replication_applier_status shows the status of channels and threads associated with group replication, and if there are many different worker threads applying transactions, this table can also be used to monitor what each worker thread is doing.

The replication channel created by the Group Replication plug-in is 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 apply the channel of the transaction directly from the group.

The following sections describe the information available in each table.

1.1 Group member instance status

Server instances in a group can be in multiple states. If the server communicates normally, all server report the same status. However, if there is a network separation, or if 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, it is clear that it cannot report the latest information about other server status. If network separation occurs, if the number of server beyond the quorum is disconnected, then the server will not be able to cooperate with each other. As a result, they cannot know the status of different server members. As a result, they report that some server is unreachable, rather than guessing their status.

Table 1.1 Server State

Field description group synchronization ONLINE this member can be a member of a group with all functions, which means that clients can connect and start executing transactions. YesRECOVERING this member is becoming a valid member of the group and is in the process of recovering, receiving status information from the data source node (the data source node). The NoOFFLINE plug-in is loaded, but the members do not belong to any group. Status of the local member of the NoERROR. Server enters this state whenever an error occurs during the recovery phase or when the changes are applied. NoUNREACHABLE whenever a local failure detector suspects that a given server may be inaccessible because it has crashed or was accidentally disconnected, the status of the server is displayed as "UNREACHABLE" No

Important

Once the instance enters the ERROR state, the super_read_only option is 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 ultimately synchronous. More specifically, transactions are delivered to all group members in the same order, but their execution is not synchronized, which means that after accepting that the transaction is committed, each member commits at its own speed.

1.2 replication_group_ members table

This performance_schema.replication_group_members table is used to monitor the status of different server instances that are members of the 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 keep synchronized and continue to collaborate. Information is shared among group replication members, so information about all group members can be queried from any member. This table can be used to obtain a high-level view of the replication group status, 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, each member's host and port number, 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 status" in section 18.3.1, in which case it shows that all three members of the group are ONLINE, and the MEMBER_ROLE column shows two slave nodes and one master node. Therefore, the group must run in single master mode. MEMBER_VERSION this column can be useful when you upgrade a group and assemble members that are running different versions of MySQL. See Section 18.3.1, "Group member instance status" for more information.

For more information about the Member_host value and its impact on the distributed recovery process, see Section 18.2.1.3, "user credentials."

1.3 Replication_group_member_stats

Each member of a replication group validates and applies transactions committed by that group. Statistics about validation and applications are useful for understanding the growth of the request queue, how many conflicts have been triggered, how many transactions have been 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 instances of group members, so information about all group members can be queried from any member. Note that statistics for refreshing remote members are controlled by the message cycle specified in the group_replication_flow_control_period option, so this information may be slightly different from the statistics collected locally by the querying member.

Table 1.2 replication_group_member_stats

Field describes the name of the CHANNEL_NAME group replication channel. VIEW_ID the current view identifier for this group. Member_id this value is the UUID of the server member we are currently connected to. Each member of the group has a different value. Because it is unique to each member, it also becomes a keyword. The number of transactions in the Count_transactions_in_queue queue waiting for conflict detection checks. After the collision check passed, they queued for the application. Count_transactions_checked indicates the number of transactions that have been checked for conflicts. Count_conflicts_detected indicates the number of transactions that failed the collision detection check. Count_transactions_rows_validating represents the current size of the conflict detection database (the database validated for each transaction). Transactions_committed_all_members represents a transaction that has been successfully committed on all members of the current view. This value is updated at regular intervals. Last_conflict_free_transaction displays the last checked transaction identifier without conflicts. Count_transactions_remote_in_applier_queue the number of transactions received by this member from the replication group waiting to be applied. Count_transactions_remote_applied the number of transactions received by this member from applied replication groups. Count_transactions_local_proposed the number of transactions initiated by this member and sent to the replication group for coordination. Count_transactions_local_rollback the number of rollbacks of transactions initiated by this member after being sent to the replication group.

These fields are important for monitoring the performance of members of the group. For example, suppose one of the members of a group has a delay and cannot synchronize with other members of the group. In this case, you may see a large number of transactions in the queue. Based on this information, you can decide to remove members from the group or delay transactions for other members of the group, thereby reducing the number of transactions queued. This information can also help you decide how to adjust the flow control of the group replication plug-in.

The answer to the question about MySQL monitoring group replication is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.