您好,登錄后才能下訂單哦!
課題:查看MySQL最近的事務(wù)執(zhí)行信息
*雖然我們可以通過查詢慢查詢?nèi)罩静樵兊揭粭l語句的執(zhí)行總時長,但是如果數(shù)據(jù)庫中存在一些大事務(wù)在執(zhí)行過程中回滾了,,或者在執(zhí)行過程中異常終止了,這個時候慢查詢?nèi)罩局惺遣粫涗浀?,這時需要借助
performance_schema的 eventstransactions—的表來查看與事務(wù)相關(guān)的記錄,在這些表中詳細(xì)記錄了是否有事務(wù)被回滾,活躍(長時間未提交的事務(wù)也屬于活躍事務(wù))活已提交等信息。**
下面模擬幾種事務(wù)情況,并查看事務(wù)事件記錄表:
事務(wù)事件,默認(rèn)是沒啟用的,首先需要進(jìn)行配置啟用
root@localhost [performance_schema]>select * from setup_instruments where name like 'transaction';
+-------------+---------+-------+
| NAME | ENABLED | TIMED |
+-------------+---------+-------+
| transaction | NO | NO |
+-------------+---------+-------+
1 row in set (0.00 sec)
root@localhost [performance_schema]>select * from setup_instruments where name like '%transaction%';
+--------------------------------------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+--------------------------------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/LOCK_transaction_cache | NO | NO |
| stage/sql/Waiting for preceding transaction to commit | NO | NO |
| stage/sql/Waiting for dependent transaction to commit | NO | NO |
| transaction | NO | NO |
| memory/performance_schema/events_transactions_summary_by_account_by_event_name | YES | NO |
| memory/performance_schema/events_transactions_summary_by_host_by_event_name | YES | NO |
| memory/performance_schema/events_transactions_summary_by_thread_by_event_name | YES | NO |
| memory/performance_schema/events_transactions_history | YES | NO |
| memory/performance_schema/events_transactions_summary_by_user_by_event_name | YES | NO |
| memory/performance_schema/events_transactions_history_long | YES | NO |
| memory/sql/THD::transactions::mem_root | YES | NO |
+--------------------------------------------------------------------------------+---------+-------+
11 rows in set (0.00 sec)
查看最近的事務(wù)執(zhí)行信息:
開啟事務(wù)生產(chǎn)者的參數(shù):
root@localhost [performance_schema]>update setup_instruments set enabled='yes',timed='yes' where name like 'transaction';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost [performance_schema]>update setup_instruments set enabled='yes' where name like '%transaction%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 11 Changed: 3 Warnings: 0
登錄mysql會話1,執(zhí)行清理,避免其他事務(wù)的干擾:
提示:線上最好不要直接truncate清除
root@localhost [performance_schema]>truncate events_transactions_current;truncate events_transactions_history; truncate events_transactions_history_long;
root@localhost [performance_schema]>select * from events_transactions_current;
Empty set (0.00 sec)
root@localhost [performance_schema]>select * from events_transactions_history;
Empty set (0.00 sec)
root@localhost [performance_schema]>select * from events_transactions_history_long;
Empty set (0.00 sec)
開啟新的會話2,用于執(zhí)行事務(wù),并模擬事務(wù)的回滾:
root@localhost [test001]>begin;
Query OK, 0 rows affected (0.00 sec)
root@localhost [test001]>update test1_event set username='fox' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost [test001]>rollback;
Query OK, 0 rows affected (0.00 sec)
在會話1查看當(dāng)前活躍的事務(wù),查看結(jié)果卻是空值:
root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_current\G
Empty set (0.00 sec)
root@localhost [(none)]>
root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_history_long\G
Empty set (0.00 sec)
root@localhost [(none)]>
root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_history
Empty set (0.00 sec)
root@localhost [(none)]>
查看結(jié)果卻是空值,原因是MySQL消費(fèi)者參數(shù)setup_consumers這一塊沒開啟
root@localhost [performance_schema]>select * from setup_consumers where name like '%transaction%';
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_transactions_current | NO |
| events_transactions_history | NO |
| events_transactions_history_long | NO |
+----------------------------------+---------+
3 rows in set (0.01 sec)
開啟消費(fèi)者參數(shù):
root@localhost [performance_schema]>update setup_consumers set enabled='yes' where name like '%transaction%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
root@localhost [performance_schema]>select * from setup_consumers where name like '%transaction%';
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_transactions_current | YES |
| events_transactions_history | YES |
| events_transactions_history_long | YES |
+----------------------------------+---------+
3 rows in set (0.00 sec)
這些消費(fèi)參數(shù)在分析完事務(wù)后記得關(guān)閉,關(guān)閉命令如下:
update setup_consumers set enabled='no' where name like '%transaction%';
再次在會話1查看當(dāng)前活躍的事務(wù):
root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_current\G
*************************** 1. row ***************************
THREAD_ID: 3957325
EVENT_NAME: transaction
STATE: ACTIVE
TRX_ID: NULL
GTID: AUTOMATIC
SOURCE:
TIMER_WAIT: 85087447373000
ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: REPEATABLE READ
AUTOCOMMIT: NO
NESTING_EVENT_ID: 27
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)
在會話2 上rollback回滾事務(wù):
root@localhost [test001]>begin;
Query OK, 0 rows affected (0.00 sec)
root@localhost [test001]>update test1_event set username='fox' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost [test001]>rollback;
Query OK, 0 rows affected (0.00 sec)
再次在會話1查看當(dāng)前活躍的事務(wù):
root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_current\G
*************************** 1. row ***************************
THREAD_ID: 3957325
EVENT_NAME: transaction
STATE: ROLLED BACK
TRX_ID: NULL
GTID: AUTOMATIC
SOURCE:
TIMER_WAIT: 170837979344000
ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: REPEATABLE READ
AUTOCOMMIT: NO
NESTING_EVENT_ID: 27
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)
查看事務(wù)事件歷史記錄表:events_transactions_history:
root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_history\G
*************************** 1. row ***************************
THREAD_ID: 3957325
EVENT_NAME: transaction
STATE: ROLLED BACK
TRX_ID: NULL
GTID: AUTOMATIC
SOURCE:
TIMER_WAIT: 170837979344000
ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: REPEATABLE READ
AUTOCOMMIT: NO
NESTING_EVENT_ID: 27
NESTING_EVENT_TYPE: STATEMENT
2 rows in set (0.00 sec)
root@localhost [test001]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from performance_schema.events_transactions_history_long where STATE='ROLLED BACK'\G
*************************** 1. row ***************************
THREAD_ID: 3957325
EVENT_NAME: transaction
STATE: ROLLED BACK
TRX_ID: NULL
GTID: AUTOMATIC
SOURCE:
TIMER_WAIT: 170837979344000
ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: REPEATABLE READ
AUTOCOMMIT: NO
NESTING_EVENT_ID: 27
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)
++++++++++++++++++++++++++++=+++++++++++++++++++++++++
在會話2上模擬正常提交事務(wù):
開始事務(wù)
root@localhost [test001]>begin;
Query OK, 0 rows affected (0.00 sec)
root@localhost [test001]>update test1_event set username='fox' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
會話1上查看事務(wù)信息:
root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_current\G
*************************** 1. row ***************************
THREAD_ID: 3957325
EVENT_NAME: transaction
STATE: ACTIVE
TRX_ID: NULL
GTID: AUTOMATIC
SOURCE:
TIMER_WAIT: 18063808685000
ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: REPEATABLE READ
AUTOCOMMIT: NO
NESTING_EVENT_ID: 75
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)
在會話2上正常提交事務(wù)
root@localhost [test001]>commit;
Query OK, 0 rows affected (0.00 sec)
會話1上查看事務(wù)信息:
root@localhost [performance_schema]>
root@localhost [performance_schema]>select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_current\G
*************************** 1. row ***************************
THREAD_ID: 3957325
EVENT_NAME: transaction
STATE: COMMITTED ##事務(wù)已經(jīng)提交成功
TRX_ID: NULL
GTID: 64a062d1-2e92-11ea-847e-801844ed7bbc:2584551 ##事務(wù)正常提交后要記錄Gtid的
SOURCE:
TIMER_WAIT: 28245631402000
ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: REPEATABLE READ
AUTOCOMMIT: NO
NESTING_EVENT_ID: 75
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)
提示:如果一個事務(wù)長時間未提交(長時間處于ACTIVE狀態(tài))對于這情況,俺們可以查看表nformation_schema.innodb_trx 表直觀的看到事務(wù)是什么時候開始的,
以及產(chǎn)生這個事務(wù)的mysql的線程id號trx_mysql_thread_id: 3957292(也就是會話2的線程id號)
root@localhost [performance_schema]>select * from information_schema.innodb_trx \G
*************************** 1. row ***************************
trx_id: 2594744
trx_state: RUNNING
trx_started: 2020-02-13 17:53:23
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 3957292
performance_schema相關(guān)表意思具體可參考下面資料:
配置詳解 | performance_schema全方位介紹(二)
http://www.sohu.com/a/253338003_610509
應(yīng)用示例薈萃 | performance_schema全方位介紹
https://blog.csdn.net/woqutechteam/article/details/81114520
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。