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

This article mainly introduces "Mysql slave delay fault analysis". In daily operation, I believe many people have doubts about Mysql slave delay fault analysis. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "Mysql slave delay fault analysis". Next, please follow the editor to study!

First of all, I would like to give some of the possible delays I have seen:

The delay of big things is slightly 2 * execution time status is: reading event from the relay log

Large table DDL delay is slightly 1 * execution time status is: altering table

Delays in things that have not been submitted for a long time will cause an instantaneous increase in delays.

There is no primary key or the unique key status on the table is: system lock or reading event from the relay log

Delay caused by innodb layer lock: system lock or reading event from the relay log

Set parameters such as sync_binlog,sync_relay_log,innodb_flush_log_at_trx_commit from library parameters

Image.png

We can clearly see that a certain MySQL thread consumes 100% of CPU, because it is 5.7 and we can easily use statements.

Select a.thd_id,b.THREAD_OS_ID,a.user, a.conn_id,b.TYPE,a.source,a.program_name from sys.processlist a,performance_schema.threads b where b.thread_id=a.thd_id

Find the corresponding relationship between the MySQL thread and the operating system as follows:

At this point, the study of "Mysql slave delay fault analysis" 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.