MySQL如何查看鎖競爭情況 鎖等待圖譜與死鎖分析工具

mysql中可通過performance schema、show engine innodb status、pt-deadlock-logger、慢查詢?nèi)罩镜?a >工具查看鎖競爭情況。1.啟用performance schema需先檢查并更新setup_instruments和setup_consumers中的鎖相關(guān)配置，再通過events_waits_summary_global_by_event_name和events_waits_current表查看鎖等待統(tǒng)計(jì)和當(dāng)前事件；2.show engine innodb status命令可顯示最近死鎖詳情及事務(wù)持有的鎖，幫助識別沖突sql；3.pt-deadlock-logger工具可定期記錄死鎖信息到日志文件，便于長期分析；4.慢查詢?nèi)罩窘Y(jié)合mysqldumpslow可輔助發(fā)現(xiàn)潛在的鎖競爭sql；5.優(yōu)化sql包括減少事務(wù)大小、使用精確索引、避免長事務(wù)、優(yōu)化執(zhí)行計(jì)劃、調(diào)整隔離級別等方式；6.調(diào)整innodb_lock_wait_timeout、innodb_deadlock_detect、連接數(shù)等參數(shù)可緩解鎖競爭影響。

鎖競爭情況的查看，核心在于理解MySQL的性能瓶頸，并通過工具和sql語句定位鎖沖突，進(jìn)而優(yōu)化SQL或調(diào)整并發(fā)策略。

解決方案：

MySQL提供了多種方式來查看鎖競爭情況，包括使用性能模式（Performance Schema）、SHOW ENGINE INNODB STATUS命令、以及一些第三方工具。

如何開啟和使用Performance Schema監(jiān)控鎖？

Performance Schema是MySQL 5.5及以上版本提供的性能監(jiān)控工具，它記錄了各種服務(wù)器事件的詳細(xì)信息，包括鎖等待。默認(rèn)情況下，Performance Schema可能未完全啟用，需要手動(dòng)配置。

1. 檢查Performance Schema是否啟用：

   SELECT NAME, ENABLED FROM performance_schema.setup_instruments WHERE NAME LIKE '%lock%'; SELECT NAME, ENABLED FROM performance_schema.setup_consumers WHERE NAME LIKE '%lock%';

   如果ENABLED為NO，則需要啟用相應(yīng)的instrument和consumer。

2. 啟用鎖相關(guān)的instrument和consumer：

   UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%lock%'; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%lock%';

   修改后，可以通過查詢events_waits_summary_global_by_event_name表來查看全局鎖等待事件的統(tǒng)計(jì)信息：

   SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT FROM performance_schema.events_waits_summary_global_by_event_name WHERE EVENT_NAME LIKE 'wait/lock/%' ORDER BY SUM_TIMER_WAIT DESC;

   這個(gè)查詢會顯示各種鎖等待事件的名稱、發(fā)生次數(shù)和總等待時(shí)間，從而幫助你識別最常見的鎖競爭類型。

3. 查看具體的鎖等待事件：

   要查看更詳細(xì)的鎖等待信息，可以查詢events_waits_current表：

   SELECT EVENT_ID, THREAD_ID, EVENT_NAME, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_waits_current WHERE EVENT_NAME LIKE 'wait/lock/%';

   結(jié)合threads表，可以找到導(dǎo)致鎖等待的線程信息，例如執(zhí)行的SQL語句。

SHOW ENGINE INNODB STATUS命令能提供哪些鎖信息？

SHOW ENGINE INNODB STATUS命令是一個(gè)快速診斷InnoDB存儲引擎狀態(tài)的工具，它包含了大量的運(yùn)行時(shí)信息，其中也包括鎖的信息。

1. 執(zhí)行命令：

   SHOW ENGINE INNODB STATUSG

   這個(gè)命令會輸出大量的信息，你需要關(guān)注LATEST DETECTED DEADLOCK部分，它會顯示最近一次死鎖的詳細(xì)信息，包括涉及的事務(wù)、鎖類型、SQL語句等。

2. 分析輸出結(jié)果：

   在TRANSACTIONS部分，你可以看到當(dāng)前活動(dòng)的事務(wù)列表，以及它們持有的鎖和等待的鎖。通過分析這些信息，可以了解哪些事務(wù)之間存在鎖沖突。

   例如，你可能會看到類似這樣的信息：

   ---TRANSACTION 1727522, ACTIVE 26 sec 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 123, OS thread handle 140735252856576, query id 18204 localhost root updating UPDATE products SET price = price * 1.1 WHERE id = 10; ... ---TRANSACTION 1727523, ACTIVE 15 sec 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 124, OS thread handle 140735252856577, query id 18205 localhost root updating UPDATE products SET quantity = quantity - 1 WHERE id = 10;

   這段信息表明兩個(gè)事務(wù)都在嘗試更新products表中id = 10的行，導(dǎo)致了鎖沖突。

如何使用pt-deadlock-logger分析死鎖？

pt-deadlock-logger是Percona Toolkit中的一個(gè)工具，它可以定期輪詢MySQL服務(wù)器的SHOW ENGINE INNODB STATUS輸出，并將死鎖信息記錄到日志文件中。這對于分析長時(shí)間的死鎖模式非常有用。

1. 安裝Percona Toolkit：

   根據(jù)你的操作系統(tǒng)，使用相應(yīng)的包管理器安裝Percona Toolkit。例如，在debian/ubuntu上：

   apt-get install percona-toolkit

2. 運(yùn)行pt-deadlock-logger：

   pt-deadlock-logger --dest /path/to/deadlock.log --user your_user --password your_password --host your_host

   這個(gè)命令會將死鎖信息記錄到/path/to/deadlock.log文件中。你需要替換your_user、your_password和your_host為你的mysql連接信息。

3. 分析日志文件：

   分析日志文件，可以了解死鎖發(fā)生的頻率、涉及的SQL語句和事務(wù)，從而找到死鎖的根本原因。

如何通過慢查詢?nèi)罩据o助定位鎖競爭問題？

雖然慢查詢?nèi)罩局饕糜谟涗泩?zhí)行時(shí)間超過閾值的SQL語句，但它也可以幫助定位鎖競爭問題。如果一個(gè)SQL語句頻繁出現(xiàn)在慢查詢?nèi)罩局?，并且?zhí)行時(shí)間很長，那么它可能正在等待鎖。

1. 啟用慢查詢?nèi)罩荆?/strong>

   在MySQL配置文件（例如my.cnf）中，設(shè)置以下參數(shù)：

   slow_query_log = 1 slow_query_log_file = /path/to/slow.log long_query_time = 1  # 超過1秒的查詢被記錄 log_queries_not_using_indexes = 1 # 記錄未使用索引的查詢

   重啟MySQL服務(wù)器使配置生效。

2. 分析慢查詢?nèi)罩荆?/strong>

   使用mysqldumpslow命令分析慢查詢?nèi)罩荆?/p>

   mysqldumpslow -s t -t 10 /path/to/slow.log

   這個(gè)命令會按照查詢時(shí)間排序，顯示最慢的10個(gè)查詢。關(guān)注那些執(zhí)行時(shí)間很長，并且涉及大量數(shù)據(jù)更新的SQL語句，它們很可能是鎖競爭的根源。