Re: [boost] ACID transactions/flushing/MySQL InnoDB

Subject: Re: [boost] ACID transactions/flushing/MySQL InnoDB
From: Jose (jmalv04_at_[hidden])
Date: 2009-12-10 10:56:27

On Thu, Dec 10, 2009 at 6:07 PM, Stefan Strasser <strasser_at_[hidden]> wrote:
> Is that what InnoDB is doing?
> the only way I see to reduce flushes even more is combining some transactions.
> but after a power failure some of those transactions can be lost even though
> they haven reported as successfully committed.

Hi Stefan,

This is the setting where you can change the performance vs durability tradeoff:

http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html
innodb_flush_log_at_trx_commit

If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is
written out to the log file once per second and the flush to disk
operation is performed on the log file, but nothing is done at a
transaction commit. When the value is 1, the log buffer is written out
to the log file at each transaction commit and the flush to disk
operation is performed on the log file. When the value is 2, the log
buffer is written out to the file at each commit, but the flush to
disk operation is not performed on it. However, the flushing on the
log file takes place once per second also when the value is 2. Note
that the once-per-second flushing is not 100% guaranteed to happen
every second, due to process scheduling issues.

The default value of this variable is 1 (prior to MySQL 4.0.13, the
default is 0).

A value of 1 is required for ACID compliance. You can achieve better
performance by setting the value different from 1, but then you can
lose at most one second worth of transactions in a crash. With a value
of 0, any mysqld process crash can erase the last second of
transactions. With a value of 2, then only an operating system crash
or a power outage can erase the last second of transactions. However,
InnoDB's crash recovery is not affected and thus crash recovery does
work regardless of the value.