X

MySQL and MySQL Community information

Comparing InnoDB to MyISAM Performance

Guest Author
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

perf_graph.jpg

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:
                          
                                 innodb-flush-log-at-trx-commit=2

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 !

Join the discussion

Comments ( 11 )
  • Roland Bouman Tuesday, January 25, 2011

    Ok - so the pitch here is, please dump MyISAM, InnoDB is better. If you want fast, MyISAM style, just set innodb-flush-log-at-trx-commit=2 and you get InnoDB fast too (with some risk of data loss, just like in MyISAM)

    so far, so good.

    Now the problem with innodb-flush-log-at-trx-commit=2 is that it is global. I work with fairly small MySQL instances where I use both MyISAM and InnoDB according to my needs. Normally I have innodb-flush-log-at-trx-commit=1 to ensure ACID, so what I would really need is the ability to set innodb-flush-log-at-trx-commit on a per table basis.

    I suppose I could do that now by giving SUPER privilege to my application user account and dynamically set the variable as needed, but that doesnt really seem an option because of the security risk, plus the chance that a wrong setting through human error would result in data loss.

    What do think, would it make sense to build a feature (per-table innodb-flush-log-at-trx-commit settings) like this?


  • Baron Schwartz Tuesday, January 25, 2011

    Roland, I think might also be helpful to set the durability setting per-session instead of globally. Percona Server offers that. I think it would be very hard to set it per-table because a transaction is really what is flushed or delayed, not a table's data, and it wouldn't be manageable to think about recovery if parts of a transaction are committed durably and others aren't. I am waiting to see what users think of the capability in Percona Server to see where this goes next.


  • Baron Schwartz Tuesday, January 25, 2011

    Matt, would you please post the actual throughput numbers for this benchmark, both read-only and read-write? It is hard to figure out exactly from the graphs.


  • mat.keep@oracle.com Tuesday, January 25, 2011

    Sure - Read/Write below:

    Cores

    6

    12

    18

    24

    30

    36

    InnoDB TPS

    2112

    3995

    5831

    7221

    8446

    8920

    MyISAM TPS

    255

    255

    256

    254

    256

    255

    For R/O

    InnoDB

    3127

    6078

    9110

    11550

    13300

    13454

    MyISAM

    2496

    3048

    3034

    3074

    2929

    2900


  • Baron Schwartz Tuesday, January 25, 2011

    Great, thanks! I want to do some scalability analysis on this. I will probably write a post about it, and enter another comment to point to that.


  • mat.keep@oracle.com Tuesday, January 25, 2011

    No problem. The whitepaper contains the configuration parameters used when running the benchmark


  • Igor Thursday, January 27, 2011

    And what MyISAM config parameters values were used for this benchmarking? I can only see those for InnoDB on pg 14 of WP.


  • mat.keep@oracle.com Thursday, January 27, 2011

    Hi Igor - The default MyISAM parameters.


  • Ivan Zoratti Sunday, February 6, 2011

    Matt,

    I think that comparing InnoDB vs MyISAM with an OLTP benchmark is pointless and by changing only one set of parameters is misleading.

    Have you tried to compare the two engines with a TCP-H like benchmark? 95% of the audience already know that InnoDB is a good choice for OLTP apps, it would be interesting to know if InnoDB 5.5 can replace MyISAM in read-intensive apps as well.

    My 2c.

    -ivan


  • mat.keep@oracle.com Sunday, February 6, 2011

    Ivan - not sure if you read the whitepaper, but as indicated in the blog, 2 variants of Sysbench were run:

    - Read/Write

    - Read Only

    In terms of running other benchmarks, yes - and we also encourage users to try it on their own workloads and report results back to us

    This way we can continue ti provide the guidance our users need to select the best storage engine for their needs


  • Mat Keep Monday, November 28, 2011

    InnoDB is the standard, default storage engine shipped with MySQL, so should also be your default choice


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha