Performance Impact of Disabling InnoDB Redo Log in MySQL 8.0.21 for Large Load Data Operations

When loading massive data sets into MySQL, log I/O can become a bottleneck. Commercial databases such as DB2 and Oracle provide a nologging table feature to bypass logging, but MySQL historically required work‑arounds like sharding instances or adjusting parameters.

MySQL 8.0.21 introduced the ability to disable the InnoDB redo log at the instance level, which can significantly reduce I/O for bulk LOAD DATA operations. The author performed simple benchmark tests to quantify the effect.

Test setup : 10 million rows (≈1.8 GB) were loaded into a Sysbench standard table on a local MySQL sandbox (port 8021). Two scenarios were compared – redo log disabled vs. enabled – while toggling sync_binlog and innodb_flush_log_at_trx_commit to isolate the impact.

Disable redo log – load data :

ALTER INSTANCE DISABLE INNODB REDO_LOG;
load data infile 'sbtest.txt' into table sbtest1;
Query OK, 10000000 rows affected (2 min 39.66 sec)
set global sync_binlog=0; set global innodb_flush_log_at_trx_commit=0;
load data infile 'sbtest.txt' into table sbtest1;
Query OK, 10000000 rows affected (2 min 30.61 sec)

Enable redo log – load data :

ALTER INSTANCE ENABLE INNODB REDO_LOG;
set global sync_binlog=1; set global innodb_flush_log_at_trx_commit=1;
load data infile 'sbtest.txt' into table sbtest1;
Query OK, 10000000 rows affected (3 min 37.55 sec)
set global sync_binlog=0; set global innodb_flush_log_at_trx_commit=0;
load data infile 'sbtest.txt' into table sbtest1;
Query OK, 10000000 rows affected (2 min 49.84 sec)

The results show a 10 %–30 % reduction in execution time when the redo log is disabled for bulk loads.

Additional tests on index creation demonstrated similar gains: disabling redo log reduced the time to add an index by roughly 10 %–15 % compared with the enabled state.

Key conclusions :

Disabling the redo log does not affect binary logging; replication continues to work.

The setting is instance‑wide and cannot be applied per table.

If a crash occurs while the redo log is disabled, recovery is impossible, so it should be used cautiously in OLTP environments.

It is well‑suited for scenarios that involve massive data imports where the risk of crash is acceptable.

Overall, the new DISABLE INNODB REDO_LOG feature provides a practical performance boost for bulk‑load workloads, but administrators must weigh the trade‑off between speed and durability.