azure mysql 实时同步_Azure MySQL 阻塞排查及性能调优方法

通过如下语句，在问题时段找出未提交事务的语句：

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx

备注

trx_started：事务是执行的起始时间，若时间较长，则要分析该事务是否合理。

可以用如下语句查看各个锁之间的等待关系：

查看当前出现的锁：

select * from information_schema.INNODB_LOCKS

查看事务之间的锁等待关系：

select * from information_schema.innodb_lock_waits

查看当前正在运行的事务：

select * from information_schema.innodb_trx

使用如下语句查看与 trx_mysql_thread_id 相同的正在运行的事务的 User 和相关信息：

show full processlist;

将以下语句中的 porcesslistid 替换成上图中的 id 可以查看更详细的信息：

SELECT a.sql_text,

c.id,

d.trx_started

FROM performance_schema.events_statements_current a

join performance_schema.threads b

ON a.thread_id = b.thread_id

join information_schema.processlist c

ON b.processlist_id = c.id

join information_schema.innodb_trx d

ON c.id = d.trx_mysql_thread_id

where c.id=

ORDER BY d.trx_started;

使用如下语句查看当前的阻塞，以及阻塞和进程之间的关系，以及是哪个 User 造成的：

select request.trx_mysql_thread_id rprocessid,

request.trx_id request_trxid,

relock.lock_type,

blocks.trx_id blockstrx_id,

blocks.trx_rows_locked,

blocks.trx_mysql_thread_id bprocessid,

threads.THREAD_ID bTHREAD_ID,

threads.PROCESSLIST_USER bPROCESSLIST_USER,

threads.PROCESSLIST_HOST bPROCESSLIST_HOST,

threads.PROCESSLIST_DB bPROCESSLIST_DB

from information_schema.INNODB_LOCK_WAITS innolock

left join information_schema.INNODB_TRX request on innolock.requesting_trx_id=request.trx_id

left join information_schema.INNODB_TRX blocks on innolock.blocking_trx_id=blocks.trx_id

left join `performance_schema`.threads threads on blocks.trx_mysql_thread_id = threads.PROCESSLIST_ID

left join information_schema.INNODB_LOCKS relock on request.trx_requested_lock_id=relock.lock_id

left join information_schema.INNODB_LOCKS block on block.lock_id=blocks.trx_requested_lock_id;

在问题存在的时段运行如下语句，可帮助找到阻塞的源头 thread-ID(表中的这一列 blocked_thread_id)：

SELECT b.trx_mysql_thread_id AS 'blocked_thread_id'

,b.trx_query AS 'blocked_sql_text'

,c.trx_mysql_thread_id AS 'blocker_thread_id'

,c.trx_query AS 'blocker_sql_text'

,( Unix_timestamp() - Unix_timestamp(c.trx_started)) AS 'blocked_time'

FROM information_schema.innodb_lock_waits a

INNER JOIN information_schema.innodb_trx b

ON a.requesting_trx_id = b.trx_id

INNER JOIN information_schema.innodb_trx c

ON a.blocking_trx_id = c.trx_id

WHERE ( Unix_timestamp() - Unix_timestamp(c.trx_started) ) > 4;