MySQL性能: InnoDB vs MyISAM in 5.6

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 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.. Whil

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 继续仰望和学习中!


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
30天前
|
存储 关系型数据库 MySQL
MySQL InnoDB数据存储结构
MySQL InnoDB数据存储结构
|
30天前
|
存储 缓存 关系型数据库
MySQL的varchar水真的太深了——InnoDB记录存储结构
varchar(M) 能存多少个字符,为什么提示最大16383?innodb怎么知道varchar真正有多长?记录为NULL,innodb如何处理?某个列数据占用的字节数非常多怎么办?影响每行实际可用空间的因素有哪些?本篇围绕innodb默认行格式dynamic来说说原理。
797 6
MySQL的varchar水真的太深了——InnoDB记录存储结构
|
10天前
|
存储 关系型数据库 MySQL
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
|
10天前
|
缓存 关系型数据库 MySQL
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
|
2天前
|
存储 关系型数据库 MySQL
MySQL引擎对决:深入解析MyISAM和InnoDB的区别
MySQL引擎对决:深入解析MyISAM和InnoDB的区别
9 0
|
5天前
|
关系型数据库 MySQL 数据库
mysql卸载、下载、安装(window版本)
mysql卸载、下载、安装(window版本)
|
30天前
|
关系型数据库 MySQL 数据库连接
关于MySQL-ODBC的zip包安装方法
关于MySQL-ODBC的zip包安装方法
|
1月前
|
SQL 关系型数据库 MySQL
【MySQL】— —熟练掌握用SQL语句实现数据库和基本表的创建。熟练掌握MySQL的安装、客户端登录方法;熟练掌握MySQL的编码、数据类型等基础知识;掌握实体完整性的定义和维护方法、掌握参照完整性
【MySQL】— —熟练掌握用SQL语句实现数据库和基本表的创建。熟练掌握MySQL的安装、客户端登录方法;熟练掌握MySQL的编码、数据类型等基础知识;掌握实体完整性的定义和维护方法、掌握参照完整性
98 1
|
23天前
|
关系型数据库 MySQL 数据库
rds安装数据库客户端工具
安装阿里云RDS的数据库客户端涉及在本地安装对应类型(如MySQL、PostgreSQL)的客户端工具。对于MySQL,可选择MySQL Command-Line Client或图形化工具如Navicat,安装后输入RDS实例的连接参数进行连接。对于PostgreSQL,可以使用`psql`命令行工具或图形化客户端如PgAdmin。首先从阿里云控制台获取连接信息,然后按照官方文档安装客户端,最后配置客户端连接以确保遵循安全指引。
73 1
|
18天前
|
Ubuntu 关系型数据库 MySQL
Ubuntu 中apt 安装MySQL数据库
Ubuntu 中apt 安装MySQL数据库
63 0