MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:September 2 2003 8:20pm
Subject:Re: InnoDB slow?
View as plain text  

----- Original Message ----- 
From: "Paul Gallier" <panthera@stripped>
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 ; mysql@stripped
> >  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=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

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


to improve performance.

Note that InnoDB really maps

fdatasync() -> fsync()

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
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL

InnoDB slow?Paul Gallier2 Sep
  • Re: InnoDB slow?Chris Nolan2 Sep
  • Re: InnoDB slow?Mikhail Entaltsev2 Sep
    • Re: InnoDB slow?Paul Gallier2 Sep
  • Re: InnoDB slow?Mikhail Entaltsev2 Sep
    • Re: InnoDB slow?Paul Gallier2 Sep
Re: InnoDB slow?Heikki Tuuri2 Sep
  • Re: InnoDB slow?Paul Gallier3 Sep