處理MySQL資料庫出現大量Locked的一個案例

標籤：

MySQL在使用過程中遇到訪問速度慢，或者無法響應這類的問題，解決方式基本都有定式，一般第一反應都會是登入到MySQL, show processlist看看當前串連狀態。

雖說簡單，但show processlist顯示的資訊確實是相當有用，有一回，三思收到反饋說MySQL查詢很慢，於是，趕緊登入到mysql中，執行show processlist查看當前串連資訊：

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))                            | 

一堆的Locked，怪不得慢啊，阻塞的時間不短了，十幾分鐘。

通常來說存在Locked就說明當前讀寫操作存在被阻塞的情況，一般我們看到鎖都會下意識認為是由於寫阻塞了讀，上面的結果看彷彿也符合這一特徵： 只有一條UPDATE，而無數條的SELECT。這畢竟是線上系統，就算想殺串連的線程，也是要殺掉造成阻塞的那個，不能把所有 Locked的全殺了。

從show processlist查看到的資訊來看，UPDATE的語句是很簡單的，分析a的表結構，該表為MyISAM表，ID為該表主鍵，該條更新應該能夠瞬間執行完，即使系統繁忙也不應該，而且通過查看當前的系統狀態，整體負載很低，iostat中看I/Owait幾可忽略，該寫操作不太可能這麼長時間都沒有執行完。

這個時候再分析show processlist中顯示的資訊，發現id 123890的語句執行時間最長，肯定是在該UPDATE語句之前執行 的，通過show full processlist查看語句詳表，看到該查詢也訪問到了a表，經此分析，應該是該語句長時間的讀阻塞了寫，而被阻塞的寫操作由於處於最優先處理隊列，又阻塞了其它的讀。不過這些都還只是我們的推論，考慮到線上系統服務的可靠性，最好還是能找到更確切的證據，而後再做操作。

mysqladmin命令有一個debug參數，可以分析當前MySQL服務的狀態資訊，同時也可以用來協助我們定位當前鎖的詳細情況，執行mysqladmin命令如下：

[[email protected] data]# mysqladmin -uroot  -p  -S  /data/3306/mysql.sock debug

debug會將狀態資訊產生到mysql的錯誤檔案，一般鎖的資訊都會儲存在最後幾行，這裡我們在作業系統層error log最後幾行：

[[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

從上述資訊可以看出，123890持有的讀鎖阻塞了2的寫入和124906的讀操作，這個狀態符合我們的推論，如果現狀不可接受，不能繼續等待，將123890殺掉，釋放資源即可：

mysql> kill 123890;

• Query OK, 0 rows affected (0.00 sec)

再次執行show processlist查看：

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             | 

已經沒有Locked的串連，此時向前端人員詢問，告知響應慢的現象也已經消除，服務恢複正常。

處理MySQL資料庫出現大量Locked的一個案例