MySQL运维系列之如何监控大事务-白红宇

long transaction

背景

大家有没有遇到这样的情况

某个SQL执行特别慢，导致整个transaction一直处于running阶段

某个Session的SQL已经执行完了，但是迟迟没有commit，一直处于sleep阶段

某个Session处于lock wait阶段，迟迟没有结束

以上，大部分原因都是大事务导致的，接下来我们好好聊聊相关话题

关键字

环境

1. MySQL5.7.22    低版本MySQL这边不再考虑，就像还有使用SAS盘的公司一样，费时费力，MySQL5.7+ 标配2. InnoDB存储引擎3. CentOS 6

大事务的相关特征

1. transaction开启到结束的时间非常长，我们这边举例为10s2. 正在执行的事务3. 未提交的事务

实战

如何监控那些正在执行的事务

1. select * from sys.processlist2. show processlist3. select * from information_schema.processlist4. select * from sys.session5. select * from information_schema.innodb_trx;6. 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;+---------+-----------+---------------------+----------------+-----------------------+------+---------+----------+-----------------------------------+-----------------------------------+| trx_id  | trx_state | trx_started         | processlist_id | trx_lock_memory_bytes | user | command | state    | current_statement                 | last_statement                    |+---------+-----------+---------------------+----------------+-----------------------+------+---------+----------+-----------------------------------+-----------------------------------+| 1592104 | LOCK WAIT | 2018-06-26 11:51:17 |              3 |                  1136 | NULL | Query   | updating | update lc_1 set id=4 where id = 1 | NULL                              || 1592100 | RUNNING   | 2018-06-26 11:49:08 |              2 |                  1136 | NULL | Sleep   | NULL     | NULL                              | update lc_1 set id=3 where id = 1 |+---------+-----------+---------------------+----------------+-----------------------+------+---------+----------+-----------------------------------+-----------------------------------+

大家可以看到，通过这个可以立马发现事务语句处于running阶段，哪些事务处于lock wait阶段，如果遇到这种情况，我们应该如何处理呢？

聪明的你，一定会去根据trx_started去寻找蛛丝马迹，可是如果再生产环境中，这是一件非常复杂和繁忙的事情

不过没关系，我们还有神器可以使用

如何快速解决锁等待问题

dba:sys> select * from sys.innodb_lock_waits\G*************************** 1. row ***************************                wait_started: 2018-06-26 11:49:58                    wait_age: 00:00:03               wait_age_secs: 3                locked_table: `lc`.`lc_1`                locked_index: GEN_CLUST_INDEX                 locked_type: RECORD              waiting_trx_id: 1592102         waiting_trx_started: 2018-06-26 11:49:58             waiting_trx_age: 00:00:03     waiting_trx_rows_locked: 2   waiting_trx_rows_modified: 0                 waiting_pid: 3               waiting_query: update lc_1 set id=4 where id = 1             waiting_lock_id: 1592102:32:3:4           waiting_lock_mode: X             blocking_trx_id: 1592100                blocking_pid: 2              blocking_query: NULL            blocking_lock_id: 1592100:32:3:4          blocking_lock_mode: X        blocking_trx_started: 2018-06-26 11:49:08            blocking_trx_age: 00:00:53    blocking_trx_rows_locked: 1  blocking_trx_rows_modified: 1     sql_kill_blocking_query: KILL QUERY 2sql_kill_blocking_connection: KILL 2

MySQL最终非常贴心都连kill SQL 语句都生产了，你只需要复制、粘贴即可

细心的你会发现，通过innodb_lock_waits你只能看到被lock的语句，但是看不到是哪个query语句拥有的锁，这又是为什么呢？

不卖关子，因为拥有锁的事务中可能拥有多条query语句，也可能已经执行完，但是没有commit，所以无法给出所有query语句。

那怎么办呢？哈哈，如果幸运的话，你可以根据我上述的案例 current_statement，last_statement 得到答案。

再换句话说，即便没有找到那条query，也不妨碍你解决当前的问题哈

总结

MySQL5.7 默默的提供了非常多的实用工具和新特性，需要DBA们去挖掘和探索。将看似平淡无奇的特性挖掘成黑武器，你才能成为那闪着光芒的Top5 MySQLer

工欲善其事必先利其器