Multi-master update everywhere solution for MySQL is here. MySQL Group Replication ensures virtual synchronous updates on any member in a group of MySQL servers, with conflict handling and failure detection. Distributed recovery is also in the package to ease the process of adding new member.

Starting with MySQL 5.7.2 there has been a constant effort from the replication team to provide more fields to monitor the replication performance in the performance schema tables. This post gives a brief  overview of the Performance Schema tables that have been introduced for MySQL Group Replication.

NOTE: For recent changes and updates check:

Group Replication – Extending Group Replication performance_schema tables

TABLES INTRODUCED

There are two new tables introduced as a part of the MySQL Group Replication monitoring.

• performance_schema.replication_group_member_stats
• performance_schema.replication_group_members

Lets go through each table in detail :

REPLICATION GROUP MEMBER STATS

Each member in a replication group will have associated to it a Certifier and an Applier. Statistics regarding those modules are useful to exported through P_S table interface.

Lets discuss this table with respect to the fields it contains and see how it gets updated below

• Channel_name – The name of the Group Replication channel.
• Member_id – This gives the member server UUID on which we are currently connected. This will have different values for each member in the group. This also serves a key since it is unique to each member and unrelated to Certification/Applier processes.
• Count_Transactions_in_queue – Number of transactions in queue pending certification and apply. For the simple run that we had this field shows value 0 but if the load is high we will have transactions in queue.
• Count_transactions_checked – This field indicates the number of transactions that were certified (both positive and negative  certification).
• Count_conflicts_detected – This field indicates the number of transaction that were negatively certified.
• Count_transactions_validating – This field indicates the size of the certification database (against which each transaction is certified).
• Transactions_committed_all_members – This is a gtid_set which indicates the set of transactions which have been successfully committed on all the members of the group(i.e. it’s an intersection of the gtid_executed sets of all members in the group). This value is updated at fixed time intervals by the certification module.
• Last_conflict_free_transaction – This indicates the GTID of the last positively certified transaction.

These fields are of importance to the DBA to monitor the performance of the members connected in the group. Suppose that one of the group’s member is delayed and is not able to keep pace with the other members of the group. In this case the DBA will see a large number of transaction on queue. Reacting to this he can either remove the member  from the group or delay the processing of transaction on the other member of the group thus reducing the number of queued transactions.

Now lets show how this table gets updated with MySQL Group Replication in place :

Initial state of the performance_schema.replication_group_member_stats before joining any group will be showing all fields empty :

After setting the group name for the group and starting group replication but before the start of any transaction this table lists the value only Channel_name.

Now execute some set of transactions parallely on different members of the group so that there are some conflicts that may lead to negative certification.

Since member1 and member2 are executing transactions in parallel there is a conflict on member1 during second insert.

Note – There is equal possibility of the transaction failing in 1 as well. But lets consider the current case where the second insert fails on the member1.

Also lets execute a set of concurrent transactions and look at the state. (Not showing in here to prevent the blog length to increase).

After the set of transactions performed the state of the table is :

The state of the table on one of the member is :

Now lets analyse the values of the individual fields in the final table :

COUNT_TRANSACTIONS_IN_QUEUE value of 0 indicates that there are no transaction in the applier queue yet to be applied.

COUNT_TRANSACTIONS_CHECKED count 12 means that there were 12 transactions that went for certification during the processing of the transactions.

COUNT_CONFLICTS_DETECTED count being 5 means that, out of the 12 transactions that underwent certification, 5 of them were negatively certified and 7 were positively certified.

COUNT_TRANSACTIONS_VALIDATING – Number of transactions present in the certification info, that one can execute certification with them, but they haven’t been Garbage Collected yet.

TRANSACTIONS_COMMITTED_ALL_MEMBERS of “8a94f357-aab4-11df-86ab-c80aa9429563:1-7” means that the transactions in this GTID set are present on all members of the group. This means that last time the garbage collection process of certification ran, this was  the group of GTIDs that was present in all members.

LAST_CONFLICT_FREE_TRANSACTION with value “8a94f357-aab4-11df-86ab-c80aa9429563:7” means that the last transaction certified without conflicts had this GTID.

REPLICATION GROUP MEMBERS

This table is used for understanding the status of the different members in the group and its connection details.

Let us go through the inidividual fields of this table to understand it much better.

• Channel_name – The name of the Group Replication channel.
• Member_id – This gives the the member server UUID on which we are currently connected. This will have different values for each member in the group.
• Member_host – The network address of the member.
• Member_port – The port at which member is listening.
• Member_state – This gives the status of the members of the group (whether they are ONLINE, RECOVERING or OFFLINE)
  • ONLINE : This means that the member is ready to serve as a fully functional group member  ie. the client can connect and start executing transactions.
  • RECOVERING : This means that the member is in the process of becoming the active member of the group and is actually going through the recovery                 process, receiving state from a donor.
  • OFFLINE : This means that the plugin is loaded but the member does not belong to any group.

Now lets show how this table gets updated with MySQL Group Replication in place :

Let us connect three members to form a MySQL Group and start group replication on all of them. The status of this table is :

Now lets analyse the values of the individual fields in the final table :

This table will have three rows with each row detailing about the state of the status of the particular member in the group.

CHANNEL_NAME with value “group_replication_applier” gives the name of the channel.

MEMBER_ID with value “597dbb72-3e2c-11e4-9d9d-ecf4bb227f3b” is an indentifies a particular member.

MEMBER_HOST of “nightfury” gives the value of the host_name of one such member.

MEMBER_PORT gives the port number of the particular member. Will have number port values of all the connected members.

MEMBER_STATE – gives the state of the particular member. Will have one value for member_state for all the connected members. In the above table we have two members ONLINE and one having state as RECOVERING.

Tables Modified

Apart from the above two new tables used we have also extended the existing table performance_schema.replication_connection_status with fields that are useful for Group Replication. Lets go through this modified table :

REPLICATION CONNECTION STATUS 

This table is an extension of the already existing table which was introduced as a part of Performance schema for Replication in MySQL-5.7.2. With “MySQL Group Replication” we have extended this table with rows which give details about the group connection status.

Now let us discuss the new fields that are related to MySQL Group Replication :

• Channel_name – The name of the Group Replication channel.
• Group_name – This field shows the value of the name of the group. It is always a valid UUID.
• Source UUID – This shows the identifier for the group. Its similar to the group name and it is used as the UUID for all the transactions that are generated during the group replication.
• Service_state – This shows whether the member is a part of the group or not. The possible values of service state can be {ON, OFF and CONNECTING};
  • ON : Member is connected to the group (RECOVERING or ONLINE).
  • OFF: Member is disconnected (OFFLINE)
• Received_transaction_set –  This field shown transactions in this GTID set are received to this member of the group.

Let’s begin with the initial state of the table with one member in the group :

Lets examine the value of the fields in the table.

CHANNEL_NAME with value “group_replication_applier” gives the name of the channel.

GROUP_NAME with value “8a94f357-aab4-11df-86ab-c80aa9429563” gives the name of the group.

SOURCE_UUID with value “8a94f357-aab4-11df-86ab-c80aa9429563” gives the identifier to the group. Note that this is same as of group name.

SERVICE_STATE with value “ON” means member is connected to the group.

RECEIVED_TRANSACTION_SET with value “8a94f357-aab4-11df-86ab-c80aa9429563:1-5” means that transaction in this gtid set were received to this member of the group.

Please note that this table will be one per member and it will provide details of that member’s connection status.

CONCLUSION

The goal of this post was to help you familiarize with the Performance Schema tables relating to MySQL Group replication. Please try out this P_S interface for monitoring this new feature of MySQL replication and let us know your feedback.