MySQL Forums
Forum List  »  InnoDB

Re: InnoDB, Performance Problems
Posted by: James Day
Date: March 28, 2005 04:16PM

MyISAM: allowed to lose the data you just inserted if there's an unexpected restart of the system. Or can lose the update to one table but not another, resulting in a partially applied transaction. MyISAM isn't transactional and doesn't come with the transactional guarantees.

InnoDB: required to ensure that your data is safe against that unexpected restart and not tell you it's saved until it can do that. For the whole transaction, not just one table. It does that by telling the disk to flush sufficient data to disk after every transaction, limiting you to no more transactions per second than the number of forced flushes/writes your disk system can handle per second, typically a maximum of 250 or so unless you have a caching disk controller. InnoDB also has better recovery after unexpected restarts, becoming more reliable and faster than MyISAM as your data size grows beyond a few gigabytes.

What's happening with one insert is that MyISAM is writing the data to RAM (OS buffers) and immediately returning. InnoDB is ensuring relational correctness and not returning until the data (or the log write necessary to recover it automatically) is on the disk surface.

Try 10,000 individual inserts instead. What you'll probably find is that your InnoDB version is limited to no more than 250 inserts per second if you're doing each as an individual transaction. If it's not, you either have a write caching disk controller or you're risking loss of data in an unexpected restart.

Now switch to inserting in batches of 100 per transaction. You may still be inserting in small enough volumes for the per-transaction overhead to exceed the work needed for the updates themselves. So try 100,000 and batches of 1,000.

Now move to enough data for real production use in a memory-constrained situation where you can't cache everything and where there tend to be sets of frequently accessed and updated records. At this point, the better caching architecture of InnoDB may help InnoDB to be faster than MyISAM. Depends on the data access pattern.

If you're not using any my.cnf file, do use settings suitable for your system. It makes a great difference to performance. Some examples (my_medium.cnf, for example) are available.

You can also tell InnoDB to compromise if performance matters more than the relational guarantees. Or you can mix and match InnoDB and MyISAM, using each where applicable.

See the InnoDB performance tuning section of the manual, at http://dev.mysql.com/doc/mysql/en/innodb-tuning.html . If you are willing to lose the transactional durability guarantee, see http://www.mysql.com/news-and-events/newsletter/2003-12/a0000000285.html and set innodb_flush_log_at_trx_commit to 0 or 2. You willl then lose the most recent transactions, just as you will with MyISAM, if there is an unexpected restart. Either setting will remove the 250 per second limit. So can a write caching disk controller with battery backup, a safer way to get similar improvement.

See http://dev.mysql.com/tech-resources/articles/storage-engine/part_1.html and parts 2 and 3 for an overview of the properties of the storage engines available.

Options: ReplyQuote


Subject
Views
Written By
Posted
3142
March 28, 2005 02:22PM
Re: InnoDB, Performance Problems
2241
March 28, 2005 04:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.