Paul,
Where did you find information about 'littlesync' and 'nosync'?
In InnoDB manual I found only
**************
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?
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.
----- Original Message -----
From: "Paul Gallier" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, September 02, 2003 2:57 AM
Subject: InnoDB slow?
I'm running a large database which is currently using MyISAM. There are
approximately 300 million rows in about a dozen tables totaling 7GB of
storage. The system is averaging 257 querries per second, probably
peaking at around 500-600+ during busy times. We're running a single
database with one programming doing insertions/updates and a web server
doing only selects. The problem is that the insertions/updates tend to
bog down a bit when the web side gets busy. I figured switching to
InnoDB might help with the row-locking support, however, on my test
system things ran very slow using InnoDB instead of MyISAM. This was
just running the script handling the inserts/updates with no web access,
but a test that took 2 minutes 34 seconds using MyISAM tables took 10
minutes 37 seconds using InnoDB. I setup enough disk space to hold the
tables and had seemingly sufficient ammounts of mmeory configured for
InnoDB, so I cannot understand the drastic slowdown. Any advise would
be greatly appreciated.
