MySQL5.6中怎么定位DDL被阻塞的问题

这篇文章将为大家详细讲解有关MySQL 5.6中怎么定位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操作,被阻塞。

  1. session1> begin;

  2. Query OK, rows affected (0.00 sec)


  3. session1> delete from slowtech.t1 where id=2;

  4. Query OK, 1 row affected (0.00 sec)


  5. session1> select * from slowtech.t1;

  6. +------+------+

  7. | id   | name |

  8. +------+------+

  9. |    1 | a    |

  10. +------+------+

  11. 1 row in set (0.00 sec)


  12. session1> update slowtech.t1 set name='c' where id=1;

  13. Query OK, 1 row affected (0.00 sec)

  14. Rows matched: 1  Changed: 1  Warnings:


  15. session2> alter table slowtech.t1 add c1 int; ##被阻塞


  16. session3> show processlist;

  17. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+

  18. | Id | User | Host      | db   | Command | Time | State                           | Info                               |

  19. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+

  20. |  2 | root | localhost | NULL | Sleep   |   51 |                                 | NULL                               |

  21. |  3 | root | localhost | NULL | Query   |     | starting                        | show processlist                   |

  22. |  4 | root | localhost | NULL | Query   |    9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |

  23. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+

  24. 3 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)

关于“MySQL 5.6中怎么定位DDL被阻塞的问题”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。


网站名称:MySQL5.6中怎么定位DDL被阻塞的问题
文章网址:http://ybzwz.com/article/gejcdi.html