DimitriK's (dim) Weblog

MySQL Performance: Re:Visiting InnoDB vs MyISAM with MySQL 5.7

Next article from the MySQL 5.7 Performance stories (if missed, see 1.6M SQL Query/sec (QPS) with MySQL 5.7, 1M SQL Query/sec on mixed OLTP_RO , true Point-Selects performance and over 100K Connect/sec Rate -- all with MySQL 5.7)..

The today's article will be about re:visited MyISAM -vs- InnoDB performance comparison within MySQL 5.7 -- in fact the main and detailed article related to these engines comparison I've already published in 2012 but with MySQL 5.6 (just before MySQL 5.6 became GA) -- however, since then I'm constantly re:asked "And what about MySQL 5.7 ?" -- so, the following is the answer ;-))

First of all, let's summarize little bit what was already observed with MySQL 5.6 before :

• so, on Full Text Search (FTS) InnoDB was already way better than MyISAM, no need to replay..
• (on OLTP RW with its table-locking design MyISAM will always loose -vs- row-locking InnoDB, no need to waste a time either)
• then, on mixed OLTP_RO workload : InnoDB was slightly, but better (215K QPS) -vs- MyISAM (200K QPS)
• on Simple-Ranges queries workload : InnoDB was much better (170K QPS) -vs- MyISAM (95K QPS)
• and on Point-Select queries workload : InnoDB was much worse (250K QPS) -vs- MyISQM (430K QPS)
  • NOTE: however, InnoDB was able to reach 450K QPS in experimental on that time "read_only mode", this showed the potential gain possible "in theory" to reach on InnoDB by by-passing the whole transactions layer, but not too much useful in real life (if writes are not allowed), so for MySQL 5.6 times it was more likely a hacking rather a real solution..

But since then, things are changed a lot with MySQL 5.7 :

• the whole transactional layer was greatly improved in InnoDB
• as well the whole MySQL Server code got a rid of all known scalability bottlenecks
• so, we're having a much higher expectation to scale today with MySQL 5.7 than with 5.6 in the past ;-)

However, keeping this all in mind, it's not yet really clear if since then the gap between InnoDB and MyISAM was increased or decreased in MySQL 5.7 -- because general improvements in MySQL Server code made also MyISAM running faster.. -- and as usual, only a real test will give us a real answer ;-)

So far, this was the main reason to re:visit 3 years old obtained results, and my new results presented below are coming from the following :

• HW config : 32cores-HT server (exactly the same I've used 3 years ago) and a newer 40cores-HT server (to observe a tendency)
• OS : the same Oracle Linux 6.5
• Test workloads :
  • load level : 8, 16, 32, .. 1024 concurrent user sessions
  • test cases: Sysbench OLTP_RO, RO Simple-Ranges, RO Distinct-Ranges, RO Point-Selects
  • datasets : single table with 10M rows, 8 tables with 1M rows each

And, finally, here are the results :

Mixed OLTP_RO workload

---

dataset 10M x 1-table @32cores-HT :

dataset 1M x 8-tables @32cores-HT :

dataset 10M x 1-table @40cores-HT :

dataset 1M x 8-tables @40cores-HT :


RO Simple-Ranges

---

dataset 10M x 1-table @32cores-HT :

dataset 1M x 8-tables @32cores-HT :

dataset 10M x 1-table @40cores-HT :

dataset 1M x 8-tables @40cores-HT :


RO Distinct-Ranges

---

dataset 10M x 1-table @32cores-HT :

dataset 1M x 8-tables @32cores-HT :

dataset 10M x 1-table @40cores-HT :

dataset 1M x 8-tables @40cores-HT :


RO Point-Selects

---

dataset 10M x 1-table @32cores-HT :

dataset 1M x 8-tables @32cores-HT :

dataset 10M x 1-table @40cores-HT :

dataset 1M x 8-tables @40cores-HT :


SUMMARY :

• in MySQL 5.7 both InnoDB and MyISAM engines are giving better results than before on the same workloads and the same HW
• all the results obtained on 40cores-HT server are better than on 32cored-HT
• the most critical Point-Select workload is now leaving only a small gain to MyISAM -vs- InnoDB (and it's easy to understand - the overhead of transactions is still present in InnoDB, even if the code was greatly improved for scalability.. -- however, this is now nothing about hacking, but a true, production ready, re:designed code!)..
• then, on all other test workloads - InnoDB is doing just better than MyISAM
• while on all the tests using a single table - InnoDB is far way faster than MyISAM, even on Point-Selects

As usual, any comments are welcome! Thank you for using MySQL ! (and preparing your upgrade to MySQL 5.7 asap ;-))

MySQL 5.7 rocks! ;-)

Rgds,
-Dimitri