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

This article shows you how to understand Semaphore wait and undo log in MySQL. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Scene:

MySQL-5.7.17, master-slave architecture, business read-write separation, read-only slave library occasional delay, and trigger Semaphore wait of Innodb to cause slave library active Crash

Conclusion:

Upgrade the database to 5.7.19, adjust block size and buffer_pool_instances

Key points!

After all, can not understand the innodb source code, no clear and detailed logic, can only analyze the problem phenomena and code fragments

Analysis:

The source code is 5.7.17

Phenomenally, only one instance of this business has this problem, and it is confirmed that the synchronization delay is not caused by external reasons by replacing the virtual machine / physical machine.

Through the observation of monitoring, it is confirmed that there is no sudden CPU utilization and IO utilization when the delay occurs.

Go to error log first.

Select r.id

From tb_r r

Inner join tb_a an on r.id = a.id

Left join tb_asf asf on r.id = asf.id and asf.created_time > '2999-99-99-99 purge 99'

Inner join tb_ua ua on a.id = ua.id

Where r.type=0 and r.status = 0 and a.type > 0 and r.num > 0 and ua.num=2

GROUP BY r.id

Order by a.col1 desc, SUM (IFNULL (asf.col2,0)) desc, a.col3 desc

Limit 28

The above is how to understand Semaphore wait and undo log in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.