MySQL Estimate of the duration of a rollback operation(估算rollback事务回滚剩余时间)

by

很久以前记录过一篇 Oracle Estimate of the duration of a rollback operation (估算rollback事务回滚剩余时间) ，oracle的大事务cancel或kill后的回滚操作，rollback所花费的时间通常比原来的操作还要长，而且在回滚完成前有可能会堵塞其它事务，在PostgreSQL中因为没有使用undo而是多版本，所以忽略rollback的时间，这是PG的强项，但是在MySQL中和oracle一样同样存在回滚问题，这里简单记录如何估算MySQL中的事务回滚时间。

 

删除一个大表中的记录。

mysql> show processlist;
+----+-----------------+-----------+---------+---------+------+------------------------+----------------------------------------+
| Id | User            | Host      | db      | Command | Time | State                  | Info                                   |
+----+-----------------+-----------+---------+---------+------+------------------------+----------------------------------------+
| 16 | root            | localhost | anbob   | Query   |    0 | init                   | show processlist                       |
| 18 | root            | localhost | anbob   | Query   |  669 | updating               | delete from anbo_t1 where id<=50000000 |
 +----+-----------------+-----------+---------+---------+------+------------------------+----------------------------------------+ 
3 rows in set (0.01 sec) 

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

查看进程状态

mysql> show processlist;
+----+-----------------+-----------+---------+---------+------+------------------------+----------------------------------------+
| Id | User            | Host      | db      | Command | Time | State                  | Info                                   |
+----+-----------------+-----------+---------+---------+------+------------------------+----------------------------------------+
| 18 | root            | localhost | anbob   | Killed  |  704 | query end              | delete from anbo_t1 where id<=50000000 |
| 19 | root            | localhost | NULL    | Query   |    0 | init                   | show processlist                       |
+----+-----------------+-----------+---------+---------+------+------------------------+----------------------------------------+

INFORMATION_SCHEMA.INNODB_TRX Table的记录了当前事务修改行数，可以多次查询根据差值估算时间，rollback时该值会逐渐减少，直到0。

TRX_ROWS_MODIFIED
The number of modified and inserted rows in this transaction.

持续跟踪TRX_ROWS_MODIFIED

mysql> pager grep -e "trx_mysql_thread_id: 18" -e trx_rows_modified
PAGER set to 'grep -e "trx_mysql_thread_id: 18" -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: 18
         trx_rows_modified: 19010230
2 rows in set (0.26 sec)

1 row in set (1 min 0.31 sec)

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

mysql> \n
PAGER set to stdout
mysql> select SEC_TO_TIME(round((18039927/(19010230-18039927)*60))) as 'Estimation Time of Rollback';
+-----------------------------+
| Estimation Time of Rollback |
+-----------------------------+
| 00:18:36                    |
+-----------------------------+

Note:
估算完成回滚操作还需要18分36秒。

mydbops上有一个好的function

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 ;

我们必须向函数传递两个参数
RollbackTimeCalc(x，y)；

x 是被终止事务的进程列表 ID，
y 是撤消日志条目的时间间隔

e.g.

​​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)

建议还是把大事务拆成几份，简化MySQL的使用，另外对数据库进行重大更改，最好设置 autocommit=0。并且不要忘记在事务结束时添加 autocommit=1。