MySQL Redo

Not to be confused with the Binary logs, the Redo logs are called ib_logfile0 and ib_logfile1 (assuming innodb_log_files_in_group is 2) and are found in your data directory (assuming innodb_log_group_home_dir is set to ./).

MySQL writes to the redo log files in a circular fashion.

The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 512GB.

innodb_flush_log_at_trx_commit

innodb_log_buffer_size

Check

To check current size...

SELECT variable_name,

variable_value AS innodb_log_buffer_size_bytes,

ROUND(variable_value / (1024*1024)) AS innodb_log_buffer_size_mb

FROM performance_schema.global_variables

WHERE variable_name LIKE 'innodb_log_buffer_size';

innodb_log_file_size

Default: 48MB (recommended default: 64MB)

Check

To check current size...

SELECT variable_name,

variable_value,

variable_value/1024/1024 As 'MB',

variable_value/1024/1024/1024 As 'GB'

FROM performance_schema.global_variables

WHERE variable_name='innodb_log_file_size';

Sizing

Correctly sizing the InnoDB Redo Logs can avoid frequent checkpoint activity and reduce overall physical I/O,

Out of the box, MySQL Enterprise Monitor recommends (in response to a "InnoDB Transaction Logs Not Sized Correctly" Event)...

  • The InnoDB transaction logs should be approximately 50-100% of the size of the InnoDB buffer pool, depending on the size of the buffer pool.

  • The maximum recommended size is 1 GB per log file.

The MySQL 5.7 documentation does not reiterate either of these points.

You can use the script below to calculate a size based on an assumed desire to log switch approximately once per hour.

SELECT @a1 := variable_value AS a1

FROM performance_schema.global_status

WHERE variable_name = 'innodb_os_log_written';

SELECT SLEEP(60);

SELECT @a2 := variable_value AS a2

FROM performance_schema.global_status

WHERE variable_name = 'innodb_os_log_written';

SELECT ROUND((@a2-@a1) * 60 / 1024 / 1024 / @@innodb_log_files_in_group) as MB;

This will return a recommended size in MB. It might be worth repeating this excerice a few times to get an average.

Change

  • Stop the MySQL server cleanly.

  • Edit my.cnf to change innodb_log_file_size. (and/or innodb_log_files_in_group).

  • Start the MySQL server.


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. (2)

innodb_log_files_in_group

Defines the number of log files in the log group. The default and recommended value is 2. (1)

Change

  • Stop the MySQL server cleanly.

  • Edit my.cnf to change innodb_log_files_in_group.

  • Start the MySQL server.

innodb_log_group_home_dir

MySQL Workbench

PERFORMANCE - Dashboard

MySQL Enterprise Monitor

In the Events section, you may see this...

Out of the box, MySQL Enterprise Monitor recommends (in response to a "InnoDB Transaction Logs Not Sized Correctly" Event)...

  • The InnoDB transaction logs should be approximately 50-100% of the size of the InnoDB buffer pool, depending on the size of the buffer pool.

  • The maximum recommended size is 1 GB per log file.

The MySQL 5.7 documentation does not reiterate either of these points.