GreatSQL 的刷新锁

GreatSQL 的刷新锁

前言

因为运维小伙伴执行dump备份命令,导致数据库卡住,很多会话都在waiting for table flush,基于这一故障,我对GreatSQL的刷新锁进行了研究。感兴趣的小伙伴请随我一探究竟吧。

刷新锁的症状

刷新锁问题的主要症状是数据库会进入嘎然而止的状态,所有需要使用部分或全部表的新查询都停下来等待刷新锁。要寻找的信号如下:

1.新查询的查询状态为Waiting for table flush。这可能出现在所有新查询中,也可能只出现在访问特定表的新查询中。

2.数据库连接数增多,最终可能由于连接数用尽,新连接失败。

3.至少有一个查询的运行时间晚于最早的刷新锁请求。

4.进程列表中可能有flush table语句,也可能flush table语句已经超时(超过lock_wait_timeout设置)或被取消(会话被Ctr +C 终止或被kill)。

刷新锁构建

本实验使用的GreatSQL版本: 8.0.32-25 GreatSQL (GPL)。

创建四个连接,第一个连接执行一个慢查询,第二个连接用于执行flush tables语句,第三个连接执行第一个连接中慢查询语句相关表的快速查询。第四个连接执行其他表的查询和插入:

Connection 1> select count(*) ,sleep(100) from t1;
Connection 2> flush tables;   (flush tables with read lock;)
Connection 3> select count(*) from t1;
Connection 4> select count(*) from t2;
​             insert into t2 values(5,'a');

刷新锁争用问题诊断及解决

1.flush tables 实验

Connection 2 执行flush tables 时 ,Connection 3 受阻塞,Connection 4成功执行。

使用sys.session视图来输出各会话运行情况,也可以使用show processlist 来展示。默认输出是按执行时间降序排列,这让 查询刷新锁争用之类的问题变得容易。

[root@GreatSQL][test]>select thd_id,conn_id,state,current_statement,statement_latency from sys.session where command='Query';
+--------+---------+-------------------------+-------------------------------------------------------------------+-------------------+
| thd_id | conn_id | state                   | current_statement                                                 | statement_latency |
+--------+---------+-------------------------+-------------------------------------------------------------------+-------------------+
|    116 |      61 | User sleep              | select count(*),sleep(100) from t1                                | 10.81 s           |
|    117 |      62 | Waiting for table flush | flush tables                                                      | 8.15 s            |
|    109 |      57 | Waiting for table flush | select count(*) from t1                                           | 3.91 s            |
|    118 |      63 | NULL                    | select thd_id,conn_id,state,cu ... .session where command='Query' | 71.49 ms          |
+--------+---------+-------------------------+-------------------------------------------------------------------+-------------------+
4 rows in set (0.07 sec)

从上面会话的查询结果可以看出,flush tables会话的状态是Waiting for table flush,在它之前有一个运行时间较长的查询,这是阻塞flush tables完成的查询。第三个查询的状态也是Waiting for table flush,说明flush tables语句又阻塞了其他查询。

而Connection4 未受影响,说明flush tables不影响其他表的读写操作。

当等待刷新锁成为问题时,这意味着有一条或多条查询阻塞了flush tables 语句获得刷新锁。由于flush tables语句需要一个排他锁,因此又会阻塞后续会话对相关表的共享锁或排他锁。

手动Ctr+C中断flush tables 会话后,再次查询各会话的运行情况。

[root@GreatSQL][test]>select thd_id,conn_id,state,current_statement,statement_latency from sys.session where command='Query';
+--------+---------+-------------------------+-------------------------------------------------------------------+-------------------+
| thd_id | conn_id | state                   | current_statement                                                 | statement_latency |
+--------+---------+-------------------------+-------------------------------------------------------------------+-------------------+
|    116 |      61 | User sleep              | select count(*),sleep(100) from t1                                | 20.10 s           |
|    109 |      57 | Waiting for table flush | select count(*) from t1                                           | 13.19 s           |
|    118 |      63 | NULL                    | select thd_id,conn_id,state,cu ... .session where command='Query' | 68.14 ms          |
+--------+---------+-------------------------+-------------------------------------------------------------------+-------------------+
3 rows in set (0.07 sec)

从查询结果可以看出,被flush table阻塞的查询依然还被阻塞着,这时候解决问题的办法就是结束第一个阻塞flush tables会话的慢查询。

2.flush table with read lock实验

Connection2 执行flush table with read lock 时,Connection 3 受阻塞,Connect 4的select成功,insert 被阻塞。

使用sys.session视图来输出各会话运行情况

[root@GreatSQL][test]>select thd_id,conn_id,state,current_statement,statement_latency from sys.session where command='Query';
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
| thd_id | conn_id | state                        | current_statement                                                 | statement_latency |
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
|    116 |      61 | User sleep                   | select count(*),sleep(100) from t1                                | 52.74 s           |
|    117 |      62 | Waiting for table flush      | flush table with read lock                                        | 26.36 s           |
|    109 |      57 | Waiting for table flush      | select count(*) from t1                                           | 22.00 s           |
|    124 |      69 | Waiting for global read lock | insert into t2 values(8,'b')                                      | 6.01 s            |
|    118 |      63 | NULL                         | select thd_id,conn_id,state,cu ... .session where command='Query' | 82.90 ms          |
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
5 rows in set (0.09 sec)

从上面结果看出, flush table with read lock 的会话状态为Waiting for table flush,Connection3 状态同样为Waiting for table flush,而Connect4的状态为Waiting for global read lock。

手动Ctr+C中断flush tables 会话后,再次查询各会话的运行情况。

[root@GreatSQL][test]>select thd_id,conn_id,state,current_statement,statement_latency from sys.session where command='Query';
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
| thd_id | conn_id | state                        | current_statement                                                 | statement_latency |
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
|    116 |      61 | User sleep                   | select count(*),sleep(100) from t1                                | 1.37 min          |
|    109 |      57 | Waiting for table flush      | select count(*) from t1                                           | 51.58 s           |
|    124 |      69 | Waiting for global read lock | insert into t2 values(8,'b')                                      | 35.57 s           |
|    118 |      63 | NULL                         | select thd_id,conn_id,state,cu ... .session where command='Query' | 65.26 ms          |
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
4 rows in set (0.06 sec)

发现Connection 3,Connection4 仍然受到阻塞。

Connection 1 查询结束后查询各会话运行情况

[root@GreatSQL][test]>select thd_id,conn_id,state,current_statement,statement_latency from sys.session where command='Query';
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
| thd_id | conn_id | state                        | current_statement                                                 | statement_latency |
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
|    124 |      69 | Waiting for global read lock | insert into t2 values(8,'b')                                      | 57.44 s           |
|    118 |      63 | executing                    | select thd_id,conn_id,state,cu ... .session where command='Query' | 2.06 ms           |
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
2 rows in set (0.06 sec)

Connection 3 成功执行,Connection 4的insert 仍然受到阻塞。

显示执行unlock tables 命令后,Connection4的insert才执行完成。

实验结论:

由上面两个实验得出,诊断刷新锁争用的问题时,只要有会话处于 Waiting for table flush状态,说明曾发生过刷新表的操作,无论当前能否看到flush tables的相关会话,而通常处于Waiting for table flush状态的会话之前发生的慢查询都有可能是造成 后续阻塞的原因。

flush tables with read lock语句要获取的全局读锁,在等待获取锁时,症状与flush tables语句差不多,不同的是:

1.flush tables with read lock等待获取锁及得到锁之后,都会阻止所有表的写入,而flush tables只是在执行过程中持有锁,它不会阻止长查询之外的其他表写操作

2.flush tables with read lock需要通过unlock tables 显示释放锁,而flush tables不需要。

为什么flush table或者flush tables with read lock 会话都结束了,后续的查询还是会被阻塞呢?

这是低版本表定义缓存(TDC)的原因,这两条命令都会close all open tables,将表版本推高(refresh_version +1), 但因为长查询线程的存在,导致旧表无法被close,在访问旧表时都会认为是旧版本,等待 TABLE cache flush,而refresh_version 的推高是不可逆的结果,也就是说即使发出flush table或flush tables with read lock 的会话中断了 ,但是实际产生的 TABLE flush 的效果还是存在的。

另外这个症状与隔离级别关系不大,笔者测试了READ COMMITTED, REPEATABLE READ两种隔离级别,症状都相同。

通常除了手动发出这两个命令,使用mysqldump工具进行备份时也会发出这两个命令。

mysqldump备份加哪种选项会触发命令flush tables

打开general log, 进行多次dump测试实验,发现有以下几种情况会触发flush tables命令

1.--flush-logs,--single-transaction 一起使用时,触发flush tables,flush tables with read lock

2.--source-data 不和--single-transaction搭配使用时,触发FLUSH /*!40101 LOCAL */ TABLES, FLUSH TABLES WITH READ LOCK

3.--flush-logs,--single-transaction,--source-data 这三个选项同时使用时,会触发FLUSH /*!40101 LOCAL */ TABLES, FLUSH TABLES WITH READ LOCK

DBA小伙伴要熟悉备份工具各个选项或者选项组合使用时带来效果,尽量避免在业务高峰进行备份操作。

结语

Flush table 的功能是关闭所有已经打开的表,强制关闭所有正在使用的表,然而,正在使用的表对象是不能关闭的,所以Flush Tables操作会被正在运行的SQL请求阻塞,而在Flush table 之后的SQL请求又会被Flush table会话阻塞,即使Flush table会话被取消了,这些发生在Flush table之后的SQL请求也还是会被阻塞。所以当会话出现大量waiting for table flush时,无论当前是否还存在flush table 命令,查询耗时比这些waiting会话更久的慢查询,将其kill掉才能解决问题。


Enjoy GreatSQL 😃

关于 GreatSQL

GreatSQL是适用于金融级应用的国内自主开源数据库,具备高性能、高可靠、高易用性、高安全等多个核心特性,可以作为MySQL或Percona Server的可选替换,用于线上生产环境,且完全免费并兼容MySQL或Percona Server。

相关链接: GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区:

社区博客有奖征稿详情:https://greatsql.cn/thread-100-1-1.html

技术交流群:

微信:扫码添加GreatSQL社区助手微信好友,发送验证信息加群

热门相关:腹黑boss霸宠:逃妻,吻我   爆萌宠妃:邪帝,要抱抱   娇妻太甜:禁欲总裁,别太急   灭世魔帝   神医娘亲:腹黑萌宝赖上门