MySQL通過(guò)performance_schema定位未提交事務(wù)所執(zhí)行的SQL

發(fā)布時(shí)間：2020-08-08 06:31:42 來(lái)源：ITPUB博客閱讀：533 作者：神諭丶欄目：MySQL數(shù)據(jù)庫(kù)

經(jīng)常會(huì)遇到這樣一個(gè)場(chǎng)景：
業(yè)務(wù)那邊覺(jué)得數(shù)據(jù)庫(kù)“很慢”，上去通過(guò)show processlist查看發(fā)現(xiàn)大量State為在等待lock，如：
Waiting for table metadata/level lock等

比如在執(zhí)行一個(gè)DDL時(shí)，發(fā)現(xiàn)被hang住，查看到目前進(jìn)程狀態(tài)，有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)

在實(shí)驗(yàn)環(huán)境里，我們很快就能定位到，應(yīng)該是id為585這個(gè)線程，但是無(wú)法知道正在執(zhí)行什么sql：
通過(guò)information_schema.innodb_trx\G，也不能查詢到具體執(zhí)行了什么sql。
通過(guò)簡(jiǎn)單的kill的確可以解決眼前的問(wèn)題，但如果繼續(xù)遇到該問(wèn)題，也難以定位具體內(nèi)容。

但其實(shí)，performance_schema.events_statements_current提供了相關(guān)信息，此處復(fù)現(xiàn)一下：

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)

發(fā)現(xiàn)被hang住

查看一下是否有事務(wù)未提交，可以發(fā)現(xiàn)的確有：
該事務(wù)內(nèi)的語(yǔ)句執(zhí)行完畢（處于Sleep），但未提交，就會(huì)看不到對(duì)應(yīng)的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)

只能根據(jù)trx_mysql_thread_id看到未提交的事務(wù)的process id，看一下processlist，INFO內(nèi)也沒(méi)有具體內(nèi)容：

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)

但只要打開(kāi)了P_S，就可以通過(guò)performance_schema.events_statements_current來(lái)查看到對(duì)應(yīng)的sql，包括已經(jīng)執(zhí)行完，但沒(méi)有提交的。

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)

當(dāng)然，在復(fù)雜的生產(chǎn)環(huán)境中，光憑上面的語(yǔ)句查出來(lái)的信息，是遠(yuǎn)遠(yuǎn)不夠的。

通過(guò)如下語(yǔ)句，可以擴(kuò)展show processlist的顯示結(jié)果，并提供對(duì)應(yīng)的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%';

結(jié)果：

+----------------+------+--------------------------------------------+---------+------+---------------------------------+
| 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)

也可以很容易定位到執(zhí)行的內(nèi)容是做了一個(gè)update操作未提交。

作者微信公眾號(hào)（持續(xù)更新）
MySQL通過(guò)performance_schema定位未提交事務(wù)所執(zhí)行的SQL

向AI問(wèn)一下細(xì)節(jié)

MySQL通過(guò)performance_schema定位未提交事務(wù)所執(zhí)行的SQL

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽