MySQL8.0的Disabling Redo Logging

官方内容摘抄

Disabling Redo Logging
As of MySQL 8.0.21, you can disable redo logging using the ALTER INSTANCE DISABLE INNODB REDO_LOG statement. This functionality is intended for loading data into a new MySQL instance. Disabling redo logging speeds up data loading by avoiding redo log writes and doublewrite buffering.

Warning
This feature is intended only for loading data into a new MySQL instance. Do not disable redo logging on a production system. It is permitted to shutdown and restart the server while redo logging is disabled, but an unexpected server stoppage while redo logging is disabled can cause data loss and instance corruption.

Attempting to restart the server after an unexpected server stoppage while redo logging is disabled is refused with the following error:

[ERROR] [MY-013598] [InnoDB] Server was killed when Innodb Redo 
logging was disabled. Data files could be corrupt. You can try 
to restart the database with innodb_force_recovery=6
In this case, initialize a new MySQL instance and start the data loading procedure again.

The INNODB_REDO_LOG_ENABLE privilege is required to enable and disable redo logging.

The Innodb_redo_log_enabled status variable permits monitoring redo logging status.

Cloning operations and redo log archiving are not permitted while redo logging is disabled and vice versa.

An ALTER INSTANCE [ENABLE|DISABLE] INNODB REDO_LOG operation requires an exclusive backup metadata lock, which prevents other ALTER INSTANCE operations from executing concurrently. Other ALTER INSTANCE operations must wait for the lock to be released before executing.

The following procedure demonstrates how to disable redo logging when loading data into a new MySQL instance.

On the new MySQL instance, grant the INNODB_REDO_LOG_ENABLE privilege to the user account responsible for disabling redo logging.

mysql> GRANT INNODB_REDO_LOG_ENABLE ON *.* to 'data_load_admin';
As the data_load_admin user, disable redo logging:

mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;
Check the Innodb_redo_log_enabled status variable to ensure that redo logging is disabled.

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | OFF   |
+-------------------------+-------+
Run the data load operation.

As the data_load_admin user, enable redo logging after the data load operation finishes:

mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;
Check the Innodb_redo_log_enabled status variable to ensure that redo logging is enabled.


mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | ON    |
+-------------------------+-------+

测试对逻辑导入速度的影响

服务器为4核4G虚拟机

备份数据过程中略，看备份后文件总大小

[root@localhost mysql]# du -sh /opt/mysql/*
1003M   /opt/mysql/dbt3

重新搭建了一个8.0.28版本的实例

不做任务调理时，逻辑导入速度如下

[root@localhost mysql]# time myloader -u root -p 123 -t 8 -B dbt3 -o -d /opt/mysql/dbt3/

real    2m10.069s
user    0m0.937s
sys     0m3.104s

vmstat 1 111

修改参数再进行逻辑导入（删除刚才导入数据）

• 修改参数

mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | OFF   |
+-------------------------+-------+
1 row in set (0.17 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dbt3               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database dbt3;
Query OK, 9 rows affected (0.75 sec)

mysql> create table dbt3;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

• 导入数据

  [root@localhost mysql]# time myloader -u root -p 123 -t 8 -B dbt3 -o -d /opt/mysql/dbt3/
  
  real    1m29.027s
  user    0m0.959s
  sys     0m2.464s
  

  • vmstat 1 111
    

前后再次导入时间对比

调整参数后：
real    1m29.027s
user    0m0.959s
sys     0m2.464s
调整参数前：
real    2m10.069s
user    0m0.937s
sys     0m3.104s

总结

• 从导入时间对比可以看出，时间上还是有一定差距的，因为测试数据只有1G，也有40秒的时间差了
• 在创建新实例需要进行逻辑数据导入的情况下，可以尝试使用
• 这只是一个简单测试