Technology sharing | MySQL 8.0.21 disable redo log performance test

Time:2021-9-28

Author: Hong bin
The person in charge of aikesheng south district and technical service director, MySQL ace, is good at database architecture planning, fault diagnosis, performance optimization analysis, has rich practical experience, helps customers in various industries solve MySQL technical problems, and provides overall MySQL solutions for customers in finance, operators, Internet and other industries.
Source: reproduced from official account – fun MySQL
*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.


I remember that five years ago, when we did a large amount of data load data test for a bank customer, in order to complete the data load within the required time, although we optimized various parameters, we still couldn’t avoid the IO overhead in the log.

The commercial databases DB2 and Oracle both have the NOLOGGING table function. For systems with a large amount of data loading requirements, they can not record logs and reduce IO overhead.
For users who are used to commercial databases, the first test of open source databases does not feel suitable.

Finally, we have to split more instances and increase parallelism to improve load data efficiency and meet the timeliness requirements.

MySQL has been improving its scalability, which is necessary for enterprise databases. It can’t beat the world only by splitting. Blindly splitting the use experience is too poor, which will also hinder users from large-scale use. Maintaining the distributed architecture is much more complex than centralized.

In MySQL 8.0.21 released yesterday, we saw the disable redo log function, which is very attractive for the load data scenario. Let’s simply test it to see the actual effect.

Simple comparison test

Compare the execution efficiency under the two scenarios of disabling and enabling redo log, process 100W record (1.8g) files, and sysbench standard table structure.
Technology sharing | MySQL 8.0.21 disable redo log performance test

From the actual test, the execution time difference between disabling and enabling redo log is 10% ~ 30%.

Disable 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

Enable 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

Disable 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

Enable 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

To sum up

  • Disabling redo log does not affect the binlog function and can be synchronized normally.
  • Disable redo log is instance level and does not support table level.
  • Disable redo log. If a crash occurs, recovery cannot be performed. The OLTP system should use it with caution.
  • It is suitable for a large number of data import scenarios.

Recommended Today

Blog Garden Background page dynamic effects

1. To set animation, you must first apply for permission 1.1 first enter [my blog park] and enter [settings] in [management] 1.2 find [blog sidebar announcement] and click [apply for JS permission] 1.3 write the content of application JS permission (examples are as follows) Dear blog administrator: Can you open JS permission for me? I […]