MySQL性能: InnoDB vs MyISAM in 5.6

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 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 继续仰望和学习中!


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
19天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
129 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
19天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的表空间
InnoDB是MySQL默认的存储引擎,主要由存储结构、内存结构和线程结构组成。其存储结构分为逻辑和物理两部分,逻辑存储结构包括表空间、段、区和页。表空间是InnoDB逻辑结构的最高层,所有数据都存放在其中。默认情况下,InnoDB有一个共享表空间ibdata1,用于存放撤销信息、系统事务信息等。启用参数`innodb_file_per_table`后,每张表的数据可以单独存放在一个表空间内,但撤销信息等仍存放在共享表空间中。
|
19天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的段、区和页
MySQL的InnoDB存储引擎逻辑存储结构与Oracle相似,包括表空间、段、区和页。表空间由段和页组成,段包括数据段、索引段等。区是1MB的连续空间,页是16KB的最小物理存储单位。InnoDB是面向行的存储引擎,每个页最多可存放7992行记录。
|
18天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
33 1
|
21天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
35 4
|
2月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
64 3
Mysql(4)—数据库索引
|
27天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
151 1
|
1月前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
86 2
|
1月前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
118 4
|
15天前
|
运维 关系型数据库 MySQL
安装MySQL8数据库
本文介绍了MySQL的不同版本及其特点,并详细描述了如何通过Yum源安装MySQL 8.4社区版,包括配置Yum源、安装MySQL、启动服务、设置开机自启动、修改root用户密码以及设置远程登录等步骤。最后还提供了测试连接的方法。适用于初学者和运维人员。
123 0