MySQL 8.x ,查詢performance_schema.data_locks造成整個例項 hang了?
問題描述
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle 例項表查詢Oracle
- vue例項+axios-天氣查詢VueiOS
- 快遞鳥查詢訂單例項單例
- sql 連線查詢例項(left join)三表連線查詢SQL
- SSM 框架快速整合例項--學生查詢SSM框架
- Mysql 巢狀查詢100例子MySql巢狀
- MySQL:一個奇怪的hang案例MySql
- 為什麼升級到 MySQL 8.x 後,帶有多個 IN 值的查詢會更昂貴?MySql
- 使用SSMS連線和查詢 SQL Server 例項SSMSQLServer
- PHP+Mysql查詢上一篇和下一篇文章例項PHPMySql
- MySQL 查詢字串的個數MySql字串
- 如何使用 MySQL 慢查詢日誌進行效能優化 - Profiling、mysqldumpslow 例項詳解MySql優化
- Linux中執行多個MySQL例項LinuxMySql
- MySQL查詢MySql
- 一條SQL完成跨資料庫例項Join查詢SQL資料庫
- MySQL查詢這一篇就夠了MySql
- 在Windows中執行多個MySQL例項WindowsMySql
- mysql like查詢 - 根據多個條件的模糊匹配查詢MySql
- Mysql設計與查詢的關鍵注意事項MySql
- 請教一個mysql查詢語句!!MySql
- 一個MySQL多表查詢的問題MySql
- MySQL - 資料查詢 - 簡單查詢MySql
- mysql查詢模型MySql模型
- MYsql 子查詢MySql
- 【MySQL】多表查詢MySql
- mysql多表查詢MySql
- MySQL子查詢MySql
- MySQL慢查詢MySql
- MySQL 多表查詢MySql
- mysql 模糊查詢MySql
- MySQL 慢查詢MySql
- mysql-分組查詢-子查詢-連線查詢-組合查詢MySql
- pytest 用例查詢原理
- Flowable啟動流程例項和查詢任務以及完成任務
- 盤點MySQL慢查詢的12個原因MySql
- mysql查詢結果多列拼接查詢MySql
- MySQL之連線查詢和子查詢MySql
- mysql查詢語句5:連線查詢MySql