Azure MySQL 阻塞排查及性能调优方法

项目
07/22/2019

本文将详细介绍 Azure MySQL 相关阻塞排查方法。用户遇到 MySQL 阻塞或者由于 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=<porcesslistid>
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;

Azure MySQL 阻塞排查及性能调优方法

其他资源

其他资源