A case of a large number of locked in MySQL database processing

MySQL in the use of the process encountered slow access, or can not respond to such problems, the solution is basically a formula, the general first reaction will be logged in to MySQL, show processlist see the current connection status.

Although simple, but show processlist shows the information is indeed quite useful, there is a time, think twice received feedback said MySQL query very slow, so, hurriedly log in to MySQL, perform show processlist view current connection information:

Mysql> show Processlist;

+--------+-------------+--------------------+-------+---------+-------+----------------------------------+----- -----------------------------------------------------------------------------+

| id     | user        |  host               | db     | Command | Time  | State                              | Info                                                                                 |

+--------+-------------+--------------------+-------+---------+-------+----------------------------------+-- --------------------------------------------------------------------------------+

|      1 | system user |                     | NULL   | connect | 342266| waiting for master to send event |  NULL                                                                                 | 

|      2 | system user |                     | hdpic |  Connect |   872 | Locked                             | UPDATE a SET STATE=0 WHERE ID=83752                                                 | 

| 123890 | hdpic_read  | 192.168.1.79:54910 | hdpic |  query   |  1512 | sending data                      | select z .id,z.title,z.creator_user_nick,z.creator_user_iden,z.lasteditor_ti      | 

| 124906 | hdpic_read  | 192.168.1.39:18844 | hdpic |  query   |   845 | locked                             | select * from a where  (id = 78789)  AND  (state  != 0))                              | 

| 124912 | hdpic_read  | 192.168.1.39:18862 | hdpic |  query   |   845 | locked                             | select * from a where  (id = 16031)  AND  (state  != 0))                              | 

| 124914 | hdpic_read  | 192.168.1.39:18865 | hdpic |  query   |   837 | locked                             | select * from a where  (id = 39109)  AND  (state  != 0))                              | 

| 124917 | Hdpic_read | 192.168.1.39:18875 | Hdpic |   Query | 833 | Locked | SELECT * from a WHERE ((ID = 16031) and (state! = 0)) |

A bunch of locked, no wonder slow ah, blocking the time is not short, more than 10 minutes.

Generally speaking, there is a locked that the current read and write operations are blocked, generally we see the lock will subconsciously think is due to write blocking read, the above results seem to conform to this feature: there is only one update, and no number of select. After all, this is an online system, even if you want to kill the connected thread, but also to kill the one that caused the blockage, can not kill all the locked.

From the information seen in show Processlist, the UPDATE statement is very simple, analysis of the table structure of a, the table is the MyISAM table, the ID of the table primary key, the update should be able to execute instantaneously, even if the system is busy should not, and by viewing the current system state, The overall load is very low, iostat see i/owait A few can be ignored, the write operation is not likely to run out so long.

This time again to analyze the information shown in show Processlist, found that the statement of ID 123890 execution time is the longest, must be executed before the UPDATE statement, through show full processlist view statement Detail table, see the query also access to a table, By this analysis, it should be that the statement has been read for a long time blocking the write, and the blocked write operation because it is in the highest priority queue, but also blocked the other read. But these are just our inferences, and given the reliability of online system services, it's best to find more accurate evidence and then do the work.

The Mysqladmin command has a debug parameter that analyzes the status information of the current MySQL service and can also be used to help us locate the details of the current lock and execute the mysqladmin command as follows:

[[email protected] data]# mysqladmin-uroot-p-s/data/3306/mysql.sock Debug

Debug will generate the status information to the MySQL error file, the general lock information will be saved in the last few lines, here we at the operating system layer error log the last few lines:

[Email protected] data]# tail-10 Phpmysql02.err

Thread database.table_name locked/waiting Lock_type

2 Hdpic.t_wiki_zutu waiting-write highest priority write lock

123890 Hdpic.t_wiki_zutu_category locked-read Low Priority read lock

123890 Hdpic.t_wiki_zutu_photo locked-read Low Priority read lock

123890 Hdpic.t_wiki_zutu locked-read Low Priority read lock

• 124906 Hdpic.t_wiki_zutu waiting-read Low Priority read lock

As can be seen from the above information, 123890 hold the read lock blocked 2 write and 124906 read operation, this state conforms to our inference, if the status quo is unacceptable, can not continue to wait, 123890 killed, release resources can:

Mysql> kill 123890;

• Query OK, 0 rows Affected (0.00 sec)

Run show processlist again to view:

Mysql> show Processlist;

+--------+-------------+--------------------+-------+---------+--------+----------------------------------+---- --------------+

| Id | User | Host | db | Command | Time | State | Info |

+--------+-------------+--------------------+-------+---------+--------+----------------------------------+---- --------------+

| 1 |                    System user | | NULL | Connect | 342390 | Waiting for Master to send event | NULL |

| 124906 | Hdpic_read | 192.168.1.39:18844 | Hdpic |      Sleep |                                  1 | | NULL |

| 124912 | Hdpic_read | 192.168.1.39:18862 | Hdpic |      Sleep |                                  2 | | NULL |

| 124914 | Hdpic_read | 192.168.1.39:18865 | Hdpic |      Sleep |                                  1 | | NULL |

There is no locked connection, at this point to the front-end personnel asked, told the slow response has been eliminated, the service returned to normal.

A case of a large number of locked in MySQL database processing