MySQL通過performance_schema定位未提交事務所執行的SQL
經常會遇到這樣一個場景:
業務那邊覺得資料庫“很慢”,上去通過show processlist檢視發現大量State為在等待lock,如:
Waiting for table metadata/level lock等
比如在執行一個DDL時,發現被hang住,檢視到目前程式狀態,有MDL
在實驗環境裡,我們很快就能定位到,應該是id為585這個執行緒,但是無法知道正在執行什麼sql:
通過information_schema.innodb_trx\G,也不能查詢到具體執行了什麼sql。
通過簡單的kill的確可以解決眼前的問題,但如果繼續遇到該問題,也難以定位具體內容。
但其實,performance_schema.events_statements_current提供了相關資訊,此處復現一下:
發現被hang住
檢視一下是否有事務未提交,可以發現的確有:
該事務內的語句執行完畢(處於Sleep),但未提交,就會看不到對應的trx_query:
只能根據trx_mysql_thread_id看到未提交的事務的process id,看一下processlist,INFO內也沒有具體內容:
但只要開啟了P_S,就可以通過performance_schema.events_statements_current來檢視到對應的sql,包括已經執行完,但沒有提交的。
當然,在複雜的生產環境中,光憑上面的語句查出來的資訊,是遠遠不夠的。
通過如下語句,可以擴充套件show processlist的顯示結果,並提供對應的SQL。
結果:
也可以很容易定位到執行的內容是做了一個update操作未提交。
作者微信公眾號(持續更新)
業務那邊覺得資料庫“很慢”,上去通過show processlist檢視發現大量State為在等待lock,如:
Waiting for table metadata/level lock等
比如在執行一個DDL時,發現被hang住,檢視到目前程式狀態,有MDL
-
mysql> SHOW PROCESSLIST;
-
+-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
-
| Id | User | Host | db | Command | Time | State | Info |
-
+-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
-
| 585 | root | localhost | test | Sleep | 1658 | | NULL |
-
| 586 | root | localhost | test | Query | 1654 | Waiting for table metadata lock | alter table t change name name varchar(32) |
-
| 590 | root | localhost | test | Query | 0 | starting | show processlist |
-
+-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
- 3 rows in set (0.00 sec)
在實驗環境裡,我們很快就能定位到,應該是id為585這個執行緒,但是無法知道正在執行什麼sql:
通過information_schema.innodb_trx\G,也不能查詢到具體執行了什麼sql。
通過簡單的kill的確可以解決眼前的問題,但如果繼續遇到該問題,也難以定位具體內容。
但其實,performance_schema.events_statements_current提供了相關資訊,此處復現一下:
-
session1> BEGIN;
-
Query OK, 0 rows affected (0.00 sec)
-
-
session1> UPDATE t SET name='fasdfsad';
-
Query OK, 3 rows affected (0.00 sec)
- Rows matched: 3 Changed: 3 Warnings: 0
- session2> ALTER TABLE t CHANGE name name varchar(32)
檢視一下是否有事務未提交,可以發現的確有:
該事務內的語句執行完畢(處於Sleep),但未提交,就會看不到對應的trx_query:
-
session3> SELECT * FROM information_schema.innodb_trx\G
-
*************************** 1. row ***************************
-
trx_id: 9614
-
trx_state: RUNNING
-
trx_started: 2017-09-19 15:58:05
-
trx_requested_lock_id: NULL
-
trx_wait_started: NULL
-
trx_weight: 2
-
trx_mysql_thread_id: 585
-
trx_query: NULL
-
trx_operation_state: NULL
-
trx_tables_in_use: 0
-
trx_tables_locked: 1
-
trx_lock_structs: 2
-
trx_lock_memory_bytes: 1136
-
trx_rows_locked: 4
-
trx_rows_modified: 0
-
trx_concurrency_tickets: 0
-
trx_isolation_level: REPEATABLE READ
-
trx_unique_checks: 1
-
trx_foreign_key_checks: 1
-
trx_last_foreign_key_error: NULL
-
trx_adaptive_hash_latched: 0
-
trx_adaptive_hash_timeout: 0
-
trx_is_read_only: 0
-
trx_autocommit_non_locking: 0
- 1 row in set (0.00 sec)
只能根據trx_mysql_thread_id看到未提交的事務的process id,看一下processlist,INFO內也沒有具體內容:
-
session3> SHOW PROCESSLIST;
-
+-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
-
| Id | User | Host | db | Command | Time | State | Info |
-
+-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
-
| 585 | root | localhost | test | Sleep | 42 | | NULL |
-
| 586 | root | localhost | test | Query | 37 | Waiting for table metadata lock | ALTER TABLE t CHANGE name name varchar(32) |
-
| 590 | root | localhost | test | Query | 0 | starting | SHOW PROCESSLIST |
-
+-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
- 3 rows in set (0.00 sec)
但只要開啟了P_S,就可以通過performance_schema.events_statements_current來檢視到對應的sql,包括已經執行完,但沒有提交的。
-
session3> SELECT sql_text FROM performance_schema.events_statements_current;
-
+-------------------------------------------------------------------+
-
| sql_text |
-
+-------------------------------------------------------------------+
-
| UPDATE t SET name='fasdfsad' |
-
| ALTER TABLE t CHANGE name name varchar(32) |
-
| select sql_text from performance_schema.events_statements_current |
-
+-------------------------------------------------------------------+
- 3 rows in set (0.00 sec)
通過如下語句,可以擴充套件show processlist的顯示結果,並提供對應的SQL。
-
SELECT b.processlist_id, c.db, a.sql_text, c.command, c.time, c.state
-
FROM performance_schema.events_statements_current a JOIN performance_schema.threads b USING(thread_id)
-
JOIN information_schema.processlist c ON b.processlist_id = c.id
- WHERE a.sql_text NOT LIKE '%performance%';
結果:
-
+----------------+------+--------------------------------------------+---------+------+---------------------------------+
-
| processlist_id | db | sql_text | command | time | state |
-
+----------------+------+--------------------------------------------+---------+------+---------------------------------+
-
| 585 | test | UPDATE t SET name='fasdfsad' | Sleep | 243 | |
-
| 586 | test | ALTER TABLE t CHANGE name name varchar(32) | Query | 238 | Waiting for table metadata lock |
-
+----------------+------+--------------------------------------------+---------+------+---------------------------------+
- 2 rows in set (0.01 sec)
作者微信公眾號(持續更新)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29773961/viewspace-2145185/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL Server 查出未提交事務(長事務)SQLSQLServer
- MySQL 事務提交過程MySql
- MySQL innodb引擎的事務執行過程MySql
- MySQL InnoDB Cluster如何定位或找出超過事務大小的SQL?MySql
- MySQL——通過EXPLAIN分析SQL的執行計劃MySqlAI
- 未提交事務造成的等待事件事件
- MySQL 執行原理【事務】MySql
- MySQL事務兩段式提交MySql
- 檢視mysql沒提交的事務MySql
- MySql定位執行效率較低的SQL語句MySql
- 通過日誌檢視mysql正在執行的SQL語句MySql
- 探究MySQL的DML提交事務的意義和DQL是否有必要提交事務MySql
- MySQL事務提交流程概述MySql
- 定位SQL的執行次數SQL
- 在事務中執行sql語句SQL
- MySQl事務建立,開始以及提交MySql
- 查詢oracle正在執行的SQL和事務OracleSQL
- MySQL實現事務的提交和回滾MySql
- mysql執行sql語句過程MySql
- 列舉mysql正在執行中的全部事務MySql
- MySQL 中一條 sql 的執行過程MySql
- MySQL事務提交的三個階段介紹MySql
- 通過qq進行ip定位
- 通過pl/sql計算程式的執行時間SQL
- MySQL 儲存過程中事務sql異常回滾MySql儲存過程
- 通過IP定位區域的SQL優化思路SQL優化
- MySQL:begin後事務為什麼不提交MySql
- 通過分析SQL語句的執行計劃優化SQL 二SQL優化
- 通過SQL_ID檢視SQL歷史執行資訊SQL
- Mysql之一次完成的sql執行過程MySql
- 通過spid,查詢執行慢的sql指令碼SQL指令碼
- 定位rac環境中某條sql執行時間過長SQL
- 通過分析SQL語句的執行計劃優化SQL(總結)SQL優化
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- Mysql 從庫如果有未提交的事務主庫ddl操作導致主從延遲MySql
- mysql隱式提交事務transaction一點筆記MySql筆記
- goldengate跳過/提交一個未完成的事務Go
- Oracle - 執行過的SQL、正在執行的SQL、消耗資源最多的SQLOracleSQL