MySQL 8.x ，查詢performance_schema.data_locks造成整個例項 hang了？

張衝andy發表於2024-01-11
原文網址 : https://blog.itpub.net/31383567/viewspace-3003472/
MySqlORM

問題描述

MySQL 8.0.25 在 2023/12/22  10:17:36 左右 出現執行緒堵塞，正常情況下原本執行很快的sql執行效率陡降（比如：原本執行0.001秒的sql執行7秒仍未結束），導致十幾筆交易超時。


問題分析

根據AWR處於非Sleep狀態的使用者執行緒，Time由高到低排序，執行耗時第一為Command處於Query執行耗時達30秒，是對 performance_schema.data_locks 的查詢，該查詢需要返回3百多萬資料。在查詢 performance_schema.data_locks 之後執行的簡單DQL&DML都沒有按照正常速度完成。

performance_schema.data_locks 在慢日誌的記錄

# Time: 2023-12-22T10:17:47.968405+08:00
# User@Host: xxx[xxx] @ [127.0.0.1] Id: 10181232
# Query_time: 41.615127 Lock_time: 0.000372 Rows_sent: 7855322 Rows_examined: 7855322
SET timestamp=1703211426;
select engine, engine_lock_id, engine_transaction_id, thread_id, event_id, object_schema, object_name, partition_name, subpartition_name, index_name, object_instance_begin, lock_type, lock_mode, lock_status, lock_data 
from performance_schema.data_locks;

疑問1 為什麼 performance_schema.data_locks 會有數百萬資料？

MySQL 8.0 中， performance_schema.data_locks 包含這些資料：

  • InnoDB 事務已申請但未獲得的鎖。
  • InnoDB 事務正在持有的鎖。

可能有事務，有大量 正在持有鎖 或者 已申請但未獲得鎖。

疑問2 什麼操作持有百萬資料量的行鎖？

根據異常期間AWR與慢日誌，發現問題sql UPDATE xxx_dtl xxx order by t.priority desc  執行過程累計掃描200多萬資料 且 在一個未提交事務累計持有 400 多萬 row locks，也就是造成performance_schema.data_locks 有百萬資料量的原因。

                                                                                            AWR截圖

                                                                                        慢日誌截圖

疑問3 為什麼 MySQL整個例項的SQL執行會變慢？

SQL執行緩慢猜測有3種原因：1、SQL執行效率低  2、資源異常/瓶頸  3、無效等待

    1、SQL執行效率低 （排除），沒有表鎖的情況下，簡單 select & insert 執行很快。
    2、資源異常/瓶頸（排除），檢視監控沒有硬體報錯與沒有資源使用率跑高。
    3、無效等待（復現），根據AWR抓取的現場資訊，模擬後能100%復現。

問題復現


 session1 session2 session3
t1 mysql> use dbtest; mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update sbtest1 set c='d'; Query OK, 0 rows affected (13.13 sec) Rows matched: 3000000 Changed: 0 Warnings: 0

t2

mysql> insert into t1 values(1113,1113); Query OK, 1 row affected (0.01 sec)
t3
mysql> select count(*) from performance_schema.data_locks;+----------+*| count(*) |+----------+| 3000001 |+----------+1 row in set (27.34 sec) mysql> insert into t1 values(1114,1114); Query OK, 1 row affected (18.58 sec)
t4

mysql> insert into t1 values(1115,1115); Query OK, 1 row affected (0.02 sec)
t5 commit

t6
mysql> select count(*) from performance_schema.data_locks;+----------+*| count(*) |+----------+| 0 |+----------+1 row in set (0.01 sec) mysql> insert into t1 values(1116,1116);Query OK, 1 row affected (0.02 sec)


用vscode檢視mysqld的函式呼叫情況

正常執行dml語句的堆疊，可見執行iterator，代表dml操作正常執行

查詢data_locks後，再執行的 dml語句堆疊變更為條件等待，等待locksys::latches的釋放

測試結論： performance_schema.data_locks有大量資料時，查詢performance_schema.data_locks會嚴重影響其他併發SQL的執行效率，執行其他SQL需要等待查詢 performance_schema.data_locks 持有的locksys::latches的釋放，才能往下執行

問題總結

MySQL 8.x當performance_schema.data_locks有大量資料時，查詢performance_schema.data_locks會嚴重影響其他併發SQL的執行效率，執行其他SQL需要等待查詢 performance_schema.data_locks 持有的locksys::latches釋放後，才能往下執行，可能會造成MySQL大量阻塞。

最佳化建議

1、業務問題sql UPDATE xxx_dtl xxx order by t.priority desc ，去掉order by t.priority desc 減少資料掃描量縮短鎖佔用時間提升執行效率
2、避免大事務，避免長時間未提交事務
3、禁止出現訪問 performance_schema.data_locks/sys.innodb_lock_waits 的"慢查詢"，最好不要查詢performance_schema.data_locks/sys.innodb_lock_waits
4、MySQL 8.X sys.innodb_lock_waits檢視依賴performance_schema.data_locks，特殊場景下也會產生阻塞問題。MySQL 8.X 中，如果只想要獲取鎖的阻塞情況，可以查詢 performance_schema.data_lock_waits。而MySQL 5.7 查詢information_schema.INNODB_LOCKS不會造成阻塞。

來自 “ ITPUB部落格 ” ，連結：https://blog.itpub.net/31383567/viewspace-3003472/，如需轉載，請註明出處，否則將追究法律責任。

相關文章