[译]如何估算MySQL中取消事务的回滚时间？

原文地址：https://mydbops.wordpress.com/2022/02/07/estimating-time-for-rollback-operation/

原文作者：Asuwini P

回滚是将事务的当前状态更改为前一状态的操作。如果我们想回滚任何未提交的事务，通常需要 undo 日志，它在隔离机制中起着重要的作用。
对于事务中所做的任何更改，必须事先存储，当我们选择回滚事务的时候，则需要这些记录。

完成数据修改后，将在 undo 日志中创建条目。如果事务使用 SQL 修改数据，它将为每个操作创建离散 undo 日志。当事务被提交后，MySQL 就可以清除在该事务中创建的 undo 日志。
要了解有关撤消日志的更多信息，您可以查看我们之前的博客，了解撤消日志概述。

如需了解更多有关 undo 日志的信息，您可以查看我们以前的博客以了解 undo 日志。
通常，回滚过程会比原始操作花费更多的时间，因为它是单线程的。

事务流程图参考

让我们思考一个包含 1 亿条记录的 sbest1 表的场景。我将删除条件为 id<=30000000 的 3000 万条记录。在此期间，我强制终止了删除操作。

mysql> show processlist;
+—-+—————–+———–+———+———+——+————————+—————————————-+
| Id | User            | Host      | db      | Command | Time | State                  | Info                                   |
+—-+—————–+———–+———+———+——+————————+—————————————-+
|  5 | event_scheduler | localhost | NULL    | Daemon  | 6669 | Waiting on empty queue | NULL                                   |
| 16 | root            | localhost | sb_test | Query   |    0 | init                   | show processlist                       |
| 17 | root            | localhost | sb_test | Query   |  669 | updating               | delete from sbtest1 where id<=30000000 |
+—-+—————–+———–+———+———+——+————————+—————————————-+
3 rows in set (0.01 sec)

mysql> kill 17;
Query OK, 0 rows affected (0.26 sec)

正如我们在进程列表中所看到的，已终止的事务当前处于回滚状态，回滚操作是在 undo 日志的帮助下执行的。

mysql> show processlist;
+—-+—————–+———–+———+———+——+————————+—————————————-+
| Id | User            | Host      | db      | Command | Time | State                  | Info                                   |
+—-+—————–+———–+———+———+——+————————+—————————————-+
|  5 | event_scheduler | localhost | NULL    | Daemon  | 7306 | Waiting on empty queue | NULL                                   |
| 17 | root            | localhost | sb_test | Killed  |  704 | query end              | delete from sbtest1 where id<=30000000 |
| 18 | root            | localhost | sb_test | Sleep   |  626 |                        | NULL                                   |
| 19 | root            | localhost | NULL    | Query   |    0 | init                   | show processlist                       |
+—-+—————–+———–+———+———+——+————————+—————————————-+
4 rows in set (0.00 sec)

现在，为了计算回滚进程的估计值，我以 60 秒的间隔从 INFORMATION_SCHEMA_Innodb_TRX 表输出中获取了撤消日志条目并终止了线程 ID 的事务。设置最小的 5 秒间隔将有助于计算准确的回滚估计时间。

mysql> pager grep -e “trx_mysql_thread_id: 17” -e trx_rows_modified
PAGER set to ‘grep -e “trx_mysql_thread_id: 17” -e trx_rows_modified’
mysql> select * from information_schema.innodb_trx\G select sleep(60); select * from information_schema.innodb_trx\G
         trx_rows_modified: 0
       trx_mysql_thread_id: 17
         trx_rows_modified: 18460230
2 rows in set (0.26 sec)

1 row in set (1 min 0.31 sec)

       trx_mysql_thread_id: 17
         trx_rows_modified: 17169927
1 row in set (0.09 sec)

mysql> \n
PAGER set to stdout
mysql> select SEC_TO_TIME(round((17169927*60)/(18460230–17169927))) as ‘Estimation Time of Rollback’;
+—————————–+
| Estimation Time of Rollback |
+—————————–+
| 00:13:18                     |
+—————————–+
1 row in set (0.18 sec)

从上面共享的日志可以看出，回滚操作还需要 13 分 18 秒。
为了使上述估算过程更加简单，我创建了Rollback TimeCalc 函数。

DELIMITER $$

CREATE FUNCTION RollbackTimeCalc(processID INT, timeInterval INT)
RETURNS VARCHAR(225)
DETERMINISTIC
BEGIN 
  DECLARE RollbackModifiedBeforeInterval INT; 
  DECLARE RollbackModifiedAfterInterval INT;
  DECLARE RollbackPendingRows INT; 
  DECLARE Result varchar(20);
      
      SELECT trx_rows_modified INTO RollbackModifiedBeforeInterval from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = ‘ROLLING BACK’;
      do sleep(timeInterval);
      SELECT trx_rows_modified INTO RollbackModifiedAfterInterval from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = ‘ROLLING BACK’;
  
      set Result=SEC_TO_TIME(round((RollbackModifiedAfterInterval*timeInterval)/(RollbackModifiedBeforeInterval-RollbackModifiedAfterInterval)));
  
      SELECT trx_rows_modified INTO RollbackPendingRows from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = ‘ROLLING BACK’;
  
      RETURN(CONCAT(‘Estimation Time of Rollback : ‘, Result, ‘ Pending rows to rollback ‘, RollbackPendingRows));
END$$
DELIMITER ;

我们必须将两个参数传递给函数。
Select RollbackTimeCalc(x,y);

x 是被 kill 事务的进程列表 ID，
y 是 undo 日志条目的时间间隔

mysql> select RollbackTimeCalc(18,5);
+———————————————————————————————————-+
| RollbackTimeCalc(18,5)                                                   |
+———————————————————————————————————+
| Estimation Time of Rollback: 00:06:09 Pending rows to rollback 10341861 |
+———————————————————————————————————-+
1 row in set (5.37 sec)

使用上面创建的函数，我们可以很容易地预估回滚操作的时间约为 6 分 9 秒。

Note:
1)已提交的事务不能回滚；
2)将交易分成较小的交易。大范围的删除和更新，可以通过 pt-asiver 和 oak-chunk-update 这样的工具来处理；
3)如果我们在数据库中执行大范围数据变更，最好设置 autoCommit=0，一定不要忘记在事务结束时添加 autoCommit=1。