Improved InnoDB I/O Scalability¶

Version Specific Information¶

• 5.6.11-60.3
  • Feature ported from Percona Server for MySQL 5.5
• 5.6.14-62.0
  • New innodb_log_checksum_algorithm variable introduced.

System Variables¶

variable innodb_use_global_flush_log_at_trx_commit¶

Command Line:	Yes
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Type:	Boolean
Default Value:	True
Range:	True/False

This variable is used to control the ability of the user to set the value of the global MySQL variable innodb_flush_log_at_trx_commit.

If innodb_use_global_flush_log_at_trx_commit=0 (False), the client can set the global MySQL variable, using:

SET innodb_use_global_flush_log_at_trx_commit=N

If innodb_use_global_flush_log_at_trx_commit=1 (True), the user session will use the current value of innodb_flush_log_at_trx_commit, and the user cannot reset the value of the global variable using a SET command.

variable innodb_log_block_size¶

Command Line:	Yes
Config File:	Yes
Scope:	Global
Dynamic:	No
Variable Type:	Numeric
Default Value:	512
Units:	Bytes

This variable changes the size of transaction log records. The default size of 512 bytes is good in most situations. However, setting it to 4096 may be a good optimization with SSD cards. While settings other than 512 and 4096 are possible, as a practical matter these are really the only two that it makes sense to use. Clean restart and removal of the old logs is needed for the variable innodb_log_block_size to be changed. Note: This feature implementation is considered BETA quality.

variable innodb_flush_method¶

Version Info:	5.6.13-61.0 - Ported from Percona Server for MySQL 5.5
Command Line:	Yes
Config File:	Yes
Scope:	Global
Dyn:	No
Variable Type:	Enumeration
Default Value:	fdatasync
Allowed Values:	fdatasync, O_DSYNC, O_DIRECT, O_DIRECT_NO_FSYNC, ALL_O_DIRECT

This is an existing MySQL 5.6 system variable that has a new allowed value ALL_O_DIRECT. It determines the method InnoDB uses to flush its data and log files. (See innodb_flush_method in the MySQL 5.6 Reference Manual).

The following values are allowed:

• fdatasync: use fsync() to flush both the data and log files.
• O_SYNC: use O_SYNC to open and flush the log files; use fsync() to flush the data files.
• O_DIRECT: use O_DIRECT to open the data files and fsync() system call to flush both the data and log files.
• O_DIRECT_NO_FSYNC: use O_DIRECT to open the data files but don’t use fsync() system call to flush both the data and log files. This option isn’t suitable for XFS file system.
• ALL_O_DIRECT: use O_DIRECT to open both data and log files, and use fsync() to flush the data files but not the log files. This option is recommended when InnoDB log files are big (more than 8GB), otherwise there might be even a performance degradation. Note: When using this option on ext4 filesystem variable innodb_log_block_size should be set to 4096 (default log-block-size in ext4) in order to avoid the unaligned AIO/DIO warnings.

variable innodb_log_checksum_algorithm¶

Version Info:	5.6.14-62.0 - Variable introduced
Command Line:	Yes
Config File:	Yes
Scope:	Global
Dyn:	Yes
Variable Type:	Enumeration
Default Value:	innodb
Allowed Values:	none, innodb, crc32, strict_none, strict_innodb, strict_crc32

This variable is used to specify how log checksums are generated and verified. Behavior of innodb_log_checksum_algorithm depending on its value is mostly identical to innodb_checksum_algorithm, except that the former applies to log rather than page checksums. NOTE: this feature is currently considered experimental.

The following values are allowed:

• none: means that a constant value will be written to log blocks instead of calculated checksum values and no checksum validation will be performed on InnoDB/XtraBackup recovery, or changed page tracking (if enabled).
• innodb: (the default) means the default InnoDB behavior – a custom and inefficient algorithm is used to calculate log checksums, but logs created with this option are compatible with upstream MySQL and earlier Percona Server for MySQL or Percona XtraBackup versions that do not support other log checksum algorithms.
• crc32: will use CRC32 for log block checksums. Checksums will also benefit from hardware acceleration provided by recent Intel CPUs.
• strict_*: Normally, XtraDB or Percona XtraBackup will tolerate checksums created with other algorithms than is currently specified with the innodb_log_checksum_algorithm option. That is, if checksums don’t match when reading the redo log on recovery, the block is considered corrupted only if no algorithm produces the value matching the checksum stored in the log block header. This can be disabled by prepending the value with the strict_ suffix, e.g. strict_none, strict_crc32 or strict_innodb will only accept checksums created using the corresponding algorithms, but not the other ones. To ensure that any log data written using the previous algorithm is fully overwritten before strictness becomes effective following migration procedure to a strict log block checksum should be used:
  • note the current LSN;
  • set the log block checksum algorithm to the non-strict version of the desired algorithm;
  • wait until the current LSN advances to at least the previous LSN + log capacity;
  • set the log block checksum algorithm to the strict version of the desired algorithm.

The max checkpoint age
The current checkpoint age target
The current age of the oldest page modification which has not been flushed to disk yet.
The current age of the last checkpoint
...
---
LOG
---
Log sequence number 0 1059494372
Log flushed up to   0 1059494372
Last checkpoint at  0 1055251010
Max checkpoint age  162361775
Checkpoint age target 104630090
Modified age        4092465
Checkpoint age      4243362
0 pending log writes, 0 pending chkp writes
...

Other Reading¶

• For Fusion-IO devices-specific tuning, see Atomic write support for Fusion-io devices documentation.