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