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.
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=nosync3 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.
Re: InnoDB slow?
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 ** 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=nosync3 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.
Re: InnoDB slow?
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=nosync3 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
