在上一篇文章《 MySQL 5.7中如何定位DDL被阻塞的問題》中，對於DDL被阻塞問題的定位，我們主要是基於MySQL 5.7新引入的performance_schema.metadata_locks表。提出的定位方法，頗有種"錦上添花"的意味，而且，也只適用於MySQL 5.7開始的版本。

但在實際生產中，MySQL 5.6還是佔絕不多數。雖然MySQL 8.0都已經GA了，但鑑於資料庫的特殊性，在對待升級的這個事情上，相當一部分人還是秉持著一種“不主動”的態度。

既然MySQL 5.6用者眾多，有沒有一種方法，來解決MySQL 5.6的這個痛點呢？

還是之前的測試Demo。

會話1開啟了事務並執行了三個操作，但未提交，此時，會話2執行了alter table操作，被阻塞。








session1


>


 


begin


;












Query


 OK


,


 




 rows affected 


(


0.00


 sec


)






































session1


>


 


delete


 


from


 slowtech


.


t1 


where


 id


=


2


;












Query


 OK


,


 


1


 row affected 


(


0.00


 sec


)






































session1


>


 


select


 


*


 


from


 slowtech


.


t1


;












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












|


 id   


|


 name 


|












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












|


    


1


 


|


 a    


|












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












1


 row 


in


 


set


 


(


0.00


 sec


)






































session1


>


 update slowtech


.


t1 


set


 name


=


'c'


 


where


 id


=


1


;












Query


 OK


,


 


1


 row affected 


(


0.00


 sec


)












Rows


 matched


:


 


1


  


Changed


:


 


1


  


Warnings


:


 








































session2


>


 alter table slowtech


.


t1 add c1 


int


;


 


##被阻塞






































session3


>


 show processlist


;












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












|


 


Id


 


|


 


User


 


|


 


Host


      


|


 db   


|


 


Command


 


|


 


Time


 


|


 


State


                           


|


 


Info


                               


|












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












|


  


2


 


|


 root 


|


 localhost 


|


 NULL 


|


 


Sleep


   


|


   


51


 


|


                                 


|


 NULL                               


|












|


  


3


 


|


 root 


|


 localhost 


|


 NULL 


|


 


Query


   


|


    




 


|


 starting                        


|


 show processlist                   


|












|


  


4


 


|


 root 


|


 localhost 


|


 NULL 


|


 


Query


   


|


    


9


 


|


 


Waiting


 


for


 table metadata 


lock


 


|


 alter table slowtech


.


t1 add c1 


int


 


|












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












3


 rows 


in


 


set


 


(


0.00


 sec


)

提示，類似程式碼可左右滑動

其實，導致DDL阻塞的操作，無非兩類：

1. 慢查詢

2. 表上有事務未提交

其中，第一類比較好定位，透過show processlist即能發現。而第二類基本沒法定位，因為未提交事務的連線在show processlist中的輸出同空閒連線一樣。

如下面Id為2的連線，雖然Command顯示為“Sleep”，其實是事務未提交。

mysql> show processlist;+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+| Id | User | Host      | db   | Command | Time | State                           | Info                               |+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+|  2 | root | localhost | NULL | Sleep   |   77 |                                 | NULL                               ||  3 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                   ||  4 | root | localhost | NULL | Query   |   44 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+3 rows in set (0.00 sec)

所以，網上有kill空閒（Command為Sleep）連線的說法，其實也不無道理，但這樣做就太簡單粗暴了，難免會誤殺。

其實，既然是事務，在information_schema. innodb_trx中肯定會有記錄，如會話1中的事務，在表中的記錄如下，

mysql> select * from information_schema.innodb_trx\G*************************** 1. row ***************************                    trx_id: 1050390                 trx_state: RUNNING               trx_started: 2018-07-17 08:55:32     trx_requested_lock_id: NULL          trx_wait_started: NULL                trx_weight: 4       trx_mysql_thread_id: 2                 trx_query: NULL       trx_operation_state: NULL         trx_tables_in_use: 0         trx_tables_locked: 1          trx_lock_structs: 2     trx_lock_memory_bytes: 1136           trx_rows_locked: 3         trx_rows_modified: 2   trx_concurrency_tickets: 0       trx_isolation_level: REPEATABLE READ         trx_unique_checks: 1    trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0          trx_is_read_only: 0trx_autocommit_non_locking: 01 row in set (0.00 sec)

其中trx_mysql_thread_id是執行緒id，結合performance_schema.threads，可以知道當前哪些連線上存在著活躍事務，這樣就進一步縮小了可被kill的執行緒範圍。

但從影響程度上，和kill所有Command為Sleep的連線沒太大區別，畢竟，kill真正的空閒連線對業務的影響不大。

有沒有辦法能更精確地定位出阻塞會話呢？

有，答案還是在於之前介紹的performance_ schema.events_statements_history表。

在上篇MySQL 5.7的分析中，我們是首先知道引發阻塞的執行緒ID，然後利用events_statements_history表，檢視該執行緒的相關SQL。

而在MySQL 5.6中，我們並不知道引發阻塞的執行緒ID，但是，我們可以反其道而行之，利用窮舉法，首先統計出所有執行緒在當前事務執行過的所有SQL，然後再判斷這些SQL中是否包含目標表。

具體SQL如下，

SELECT    processlist_id,    sql_text FROM    (    SELECT        c.processlist_id,        substring_index( sql_text, "transaction_begin;",-1 ) sql_text     FROM        information_schema.innodb_trx a,        (        SELECT            thread_id,            group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text         FROM            performance_schema.events_statements_history         GROUP BY            thread_id         ) b,        performance_schema.threads c     WHERE        a.trx_mysql_thread_id = c.processlist_id         AND b.thread_id = c.thread_id     ) t WHERE    sql_text LIKE '%t1%';+----------------+---------------------------------------------------------------------------------------------------------+| processlist_id | sql_text                                                                                                |+----------------+---------------------------------------------------------------------------------------------------------+|              2 | delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1 |+----------------+---------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)

SQL可點選文末的“閱讀原文”獲取

從輸出來看，確實也達到了預期效果。

需要注意的是，在MySQL5.6中，events_ statements_history預設是沒有開啟的。

mysql> SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%statements%';+--------------------------------+---------+| NAME                           | ENABLED |+--------------------------------+---------+| events_statements_current      | YES     || events_statements_history      | NO      || events_statements_history_long | NO      || statements_digest              | YES     |+--------------------------------+---------+4 rows in set (0.00 sec)

原文地址：

https://mp.weixin.qq.com/s/BeDnS27zgiPbgGtlrnSOSg

【Mysql】MySQL 5.6中如何定位DDL被阻塞的問題

相關文章