Re: InnoDB slow?

[Paul Gallier]

I just want to note the final figures after adjusting the log file size, 
setting innodb_flush_method=O_DSYNC and innodb_flush_log_at_trx_commit=0 
the time was down to 2 minutes 52 seconds (from 10 minutes 37 seconds 
when I first attempted to use InnoDB) compared to 2 minutes 34 seconds 
with MyISAM, however, performance should now be much better when people 
are accessing the web site while data is being written to the database 
(which is fairly constant).
My thanks to everything that helped!

         Paul

Heikki Tuuri wrote:

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