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

If the master-slave delay is checked?

1. Show slave status\ G, check whether the value of relay_master_log_file & exec_master_log_pos has changed (if it is a GTID copy, you can also see whether the number of things in executed_gtid_set has increased). If it does not change all the time, it means that there is something big that causes the sql_ thread hang to live. At this time, you need to check the binlog of the main database to see what it is:

Mysqlbinlog-v-base64-output=decode-rows-start-position= exec_master_log_pos relay_master_log_file | less

And then wait for the big thing to end or roll back.

After this thing is over, what if the delay cannot be accepted from the library next time? what is the fundamental solution?

(1) switch the business that requires high reading from the slave database to the master database.

(2) in the future, update big things into several small things, for example, change 200000 articles at a time to 100000 items at a time.

two。 What if the relay_master_log_file & exec_master_log_pos number grows slowly?

(1) parse the corresponding binlog: mysqlbinlog-v-- base64-output=decode-rows-- start-position= exec_master_log_pos relay_master_log_file | less

Check the corresponding table to see if the table has primary key, index and other structures.

(2) check whether the system is overloaded with cpu,memory,io

Io can be viewed through the tools iotop and pt-ioprofile

If you find that the slave_relay_log_info.ibd file under the mysql library occupies a high IO, you can consider enlarging the sync_relay_log_info so that the file is synchronized less frequently.

Cpu can be viewed through the top command:

If the user is relatively high, you can view it with show processlist, slow logs, and there is no large number of sorting, mainly sql statements.

If the sys is high, generally speaking, because:

1. Swap occurs

2. A serious lock wait occurs in the database

3. The use of ssd and other equipment, resulting in a large number of interrupts, or network card interruptions (cpu interruptions are not balanced)

4. Frequently create connections and close them in MySQL

5. The timestamp column is used frequently, and time_zone=SYSTEM

Memory:

Free-gt

Vmstat

Check whether the errorlog of mysql and the server-id of master and slave are different.

How to check if the network card is connected:

Ifconfig | grep up

Dmesg | grep eth

If none of the above methods work, you can try other methods:

Use the tool perf top

Pstack `pidof mysqld`

Ipmitool

Hardware aspect, raid card, etc.

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.