經(jīng)常會(huì)遇到這樣一個(gè)場景:
業(yè)務(wù)那邊覺得數(shù)據(jù)庫“很慢”,上去通過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| Waitingfor table metadata lock| altertable t change name namevarchar(32) |
-
| 590| root| localhost| test| Query | 0 | starting | show processlist |
-
+-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
-
3 rowsin set (0.00 sec)
在實(shí)驗(yàn)環(huán)境里,我們很快就能定位到,應(yīng)該是id為585這個(gè)線程,但是無法知道正在執(zhí)行什么sql:
通過information_schema.innodb_trx\G,也不能查詢到具體執(zhí)行了什么sql。
通過簡單的kill的確可以解決眼前的問題,但如果繼續(xù)遇到該問題,也難以定位具體內(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 SETname='fasdfsad';
-
Query OK, 3 rows affected(0.00 sec)
-
Rows matched: 3 Changed: 3 Warnings: 0
-
session2> ALTER TABLE t CHANGEname name varchar(32)
發(fā)現(xiàn)被hang住
查看一下是否有事務(wù)未提交,可以發(fā)現(xiàn)的確有:
該事務(wù)內(nèi)的語句執(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 rowin set (0.00 sec)
只能根據(jù)trx_mysql_thread_id看到未提交的事務(wù)的process id,看一下processlist,INFO內(nè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 | Waitingfor table metadata lock| ALTERTABLE t CHANGE name namevarchar(32) |
-
| 590| root| localhost| test| Query | 0 | starting | SHOW PROCESSLIST |
-
+-----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------+
-
3 rowsin set (0.00 sec)
但只要打開了P_S,就可以通過performance_schema.events_statements_current來查看到對(duì)應(yīng)的sql,包括已經(jīng)執(zhí)行完,但沒有提交的。
-
session3> SELECT sql_textFROM performance_schema.events_statements_current;
-
+-------------------------------------------------------------------+
-
| sql_text |
-
+-------------------------------------------------------------------+
-
| UPDATE tSET name='fasdfsad' |
-
| ALTERTABLE t CHANGE name namevarchar(32) |
-
| select sql_textfrom performance_schema.events_statements_current|
-
+-------------------------------------------------------------------+
-
3 rowsin set (0.00 sec)
當(dāng)然,在復(fù)雜的生產(chǎn)環(huán)境中,光憑上面的語句查出來的信息,是遠(yuǎn)遠(yuǎn)不夠的。
通過如下語句,可以擴(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_textNOT LIKE '%performance%';
結(jié)果:
-
+----------------+------+--------------------------------------------+---------+------+---------------------------------+
-
| processlist_id| db | sql_text | command| time | state |
-
+----------------+------+--------------------------------------------+---------+------+---------------------------------+
-
| 585 | test| UPDATE tSET name='fasdfsad' | Sleep | 243 | |
-
| 586 | test| ALTERTABLE t CHANGE name namevarchar(32) | Query | 238 | Waitingfor table metadata lock|
-
+----------------+------+--------------------------------------------+---------+------+---------------------------------+
-
2 rowsin set (0.01 sec)
也可以很容易定位到執(zhí)行的內(nèi)容是做了一個(gè)update操作未提交。
作者微信公眾號(hào)(持續(xù)更新)
新聞標(biāo)題:MySQL通過performance_schema定位未提交事務(wù)所執(zhí)行的SQL
新聞來源:http://m.rwnh.cn/article42/pedoec.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供定制網(wǎng)站、網(wǎng)站設(shè)計(jì)公司、手機(jī)網(wǎng)站建設(shè)、虛擬主機(jī)、品牌網(wǎng)站制作、品牌網(wǎng)站設(shè)計(jì)
廣告
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場,如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源:
創(chuàng)新互聯(lián)