阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

222次阅读
没有评论

共计 5178 个字符,预计需要花费 13 分钟才能阅读完成。

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

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计5178字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中