MySQL8 Innodb Redo&Undo表空间管理

原创杨明翰 2022-10-19

1831

在MySQL运维场景中，动态调整Redo大小，清理Undo表空间的需求会持续存在，例如、大事务导致undo日志的“膨胀”，或者TPS变化导致原有Redo日志大小需要调整。

1、动态调整Undo表空间

在MySQL中Undo文件可以自动扩展，业务侧的大事务很容易就将Undo日志文件撑大。在MySQL 8.0中可以使用以下步骤清理被撑大的Undo日志。


#查看系统当前存在两个undo文件，都是active状态，大小为16M
mysql> select * from information_schema.innodb_tablespaces where ROW_FORMAT='undo'\G
*************************** 1. row ***************************
          SPACE: 4294967279
           NAME: innodb_undo_001
           FLAG: 0
     ROW_FORMAT: Undo
      PAGE_SIZE: 16384
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: Undo
  FS_BLOCK_SIZE: 4096
      FILE_SIZE: 16777216
 ALLOCATED_SIZE: 16777216
AUTOEXTEND_SIZE: 0
 SERVER_VERSION: 8.0.30
  SPACE_VERSION: 1
     ENCRYPTION: N
          STATE: active
*************************** 2. row ***************************
          SPACE: 4294967278
           NAME: innodb_undo_002
           FLAG: 0
     ROW_FORMAT: Undo
      PAGE_SIZE: 16384
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: Undo
  FS_BLOCK_SIZE: 4096
      FILE_SIZE: 16777216
 ALLOCATED_SIZE: 16777216
AUTOEXTEND_SIZE: 0
 SERVER_VERSION: 8.0.30
  SPACE_VERSION: 1
     ENCRYPTION: N
          STATE: active
2 rows in set (0.00 sec)


mysql> system ls -lthr /data/mysql/data |grep undo
-rw-r-----. 1 mysql mysql  16M Oct 17 22:26 undo_002
-rw-r-----. 1 mysql mysql  16M Oct 17 22:26 undo_001


#设置其中一个undo表空间为inactive状态，打开系统参数innodb_undo_log_truncate 交MySQL自动回收
mysql> alter undo tablespace innodb_undo_001 set inactive;
ERROR 3655 (HY000): Cannot set innodb_undo_001 inactive since there would be less than 2 undo tablespaces left active.
可以看到系统需要保留至少2个active的undo表空间

#创建一个新的undo表空间，注意文件名必须为ibu,且表空间名前缀不能是innodb_
mysql> create undo tablespace undo_003 add datafile '/data/mysql/data/undo_003.ibu';
Query OK, 0 rows affected (0.13 sec)

#现在有三个undo表空间了，可以设置其中一个undo表空间为inactive状态
mysql> select * from information_schema.innodb_tablespaces where ROW_FORMAT='undo'\G
*************************** 1. row ***************************
          SPACE: 4294967152
           NAME: innodb_undo_001
           FLAG: 0
     ROW_FORMAT: Undo
      PAGE_SIZE: 16384
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: Undo
  FS_BLOCK_SIZE: 4096
      FILE_SIZE: 16777216
 ALLOCATED_SIZE: 16777216
AUTOEXTEND_SIZE: 0
 SERVER_VERSION: 8.0.30
  SPACE_VERSION: 1
     ENCRYPTION: N
          STATE: empty
*************************** 2. row ***************************
          SPACE: 4294967278
           NAME: innodb_undo_002
           FLAG: 0
     ROW_FORMAT: Undo
      PAGE_SIZE: 16384
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: Undo
  FS_BLOCK_SIZE: 4096
      FILE_SIZE: 16777216
 ALLOCATED_SIZE: 16777216
AUTOEXTEND_SIZE: 0
 SERVER_VERSION: 8.0.30
  SPACE_VERSION: 1
     ENCRYPTION: N
          STATE: active
*************************** 3. row ***************************
          SPACE: 4294967277
           NAME: undo_003
           FLAG: 0
     ROW_FORMAT: Undo
      PAGE_SIZE: 16384
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: Undo
  FS_BLOCK_SIZE: 4096
      FILE_SIZE: 16777216
 ALLOCATED_SIZE: 16777216
AUTOEXTEND_SIZE: 0
 SERVER_VERSION: 8.0.31
  SPACE_VERSION: 1
     ENCRYPTION: N
          STATE: active
3 rows in set (0.00 sec)

#确认自动回收是否开启
mysql> select @@global.innodb_undo_log_truncate;
+-----------------------------------+
| @@global.innodb_undo_log_truncate |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

#如果希望加快undo表空间的回收，可以适当调低innodb_purge_rseg_truncate_frequency的值。默认128
mysql> select  @@global.innodb_purge_rseg_truncate_frequency;
+-----------------------------------------------+
| @@global.innodb_purge_rseg_truncate_frequency |
+-----------------------------------------------+
|                                           128 |
+-----------------------------------------------+
1 row in set (0.00 sec)


#将回收的undo表空间设置为active，删除临时增加的undo3
mysql> alter undo tablespace innodb_undo_001 set active;  
Query OK, 0 rows affected (0.00 sec)

mysql> alter undo tablespace undo_003 set inactive;        
Query OK, 0 rows affected (0.00 sec)

mysql> drop undo tablespace undo_003;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.innodb_tablespaces where ROW_FORMAT='undo'\G
*************************** 1. row ***************************
          SPACE: 4294966898
           NAME: innodb_undo_001
           FLAG: 0
     ROW_FORMAT: Undo
      PAGE_SIZE: 16384
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: Undo
  FS_BLOCK_SIZE: 4096
      FILE_SIZE: 16777216
 ALLOCATED_SIZE: 16777216
AUTOEXTEND_SIZE: 0
 SERVER_VERSION: 8.0.30
  SPACE_VERSION: 1
     ENCRYPTION: N
          STATE: active
*************************** 2. row ***************************
          SPACE: 4294967278
           NAME: innodb_undo_002
           FLAG: 0
     ROW_FORMAT: Undo
      PAGE_SIZE: 16384
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: Undo
  FS_BLOCK_SIZE: 4096
      FILE_SIZE: 16777216
 ALLOCATED_SIZE: 16777216
AUTOEXTEND_SIZE: 0
 SERVER_VERSION: 8.0.30
  SPACE_VERSION: 1
     ENCRYPTION: N
          STATE: active
2 rows in set (0.00 sec)

小结步骤如下

·1、增加undo表空间的数量大于2.

2、设置膨胀的undo表空间为inactive

3、系统参数innodb_undo_log_truncate打开后会自动回收。

4、设置原undo表空间为active，删除新添加的表空间。

2、动态调整Redo日志

从MySQL8.0.30开始支持参数innodb_redo_log_capacity。该参数设定redo日志的总大小，默认100MB，最大值为128G。该参数设置后原有的innodb_log_files_in_group和innodb_log_file_size参数将被忽略。

MySQL会自动在innodb_log_group_home_dir目录中创建#innodb_redo，每个文件大小为innodb_redo_log_capacity/32。测试环境中设置总大小320M，每一个redo文件大小10M。未使用的redo日志文件名带_tmp后缀。

mysql> select  @@global.innodb_redo_log_capacity;
+-----------------------------------+
| @@global.innodb_redo_log_capacity |
+-----------------------------------+
|                         335544320 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> system ls -lthr /data/mysql/log/#innodb_redo    
total 320M
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo2_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo3_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo4_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo5_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo1
-rw-r-----. 1 mysql mysql 10M Oct 19 15:58 #ib_redo6_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo7_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo8_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo9_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo10_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo11_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo12_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo13_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo14_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo15_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo16_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo17_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo18_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo19_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo20_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo21_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo22_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo23_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo24_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo25_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo26_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo27_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo28_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo29_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo30_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo31_tmp
-rw-r-----  1 mysql mysql 10M Oct 19 15:58 #ib_redo32_tmp

当前使用的redo日志可以通过以下SQL查询

mysql> select * from  performance_schema.innodb_redo_log_files\G
*************************** 1. row ***************************
       FILE_ID: 1
     FILE_NAME: /data/mysql/log/#innodb_redo/#ib_redo1
     START_LSN: 101996544
       END_LSN: 112480256
 SIZE_IN_BYTES: 10485760
       IS_FULL: 0
CONSUMER_LEVEL: 0
1 row in set (0.00 sec)

redo日志写入的当前LSN可以使用以下SQL查询，也可以用LSN的变化量预估一个合理的redo文件大小

mysql> show global status like 'Innodb_redo_log_current_lsn';
+-----------------------------+-----------+
| Variable_name               | Value     |
+-----------------------------+-----------+
| Innodb_redo_log_current_lsn | 102001409 |
+-----------------------------+-----------+
1 row in set (0.00 sec)

#可以使用以下SQL评估1分钟lsn的变化量，*60 作为innodb_redo_log_capacity
select @a:=(select VARIABLE_VALUE from global_status where VARIABLE_NAME='Innodb_redo_log_current_lsn');select sleep(60);select @b:=(select VARIABLE_VALUE from global_status where VARIABLE_NAME='Innodb_redo_log_current_lsn');select (@b-@a)/1024/1024;


mysql> select @a:=(select VARIABLE_VALUE from global_status where VARIABLE_NAME='Innodb_redo_log_current_lsn');select sleep(60);select @b:=(select VARIABLE_VALUE from global_status where VARIABLE_NAME='Innodb_redo_log_current_lsn');select (@b-@a)/1024/1024;
+--------------------------------------------------------------------------------------------------+
| @a:=(select VARIABLE_VALUE from global_status where VARIABLE_NAME='Innodb_redo_log_current_lsn') |
+--------------------------------------------------------------------------------------------------+
| 102016920                                                                                        |
+--------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.00 sec)

+--------------------------------------------------------------------------------------------------+
| @b:=(select VARIABLE_VALUE from global_status where VARIABLE_NAME='Innodb_redo_log_current_lsn') |
+--------------------------------------------------------------------------------------------------+
| 102037622                                                                                        |
+--------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

+----------------------+
| (@b-@a)/1024/1024    |
+----------------------+
| 0.019742965698242188 |
+----------------------+
1 row in set (0.01 sec)

MySQL 8.0随着这些特性的逐渐丰富，Undo、Redo的调整都不需要重启数据库了，有条件的同学可以多测试并推广使用。

最后修改时间：2022-10-19 17:00:56

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

MySQL8 Innodb Redo&Undo表空间管理

1、动态调整Undo表空间

2、动态调整Redo日志

评论