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.

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

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.

Bibliography