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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
