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

張衝andy發表於2024-01-11

問題描述

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章