在上一篇文章《mysql 5.7中如何定位ddl被阻塞的问题》中，对于ddl被阻塞问题的定位，我们主要是基于mysql 5.7新引入的performance_schema.metadata_locks表。提出的定位方法，颇有种"锦上添花"的意味，而且，也只适用于mysql 5.7开始的版本。
但在实际生产中，mysql 5.6还是占绝不多数。虽然mysql 8.0都已经ga了，但鉴于数据库的特殊性，在对待升级的这个事情上，相当一部分人还是秉持着一种“不主动”的态度。
既然mysql 5.6用者众多，有没有一种方法，来解决mysql 5.6的这个痛点呢？
还是之前的测试demo
会话1开启了事务并执行了三个操作，但未提交，此时，会话2执行了alter table操作，被阻塞。
session1> begin; query ok, 0 rows affected (0.00 sec) session1> delete from slowtech.t1 where id=2; query ok, 1 row affected (0.00 sec) session1> select * from slowtech.t1; +------+------+ | id | name | +------+------+ | 1 | a | +------+------+ row in set (0.00 sec) session1> update slowtech.t1 set name='c' where id=1; query ok, 1 row affected (0.00 sec) rows matched: 1 changed: 1 warnings: 0 session2> alter table slowtech.t1 add c1 int; ##被阻塞 session3> show processlist; +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+ | id | user | host | db | command | time | state | info | +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+ | 2 | root | localhost | null | sleep | 51 | | null | | 3 | root | localhost | null | query | 0 | starting | show processlist | | 4 | root | localhost | null | query | 9 | waiting for table metadata lock | alter table slowtech.t1 add c1 int | +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+ rows in set (0.00 sec)
其实，导致ddl阻塞的操作，无非两类：
1. 慢查询
2. 表上有事务未提交
其中，第一类比较好定位，通过show processlist即能发现。而第二类基本没法定位，因为未提交事务的连接在show processlist中的输出同空闲连接一样。
如下面id为2的连接，虽然command显示为“sleep”，其实是事务未提交。
mysql> show processlist; +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+ | id | user | host | db | command | time | state | info | +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+ | 2 | root | localhost | null | sleep | 77 | | null | | 3 | root | localhost | null | query | 0 | starting | show processlist | | 4 | root | localhost | null | query | 44 | waiting for table metadata lock | alter table slowtech.t1 add c1 int | +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+ 3 rows in set (0.00 sec)
所以，网上有kill空闲（command为sleep）连接的说法，其实也不无道理，但这样做就太简单粗暴了，难免会误杀。
其实，既然是事务，在information_schema. innodb_trx中肯定会有记录，如会话1中的事务，在表中的记录如下，
我要评论mysql> select * from information_schema.innodb_trx\g
*************************** 1. row ***************************
trx_id: 1050390
trx_state: running
trx_started: 2018-07-17 08:55:32
trx_requested_lock_id: null
trx_wait_started: null
trx_weight: 4
trx_mysql_thread_id: 2
trx_query: null
trx_operation_state: null
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 3
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: repeatable read
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: null
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
其中trx_mysql_thread_id是线程id，结合performance_schema.threads，可以知道当前哪些连接上存在着活跃事务，这样就进一步缩小了可被kill的线程范围。
但从影响程度上，和kill所有command为sleep的连接没太大区别，毕竟，kill真正的空闲连接对业务的影响不大。
此时，依然可以借助performance_schema. events_statements_history表。
在上篇mysql 5.7的分析中，我们是首先知道引发阻塞的线程id，然后利用events_statements_history表，查看该线程的相关sql。
而在mysql 5.6中，我们并不知道引发阻塞的线程id，但是，我们可以反其道而行之，利用穷举法，首先统计出所有线程在当前事务执行过的所有sql，然后再判断这些sql中是否包含目标表。
具体sql如下，
select
processlist_id,
sql_text
from
(
select
c.processlist_id,
substring_index( sql_text, "transaction_begin;",-1 ) sql_text
from
information_schema.innodb_trx a,
(
select
thread_id,
group_concat( case when event_name = 'statement/sql/begin' then "transaction_begin" else sql_text end order by event_id separator ";" ) as sql_text
from
performance_schema.events_statements_history
group by
thread_id
) b,
performance_schema.threads c
where
a.trx_mysql_thread_id = c.processlist_id
and b.thread_id = c.thread_id
) t
where
sql_text like '%t1%';
+----------------+---------------------------------------------------------------------------------------------------------+
| processlist_id | sql_text |
+----------------+---------------------------------------------------------------------------------------------------------+
| 2 | delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1 |
+----------------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
从输出来看，确实也达到了预期效果。
需要注意的是，在mysql5.6中，events_statements_history默认是没有开启的。
mysql> select * from performance_schema.setup_consumers where name like '%statements%'; +--------------------------------+---------+ | name | enabled | +--------------------------------+---------+ | events_statements_current | yes | | events_statements_history | no | | events_statements_history_long | no | | statements_digest | yes | +--------------------------------+---------+ 4 rows in set (0.00 sec)
如对本文有疑问， 点击进行留言回复！！
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火，普罗米修斯的崛起
网友评论