casyup.me@outlook.com

0%

read/InnoDBRedoLog

Posted on Edited on

15.6.5 Redo Log

By default, the redo log is physically represented on disk by two files named ib_logfile0 and ib_logfile1. MySQL writes to the redo log files in a circular fashion. Data in the redo log is encoded in terms of records affected; this data is collectively referred to as redo. The passage of data through the redo log is represented by an ever-increasing LSN value.

默认 redo 日志通过两个叫 ib_logfile0 和 ib_logfile1 的文件物理地记录在磁盘上. MySQL 以循环方式写入到文件中. redo 日志中的数据受记录影响编码. 这些数据统称为 redo. 数据通过 redo 日志的传递由一个不断增长的 LSN 值表示

For related information, see Redo Log File Configuration, and Section 8.5.4, “Optimizing InnoDB Redo Logging”.

For information about data-at-rest encryption for redo logs, see Redo Log Encryption.

Changing the Number or Size of Redo Log Files

  1. Stop the MySQL server and make sure that it shuts down without errors.
  2. Edit my.cnf to change the log file configuration. To change the log file size, configure innodb_log_file_size. To increase the number of log files, configureinnodb_log_files_in_group.
  3. Start the MySQL server again.

If InnoDB detects that the innodb_log_file_size differs from the redo log file size, it writes a log checkpoint, closes and removes the old log files, creates new log files at the requested size, and opens the new log files.

Group Commit for Redo Log Flushing

InnoDB, like any other ACID-compliant database engine, flushes the redo log of a transaction before it is committed. InnoDB uses group commit functionality to group multiple such flush requests together to avoid one flush for each commit. With group commit, InnoDB issues a single write to the log file to perform the commit action for multiple user transactions that commit at about the same time, significantly improving throughput.

InnoDB 像其他 ACID 数据引擎一样, 在事务提交前冲刷 redo 日志, InnoDB 使用组提交功能, 将多个提交请求分组, 避免每次提交都冲刷记录. 在组提交中, InnoDB 为在大约同一时间提交的多次用户事务执行单次文件写入, 显著提高了吞吐量

For more information about performance of COMMIT and other transactional operations, see Section 8.5.2, “Optimizing InnoDB Transaction Management”.

Redo Log Archiving

Backup utilities that copy redo log records may sometimes fail to keep pace with redo log generation while a backup operation is in progress, resulting in lost redo log records due to those records being overwritten. This issue most often occurs when there is significant MySQL server activity during the backup operation, and the redo log file storage media operates at a faster speed than the backup storage media. The redo log archiving feature, introduced in MySQL 8.0.17, addresses this issue by sequentially writing redo log records to an archive file in addition to the redo log files. Backup utilities can copy redo log records from the archive file as necessary, thereby avoiding the potential loss of data.

拷贝 redo 日志记录的备份工具可能有时会跟丢 redo 记录日志的生成速度, 当备份操作运行中, 因为这些记录被覆写而导致丢失 redo 日志记录

当备份操作产生大量的 MySQL 服务器活动时这个问题经常发生, 此时 redo 日志文件存储媒介操作处于比备份存储媒介操作更快的速率. 在 MySQL 8.0.17 引进的 redo 日志归档功能, 通过除写入 redo 日志外, 还序列写入 redo 日志记录到归档文件解决这个问题. 备份工具尽可能从归档文件中拷贝 redo 日志记录, 从而规避潜在的数据丢失问题

If redo log archiving is configured on the server, MySQL Enterprise Backup, available with the MySQL Enterprise Edition, uses the redo log archiving feature when backing up a MySQL server.

Enabling redo log archiving on the server requires setting a value for the innodb_redo_log_archive_dirs system variable. The value is specified as a semicolon-separated list of labeled redo log archive directories. The *label:directory* pair is separated by a colon (:). For example:

1
mysql> SET GLOBAL innodb_redo_log_archive_dirs='label1:directory_path1[;label2:directory_path2;…]';

The label is an arbitrary identifier for the archive directory. It can be any string of characters, with the exception of colons (:), which are not permitted. An empty label is also permitted, but the colon (:) is still required in this case. A directory_path must be specified. The directory that is selected for the redo log archive file must exist when redo log archiving is activated, or an error is returned. The path can contain colons (‘:’), but semicolons (;) are not permitted.

The innodb_redo_log_archive_dirs variable must be configured before the redo log archiving can be activated. The default value is NULL, which does not permit activating redo log archiving.

Notes

The archive directories that you specify must satisfy the following requirements. (The requirements are enforced when redo log archiving is activated.):

  • Directories must exist. Directories are not created by the redo log archive process. Otherwise, the following error is returned:

    ERROR 3844 (HY000): Redo log archive directory ‘directory_path1‘ does not exist or is not a directory

  • Directories must not be world-accessible. This is to prevent the redo log data from being exposed to unauthorized users on the system. Otherwise, the following error is returned:

    ERROR 3846 (HY000): Redo log archive directory ‘directory_path1‘ is accessible to all OS users

  • Directories cannot be those defined by datadir, innodb_data_home_dir, innodb_directories, innodb_log_group_home_dir,innodb_temp_tablespaces_dir, innodb_tmpdir innodb_undo_directory, or secure_file_priv, nor can they be parent directories or subdirectories of those directories. Otherwise, an error similar to the following is returned:

    ERROR 3845 (HY000): Redo log archive directory ‘directory_path1‘ is in, under, or over server directory ‘datadir’ - ‘/path/to/data_directory‘

When a backup utility that supports redo log archiving initiates a backup, the backup utility activates redo log archiving by invoking the innodb_redo_log_archive_start() user-defined function.

If you are not using a backup utility that supports redo log archiving, redo log archiving can also be activated manually, as shown:

1
2
3
4
5
6
mysql> SELECT innodb_redo_log_archive_start('label', 'subdir');
+------------------------------------------+
| innodb_redo_log_archive_start('label') |
+------------------------------------------+
| 0                                        |
+------------------------------------------+

Or:

1
2
mysql> DO innodb_redo_log_archive_start('label', 'subdir');
Query OK, 0 rows affected (0.09 sec)

Note

The MySQL session that activates redo log archiving (using innodb_redo_log_archive_start()) must remain open for the duration of the archiving. The same session must deactivate redo log archiving (using innodb_redo_log_archive_stop()). If the session is terminated before the redo log archiving is explicitly deactivated, the server deactivates redo log archiving implicitly and removes the redo log archive file.

where label is a label defined by innodb_redo_log_archive_dirs; subdir is an optional argument for specifying a subdirectory of the directory identified by label for saving the archive file; it must be a simple directory name (no slash (/), backslash (), or colon (:) is permitted). subdir can be empty, null, or it can be left out.

Only users with the INNODB_REDO_LOG_ARCHIVE privilege can activate redo log archiving by invoking innodb_redo_log_archive_start(), or deactivate it usinginnodb_redo_log_archive_stop(). The MySQL user running the backup utility or the MySQL user activating and deactivating redo log archiving manually must have this privilege.

The redo log archive file path is *directory_identified_by_label*/[*subdir*/]archive.*serverUUID*.000001.log, where *directory_identified_by_label* is the archive directory identified by the *label* argument for innodb_redo_log_archive_start(). *subdir* is the optional argument used for innodb_redo_log_archive_start().

For example, the full path and name for a redo log archive file appears similar to the following:

1
/directory_path/subdirectory/archive.e71a47dc-61f8-11e9-a3cb-080027154b4d.000001.log

After the backup utility finishes copying InnoDB data files, it deactivates redo log archiving by calling the innodb_redo_log_archive_stop() user-defined function.

If you are not using a backup utility that supports redo log archiving, redo log archiving can also be deactivated manually, as shown:

1
2
3
4
5
6
mysql> SELECT innodb_redo_log_archive_stop();
+--------------------------------+
| innodb_redo_log_archive_stop() |
+--------------------------------+
| 0                              |
+--------------------------------+

Or:

1
2
mysql> DO innodb_redo_log_archive_stop();
Query OK, 0 rows affected (0.01 sec)

After the stop function completes successfully, the backup utility looks for the relevant section of redo log data from the archive file and copies it into the backup.

After the backup utility finishes copying the redo log data and no longer needs the redo log archive file, it deletes the archive file.

Removal of the archive file is the responsibility of the backup utility in normal situations. However, if the redo log archiving operation quits unexpectedly beforeinnodb_redo_log_archive_stop() is called, the MySQL server removes the file.

Performance Considerations

Activating redo log archiving typically has a minor performance cost due to the additional write activity.

On Unix and Unix-like operating systems, the performance impact is typically minor, assuming there is not a sustained high rate of updates. On Windows, the performance impact is typically a bit higher, assuming the same.

If there is a sustained high rate of updates and the redo log archive file is on the same storage media as the redo log files, the performance impact may be more significant due to compounded write activity.

If there is a sustained high rate of updates and the redo log archive file is on slower storage media than the redo log files, performance is impacted arbitrarily.

Writing to the redo log archive file does not impede normal transactional logging except in the case that the redo log archive file storage media operates at a much slower rate than the redo log file storage media, and there is a large backlog of persisted redo log blocks waiting to be written to the redo log archive file. In this case, the transactional logging rate is reduced to a level that can be managed by the slower storage media where the redo log archive file resides.