欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL 5.6中如何定位DDL被阻塞的问题

程序员文章站 2022-06-23 14:06:18
在上一篇文章《MySQL 5.7中如何定位DDL被阻塞的问题》中,对于DDL被阻塞问题的定位,我们主要是基于MySQL 5.7新引入的performance_schema.metadata_locks表。提出的定位方法,颇有种"锦上添花"的意味,而且,也只适用于MySQL 5.7开始的版本。 但在实 ......

在上一篇文章《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)