List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:February 4 2011 8:38pm
Subject:RE: writing to disk at a configurable time
View as plain text  
innodb_flush_log_at_trx_commit is the parameter to tweek

The following is an excerpt from "MySQL 5.0 Certification Study Guide" (ISBN
Chapter 29, Pages 429,430

InnoDB tries to flush the log approximately once a second in any case, but the
innodb_flush_log_at_trx_commit option cat be set to determine how long writing and
flushing occurs in addition. The setting of this option is directly related to the ACID
durability and the performance as follows:

If you set  innodb_flush_log_at_trx_commit to 1, changes are written form the log buffer
and the log file is flushed to disk for each commit. This guaranteesthat the changes will
not be lost even in the event of a crash. This is the safest setting, and is also the
required setting if you need ACID durability. However, this setting also produces slowest

A setting of 0 causes the log file to be written and flushed to disk approximately once a
second, but not after each commit. Os a bust system, this can reduce log-related disk
activity significantly, but in the event of a crash can result in a loss of about a
second's worth of committed changes.

A setting of 2 causes the log buffer to be written to the log file after each commit, but
file writes are flushed to disk approximately once a second. This is somewhat slower than
a setting of 0. However, the committed changes will not be lost if it is only the MySQL
server that crashes and not the operating system or server host: The machine continues to
run, so the changes written to the log file are in the filesystem cache and eventually
will flushed normally.

If The tradeoff controlled by the innodb_flush_log_at_trx_commit setting therefore is
between durability and performance. If ACID durability is required, a setting of 1 is
necessary. If a slight risk to durability is acceptable to achieve better performance, a
value of 0 or 2 may be used.

Rolando A. Edwards

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx

-----Original Message-----
From: vegivamp@stripped [mailto:vegivamp@stripped] On Behalf Of Johan De Meersman
Sent: Friday, February 04, 2011 2:57 PM
To: Vinubalaji Gopal
Cc: mysql@stripped
Subject: Re: writing to disk at a configurable time

InnoDB definitely has some parameters you can play with, but I've never
actually done so myself.

On Fri, Feb 4, 2011 at 8:09 PM, Vinubalaji Gopal <vinubalaji@stripped>wrote:

> Hi all,
>  I wanted to know if Mysql allows me to configure it such that the
> writes to disk happen at a configurable time or after the buffers have
> enough data. Say there are 10 clients connecting to mysql and each one
> is inserting a row - I want to only write to disk when the buffer has
> enough data (say all the 10 rows are written to the main memory) or at
> some configurable interval. I know there could be  a data loss in this
> case (if a crash happens before the buffer is written to disk) but I
> am willing to take that risk in the application.
> --
> Vinu
> --
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
writing to disk at a configurable timeVinubalaji Gopal4 Feb
  • Re: writing to disk at a configurable timeJohan De Meersman4 Feb
    • RE: writing to disk at a configurable timeRolando Edwards4 Feb
      • Re: writing to disk at a configurable timeVinubalaji Gopal5 Feb
Re: writing to disk at a configurable timeCui Yuming5 Feb