MySQL性能: InnoDB vs MyISAM in 5.6

Since the latest changes made recently within InnoDB code (MySQL 5.6) to improve OLTP Read-Only performance support of full text search (FTS), I was curious to compare it now with MyISAM.. While there was no doubt that using MyISAM as a st

Since the latest changes made recently within InnoDB code (MySQL 5.6) to improve OLTP Read-Only performance + support of full text search (FTS), I was curious to compare it now with MyISAM..

While there was no doubt that using MyISAM as a storage engine for a heavy RW workloads may become very quickly problematic due its table locking on write design, the Read-Only workloads were still remaining favorable for MyISAM due it's extreme simplicity in data management (no transaction read views overhead, etc.), and specially when FTS was required, where MyISAM until now was the only MySQL engine capable to cover this need.. But then FTS came into InnoDB, and the open question for me is now: is there still any reason to use MyISAM for RO OLTP or FTS wokloads from performance point of view, or InnoDB may now cover this stuff as well..

For my test I will use:

• Sysbench for OLTP RO workloads
• for FTS - slightly remastered test case with "OHSUMED" data set (freely available on Internet)
• All the tests are executed on the 32cores Linux box
• As due internal MySQL / InnoDB / MyISAM contentions some workloads may give a better results if MySQL is running within a less CPU cores, I've used Linux "taskset" to bind mysqld process to a fixed number of cores (32, 24, 16, 8, 4)

Let's get a look on the FTS performance first.

The OHSUMED test contains a less than 1GB data set and 30 FTS similar queries, different only by the key value they are using. However not every query is returning the same number of rows, so to keep the avg load more comparable between different tests, I'm executing the queries in a loop rather to involve them randomly.

The schema is the following:

 CREATE TABLE `ohsumed_innodb` (
	`docid` int(11) NOT NULL,
	`content` text,
	 PRIMARY KEY (`docid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 CREATE TABLE `ohsumed_myisam` (
	`docid` int(11) NOT NULL,
	`content` text,
	 PRIMARY KEY (`docid`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
 alter table ohsumed_innodb add fulltext index ohsumed_innodb_fts(content);
 alter table ohsumed_myisam add fulltext index ohsumed_myisam_fts(content);

And the FTS query is looking like this:

SQL> SELECT count(*) as cnt FROM $(Table) WHERE match(content) against( '$(Word)' );    //?

The $(Table) and $(Word) variables are replaced on fly during the test depending which table (innoDB or MyISAM) and which key word is used during the given query.

And there are 30 key words, each one bringing the following number of records in the query result:

 ------------------------------------------------------------
   Table: ohsumed_innodb
 ------------------------------------------------------------
  1. Pietersz             : 6
  2. REPORTS              : 4011
  3. Shvero               : 4
  4. Couret               : 2
  5. eburnated            : 1
  6. Fison                : 1
  7. Grahovac             : 1
  8. Hylorin              : 1
  9. functionalized       : 4
 10. phase                : 6676
 11. Meyers               : 157
 12. Lecso                : 0
 13. Tsukamoto            : 34
 14. Smogorzewski         : 5
 15. Favaro               : 1
 16. Germall              : 1
 17. microliter           : 170
 18. peroxy               : 5
 19. Krakuer              : 1
 20. APTTL                : 2
 21. jejuni               : 60
 22. Heilbrun             : 9
 23. athletes             : 412
 24. Odensten             : 4
 25. anticomplement       : 5
 26. Beria                : 1
 27. coliplay             : 1
 28. Earlier              : 2900
 29. Gintere              : 0
 30. Abdelhamid           : 4
 ------------------------------------------------------------

Results are exactly the same for MyISAM and InnoDB, while the response times are not. Let's go in details now.

---

FTS : InnoDB vs MyISAM

---

The following graphs are representing the results obtained with:

• MySQL is running on 32, 24, 16, 8, 4 cores
• Same FTS queries are executed non-stop in a loop by 1, 2, 4, .. 256 concurrent users
• So, the first part of graphs is representing 1-256 users test on 32 cores
• The second one the same, but on 24 cores, and so on..
• On the first graph, once again, Performance Schema (PFS) is helping us to understand internal bottlenecks - you'll see the wait events reported by PFS
• And query/sec (QPS) reported by MySQL on the second one

InnoDB FTS :

Observations :

• InnoDB FTS is scaling well from 4 to 16 cores, then performance is only slightly increased due contention on the dictionary mutex..
• However, there is no regression up to 32 cores, and performance continues to increase
• The best result is 13000 QPS on 24 or 32 cores

MyISAM FTS :

Observations :

• MyISAM FTS is scaling only from 4 to 8 cores, and then drop in regression with more cores..
• The main contention is on the LOCK_status mutex
• The best result is 3900 QPS on 8 cores

What about this LOCK_status mutex contention?.. - it gives an impression of a killing bottleneck, and if was resolved, would give an expectation to see MyISAM scale much more high and maybe see 16000 QPS on 32 cores?..

Well, I'd prefer a real result rather an expectation here ;-) So, I've opened MyISAM source code and seek for the LOCK_status mutex usage. In fact this mutex is mainly used to protect table status and other counters. Sure this code can be implemented better to avoid any blocking on counters at all. But my goal here is just to validate the potential impact of potential fix -- supposing there is no more contention on this mutex, what kind of the result may we expect then??

So, I've compiled an experimental MySQL binary having call to LOCK_status mutex commented within MyISAM code, and here is the result:

MyISAM-noLock FTS :

Observations :

• LOCK_status contention is gone
• But its place is taken now by data file read waits... - keeping in mind that all data are already in the file system cache...
• So, the result is slightly better, but data file contention is killing scalability
• Seems like absence of its own cache buffer for data is the main show-stopper for MyISAM here (while FTS index is well cached and key buffer is bigger than enough)..
• The best result now is 4050 QPS still obtained on 8 cores
• NOTE :
  • using mmap() (myisam_use_mmap=1) did not help here, and yet added MyISAM mmap_lock contention
  • interesting that during this RO test performance on MyISAM was better when XFS was used and worse on EXT4 (just thinking about another point inXFS vs EXT4 discussion for MySQL) -- particularly curious because whole data set was cached by the filesystem..

So far:

• InnoDB FTS is at least x3 times faster on this test vs MyISAM
• As well x1.5 times faster on 8 cores where MyISAM shows its top result, and x2 times faster on 4cores too..
• And once dictionary mutex lock contention will be fixed, InnoDB FTS performance will be yet better!

---

OLTP Read-Only : InnoDB vs MyISAM

---

As a start point, I've used "classic" Sysbench OLTP workloads, which are accessing a single table in a database. Single table access is not favorable for MyISAM, so I will even not comment each result, will just note that:

• the main bottleneck in MyISAM during this test is on the "key_root_lock" and "cache_lock" mutex
• if I understood well, the solution to fix "cache_lock" contention in such a workload was proposed withcache segments in MariaDB
• however, it may work only in the POINT SELECTS test (where cache_lock contention is the main bottleneck)
• while in all other tests the "key_root_lock" contention is dominating and for the moment remains not fixed..
• using partitioned table + having per partition key buffer should help here MyISAM, but I'll simply use several tables in the next tests
• InnoDB performance is only limited by MDL locks (MySQL layer), so expected to be yet better once MDL code will be improved
• in the following tests InnoDB is x3-6 times faster than MyISAM..

Sysbench OLTP_RO @InnoDB :


Sysbench OLTP_RO @MyISAM :


Sysbench Simple-Ranges @InnoDB :


Sysbench Simple-Ranges @MyISAM :


Sysbench Point-Selects @InnoDB :


Sysbench Point-Selects @MyISAM :

---

OLTP Read-Only with 8 tables : InnoDB vs MyISAM

---

Test with 8 tables become much more interesting, as it'll dramatically lower key_root_lock contention in MyISAM, and MDL contentions as well. However, we're hitting in MyISAM the key cache mutex contention, so there are 8 key buffers used (one per table) to avoid it. Then, scalability is pretty good on all these tests, so I'm limiting test cases to 64, 32, 24 and 16 cores (64 - means 32cores with both threads enabled (HT)). As well, concurrent users are starting from 8 to use all 8 tables at time.

Let's get a look on OLTP_RO workload first :

Sysbench OLTP_RO 8-tables @InnoDB :


Sysbench OLTP_RO 8-tables @MyISAM :

Observations :

• InnoDB is still better on OLTP_RO than MyISAM..
• for InnoDB, the main bottleneck seems to be on the MDL related part
• for MyISAM - key_root_lock is still here (not as much as before, but still blocking)
• InnoDB is reaching 215K QPS max, and MyISAM 200K QPS
• As you see, speed-up is very significant for both storage engines when activity is not focused on a single table..

And to finish with this workload, let me present you the "most curious" case ;-) -- this test is getting a profit from the fact that within auto-commit mode MySQL code is opening and closing table(s) on every query, while if BEGIN / END transactions statements are used, table(s) are opened since BEGIN and closed only at the END statement, and as OLTP_RO "transaction" contains several queries, this is giving a pretty visible speep-up! Which is even visible on MyISAM tables as well ;-)

So, I'm just turning transactions option "on" within Sysbench OLTP_RO:

Sysbench OLTP_RO 8-tables TRX=on @InnoDB :


Sysbench OLTP_RO 8-tables TRX=on @MyISAM :

Observations :

• InnoDB is going from 215K to 250K QPS
• MyISAM is going from 200K to 220K QPS
• there is definitively something to do with it.. ;-))

Now, what about SIMPLE-RANGES workload?

Sysbench RO Simple-Ranges 8-tables @InnoDB :


Sysbench RO Simple-Ranges 8-tables @MyISAM :

Observations :

• InnoDB is reaching 170K QPS here, mainly blocked by MDL related stuff..
• MyISAM is getting only 95K QPS max, seems to be limited by key_root_lock contention..

So far, InnoDB won over MyISAM on every presented test cases until here.
But get a look now on one case where MyISAM is still better..

---

POINT-SELECTS WITH 8 TABLES

---

I'm dedicating a separate chapter for this particular test workload, as it was the only case I've tested where MyISAM out-passed InnoDB in performance, so required more detailed analyze here.. Both storage engines are scaling really well on this test, so I'm limiting result graphs to 64 (HT) and 32 cores configurations only.

Let's get a look on MyISAM results on MySQL 5.6-rc1 :

Sysbench RO Point-Selects 8-tables @MyISAM 5.6-rc1 :

Observations :

• MyISAM is reaching 270K QPS max on this workload
• and starting to hit MDL-related contentions here!

While MySQL 5.6-rc2 already contains the first part of MDL optimizations ("metadata_locks_hash_instances"), and we may expect a better results now on workloads having MDL_map::mutex contention in the top position. So, let's see hot it helps MyISAM here.

Sysbench RO Point-Selects 8-tables @MyISAM 5.6-rc2 :

Observations :

• Wow! - 360K QPS max(!) - this is a very impressive difference :-)
• then key cache lock contention is blocking MyISAM from going more high..

Then, what about InnoDB here?.. - the problem with InnoDB that even with getting a more light code path with READ ONLY transactions it'll still create/destroy read-view, and on such a workload with short and fast queries such an overhead will be seen very quickly:

Sysbench RO Point-Selects 8-tables @InnoDB 5.6-rc2 :

Observations :

• InnoDB is reaching only 210K QPS max on this workload
• the main bottleneck is coming from trx_sys::mutex contention (related to read-views)
• this contention is even making a QPS drop on 64 cores threads (HT), so the result is better on pure 32cores..

Such a contention is still possible to hide (yes, "hide", which is different from "fix" ;-)) -- we may try to use a bigger "innodb_spin_wait_delay" value. The changes can be applied live on a running system as the setting is dynamic. Let's try now innodb_spin_wait_delay=256 instead of 96 that I'm using usually :

Sysbench RO Point-Selects 8-tables @InnoDB 5.6-rc2 sd=256 :

Observations :

• as you can see, the load is more stable now
• but we got a regression from 210K to 200K QPS..

So, a true fix for trx_sys mutex contention is really needing here to go more far. This work is in progress, so stay tuned ;-) Personally I'm expecting at least 400K QPS here on InnoDB or more (keeping in mind that MyISAM is going throw the same code path to communicate with MySQL server, having syscalls overhead on reading data from the FS cache, and still reaching 360K QPS ;-))

However, before to finish, let's see what are the max QPS numbers may be obtained on this server by reducing some overheads on internals:

• I'll disable Performance Schema instrumentation
• and use prepared statements to reduce SQL parser time..

Sysbench RO Point-Selects 8-tables @MyISAM 5.6-rc2 PFS=off prep_smt=1 :


Sysbench RO Point-Selects 8-tables @InnoDB 5.6-rc2 PFS=off prep_smt=1 :

Observations :

• Wow! 430K (!) QPS max on MyISAM!...
• and 250K (!) QPS on InnoDB!

These results are great!.. - and both are coming due the great improvement made in MySQL 5.6 code.
(specially keeping in mind that just one year ago on the same server I was unable to get more than 100K QPS on InnoDB ;-))

While, anyway, I'm still willing to see something more better from InnoDB (even if I understand all these transactional related stuff constrains, and so on)..

So far, let me show you something ;-))

Starting from the latest MySQL 5.6 version, InnoDB has a "read-only" option -- to switch off all database writes globally for a whole InnoDB instance (innodb_read_only=1).. This option is working very similar to READ ONLY transactions today, while it should do much more better in the near future (because when we know there is no changes possible in the data, then any transaction related constraints may be ignored). And I think the READ ONLY transactions may yet work much more better than today too ;-))

Sunny is working hard on improvement of all this part of code, and currently we have a prototype which is giving us the following on the same workload :

Sysbench RO Point-Selects 8-tables @InnoDB 5.6-rc2-ro_patch PFS=off prep_smt=1 :

Observations :

• as you can see, we're rising 450K (!) QPS within the same test conditions!!! :-)
• and it's yet on an old 32cores bi-thread server..
• it reminds me the famous 750K QPS on "Handler Socket".. - as you see, we become more and more close to it ;-)
• and still passing by a normal SQL and keeping all other RDBMS benefits ;-)
• so, for all users hesitating to use MySQL or move to noSQL land.. - you'll yet be surprised by MySQL power ;-))

INSTEAD OF SUMMARY

• InnoDB seems to be today way faster on FTS than MyISAM
• on OLTP RO workloads InnoDB is also faster than MyISAM, except on point selects, but this gap should be removed too in the near future ;-)
• if you did not try MySQL 5.6 yet -- please, do! -- it's already great, but with your feedback will be yet better! ;-)

And what kind of performance difference you're observing in your workloads?..

Please, share!..

URL

My words

①.  我竟然发现了这篇博文的一个错别字! prefere (should be prefer)!

②. sysbench的结果绘制出图，真心没用过，可以研究一下！还有，怎么用 "OHSUMED" 免费的数据做实验？吃到了"鱼", 没能力学到 "渔"呀！遗憾！

③. 只读事务      [以为只有oracle中有]
只读事务是指只允许执行查询的操作，而不允许执行任何其他dml操作的事务，使用只读事务可以确保用户只能取得某时间点的数据。例如机票代售点每天18点开始统计今天的销售情况，这时可以使用只读事务。在设置了只读事务后，这时可以使用只读事务。在设置了只读事务后，尽管其他会话可能会提交新的事务，但是只读事务将不会取得最新数据的变化，从而可以保证取得特定时间点的数据信息。

如何设置只读事务
SQL>set transaction read only        ///oracle

④.MariaDB

⑤. Dimitrik的测试环境太霸气了！咱这种小罗罗再怎么也弄不出一台可以拿来测试的服务器！所以就先读读博文喽！还是很有收获的！Thanks!

⑥. 可以尝试一下低性能的测试环境下的InnoDB和MyISAM之间的不同！ Just try it！

⑦. 关于 InnoDB和 MyISAM 自己还没有充分地思考，也没有做很多具体的测试！路漫漫……

⑧. 不要相信神话，要自己思考！ mysql 体系结构和 InnoDB 继续仰望和学习中！