技术分享 | 快速定位令人头疼的全局锁 - 技术分享 - 新闻资讯 - 爱可生

技术分享 | 快速定位令人头疼的全局锁

发布时间:2021-01-04 浏览次数:269

关键词:分布式关系型数据库分布式数据库 MySQL分布式数据库 分片


背景

在用 xtrabackup 等备份工具做备份时会有全局锁,正常情况锁占用时间很短,但偶尔会遇到锁长时间占用导致系统写入阻塞,现象是 show processlist 看到众多会话显示 wait global read lock,那可能对业务影响会很大。而且 show processlist 是无法看到哪个会话持有了全局锁,如果直接杀掉备份进程有可能进程杀掉了,但锁依然没释放,数据库还是无法写入。这时我们需要有快速定位持有全局锁会话的方法,杀掉对应会话数据库就恢复正常了。

通常这种紧急情况发生,需要 DBA 有能力快速恢复业务,如果平时没有储备,现找方法肯定是来不及的,所以我整理了几种方法,在实际故障中帮助我快速的定位到锁会话恢复了业务,非常有效,与大家分享。

方法

方法1:利用 metadata_locks 视图

此方法仅适用于 MySQL 5.7 以上版本,该版本 performance_schema 新增了 metadata_locks,如果上锁前启用了元数据锁的探针(默认是未启用的),可以比较容易的定位全局锁会话。过程如下。

  1. 开启元数据锁对应的探针

  2. mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

  3. Query OK, 1 row affected (0.04 sec)

  4. Rows matched: 1  Changed: 1  Warnings: 0


  5. 模拟上锁

  6. mysql> flush tables with read lock;

  7. Query OK, 0 rows affected (0.06 sec)


  8. mysql> select * from performance_schema.metadata_locks;

  9. +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+

  10. | OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |

  11. +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+

  12. | GLOBAL      | NULL               | NULL           |       140613033070288 | SHARED              | EXPLICIT      | GRANTED     | lock.cc:1110      |          268969 |             80 |

  13. | COMMIT      | NULL               | NULL           |       140612979226448 | SHARED              | EXPLICIT      | GRANTED     | lock.cc:1194      |          268969 |             80 |

  14. | GLOBAL      | NULL               | NULL           |       140612981185856 | INTENTION_EXCLUSIVE | STATEMENT     | PENDING     | sql_base.cc:3189  |          303901 |            665 |

  15. | TABLE       | performance_schema | metadata_locks |       140612983552320 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6030 |          268969 |             81 |

  16. +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+

  17. 4 rows in set (0.01 sec)


  18. OBJECT_TYPE=GLOBAL  LOCK_TYPE=SHARED 表示全局锁



  19. mysql> select t.processlist_id from performance_schema.threads t join performance_schema.metadata_locks ml on ml.owner_thread_id = t.thread_id where ml.object_type='GLOBAL' and ml.lock_type='SHARED';

  20. +----------------+

  21. | processlist_id |

  22. +----------------+

  23. |         268944 |

  24. +----------------+

  25. 1 row in set (0.00 sec)

定位到锁会话 ID 直接 kill 该会话即可。

方法2:利用 events_statements_history 视图
此方法适用于 MySQL 5.6 以上版本,启用 performance_schema.eventsstatements_history(5.6 默认未启用,5.7 默认启用),该表会 SQL 历史记录执行,如果请求太多,会自动清理早期的信息,有可能将上锁会话的信息清理掉。过程如下。

  1. mysql> update performance_schema.setup_consumers set enabled = 'YES' where NAME = 'events_statements_history'

  2. Query OK, 0 rows affected (0.00 sec)

  3. Rows matched: 1  Changed: 0  Warnings: 0


  4. mysql> flush tables with read lock;

  5. Query OK, 0 rows affected (0.00 sec)


  6. mysql> select * from performance_schema.events_statements_history where sql_text like 'flush tables%'\G

  7. *************************** 1. row ***************************

  8.              THREAD_ID: 39

  9.               EVENT_ID: 21

  10.           END_EVENT_ID: 21

  11.             EVENT_NAME: statement/sql/flush

  12.                 SOURCE: socket_connection.cc:95

  13.            TIMER_START: 94449505549959000

  14.              TIMER_END: 94449505807116000

  15.             TIMER_WAIT: 257157000

  16.              LOCK_TIME: 0

  17.               SQL_TEXT: flush tables with read lock

  18.                 DIGEST: 03682cc3e0eaed3d95d665c976628d02

  19.            DIGEST_TEXT: FLUSH TABLES WITH READ LOCK

  20. ...

  21.    NESTING_EVENT_LEVEL: 0

  22. 1 row in set (0.00 sec)


  23. mysql> select t.processlist_id from performance_schema.threads t join performance_schema.events_statements_history h on h.thread_id = t.thread_id where h.digest_text like 'FLUSH TABLES%';

  24. +----------------+

  25. | processlist_id |

  26. +----------------+

  27. |             12 |

  28. +----------------+

  29. 1 row in set (0.01 sec)


方法3:利用 gdb 工具
如果上述两种都用不了或者没来得及启用,可以尝试第三种方法。利用 gdb 找到所有线程信息,查看每个线程中持有全局锁对象,输出对应的会话 ID,为了便于快速定位,我写成了脚本形式。也可以使用 gdb 交互模式,但 attach mysql 进程后 mysql 会完全 hang 住,读请求也会受到影响,不建议使用交互模式。

  1. #!/bin/bash

  2. set -v


  3. threads=$(gdb -p $1 -q -batch -ex 'info threads'| awk '/mysql/{print $1}'|grep -v '*'|sort -nk1)

  4. for i in $threads; do

  5.  echo "######## thread $i ########"

  6.  lock=`gdb -p $1 -q -batch -ex "thread $i" -ex 'p do_command::thd->thread_id' -ex 'p do_command::thd->global_read_lock'|grep -B3 GRL_ACQUIRED_AND_BLOCKS_COMMIT`

  7.  if [[ $lock =~ 'GRL_ACQUIRED_AND_BLOCKS_COMMIT' ]]; then

  8.    echo "$lock"

  9.    break

  10.  fi

  11. done


  12. # thread_id变量,5.6和5.7版本有所不同,5.6版本是thd->thread_id,5.7版本是thd->m_thread_id,这里需要留意下

脚本输出

######## thread 2 ########[Switching to thread 2 (Thread 0x7f610812b700 (LWP 10702))]#0  0x00007f6129685f0d in poll () from /lib64/libc.so.6$1 = 9 此处就是mysql中的会话ID$2 = {static m_active_requests = 1, m_state = Global_read_lock::GRL_ACQUIRED_AND_BLOCKS_COMMIT, m_mdl_global_shared_lock = 0x7f60e800cb10, m_mdl_blocks_commits_lock = 0x7f60e801c900}

但实际环境可能会比较复杂,用 gdb 可能也无法获得你想要的信息,是不是就没辙了。


方法4:show processlist

如果备份程序使用的特定用户执行备份,如果是 root 用户备份,那 time 值越大的是持锁会话的概率越大,如果业务也用 root 访问,重点是 state 和 info 为空的,这里有个小技巧可以快速筛选,筛选后尝试 kill 对应 ID,再观察是否还有 wait global read lock 状态的会话。

mysql>pager awk '/username/{if (length($7) == 4) {print $0}}'|sort -rk6mysql>show processlist

如果以上方法全部无效,最后释放终极大招...


方法5:重启试试!
如果你有更好的方法,可以留言分享。
关键词:分布式关系型数据库分布式数据库 MySQL分布式数据库 分片


相关推荐

产品试用 产品试用
400-820-6580 免费电话