The MySQL performance team in Oracle has recently completed a series of benchmarks comparing Read / Write and Read-Only performance of MySQL 5.5 with the InnoDB and MyISAM storage engines.
Compared to MyISAM, InnoDB delivered 35x higher throughput on the Read / Write test and 5x higher throughput on the Read-Only test, with 90% scalability across 36 CPU cores.
A full analysis of results and MySQL configuration parameters are documented in a new whitepaper
In addition to the benchmark, the new whitepaper
, also includes:
- A discussion of the use-cases for each storage engine
- Best practices for users considering the migration of existing applications from MyISAM to InnoDB
- A summary of the performance and scalability enhancements introduced with MySQL 5.5 and InnoDB 1.1
As the whitepaper
shows, one of the benefits of InnoDB is that its behavior is highly configurable. Therefore, properties such as ACID-compliance can be set according to the demands of the workload. For example, ecommerce would demand the highest levels of data integrity, while other workloads, such as content management where throughput is more important, are fine with relaxed ACID properties.
These benchmarks were run with relaxed ACID constraints to provide a more comparable analysis with MyISAM, using the following configuration parameter:
Using the value above, the log file is flushed once per second, essentially batching writes to disk, rather than committing transactions individually. The result is higher throughput, especially for update-intensive and I/O bound workloads.
When configured in this way, only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB's crash recovery is not affected and continues to work regardless of the configuration value, so users still get the benefit of much higher levels of protection than they do with MyISAM.
The benchmark itself was based on Sysbench, running on AMD Opteron "Magny-Cours" processors, and Oracle Linux with the Unbreakable Enterprise Kernel
You can learn more about MySQL 5.5 and InnoDB 1.1 from here
and download it from here
to test whether you witness performance gains in your real-world applications. Let us know how you get on !