hi,all,
I belive this is a very interesting problem。 For some very unimportant data, could we can set any hidden option or variables to cause the innodb redo log file to be flushed to disk once 5,10 or 60 seconds for better performance? robincui ------------------ Original ------------------ From: "Vinubalaji Gopal"<vinubal...@gmail.com>; Date: Sat, Feb 5, 2011 08:47 AM To: "Rolando Edwards"<redwa...@logicworks.net>; Cc: "Johan De Meersman"<vegiv...@tuxera.be>; "mysql@lists.mysql.com"<mysql@lists.mysql.com>; Subject: Re: writing to disk at a configurable time 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=robin...@vip.qq.com
