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

This article mainly introduces "how to solve the problem of Slave delay and motionless in Mysql". In daily operation, I believe many people have doubts about how to solve the problem of Slave delay and motionless in Mysql. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubt of "how to solve the problem of Slave delay and motionless in Mysql". Next, please follow the editor to study!

Problem description

Receive an alarm with a high SLAVE delay, so check the SLAVE status (I have hidden the irrelevant status):

Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000605-the binlog of the current master

Read_Master_Log_Pos: 305864-master binlog location

Relay_Log_File: mysql-relay-bin.003224 Relay_Log_Pos: 295105 Relay_Master_Log_File: mysql-bin.000604-the binlog log of the master to which the current salve is synchronized

Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Exec_Master_Log_Pos: 294959-the pos of the binlog of the master to which the current slave executes

Relay_Log_Space: 4139172581 Seconds_Behind_Master: 10905-delay is usually Read_Master_Log_Pos-Exec_Master_Log_Pos

As you can see, the latency is indeed high, and from the results of multiple show slave status, it is found that the position of binlog has been motionless.

Click (here) to collapse or open

Relay_Master_Log_File: mysql-bin.000604

Exec_Master_Log_Pos: 294959

Relay_Log_Space: 4139172581

Check processlist and found no abnormal sql statement.

Check the corresponding binlog on master and confirm that they are all doing something:

[yejr@imysql.com] # mysqlbinlog-vvv-- base64-output=decode-rows-j 294959 mysql-bin.000604 | more

-- base64-output=decode-rows-- remove some unnecessary binary log displays / *! 40019 SET @ @ session.max_insert_delayed_threads=0*/; / *! 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER / *! *; * * # at 294959 server id * # 160204 6:16:30 server id 1 end_log_pos 295029 * * Query thread_id=461151** exec_time=2144** error_code=0 SET timestamp 1454537790 SET @ @ session.pseudoconversation thread readreadable license 461151 racket; SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.autoimmune thread, @ @ session.unique_checks=1; SET @ @ session.sqlhammer; SET @ @ session.auto_increment_increment=1; SET @ @ session.auto_increment_increment=1, @ session.autoincrementincrementincrementincrementoffsetsetting; / *!\ C latin1 * / /! SET @ @ session.session. Collationalization setting setting clientholders; SET @ @ session.session. Collationalization timekeeping namespace; SET @ @ session.collationalization databasedisabled default clientpact; BEGIN / *! * /; # at 295029 # at 295085 # at 296040 # at 297047 # at 298056 # at 299068 # at 300104

There are several key messages in the above paragraph:

# at 294959-binlog starting point

Thread ID executed on thread_id=461151-master

Exec_time=2144-the total execution time of this transaction

Further down, there is a pile of binlog position information, which is not readable in this way. Let's take a look at it in a different position.

[yejr@imysql.com (test)] > show binlog events in 'mysql-bin.000604' from 294959 limit 10 +-+-+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | | +-+ | mysql-bin.000604 | 294959 | Query | 1 | 295029 | BEGIN | | mysql-bin | . 000604 | 295029 | Table_map | 1 | 295085 | table_id: 84 (bacula.File) | mysql-bin.000604 | 295085 | Delete_rows | 1 | 296040 | table_id: 84 | mysql-bin.000604 | 296040 | Delete_rows | 1 | 297047 | table_id: 84 | mysql-bin.000604 | 297047 | Delete_rows | 1 | 298056 | table_id: 84 | mysql-bin.000604 | 298056 | Delete_rows | 1 | 299068 | table_id: 84 | | mysql-bin . 000604 | 299068 | Delete_rows | 1 | 300104 | table_id: 84 | mysql-bin.000604 | 300104 | Delete_rows | 1 | 301116 | table_id: 84 | mysql-bin.000604 | 301116 | Delete_rows | 1 | 302147 | table_id: 84 | | mysql-bin.000604 | 302147 | Delete_rows | 1 | 303138 | table_id: 84 |

As you can see, this transaction does nothing else and deletes data all the time.

This is a Bacula backup system that automatically deletes data that expired a month ago every day.

In fact, this transaction is indeed very large, from 294959 of the binlog to the end of the binlog 4139169218, has been doing this, a total of about 3.85G of binlog waiting for the apply.

-rw-rw---- 1 mysql mysql 1.1G Feb 3 03:07 mysql-bin.000597

-rw-rw---- 1 mysql mysql 1.1G Feb 3 03:19 mysql-bin.000598

-rw-rw---- 1 mysql mysql 2.1G Feb 3 03:33 mysql-bin.000599

-rw-rw---- 1 mysql mysql 1.4G Feb 3 03:45 mysql-bin.000600

-rw-rw---- 1 mysql mysql 1.8G Feb 3 04:15 mysql-bin.000601

-rw-rw---- 1 mysql mysql 1.3G Feb 3 04:53 mysql-bin.000602

-rw-rw---- 1 mysql mysql 4.5G Feb 4 06:16 mysql-bin.000603

-rw-rw---- 1 mysql mysql 3.9G Feb 4 06:52 mysql-bin.000604

-rw-rw---- 1 mysql mysql 1.2K Feb 4 06:52 mysql-bin.000605

How to solve the problem?

Since this is a big transaction built into the Bacula backup system, there is no good way to do it unless you modify its source code.

For our general application, it is best to commit a transaction after saving enough operations, such as deleting thousands of records and committing once, instead of, as in this case, a deleted transaction consumes nearly 3.9g of binlog-day quality. this is terrible.

In addition to causing the SLAVE to look still, it may also cause some rows of data (data rows) to be locked for a long time and cause a large number of row locks to wait.

At this point, the study on "how to solve the problem of Slave delay and not moving in Mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.