记得5年前我们在某银行客户做大数据量load data测试时，为了能在要求的时间内完成数据加载，尽管优化了各种参数，但还是避免不了在日志的IO开销。


在商业数据库DB2、Oracle都有nologging table功能，对于有大量数据加载需求的系统，就可以不记录日志，减少IO的开销。


这对用惯了商业数据库的用户来说，首测尝试开源数据库，感觉各种不适应。


最后只好拆分更多实例，增加并行度来提高load data效率，来满足时效性要求。


MySQL 一直在改善自身的扩展性，这对于企业级数据库是必须的，不能仅靠拆分打天下，一味的拆分使用体验太差，也会阻碍用户大规模使用，维护分布式架构的复杂性远比集中式复杂的多。


昨天发布的MySQL 8.0.21 ，我们看到了disable redo log功能，这对load data场景太有吸引力了，我们简单测试下看实际效果如何。




简单对比测试


对比禁用与启用redo log两种场景下的执行效率，处理100w记录（1.8G）文件，sysbench标准表结构。

场景

load data

add index

禁用 REDO_LOG

innodb_flush_log_at_trx_commit

sync_binlog = 双1

2 min 39.66 sec38.96 sec

禁用 REDO_LOG

innodb_flush_log_at_trx_commit

sync_binlog = 双0

2 min 30.61 sec35.13 sec

启用 REDO_LOG

innodb_flush_log_at_trx_commit

sync_binlog = 双1

3 min 37.55 sec    47.05 sec

启用 REDO_LOG

innodb flush log

sync_binlog= 双0

2 min 49.84 sec47.32 sec


从实际测试情况来看，禁用与启用redo log有 10%~30%的执行时间差异。



禁用redo log load data

mysql [localhost:8021] {msandbox} (test) > ALTER INSTANCE DISABLE INNODB REDO_LOG;Query OK, 0 rows affected (0.10 sec)
mysql [localhost:8021] {msandbox} (test) > load data infile 'sbtest.txt' into table sbtest1;Query OK, 10000000 rows affected (2 min 39.66 sec)Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0
mysql [localhost:8021] {msandbox} (test) > truncate sbtest1;Query OK, 0 rows affected (0.36 sec)
mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=0;set global innodb_flush_log_at_trx_commit=0;Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)mysql [localhost:8021] {msandbox} (test) > load data infile 'sbtest.txt' into table sbtest1;Query OK, 10000000 rows affected (2 min 30.61 sec)Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0


启用redo log load data

mysql [localhost:8021] {msandbox} (test) > ALTER INSTANCE ENABLE INNODB REDO_LOG;Query OK, 0 rows affected (0.09 sec)
mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=1;set global innodb_flush_log_at_trx_commit=1;Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8021] {msandbox} (test) > load data infile 'sbtest.txt' into table sbtest1;Query OK, 10000000 rows affected (3 min 37.55 sec)Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0
mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=0;set global innodb_flush_log_at_trx_commit=0;Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8021] {msandbox} (test) > truncate sbtest1;Query OK, 0 rows affected (0.34 sec)
mysql [localhost:8021] {msandbox} (test) > load data infile 'sbtest.txt' into table sbtest1;Query OK, 10000000 rows affected (2 min 49.84 sec)Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0



禁用redo log add index

mysql [localhost:8021] {msandbox} (test) > ALTER INSTANCE DISABLE INNODB REDO_LOG;Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=1;set global innodb_flush_log_at_trx_commit=1;Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8021] {msandbox} (test) > alter table sbtest1 add index idx_c(c);Query OK, 0 rows affected (38.96 sec)Records: 0  Duplicates: 0  Warnings: 0
mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=0;set global innodb_flush_log_at_trx_commit=0;Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8021] {msandbox} (test) > alter table sbtest1 drop index idx_c;Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0
mysql [localhost:8021] {msandbox} (test) > alter table sbtest1 add index idx_c(c);Query OK, 0 rows affected (35.13 sec)Records: 0  Duplicates: 0  Warnings: 0


启用redo log add index


mysql [localhost:8021] {msandbox} (test) > ALTER INSTANCE ENABLE INNODB REDO_LOG;Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=1;set global innodb_flush_log_at_trx_commit=1;Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8021] {msandbox} (test) > alter table sbtest1 add index idx_c(c);Query OK, 0 rows affected (47.05 sec)Records: 0  Duplicates: 0  Warnings: 0
mysql [localhost:8021] {msandbox} (test) > set global sync_binlog=0;set global innodb_flush_log_at_trx_commit=0;Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8021] {msandbox} (test) > alter table sbtest1 drop index idx_c;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0
mysql [localhost:8021] {msandbox} (test) > alter table sbtest1 add index idx_c(c);Query OK, 0 rows affected (47.32 sec)Records: 0  Duplicates: 0  Warnings: 0


总结一下


  • 禁用redo log不影响binlog功能，可以正常同步。

  • 禁用redo log是实例级，不支持表级。


  • 禁用redo log若发生crash是无法recovery的，OLTP 系统谨慎使用。

  • 适用于大量数据导入场景。