List:General Discussion« Previous MessageNext Message »
From:Vinubalaji Gopal Date:February 5 2011 12:47am
Subject:Re: writing to disk at a configurable time
View as plain text  
Thank you. I will try the different options and see how it performs.

On Fri, Feb 4, 2011 at 12:38 PM, Rolando Edwards
<redwards@stripped> wrote:
> innodb_flush_log_at_trx_commit is the parameter to tweek
>
> The following is an excerpt from "MySQL 5.0 Certification Study Guide" (ISBN
> 0-672-32812-7)
>
> http://www.amazon.com/MySQL-5-0-Certification-Study-Guide/dp/0672328127/ref=sr_1_1?ie=UTF8&qid=1296851778&sr=8-1
> 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 performance.
>
> 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
> MySQL DBA (SCMDBA)
>
> 155 Avenue of the Americas, Fifth Floor
> New York, NY 10013
> 212-625-5307 (Work)
> 201-660-3221 (Cell)
> AIM & Skype : RolandoLogicWorx
> redwards@stripped
> http://www.linkedin.com/in/rolandoedwards
>
>
> -----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: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>



-- 
Vinu

In a world without fences who needs Gates?
Thread
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