MySQL資料庫 DDL 阻塞問題定位 【轉載】

来源:https://www.cnblogs.com/xuliuzai/archive/2022/06/25/16411582.html
-Advertisement-
Play Games

1、簡述 binlog 二進位日誌文件,這個文件記錄了MySQL所有的DML操作。通過binlog日誌我們可以做數據恢復,增量備份,主主複製和主從複製等等。 2、Docker中無法使用vim問題解決 https://blog.csdn.net/Tomwildboar/article/details/ ...


轉載

【即拿即用:MySQL 中如何定位 DDL 被阻塞的問題?】

https://dbaplus.cn/news-11-4579-1.html

作者介紹

陳臣,甲骨文MySQL首席解決方案工程師,公眾號《MySQL實戰》作者,有大規模的MySQL,Redis,MongoDB,ES的管理和維護經驗,擅長MySQL資料庫的性能優化及日常操作的原理剖析。

1.引入

經常碰到開發、測試童鞋會問,線下開發、測試環境,執行了一個DDL,發現很久都沒有執行完,是不是被阻塞了?要怎麼解決?包括在群里,也經常會碰到類似問題:DDL 被阻塞了,如何找到阻塞它的 SQL ?

實際上,如何解決 DDL 被阻塞的問題,是 MySQL 中一個共性且高頻的問題。

下麵,就這個問題,給一個清晰明瞭、拿來即用的解決方案:

  • 怎麼判斷一個DDL是不是被阻塞了 ?

  • 當DDL被阻塞時,怎麼找出阻塞它的會話 ?

2.怎麼判斷一個 DDL是不是被阻塞了

首先,看一個簡單的Demo。

session1> create table sbtest.t1(id int primary key,name varchar(10));
Query OK, 0 rows affected (0.02 sec)
session1> insert into sbtest.t1 values(1,'a');
Query OK, 1 row affected (0.01 sec)
session1> begin;
Query OK, 0 rows affected (0.00 sec)
session1> select * from sbtest.t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)
session2> alter table sbtest.t1 add c1 datetime;
阻塞中。。。
session3> show processlist;
+----+-----------------+-----------+------+---------+-------+---------------------------------+---------------------------------------+
| Id | User            | Host      | db   | Command | Time  | State                           | Info                                  |
+----+-----------------+-----------+------+---------+-------+---------------------------------+---------------------------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 47628 | Waiting on empty queue          | NULL                                  |
| 24 | root            | localhost | NULL | Sleep   |    11 |                                 | NULL                                  |
| 25 | root            | localhost | NULL | Query   |     5 | Waiting for table metadata lock | alter table sbtest.t1 add c1 datetime |
| 26 | root            | localhost | NULL | Query   |     0 | init                            | show processlist                      |
+----+-----------------+-----------+------+---------+-------+---------------------------------+---------------------------------------+
4 rows in set (0.00 sec)

判斷一個 DDL 是不是被阻塞了,很簡單,就是執行 show processlist ,查看 DDL 操作對應的狀態。如果顯示的是 Waiting for table metadata lock ,則意味著這個 DDL 被阻塞了。DDL 一旦被阻塞了,後續針對該表的所有操作都會被阻塞,都會顯示 Waiting for table metadata lock 。這也是 DDL 讓人聞之色變的原因。碰到了類似場景,要麼 Kill DDL 操作,要麼 Kill 阻塞 DDL 的會話。Kill DDL 操作是一個治標不治本的方法,畢竟 DDL 操作總要執行。除此之外,對於 DDL 操作,需要獲取元資料庫鎖的階段有兩個:DDL 開始之初和 DDL 結束之前。如果是後者,就意味著之前的操作都要回滾,成本相對較高。所以,碰到類似場景,我們一般都會 Kill 阻塞 DDL 的會話。

那麼,怎麼知道是哪些會話阻塞了 DDL 呢?下麵我們看看具體的定位方法。

3.定位方法

3.1 方法一:sys.schema_table_lock_waits

sys.schema_table_lock_waits 是MySQL 5.7引入的,用來定位 DDL 被阻塞的問題。

針對上面這個Demo,我們看看sys.schema_table_lock_waits的輸出。

mysql> select * from sys.schema_table_lock_waits\G
*************************** 1. row ***************************
               object_schema: sbtest
                 object_name: t1
           waiting_thread_id: 62
                 waiting_pid: 25
             waiting_account: root@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: alter table sbtest.t1 add c1 datetime
          waiting_query_secs: 17
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 61
                blocking_pid: 24
            blocking_account: root@localhost
          blocking_lock_type: SHARED_READ
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 24
sql_kill_blocking_connection: KILL 24
*************************** 2. row ***************************
               object_schema: sbtest
                 object_name: t1
           waiting_thread_id: 62
                 waiting_pid: 25
             waiting_account: root@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: alter table sbtest.t1 add c1 datetime
          waiting_query_secs: 17
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 62
                blocking_pid: 25
            blocking_account: root@localhost
          blocking_lock_type: SHARED_UPGRADABLE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 25
sql_kill_blocking_connection: KILL 25
2 rows in set (0.00 sec)

只有一個 alter 操作,卻產生了兩條記錄,而且兩條記錄的 Kill 對象還不一樣,其中一條 Kill 的對象還是 alter 操作本身。如果對錶結構不熟悉或不仔細看記錄內容的話,難免會 Kill 錯對象。不僅如此,在 DDL 操作被阻塞後,如果後續有 N 個查詢被 DDL 操作堵塞,還會產生 N*2 條記錄。在定位問題時,這 N*2 條記錄完全是個噪音。這個時候,就需要我們對上述記錄進行過濾了。過濾的關鍵是 blocking_lock_type 不等於 SHARED_UPGRADABLE。SHARED_UPGRADABLE 是一個可升級的共用元數據鎖,加鎖期間,允許併發查詢和更新,常用在 DDL 操作的第一階段。所以,阻塞DDL的不會是SHARED_UPGRADABLE。

故而,針對上面這個 case,我們可以通過下麵這個查詢來精確地定位出需要 Kill 的會話。

SELECT sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE blocking_lock_type <> 'SHARED_UPGRADABLE'
 AND waiting_query = 'alter table sbtest.t1 add c1 datetime';

 3.2 方法二:Kill DDL 之前的會話

sys.schema_table_lock_waits 是 MySQL 5.7 才引入的。但在實際生產環境,MySQL 5.6還是占有相當多的份額。如何解決MySQL 5.6的這個痛點呢 ?細究下來,導致 DDL 被阻塞的操作,無非兩類:

  • 表上有慢查詢未結束。

  • 表上有事務未提交。

其中,第一類比較好定位,通過 show processlist 就能發現。而第二類僅憑 show processlist 很難定位,因為未提交事務的連接在 show processlist 中的狀態同空閑連接一樣,都是 Sleep 。所以,網上有 Kill 空閑連接的說法,其實也不無道理,但這樣做就太簡單粗暴了,難免會誤殺。其實,既然是事務,在 information_schema.innodb_trx中肯定會有記錄,如 session1 中的事務,在表中的記錄如下,

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421568246406360
                 trx_state: RUNNING
               trx_started: 2022-01-02 08:53:50
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 24
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.00 sec)

其中 trx_mysql_thread_id 是線程 id ,結合 information_schema.processlist ,可進一步縮小範圍。所以,我們可以通過下麵這個 SQL ,定位出執行時間早於 DDL 的事務。

SELECT concat('kill ', i.trx_mysql_thread_id, ';')
FROM information_schema.innodb_trx i, (
    SELECT MAX(time) AS max_time
    FROM information_schema.processlist
    WHERE state = 'Waiting for table metadata lock'
      AND (info LIKE 'alter%'
      OR info LIKE 'create%'
      OR info LIKE 'drop%'
      OR info LIKE 'truncate%'
      OR info LIKE 'rename%'
  )) p
WHERE timestampdiff(second, i.trx_started, now()) > p.max_time;

可喜的是,當前正在執行的查詢也會顯示在information_schema.innodb_trx中。所以,上面這個 SQL 同樣也適用於慢查詢未結束的場景。

4.MySQL 5.7中使用sys.schema_table_lock_waits的註意事項

sys.schema_table_lock_waits 視圖依賴了一張 MDL 相關的表-performance_schema.metadata_locks。該表是 MySQL 5.7 引入的,會顯示 MDL 的相關信息,包括作用對象、鎖的類型及鎖的狀態等。但在 MySQL 5.7 中,該表預設為空,因為與之相關的 instrument 預設沒有開啟。MySQL 8.0 才預設開啟。

mysql> select * from performance_schema.setup_instruments where name='wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+
| NAME                       | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | NO      | NO    |
+----------------------------+---------+-------+
1 row in set (0.00 sec)

所以,在 MySQL 5.7 中,如果我們要使用 sys.schema_table_lock_waits ,必須首先開啟 MDL 相關的 instrument。開啟方式很簡單,直接修改 performance_schema.setup_instruments 表即可。

具體SQL如下:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

但這種方式是臨時生效,實例重啟後,又會恢復為預設值。建議同步修改配置文件。

[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

5.總結

1)執行 show processlist ,如果 DDL 的狀態是 Waiting for table metadata lock  ,則意味著這個 DDL 被阻塞了。

2)定位導致 DDL 被阻塞的會話,常用的方法有兩種:

  • sys.schema_table_lock_waits

SELECT sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE blocking_lock_type <> 'SHARED_UPGRADABLE'
  AND (waiting_query LIKE 'alter%'
  OR waiting_query LIKE 'create%'
  OR waiting_query LIKE 'drop%'
  OR waiting_query LIKE 'truncate%'
  OR waiting_query LIKE 'rename%');

這種方法適用於 MySQL 5.7 和 8.0。

註意,MySQL 5.7 中,MDL 相關的 instrument 預設沒有打開。

  • Kill DDL 之前的會話
SELECT concat('kill ', i.trx_mysql_thread_id, ';')
FROM information_schema.innodb_trx i, (
    SELECT MAX(time) AS max_time
    FROM information_schema.processlist
    WHERE state = 'Waiting for table metadata lock'
      AND (info LIKE 'alter%'
      OR info LIKE 'create%'
      OR info LIKE 'drop%'
      OR info LIKE 'truncate%'
      OR info LIKE 'rename%'
  )) p
WHERE timestampdiff(second, i.trx_started, now()) > p.max_time;

如果 MySQL 5.7 中 MDL 相關的 instrument 沒有打開或在 MySQL 5.6 中,可使用該方法。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 表弟大學快畢業了,學了一個學期Python居然還不會寫學生管理系統,真的給我丟臉啊,教他又不肯學,還讓我直接給他寫,我真想兩巴掌上去,最終還是寫了給他,誰讓他是我表弟呢,關鍵時候還是得幫他一把! 寫完了放在那也是放著,所以今天分享給大家吧! 話不多說,咱們直接開始吧! 代碼解析 一、登錄頁面 1、定 ...
  • 領域邏輯 & 應用邏輯 如前所述,領域驅動設計中的業務邏輯分為兩部分(層):領域邏輯和應用邏輯: 領域邏輯由系統的核心領域規則組成,應用邏輯實現應用特定的用例 雖然定義很明確,但實現起來可能並不容易。您可能無法決定哪些代碼應該位於應用程式層,哪些代碼應該位於領域層。本節試圖解釋其中的差異 多個應用程 ...
  • 用例演示 - 創建實體 本節將演示一些示例用例並討論可選場景。 創建實體 從實體/聚合根類創建對象是實體生命周期的第一步。聚合/聚合根規則和最佳實踐部分 建議為Entity類創建一個主構造函數,以保證創建一個有效的實體。因此,無論何時我們需要創建實體的實例,我們都應該使用那個構造函數 參見下麵的問題 ...
  • 痞子衡嵌入式半月刊: 第 57 期 這裡分享嵌入式領域有用有趣的項目/工具以及一些熱點新聞,農曆年分二十四節氣,希望在每個交節之日準時發佈一期。 本期刊是開源項目(GitHub: JayHeng/pzh-mcu-bi-weekly),歡迎提交 issue,投稿或推薦你知道的嵌入式那些事兒。 上期回顧 ...
  • 2022年6月初合宙新上市了 Air32F103 系列 MCU, 分 Air32F103CBT6 和 Air32F103CCT6 兩個型號, 分別是 32K RAM + 128K FLASH 和 32K RAM + 256K FLASH, 支持的最高主頻216MHz, 可以Pin2Pin替換STM3... ...
  • ## 電腦性能設置 優化設置一:開啟卓越性能 其實,win10系統中有一個卓越性能的隱藏設置,它可以讓我們的電腦,在現有配置的情況下,發揮出最優良的性能。 1、 同時按住“win+R”打開運行視窗,輸入“powershell”並點擊“確定” 2、 打開命令提示符頁面後,輸入並執行以下字元,就會出現 ...
  • 寫在前面: 這幾天留校,在做一個電機驅動的項目,使用的是合肥傑發的平臺,車規級晶元AC7801/11系列晶元。 但在進行模擬和程式燒錄的時候遇到了各種問題,藉助這個機會,私下裡總結了常見的模擬與燒錄程式常見的幾種方式,以及相關的操作,希望對大家有幫助。 單片機ISP、IAP和ICP幾種燒錄方式的區別 ...
  • 對於 MySQL 資料庫作為各個業務系統的存儲介質,在系統中承擔著非常重要的職責,如果資料庫崩了,那麼對於讀和寫資料庫的操作都會受到影響。如果不能迅速恢復,對業務的影響是非常大的。本文我將分享MySQL 雙主 + Keepalived 的高可用落地和踩坑之路。 ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...