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

Today, a classmate in development asked me a question about MySQL and said that he had not responded for a long time after executing an Insert statement in the test database. I see that the statement is a very regular statement in the form of insert into xxx values. It doesn't seem to make sense. I became interested in this kind of problem right away, and I'm going to take a good look at the reason.

After learning about the environment from my developer, I logged in to the server and first checked to see if the problem might be caused by insufficient disk space. As a result, the results of df-h show that there is more than enough space.

Use show proceslist to view threads.

You can see that a large number of threads are Waiting for table level lock, and the SQL statements submitted by the developer are also locked, which is the same lock.

| | 253688 | webadmin | xxxx | pt_test | Query | 253688 | Waiting for table level lock | insert into ptp_jgg (sub_type) values (9999) Table-level locks seem to have been seen in MyISAM. As a result, check the storage engine of the table and find that they are all InnoDB. |

One solution to this kind of problem is to kill the thread using kill.

Mysql > SELECT

-> a. Kill, a.id, ";")

-> FROM

-> information_ schema.`PROCESSLIST`a

-> WHERE

-> a.STATE = 'Waiting for table level lock'

This results in regular kill statements.

Of course, I am in no hurry to do this. I have a simple understanding with my developer classmates that they have encountered this kind of problem before, they are looking for the students of the system operation and maintenance staff to restart MySQL directly. It seems that I have encountered this problem before, so I am more interested in understanding it.

Looking at MySQL's error log also did not find any obvious errors. Using ps-ef | grep mysql to check the process information, I suddenly found that a scheduled task was set up in the system to back up data, but it didn't attract my attention at first, but after these clues were eliminated one by one, my attention naturally fell on this backup script.

Open the backup script and I will understand the cause of the problem.

The core statement of backup calls mysqldump in the form of variables.

Mysqldump-uroot-p$passwd pt_test | $GZIP-9 > $dump_path/pt_test$date.gz

In this way, there is no doubt that this statement is the culprit of the locking table.

The default mysqldump calls the option-- lock-all-tables, which means that the binlog and postion information of master is written to the header of the SQL file, and the common way is to use-- single-transaction

There is a short global read lock during execution, and the impact is much lower.

So the solution to this problem is very straightforward, the current solution is to directly kill the ongoing mysqldump, kill the backup mysqldump, and then check the show processlist again, and these threads will soon be in sleep state.

In this way, this problem is basically solved, and I think at least it will not restart MySQL for no reason in the future.

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.