Databases Reference
In-Depth Information
entries, which is likely to be enough. As a rule of thumb, you can make your total log
file size large enough to hold an hour's worth of server activity.
When InnoDB flushes the log buffer to the log files on
disk, it locks the buffer with a mutex, flushes it up to the desired point, and then moves
any remaining entries to the front of the buffer. It is possible that more than one trans-
action will be ready to flush its log entries when the mutex is released. InnoDB has a
group commit feature that can commit all of them to the log in a single I/O operation,
but this is broken in MySQL 5.0 when the binary log is enabled. We wrote about group
commit in the previous chapter.
The log buffer
must
be flushed to durable storage to ensure that committed transactions
are fully durable. If you care more about performance than durability, you can change
innodb_flush_log_at_trx_commit
to control where and how often the log buffer is
flushed. Possible settings are as follows:
0
How InnoDB flushes the log buffer.
Write the log buffer to the log file and flush the log file every second, but do nothing
at transaction commit.
1
Write the log buffer to the log file and flush it to durable storage every time a
transaction commits. This is the default (and safest) setting; it guarantees that you
won't lose any committed transactions, unless the disk or operating system “fakes”
the flush operation.
2
Write the log buffer to the log file at every commit, but don't flush it. InnoDB
schedules a flush once every second. The most important difference from the
0
setting (and what makes
2
the preferable setting) is that
2
won't lose any transac-
tions if the MySQL process crashes. If the entire server crashes or loses power,
however, you can still lose transactions.
It's important to know the difference between
writing
the log buffer to the log file and
flushing
the log to durable storage. In most operating systems, writing the buffer to the
log simply moves the data from InnoDB's memory buffer to the operating system's
cache, which is also in memory. It doesn't actually write the data to durable storage.
Thus, settings
0
and
2
usually
result in at most one second of lost data if there's a crash
or a power outage, because the data might exist only in the operating system's cache.
We say “usually” because InnoDB tries to flush the log file to disk about once per second
no matter what, but it is possible to lose more than a second of transactions in some
cases, such as when a flush gets stalled.
In contrast, flushing the log to durable storage means InnoDB asks the operating
system to actually flush the data out of the cache and ensure it is
written to the disk
.
This is a blocking I/O call that doesn't complete until the data is completely written.
Because writing data to a disk is slow, this can dramatically reduce the number of
transactions InnoDB can commit per second when
innodb_flush_log_at_trx_commit
is
