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

This article mainly introduces the example analysis of MGR monitoring and optimization points in Mysql, which is very detailed and has a certain reference value. Interested friends must read it!

Monitoring point

Availability monitoring

Is this node online:

Select member_state from replication_group_members where member_id=@@server_uuid

Can the current node write:

Select * from performance_schema.global_variables where variable_name in ('read_only',' super_read_only')

The node is Online, which means it belongs to the cluster and is working properly. A node is not writable, indicating that it is a non-Master node in Single-master.

Performance monitoring

Is there a delay in replication:

Compare whether the obtained GTID is consistent with the GTID executed by this node:

The GTID obtained:

SELECT Received_transaction_set FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier'

The GTID executed by this node:

Select @ @ gtid_executed

GTID obtained remotely-GTID executed by this node = number of delayed GTID

Is there any accumulation in the execution queue of this node (greater than 0 indicates delay):

Select count_transactions_in_queue from replication_group_member_stats where member_id=@@server_uuid

Flow control (flow control)

In MGR, if a node lags behind too many other members of the cluster, it initiates to let the other nodes wait for them to complete the control they are doing. This is called flow control.

When enabled: group_replication_flow_control_mode=QUOTA means to enable flow control. Flow control is controlled by two parameters by default:

Group_replication_flow_control_applier_threshold (default: 25000)

Group_replication_flow_control_certifier_threshold (default: 25000)

In other words, when the default delay is 25000 GTID, write operations will be stopped for the entire cluster Block.

Of course, it is also possible to allow node delay, just like our master-slave structure, slave node delay without sending a request to it.

Turn off Flow control:

Set global group_replication_flow_control_mode='DISABLED'

Tip: turn off the flow control and check to see if there is a delay. If there is a delay, the self-control threshold does not send a request to the above. For MGR with multi-IDC structure, it is recommended to disable traffic control.

MGR tuning parameters

Because of the basic replication structure, all data replication, or logical replay, optimization is also a replication optimization point.

Change:

Slave_parallel_type-> LOGICAL_CLOCK

Number of enhanced sql_thread:

Slave_parallel_workers-> 2-8

If CPU is a bottleneck and the network is fine, reduce CPU compression:

Group_replication_compression_threshold = 1000000-> 2000000

Change from 1m to 2m, and then compress it (mainly for large transaction reporting optimization)

The above is all the contents of the article "sample Analysis of MGR Monitoring and Optimization points in Mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.