mysql集群监控工具_MySQL 5.7.20 MySQL-MGR 集群监控

MySQL5.7 Group Replication

(MGR) 集群监控

Monitoring Group

Replication

Use the Perfomance Schema tables to monitor Group Replication,

assuming that the Performance Schema is enabled. Group Replication

adds the following tables:

performance_schema.replication_group_member_stats

performance_schema.replication_group_members

These Perfomance Schema replication tables also show information

about Group Replication:

performance_schema.replication_connection_status

performance_schema.replication_applier_status

The replication channels created by the Group Replication plugin

are named:

group_replication_recovery - This channel is used

for the replication changes that are related to the distributed

recovery phase.

group_replication_applier - This channel is used

for the incoming changes from the group. This is the channel used

to apply transactions coming directly from the group.

Replication_group_member_stats

Each member in a replication group certifies and applies

transactions committed by the group. Statistics regarding the

certifier and applier procedures are useful to understand how the

applier queue is growing, how many conflicts have been found, how

many transactions were checked, which transactions are committed

everywhere, and so on.

The performance_schema.replication_group_member_stats table

provides information related to the certification process. The

information is shared between all the server instances that are

members of the replication group, so information on all the group

members can be queried from any member.

replication_group_member_stats

Field

Description

Channel_name

The name of the Group Replication channel.

Member_id

The member server UUID. This has a different value for each member

in the group. This also serves as a key since it is unique to each

member.

Count_transactions_in_queue

The number of transactions in the queue pending conflict detection

checks. Once the transactions have been checked for conflicts, if

they pass the check, they are queued to be applied as

well.

Count_transactions_checked

The number of transactions that have been checked for

conflicts.

Count_conflicts_detected

The number of transactions that did not pass the conflict detection

check.

Count_transactions_rows_validating

The current size of the conflict detection database (against which

each transaction is certified).

Transactions_committed_all_members

The transactions that have been successfully committed on all

members of the replication group. This is updated at a fixed time

interval.

Last_conflict_free_transaction

The transaction identifier of the last conflict free transaction

checked.

These fields are important for monitoring the performance of the

members connected in the group. For example, suppose that one of

the group’s members is

delayed and is not able to keep up to date with the other members

of the group. In this case you might see a large number of

transactions in the queue. Based on this information, you could

decide to either remove the member from the group, or delay the

processing of transactions on the other members of the group in

order to reduce the number of queued transactions. This information

can also help you to decide how to adjust the flow control of the

Group Replication plugin.

Replication_group_members

This table is used for monitoring the status of the different

server instances that are tracked in the current view, or in other

words are part of the group and as such are tracked by the

membership service.

replication_group_members

Field

Description

Channel_name

The name of the Group Replication channel.

Member_id

The member server UUID.

Member_host

The network address of the group member.

Member_port

The MySQL connection port on which the group member is

listening.

Member_state

The current state of the group member (which can

be ONLINE, ERROR, RECOVERING, OFFLINE or UNREACHABLE).

For more information about

the Member_host value and its

impact on the distributed recovery process

Replication_connection_status

When connected to a group, some fields in this table show

information regarding Group Replication. For instance, the

transactions that have been received from the group and queued in

the applier queue (the relay log).

replication_connection_status

Field

Description

Channel_name

The name of the Group Replication channel.

Group_name

Shows the value of the name of the group. It is always a valid

UUID.

Source_UUID

Shows the identifier for the group. It is similar to the group name

and it is used as the UUID for all the transactions that are

generated during group replication.

Service_state

Shows whether the member is a part of the group or not. The

possible values of service state can be {ON, OFF and

CONNECTING};

Received_transaction_set

Transactions in this GTID set have been received by this member of

the group.

Replication_applier_status

The state of the Group Replication related channels and threads can

be observed using the regular replication_applier_status table as

well. If there are many different worker threads applying

transactions, then the worker tables can also be used to monitor

what each worker thread is doing.

replication_applier_status

Field

Description

Channel_name

The name of the Group Replication channel.

Service_state

Shows whether the applier service is ON or OFF.

Remaining_delay

Shows whether there is some applier delay configured.

Count_transactions_retries

The number of retries performed while applying a

transaction.

Received_transaction_set

Transactions in this GTID set have been received by this member of

the group.

Group Replication Server

States

The

table replication_group_members is

updated whenever there is a view change, for example when the

configuration of the group is dynamically changed. At that point,

servers exchange some of their metadata to synchronize themselves

and continue to cooperate together.

There are various states that a server instance can be in. If

servers are communicating properly, all report the same states for

all servers. However, if there is a network partition, or a server

leaves the group, then different information may be reported,

depending on which server is queried. Note that if the server has

left the group then obviously it cannot report updated information

about other servers' state. If there is a partition, such that

quorum is lost, servers are not able to coordinate between

themselves. As a consequence, they cannot guess what the status of

different servers is. Therefore, instead of guessing their state

they report that some servers are unreachable.

Server

State

Field

Description

Group Synchronized

ONLINE

The member is ready to serve as a fully functional group member,

meaning that the client can connect and start executing

transactions.

Yes

RECOVERING

The member is in the process of becoming an active member of the

group and is currently going through the recovery process,

receiving state information from a donor.

No

OFFLINE

The plugin is loaded but the member does not belong to any

group.

No

ERROR

The state of the member. Whenever there is an error on the recovery

phase or while applying changes, the server enters this

state.

No

UNREACHABLE

Whenever the local failure detector suspects that a given server is

not reachable, because maybe it has crashed or was disconnected

involuntarily, it shows that server's state as

'unreachable'.

No

Note that Group Replication

is not synchronous, but

eventually synchronous. More precisely, transactions are delivered

to all group members in the same order, but their execution is not

synchronized, meaning that after a transaction is accepted to be

committed, each member commits at its own pace.