Hi! ----- Original Message ----- From: "Paul Gallier" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, September 02, 2003 9:55 PM Subject: Re: InnoDB slow?
> --------------060404050304080006000506 > Content-Type: text/plain; charset=us-ascii; format=flowed > Content-Transfer-Encoding: 7bit > > I've not a clue - digging around somewhere on the Internet. I didn't > see the info in the manual regarding fsync being used as default for > Linux, but now I also notice that my manual says version 4.0.5.... <off > to grab current manual> > > > Mikhail Entaltsev wrote: > > >Paul, > > > >Where did you find information about 'littlesync' and 'nosync'? > >In InnoDB manual I found only 'nosync' and 'littlesync' are undocumented features :). They were documented 2 years ago, but I removed the documentation because I did not want to maintain these features. > >************** > >This is only relevant on Unix. The default value for this is fdatasync. Then InnoDB uses fsync() to flush both the data and log files. If O_DSYNC is specified, InnoDB uses O_SYNC to open and flush the log files, but uses fsync() to flush the data files. If O_DIRECT is specified (available on some Linux versions starting from MySQL-4.0.14), InnoDB uses O_DIRECT to open the data files, and uses fsync() to flush both the data and log files. Note that InnoDB does not use fdatasync() or O_DSYNC because there have been problems with them on many Unix flavors. > >************** > > > >Mikhail. > > ----- Original Message ----- > > From: Paul Gallier > > To: Mikhail Entaltsev ; [EMAIL PROTECTED] > > Sent: Tuesday, September 02, 2003 4:24 AM > > Subject: Re: InnoDB slow? > > > > > > Thanks for the info. > > I'm running MySQL 4.0.14 under Redhat 8.0 / Linux 2.4.20. > > Here are the timings I ended up with from playing with innodb_flush_method: > > > > innodb_flush_method=fdatasync (default) 10 minutes 37 seconds > > innodb_flush_method=littlesync 10 minutes 22 seconds > > innodb_flush_method=O_DSYNC 5 minutes 18 seconds > > innodb_flush_method=nosync 3 minutes 12 seconds > > MyISAM tables instead of InnoDB 2 minutes 34 seconds > > > > Now of course, the question is what potential harm am I looking at by using nosync or o_dsync? > > 'nosync' is dangerous. If there is a power outage, or the OS crashes, there is a great chance that your tablespace will be corrupted. MyISAM always runs in the 'nosync' mode, that is, it never calls fsync() to flush the files to disk. InnoDB's nosync is useful in testing if some OS/computer is extremely slow in fsync(). But it should not be used in a production system. O_DSYNC is safe, assuming there are no bugs in Linux/drivers/hardware. Since it is not very much used, the risk of bugs is bigger than for the default value fdatasync. I would rather tweak innodb_buffer_pool_size innodb_log_file_size innodb_flush_log_at_trx_commit to improve performance. Note that InnoDB really maps fdatasync() -> fsync() O_DSYNC -> O_SYNC This is because in 2001 there was some evidence that fdatasync() caused file corruption both in Linux and Solaris. > > Mikhail Entaltsev wrote: > > > >Paul, > > > >if your MySQL server is runnign under Linux then try to play with > >"innodb_flush_method" variable. > >I've changed it to O_DSYNC and InnoDB became ~ 9 times faster (Suse 8.2 > >Linux 2.4.20-4GB i386). > >Also check that you didn't allocate too much memory (OS shouldn't swap). > > > >Best regards, > >Mikhail. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
