【Mysql】MySQL 5.6中如何定位DDL被阻塞的問題
在上一篇文章《 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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29096438/viewspace-2212950/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL 中如何定位 DDL 被阻塞的問題MySql
- MySQL 5.6中如何定位DDL被阻塞的問題MySql
- MySQL表結構變更引起的Metadata Lock|如何定位DDL被阻塞MySql
- HHMySQL?中定位?DDL?被阻塞的問題及解決方案xmwMySql
- 在Linux中,mysql的innodb如何定位鎖問題?LinuxMySql
- Oracle有沒有MySQL的分割槽DDL遇到的問題OracleMySql
- Mysql DDL出現長時間等待MDL問題分析MySql
- MySQL 由於MDL讀鎖select被阻塞MySql
- MySQL問題定位-效能優化之我見MySql優化
- MySQL中2個select被阻塞場景的原因MySql
- MySQL DDL操作表MySql
- 對比上次MySQL的DDLMySql
- MySQL Online DDL詳解MySql
- 04 MySQL 表的基本操作-DDLMySql
- mysql DDL時鎖表的排查MySql
- mysql 原生 線上DDL 的bug .MySql
- MySQL DDL執行方式-Online DDL介紹MySql
- mysql亂碼的問題如何解決MySql
- MySQL 問題MySql
- mysql 遇到的問題MySql
- MySQL的DDL和DML操作語法MySql
- 故障分析 | ClickHouse 叢集分散式 DDL 被阻塞案例一則分散式
- mysql 1130 問題MySql
- MySQL(十三)DDL之庫和表的管理MySql
- MySQL入門---(一)SQL的DDL語句MySql
- mysql之 openark-kit online ddlMySql
- MySQL 線上DDL "gh-ost"MySql
- MySQL & MariaDB Online DDL 參考指南MySql
- MySQL - DDL詳解(Data Definition Language)MySql
- 詳談 MySQL 8.0 原子 DDL 原理MySql
- 如何在不相容的DDL命令後修復MySQL複製MySql
- MySQL:一個特殊的問題MySql
- mysql insert into ... select的鎖問題MySql
- mysql 刪表引出的問題MySql
- 測試MySQL鎖的問題MySql
- mysql 轉義問題MySql
- Mysql:常見問題MySql
- MySQL死鎖問題MySql