mysql 模拟 数据库阻塞_[数据库]MySQL 5.6中如何定位DDL被阻塞的问题
[数据库]MySQL 5.6中如何定位DDL被阻塞的问题
0 2018-08-21 22:00:36
在上一篇文章《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: 0session2> 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: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0trx_autocommit_non_locking: 01 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)
本文网址:http://www.shaoqun.com/a/379792.html
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:admin@shaoqun.com。
MYSQL
0