Xtrabackup：和FTWRL相关参数的说明

可能还是经常遇到FTWRL堵塞备份的情况，这里稍微理了一下这部分，当然内容不多，仅供参考。
当然这部分主要是5.7先关比较紧密，到了8.0，如果没有MyISAM表使用是新的备份锁，可参考

//www.greatytc.com/p/9007883b58b8 MySQL：8.0新的备份锁的浅析及其重要BUG

相关测试和说明

当前状态数据库为5.7.40，没有业务的myisam表。

一、备份和全局锁风险

5.7的xtrabackup在备份主要分为如下：

开启redo备份线程
备份innodb表
备份完成innodb表后，执行FTWRL，全库上锁
备份myisam表
解锁
备份结束关闭redo备份线程

整个备份过程中执行FTWRL的风险最大堵塞面积最大如下，如果没有myisam表，其基本是在备份快要结束的时候执行的。

-- 被什么堵塞
长时间的DDL\DML\FOR UPDATE堵塞FTWRL，因为FTWRL需要获取 GLOBAL的S锁，而这些语句都会对GLOBAL持有IX（MDL_INTENTION_EXCLUSIVE）锁，根据兼容矩阵不兼容。等待为：Waiting for global read lock 。本文的案例1就是这种情况。
长时间的select堵塞FTWRL，因为FTWRL会释放所有空闲的table缓存，如果有占用者占用某些table缓存，则会等待占用者自己释放这些table缓存。等待为：Waiting for table flush 。本文的案例2就是这种情况，会堵塞随后关于本表的任何语句，即便KILL FTWRL会话也不行，除非KILL掉长时间的select操作才行。实际上flush table也会存在这种堵塞情况。
长时间的commit(如大事务提交)也会堵塞FTWRL，因为FTWRL需要获取COMMIT的S锁，而commit语句会对commit持有IX（MDL_INTENTION_EXCLUSIVE）锁，根据兼容矩阵不兼容。
-- 堵塞什么
FTWRL会堵塞DDL\DML\FOR UPDATE操作，堵塞点为 GLOBAL级别的S锁，等待为：Waiting for global read lock 。
FTWRL会堵塞commit操作，堵塞点为COMMIT的S锁，等待为Waiting for commit lock 。
FTWRL不会堵塞select操作，因为select不会在GLOBAL级别上锁。

二、FTWRL执行前规避

xtrabackup可以进行提前检测数据库中是否有长时间运行的语句，来规避执行FTWRL，如果不执行FTWRL则不会导致堵塞，参数如下：

1、ftwrl-wait-threshold
默认为60秒，也就是通过show processlist中语句执行时间的time进行判定，大于多少秒的语句定义为长查询。

2、ftwrl-wait-timeout
默认为0，代表不开启本类判定，可以设置为秒，主要用于在执行FTWRL之前根据数据库中是否有长语句(包含DML/DDL/SELECT)可能导致堵塞FTWRL。如果从准备执行FTWRL的时间点+ftwrl-wait-timeout设置的时间，这些长查询依旧没有结束则停止备份退出。

3、ftwrl-wait-query-type
默认为ALL，就是包含了DDL/DML/SELECT等语句都计入长查询判定的范畴。可以设置为SELECT，则排除其他类型的语句。这个选项保证为ALL比较好，因为还没有实际的执行FTWRL，只是影响判定的范围而已。

因此我们通过这3个参数配合来最小化可能造成的堵塞，如下备份语句

./innobackupex --defaults-file=/opt/mysql5740/install/my.cnf --socket='/opt/mysql5740/tmp/mysql3310.sock' --ftwrl-wait-timeout=60 ftwrl-wait-threshold=20 /newdata/bak

当数据库中show processlist中有时间查过20秒的语句则等待语句执行完成，等待超时为60秒，如果超过60秒还是没有执行完成则退出备份。

三、FTWRL执行后风险

如果FTWRL一旦执行成功，则会备份myisam表，查询数据库中没有大量的myisam表，应该可以快速释放，堵塞时间应该很短
同时在xtrabackup中也有另外一批参数，用于如果FTWRL被堵塞就进行kill堵塞的语句，如下，

kill-long-queries-timeout
默认为0，代表不开启这类判定，可以设置为秒，主要是如果监控FTWRL是否被堵塞，如果从发起FTWRL开始+kill-long-queries-timeout依旧没有完成FTWRL，则说明被堵塞了，就需要杀掉数据库中的长SQL，也就是可能堵塞FTWRL的语句，查杀的范围很较广，判定时间的标准依旧为show processlist中语句执行时间的time大于了kill-long-queries-timeout设置则会杀掉。

kill-long-query-type
默认为为ALL（8.0.26的XTRABACKUP默认修改为了SELECT），就是包含了DDL/DML/SELECT等语句都计入被杀掉的范畴。可以设置为SELECT，则排除其他类型的语句。这个选项保险起见就是设置为SELECT，排除杀掉其他类的语句。

不建议设置这批参数，因为会对业务session进行干预。

四、测试

使用官方5740版本和xtrabackup2.4.28进行备份，首先通过sleep语句模拟长时间执行的语句

mysql> select sleep(1) from testi;

mysql> select id,time,STATE,info from information_schema.processlist where command<>'sleep';
+----+------+--------------------------------------------------------+--------------------------------------------------------------------------------------+
| id | time | STATE                                                  | info                                                                                 |
+----+------+--------------------------------------------------------+--------------------------------------------------------------------------------------+
| 12 | 1651 | User sleep                                             | select sleep(1) from testi                                                           |

这里看到sleep(1)语句执行时间已经比较长了为1651秒，备份使用语句

./innobackupex --defaults-file=/opt/mysql5740/install/my.cnf --socket='/opt/mysql5740/tmp/mysql3310.sock' --ftwrl-wait-timeout=60 ftwrl-wait-threshold=20 /newdata/bak

这里明显1651秒>(ftwrl-wait-threshold=20)，因此备份会被堵塞，备份日志如下，

240313 11:04:08 [01] Copying ./test/testi.ibd to /newdata/bak/2024-03-13_11-04-07/test/testi.ibd
240313 11:04:08 [01]        ...done
240313 11:04:09 >> log scanned up to (3177498)
240313 11:04:09 Waiting 60 seconds for queries running longer than 20 seconds to finish
240313 11:04:09 Waiting for query 12 (duration 31 sec): select sleep(1) from testi240313 11:04:10 >> log scanned up to (3177498)
240313 11:04:10 Waiting for query 12 (duration 32 sec): select sleep(1) from testi240313 11:04:11 >> log scanned up to (3177498)
...
240313 11:05:09 Waiting for query 12 (duration 91 sec): select sleep(1) from testi240313 11:05:10 >> log scanned up to (3177498)
240313 11:05:10 Unable to obtain lock. Please try again later.

可以看到在等待(--ftwrl-wait-timeout=60)的60秒后，语句依旧没有完成，则退出了备份，同时查看general log发现，没有加锁日志。
下面是正常备份的加锁日志，

2024-03-13T11:02:46.102453+08:00           14 Query     FLUSH TABLES WITH READ LOCK
2024-03-13T11:02:47.321805+08:00           14 Query     SHOW MASTER STATUS
2024-03-13T11:02:47.322073+08:00           14 Query     SHOW VARIABLES
2024-03-13T11:02:47.327517+08:00           14 Query     FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS

也就是说FTWRL 没有执行。

五、其他风险

ftwrl-wait-threshold=20
如果在FTWRL判定的时候刚好有一个长查询开始执行，这个时候可能time没有超过20秒，但是FTWRL依旧会执行，并且堵塞其他语句，不过这是小概率事件。
系统IO负载风险
系统CPU负载风险

Xtrabackup：和FTWRL相关参数的说明