MySQL Replication latency Troubleshooting

Master and slave delay if troubleshooting?

1. Show slave status\g, see Relay_master_log_file & Exec_master_log_pos values have changed (if Gtid copy can also see Executed_gtid_ If the set's thing number has no growth), if it has not changed, indicating that there is a big thing, causing the sql_thread thread to hang, this time need to look at the main library of Binlog, see what is the thing:

Mysqlbinlog-v--base64-output=decode-rows--start-position= Exec_master_log_pos relay_master_log_file | Less

Then wait for the big thing to end or roll back;

After the end of this thing, if the next time from the library can not accept such a delay, how to do, what is the fundamental solution?

(1) Switching from the library to the high-read requirements of the business to the main library;

(2) Later to update the big things split into a number of small things, such as one time update 200,000 to update the 100,000 article;

2. What if the Relay_master_log_file & Exec_master_log_pos values grow very slowly?

(1) resolve the corresponding binlog:mysqlbinlog-v--base64-output=decode-rows--start-position= exec_master_log_pos relay_master_log_ File | Less

View the corresponding table to see if the table has a primary key, index structure, etc.

(2) Check the system is not overloaded Cpu,memory,io,

IO can be viewed through tools iotop and Pt-ioprofile

If it is found that the slave_relay_log_info.ibd file under the MySQL library occupies a high IO, you can consider the large sync_relay_log_info, so that the file synchronization is not too frequent.

The CPU can be viewed through the top command:

If user is taller, can show Processlist view, slow log, there is not a lot of sort, mostly SQL statements

If SYS is high, in general, because:

1. Swap occurs

2, the database has a serious lock waiting

3, using the SSD and other devices, resulting in a large number of interrupts, or network card interrupt (CPU interrupt imbalance)

4, MySQL frequently create connections and close

5, frequent use to timestamp column, and Time_zone=system

Memory

Free-gt

Vmstat

Look at the MySQL errorlog, the master-slave Server-id is not the same

How to check if the NIC is connected:

Ifconfig |grep up

Dmesg|grep ETH

If none of the above methods are available, try another method:

Using the tool perf top

Pstack ' Pidof mysqld '

Ipmitool

Hardware-side RAID cards, etc.

This article is from the "10979687" blog, please be sure to keep this source http://10989687.blog.51cto.com/10979687/1923523

MySQL Replication latency Troubleshooting