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社区GiteeGitHubBilibili
GreatSQL社区:
社区博客有奖征稿详情:https://greatsql.cn/thread-100-1-1.html
技术交流群:
微信:扫码添加
GreatSQL社区助手
微信好友,发送验证信息加群
。