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 us本文来源gaodai#ma#com搞*!代#%^码网%ed 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 ------------------------------------------------------------

喜欢 (0)赏

[搞代码]

分享 (0)