Improving Large Transaction Rollback Speed in MySQL: Buffer Pool Tuning and Force Recovery

When operating on a large table, rollback time can become excessively long, potentially impacting online services. This article investigates two primary methods to speed up rollback of large MySQL transactions: increasing the innodb_buffer_pool_size and using innodb_force_recovery=3 after stopping the instance.

Method 1 – Increase Buffer Pool Size

The experiment first sets the buffer pool to 1 GB, runs an update on sbtest1 , and then expands the pool to 5 GB before rolling back. The relevant commands are shown below:

mysql> set global innodb_buffer_pool_size = 1073741824;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> use sbtest;
Database changed
mysql> update sbtest1 set k=k+1;
Query OK, 16023947 rows affected (7 min 23.23 sec)
mysql> set global innodb_buffer_pool_size = 5368709120;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%uffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_size             | 5368709120     |
+-------------------------------------+----------------+
mysql> rollback;
Query OK, 0 rows affected (6 min 39.41 sec)

The rollback time decreased by about one minute compared with the update duration, which is not a dramatic improvement. Further tests with larger buffer pool sizes showed that noticeable speed‑up occurs only when the pool exceeds the data volume.

Method 2 – Force Recovery

The second approach involves forcibly terminating the MySQL process, backing up data and logs, setting innodb_force_recovery=3 , restarting the server, and then shutting it down normally. This skips the rollback phase entirely; the time taken is the time to restart the server. The steps are:

Kill the MySQL process (e.g., kill -9 ).

Backup MySQL data and log directories.

Configure innodb_force_recovery=3 in my.cnf .

Start MySQL, then shut it down normally.

Remove the recovery setting and restart.

After the final start, the error log records “ [Note] InnoDB: Rollback of non‑prepared transactions completed ”, indicating that the rollback was bypassed.

Conclusion

Both methods have trade‑offs: increasing the buffer pool is less intrusive and does not interrupt online services, while using force recovery yields a more significant reduction in downtime but briefly halts business continuity. Administrators should choose based on the severity of the situation and tolerance for service interruption.