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

This article focuses on "how to solve the lock waiting failure caused by Mysql Sleep threads", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to solve the lock waiting failure caused by Mysql Sleep threads.

Background:

There is an alarm too many connections in the responsible production warehouse.

Resolution process:

1. The first reaction is that there are a large number of connections or DDL filled up the instance, and then log on to the instance to try to log in, only to find that the connection is no longer available. Then dynamically adjust the number of Dalian. Use the following SQL to see that a large number of UPDATE threads have not finished executing.

Select id,host,user,db,command,state,time,left (info,200) from information_schema.processlist where command 'Sleep' order by time desc limit 100

two。 When you see this scenario, you will most likely think that the ID of table tbl_prod has no index, so it is a full table lookup update, but when you see the execution plan, you will find that it is not, but the primary key update.

Explain UPDATE tbl_prod SET status=1 WHERE id = 1198445

3. Then look at the current state of the innodb engine and find that it's interesting that these update threads are waiting for a lock, taking one of them as an example.

Show engine innodb status\ G

4. At this point, it can basically be concluded that there are a large number of primary key updates waiting due to long transactions that have not been committed. We can find this persistent connection using the following SQL.

Select p.hostrep. Timestation.trxbooks mysqlthreadcalendar on t.trx_mysql_thread_id. Trxstores startedleft (p.info.100) from information_schema.innodb_trx t join information_schema.processlist p on t.trx_mysql_thread_id = p.id order by time desc,t.trx_started desc limit 20

5. You can see that the first thread with the longest execution time above is NULL, which occupies the longest lock time, followed by a large number of update waiting threads. Then we drop the longest null thread to KILL and the problem is solved.

Kill 2323514; at this point, I believe you have a better understanding of "how to solve the lock waiting failure caused by Mysql Sleep threads". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.