Shulou(Shulou.com)06/01 Report--

Methods of confirming replication delay

Execution

Mysql > show slave status\ G

If "seconds_behind_master" is not "0", we need to pay attention to it and investigate its root cause.

First of all, you need to confirm that the reason for the lag comes from two aspects, from IO_Thread (such as slow network connection and slow disk) or SQL_Thread (slow SQL in relay log)

Execution

Mysql > show master status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Verify that the two threads are running, and if not, confirm the contents of the error log, and then make it run.

Next we need to confirm whether or not

Master_log_file

Relay_Master_Log_File

Read_Master_Log_Pos

Exec_Master_Log_Pos

Values are equal, but Seconds_Behind_Master never decreases.

Then execute SHOW MASTER STATUS on the main service to confirm the log file and location of the current primary service and whether Exec_Master_Log_Pos lags behind Read_Master_Log_Pos.

Solution method

If there is more than one service, check:

Whether all hosts use the same hardware

Whether to use the same version of MySQL (the slave server version should be equal to or higher than the master server version)

Check routing, network firewall

IO thread lag

It's usually due to network or saturation.

Use high-capacity binlog

Check the speed of the network and disk

SQL thread lag

1. Check for long transactions executed on the slave server

2. Check the IO operations in the active set on the slave server

Stop the IO thread and confirm that the problem is resolved

Change the innodb_flush_log_at_trx_commit parameter to confirm whether it is resolved, try to change it to 0, confirm the result, and eventually try to change it to 2.

Reduce the max_relay_log_ size value to avoid excessive read load, for example, 8m mysql > set global max_relay_log_size = 8" 1024024 "1024

Check that the slave server is not using log_slave_updates = 1

3. Consider using Multi Threaded Slave (MTS)

4. Confirm whether too many tables do not use primary keys

If the log is in row or MIXED format, there will be a delay if the table does not have a primary key.

This is because any available key or direct table scan can be used when performing a transaction on the primary server

These are not used when applying row events on the slave server SQL.

When row events from the binary log of the master server are applied line by line to the matching row image of the slave server

If you use the primary key to uniquely identify each row, you can quickly apply changes to the appropriate row image of the slave server.

However, when no primary key is defined, the entire row image must be compared line by line for each affected row on the primary server.

5. If a continuous statistical feature has a negative impact on MySQL replication in MySQL 5.7, it will be disabled and checked for any improvements.

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.