Improved InnoDB I/O Scalability¶
Because InnoDB is a complex storage engine it must be configured properly in order to perform at its best. Some points are not configurable in standard InnoDB. The goal of this feature is to provide a more exhaustive set of options for XtraDB.
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 enables or disables the effect of the per-session value of the innodb_flush_log_at_trx_commit variable.
If the global variable innodb_use_global_flush_log_at_trx_commit is
set to 1
, the session uses the current
global value of innodb_flush_log_at_trx_commit. This is the
upstream-compatible mode. If the user attempts to change the
innodb_flush_log_at_trx_commit value for a
session, the session value is ignored.
If the global variable innodb_use_global_flush_log_at_trx_commit is set to
0
, a user can modify the
innodb_flush_log_at_trx_commit
per-session using the following command:
SET SESSION innodb_flush_log_at_trx_commit=0
This modification only affects the transactions in that session. Other sessions, if they have not been individually modified, continue to use the global innodb_use_flush_log_at_trx_commit value.
SET innodb_use_global_flush_log_at_trx_commit=1
-
variable
innodb_flush_method
¶ Version Info: 5.7.10-3
- Ported from Percona Server for MySQL 5.6
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.7 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.7 Reference Manual).
The following values are allowed:
fdatasync
: usefsync()
to flush data, log, and parallel doublewrite files.O_SYNC
: useO_SYNC
to open and flush the log and parallel doublewrite files; usefsync()
to flush the data files. Do not usefsync()
to flush the parallel doublewrite file.O_DIRECT
: use O_DIRECT to open the data files andfsync()
system call to flush data, log, and parallel doublewrite files.O_DIRECT_NO_FSYNC
: useO_DIRECT
to open the data files, but don’t usefsync()
system call to flush data, log, and parallel doublewrite files.ALL_O_DIRECT
: useO_DIRECT
to open both data and log files, and usefsync()
to flush the data files but not the log or parallel doublewrite 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_write_ahead_size should be set to 4096 (default log-block-size in ext4) in order to avoid theunaligned AIO/DIO
warnings.
Status Variables¶
The following information has been added to SHOW ENGINE INNODB STATUS
to confirm the checkpointing activity:
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
...
Contact Us
For free technical help, visit the Percona Community Forum.To report bugs or submit feature requests, open a JIRA ticket.
For paid support and managed or professional services, contact Percona Sales.