Mysql 如何预估后台回滚事务的时长

2023年 9月 2日 49.8k 0

没有停机时间和潜在的数据丢失,就不能简单地停止回滚。

建议等待回滚自行完成。要估计回滚需要多长时间,请登录mysql并执行几次以监控其进度:

<code style="text-align: left;">mysql> select now(),trx_state,trx_operation_state,trx_weight,trx_rows_locked,trx_rows_modified from information_schema.innodb_trx where trx_state='ROLLING BACK';do sleep(10);
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| now() | trx_state | trx_operation_state | trx_weight | trx_rows_locked | trx_rows_modified |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| 2017-11-15 15:20:19 | ROLLING BACK | rollback | 17028388 | 64342541 | 16672912 |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (10.00 sec)

mysql> select now(),trx_state,trx_operation_state,trx_weight,trx_rows_locked,trx_rows_modified from information_schema.innodb_trx where trx_state='ROLLING BACK';do sleep(10);
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| now() | trx_state | trx_operation_state | trx_weight | trx_rows_locked | trx_rows_modified |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| 2017-11-15 15:20:29 | ROLLING BACK | rollback | 16245596 | 64342541 | 15906465 |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (10.00 sec)

mysql> select now(),trx_state,trx_operation_state,trx_weight,trx_rows_locked,trx_rows_modified from information_schema.innodb_trx where trx_state='ROLLING BACK';do sleep(10);
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| now() | trx_state | trx_operation_state | trx_weight | trx_rows_locked | trx_rows_modified |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
| 2017-11-15 15:20:39 | ROLLING BACK | rollback | 15463244 | 64342541 | 15140438 |
+---------------------+--------------+---------------------+------------+-----------------+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (10.00 sec)

在上面的输出中,我们可以看到回滚正在以如下速度进行:

16672912 - 15906465 = 766447 rows per 10 seconds,  or 76644 rows per second.

因此,预计回滚可能需要16672912 / 76644 = 218秒才能完成。

​注意:这只是一个估计,它取决于数据大小、当前服务器负载和配置

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论