MySQL performance: InnoDBvsMyISAMin5.6

SincethelatestchangesmaderecentlywithinInnoDBcode (MySQL5.6) toimproveOLTPRead-Onlyperformancesupportoffulltextsearch (FTS), IwascurioustocompareitnowwithMyISAM.

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 fromPerformancePoint 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 32 cores 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 1 GB 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 MyISAMThe following graphs are representing the results obtained:

• 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 is13000 QPSOn 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 is3900 QPSOn 8 cores

What about this LOCK_status mutex contention ?.. -It gives an impression of a killing bottleneck, and if was resolved, wocould 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 m Ore contention on this mutex, what kind of the result may we have CT 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 is4050 QPSStill 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) -- maid data set was cached by the filesystem ..

So far:

• InnoDB FTS is at leastX3 timesFaster 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 4 cores too ..
• And once dictionary mutex lock contention will be fixed, InnoDB FTS performance will be yet better!

OLTP Read-Only: InnoDB vs MyISAMAs 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 shocould 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 isX3-6 timesFaster 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 MyISAMTest 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 32 cores 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 reaching215 k qps max, And MyISAM200 K 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 215 K250 K QPS
• MyISAM is going from 200 K220 K 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 reaching170 K QPSHere, mainly blocked by MDL related stuff ..
• MyISAM is getting only95 K QPSMax, 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 reaching270 k qps maxOn 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 have CT 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! -360 k 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 only210 K QPSMax 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 210 K to 200 k 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 400 K QPS here on InnoDB or more (keeping in mind that MyISAM is going throw the same code path to communicate with MySQL server, having syscils overhead on reading data from the FS cache, and still reaching 360 k qps ;-))

However, before to finish, let's see what are the max QPS numbers may be obtained on this server by grouping 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!430 K (!) QPS maxOn MyISAM !...
• And250 K (!) QPSOn 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 100 K 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 shocould 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 rising450 K (!) QPSWithin the same test conditions !!! :-)
• And it's yet on an old 32 cores bi-thread server ..
• It reminds me the famous 750 k 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 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, snapshot t on point selects, but this gap shocould 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

①. I found a typo in this blog! Prefere (shocould be prefer )!

②. The result of sysbench is used to plot the image. You can study it! Also, how to use "OHSUMED" for free data testing? "Fish", Unable to learn"Yu"Ah! Sorry!

3. Read-only transactions [only in oracle]
A read-only transaction is a transaction that allows only query operations, but not any other dml operations. Using a read-only transaction, you can only obtain data at a certain time point. For example, you can use read-only transactions to count the sales status of a ticket at every day. After a read-only transaction is set, you can use a read-only transaction. After a read-only transaction is set, although other sessions may commit new transactions, the read-only transaction will not get the latest data changes, so as to ensure that the data information at a specific time point is obtained.

How to Set read-only transactions
SQL> set transaction read only // oracle

④. MariaDB

⑤. The Dimitrik testing environment is too domineering! Why can't I find a server that can be used for testing! So read the blog first! It is still very rewarding! Thanks!

6. Try the differences between InnoDB and MyISAM in a low-performance testing environment! Just try it!

7. I have not fully thought about InnoDB and MyISAM, and I have not done many specific tests! Long journey ......

Invalid.Do not believe in myth. Think for yourself! Mysql architecture and InnoDB continue looking forward to and learning!