Databases Reference
In-Depth Information
set to
1
. Today's high-speed drives
9
can perform only a couple of hundred real disk
transactions per second, simply because of the limitations of drive rotation speed and
seek time.
Sometimes the hard disk controller or operating system fakes a flush by putting the
data into yet
another
cache, such as the hard disk's own cache. This is faster but very
dangerous, because the data might still be lost if the drive loses power. This is even
worse than setting
innodb_flush_log_at_trx_commit
to something other than
1
, because
it can cause data corruption, not just lost transactions.
Setting
innodb_flush_log_at_trx_commit
to anything other than
1
can cause you to lose
transactions. However, you might find the other settings useful if you don't care about
durability (the D in ACID). Maybe you just want some of InnoDB's other features, such
as clustered indexes, resistance to data corruption, and row-level locking. This is not
uncommon when using InnoDB to replace MyISAM solely for performance reasons.
The best configuration for high-performance transactional needs is to leave
innodb_
flush_log_at_trx_commit
set to
1
and place the log files on a RAID volume with a
battery-backed write cache. This is both safe and very fast. In fact, we dare say that any
production database server that's expected to handle a serious workload needs to have
this kind of hardware.
Percona Server extends
innodb_flush_log_at_trx_commit
to make it a per-session
variable, instead of global for the whole server. This allows applications with varying
performance and durability needs to use the same database, and avoids the one-size-
fits-all solution offered by standard MySQL.
How InnoDB opens and flushes log and data files
The
innodb_flush_method
option lets you configure how InnoDB actually interacts with
the filesystem. Despite its name, it can affect how InnoDB reads data, not just how it
writes it. The Windows and non-Windows values for this option are mutually exclu-
sive: you can use
async_unbuffered
,
unbuffered
, and
normal
only on Windows, and you
cannot use any other values on Windows. The default value is
unbuffered
on Windows
and
fdatasync
on all other systems. (If
SHOW GLOBAL VARIABLES
shows the variable with
an empty value, that means it's set to the default.)
Changing how InnoDB performs I/O operations can impact perfor-
mance greatly, so be sure you understand what you're doing before you
change anything!
This is a slightly confusing option, because it affects both the log files and the data files,
and it sometimes does different things to each kind of file. It would be nice to have one
9. We're talking about spindle-based disk drives with rotating platters, not solid-state hard drives, which
have completely different performance characteristics.
