mysql 监测更新事物_MySQL如何监控大事务

MySQL如何监控大事务

查看正在执行的事务

select * from sys.processlist

show processlist

select * from information_schema.processlist

select * from sys.session

select * from information_schema.innodb_trx;

select * from performance_schema.events_statements_current

查看未提交的事务

select * from information_schema.innodb_trx

两者结合一下得到

select trx_id,

INNODB_TRX.trx_state,

INNODB_TRX.trx_started,

se.conn_id as processlist_id,

trx_lock_memory_bytes,

se.user,

se.command,

se.state,

se.current_statement,

se.last_statement

from information_schema.INNODB_TRX,

sys.session as se

where trx_mysql_thread_id = conn_id;

select now(),

(

UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)

) diff_sec,

b.id,

b.user,

b.host,

b.db,

d.SQL_TEXT

from information_schema.innodb_trx a

inner join information_schema.PROCESSLIST b on a.TRX_MYSQL_THREAD_ID = b.id

and b.command = 'Sleep'

inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID

inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

select * from sys.innodb_lock_waits\G