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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- asm例項查詢asm相關檢視hang住解決方法ASM
- python連結mysql查詢資料例項PythonMySql
- oracle 例項表查詢Oracle
- flask查詢whois資訊例項Flask
- SQL查詢月、天、周、年(MySql的例項對比)MySql
- Python查詢MySQL進行遠端採集圖片例項PythonMySql
- 快遞鳥查詢訂單例項單例
- MySQL 查詢大表注意事項MySql
- Mysql大表查詢注意事項MySql
- sql 連線查詢例項(left join)三表連線查詢SQL
- 為什麼升級到 MySQL 8.x 後,帶有多個 IN 值的查詢會更昂貴?MySql
- vue例項+axios-天氣查詢VueiOS
- SSM 框架快速整合例項--學生查詢SSM框架
- 閃回查詢(基礎例項講解)
- 例項講解MySQL資料庫的查詢最佳化技術(轉)MySql資料庫
- MySQL 查詢字串的個數MySql字串
- 閃回事務查詢(基礎例項講解)
- 使用SSMS連線和查詢 SQL Server 例項SSMSQLServer
- MySQL:一個奇怪的hang案例MySql
- PHP+Mysql查詢上一篇和下一篇文章例項PHPMySql
- MySQL查詢這一篇就夠了MySql
- 查詢前100個含有根阻塞程式的Hang Chain程式指令碼AI指令碼
- SQL查詢連續號碼段的解法(例項)SQL
- [MySQL] - 聯表查詢,查詢一個不在另一個表的記錄MySql
- MySQL查詢MySql
- mysql like查詢 - 根據多個條件的模糊匹配查詢MySql
- 請教一個mysql查詢語句!!MySql
- 一個MySQL多表查詢的問題MySql
- 一條SQL完成跨資料庫例項Join查詢SQL資料庫
- 利用jQuery查詢子元素和父元素程式碼例項jQuery
- Excel函式應用例項:職工資訊查詢(轉)Excel函式
- 查詢有多少JOB正在執行,程式被hang住
- C語言連線mysql簡單查詢例項入門-mysql_init,mysql_real_connect,mysql_query,mysql_close等C語言MySql
- Mysql設計與查詢的關鍵注意事項MySql
- 儲存壞道造成資料庫hang住資料庫
- MySQL - 資料查詢 - 簡單查詢MySql
- MySQL聯結查詢和子查詢MySql
- [Mysql 查詢語句]——查詢欄位MySql