Thank you. I will try the different options and see how it performs. On Fri, Feb 4, 2011 at 12:38 PM, Rolando Edwards <redwa...@logicworks.net> 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 > redwa...@logicworks.net > http://www.linkedin.com/in/rolandoedwards > > > -----Original Message----- > From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De > Meersman > Sent: Friday, February 04, 2011 2:57 PM > To: Vinubalaji Gopal > Cc: mysql@lists.mysql.com > 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 <vinubal...@gmail.com>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=vegiv...@tuxera.be >> >> > > > -- > 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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
