Comparing InnoDB to MyISAM Performance

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 !

Comments:

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?

Posted by Roland Bouman on January 25, 2011 at 02:52 AM GMT #

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.

Posted by Baron Schwartz on January 25, 2011 at 04:03 AM GMT #

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.

Posted by Baron Schwartz on January 25, 2011 at 04:09 AM GMT #

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

Posted by mat.keep@oracle.com on January 25, 2011 at 04:34 AM GMT #

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.

Posted by Baron Schwartz on January 25, 2011 at 05:33 AM GMT #

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

Posted by mat.keep@oracle.com on January 25, 2011 at 05:40 AM GMT #

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

Posted by Igor on January 27, 2011 at 06:08 AM GMT #

Hi Igor - The default MyISAM parameters.

Posted by mat.keep@oracle.com on January 27, 2011 at 06:59 AM GMT #

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

Posted by Ivan Zoratti on February 06, 2011 at 01:51 PM GMT #

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

Posted by mat.keep@oracle.com on February 06, 2011 at 10:53 PM GMT #

If I have table with a lot of reads, but ones a month I write to this table I should use MyISAM.
Am I right?

Posted by בונה אתרים on November 28, 2011 at 05:49 AM GMT #

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

Posted by Mat Keep on November 28, 2011 at 05:51 AM GMT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Get the latest updates on products, technology, news, events, webcasts, customers and more.

Twitter


Facebook

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
5
6
9
10
11
12
13
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today