MyISAM read is much slower than innodb

Question

I am trying to compare the myisam and innodb write/read performance, but I am suprised that the myisam's read is much more slower than innodb while its write is much more faster, this is totally opposite compared to what I have learned.

The mysql version is 5.7.18-0ubuntu0.16.04.1.

here are my two tables:

mysql> show create table inno_1;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                      |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inno_1 | CREATE TABLE `inno_1` (
  `id` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+



mysql> show create table isam_1;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                      |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| isam_1 | CREATE TABLE `isam_1` (
  `id` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

I tried to write twice 50000 rows to the two tables

[2017-08-07 15:57:12]  [0.86ms]  INSERT INTO `inno_1` (`id`,`name`,`created_at`,`updated_at`) VALUES ('11e77b46-0576-5c30-8e53-1c1b0d1700f9','xxx','2017-08-07 15:57:12','2017-08-07 15:57:12')  
[1 rows affected or returned ] 
insert time : 1m23.905679587s



[2017-08-07 15:55:49]  [0.11ms]  INSERT INTO `isam_1` (`id`,`name`,`created_at`,`updated_at`) VALUES ('11e77b45-d416-79b0-8804-1c1b0d1700f9','xxx','2017-08-07 15:55:49','2017-08-07 15:55:49')  
[1 rows affected or returned ] 
insert time : 5.500709602s

while for the read:

[2017-08-07 15:57:17]  [20.95ms]  SELECT * FROM `inno_1`   ORDER BY id asc LIMIT 1000 OFFSET 100000  
[0 rows affected or returned ] 
select time : 2.076151355s


[2017-08-07 15:56:24]  [353.06ms]  SELECT * FROM `isam_1`   ORDER BY id asc LIMIT 1000 OFFSET 100000  
[0 rows affected or returned ] 
select time : 32.030940358s

I don't understand why it is so different from the official advice, if I changed the id to int, the myisam's read gets improved a lot.

mysql> show create table inno;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inno  | CREATE TABLE `inno` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table isam;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| isam  | CREATE TABLE `isam` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

myisam

[2017-08-07 16:08:37]  [0.09ms]  INSERT INTO `isam` (`name`,`created_at`,`updated_at`) VALUES ('xxx','2017-08-07 16:08:37','2017-08-07 16:08:37')  
[1 rows affected or returned ] 
insert time : 4.745437221s


[2017-08-07 16:08:41]  [12.55ms]  SELECT * FROM `isam`   ORDER BY id asc LIMIT 1000 OFFSET 50000  
[0 rows affected or returned ] 
select time : 1.105638295s

innodb:

[2017-08-07 16:09:26]  [0.87ms]  INSERT INTO `inno` (`name`,`created_at`,`updated_at`) VALUES ('xxx','2017-08-07 16:09:26','2017-08-07 16:09:26')  
[1 rows affected or returned ] 
insert time : 1m28.577975501s


[2017-08-07 16:09:30]  [9.58ms]  SELECT * FROM `inno`   ORDER BY id asc LIMIT 1000 OFFSET 50000  
[0 rows affected or returned ] 
select time : 739.580336ms

could anyone explain?

UPDATE:

I use 10 threads for writing first, then 1 thread for reading.

Three possible explanations: 1. You did this test with a mysql server version from 2017, not from 2007. Things you might have read that were true some years ago do not have to be applicable today. 2. 50000 rows are far from being representative for a select-performance (for insert though). An important difference of innodb and myisam is how they use the disk. Your table with 5-50MB will live in memory on any server built after 1999. 3. There are configuration settings that have influence on your measurements, e.g. cache/buffer sizes. Your testserver is probably not optimized for either engine. — Solarflare
I am using mysql 5.7, I have only set "key_buffer_size = 64M" for myisam, the most surprising thing here is " [2136.55ms] SELECT * FROM isam_1 ORDER BY id asc LIMIT 1000 OFFSET 147000", a query order by string id, takes too much time, while innodb is much faster. — seaguest
I am not entirely sure what you are wondering about. Do you need MyISAM to be faster? MyISAM hasn't been developed for a while now (and will be removed soon), while InnoDB still gets optimized. So a 5.7-InnoDB may now be faster than you expect. Isn't that a nice thing? (Although, simple selects should require a similar time, maybe +-15%. Try repeating it 20 times (without query cache) and add the times up, it might be just statistical noise; or because of using an out-of-the-box-configuration (which isn't perfect for either MyISAM or InnoDB) - you could e.g. use fixed row format for MyISAM.) — Solarflare
thanks for your advice, indeed mysql 8.0 will not support MyISAM any more and it is kind of deprecated, it's better to not use it. The only thing which annoys me is that the "select count(1) " from a 20 million rows takes 7 minutes. — seaguest
Count is a very special case. MyISAM can just take the current number from statistics for it. InnoDB cannot do that, because that value isn't stored for InnoDB tables. That is because of the way transactions work - the count will e.g. reflect the count for the current transaction, and there is currently not a single place where is can be stored. Maybe some day someone will add such a feature, probably as a by-product of some other optimization, as for just "count" it is probably not worth the required changes. If you need it, you can create your own statistics table using e.g. a trigger. — Solarflare

Rick James Rick James · Accepted Answer · 2017-08-16T03:32:25

Black swans do exist; you just have not seen any yet.

Writes

Transactions: MyISAM has no such concept. InnoDB, by default, "autocommits" each of your INSERTs immediately. This essentially requires an extra disk hit; that is what slows down InnoDB. If, on the other hand, you did all the inserts in a single transaction (which is logical for your fabricated test), it would run much faster, maybe as fast as InnoDB.
innodb_flush_log_at_trx_commit can partially control the above overhead -- at the potential risk to safety.
Batching: A single INSERT statement with 100 rows will run about 10 times as fast as 100 single-row INSERTs.
Both engines have some optimization of inserting at the "end" of the table. Keep in mind that this is just one write pattern, so drawing a conclusion here is risky.
Your Write timings differ by a factor of about 10:1. This is consistent with a spinning HDD when you need to read versus having the block cached. Using SSD (Flash) would shrink that ratio significantly.

Reads

MyISAM indexes, including the PRIMARY KEY, are in separate BTrees. They 'point' to the data via a byte offset (or record number). This level of indirection causes your particular read to run slower. Other benchmarks may run faster.
InnoDB "clusters" the PK with the data, hence the extra lookup is avoided.

Id

If id is monotonically increasing, data will be written to the 'end'. (End of data file (.MYD) and end of PK BTree (in .MYI) for MyISAM; End of data+PK BTree for InnoDB.)
If id jumps around, as you are apparently doing with a UUID, then other things are happening. MyISAM's data is appended to, but the BTree for the PK is randomly updated. InnoDB only needs to randomly update the data+PK BTree.
UUID is terrible with cache is blown. Once the BTree for the UUID (PK and/or data) is bigger than can be cached, performance goes downhill. Eventually, the cache is useless and every INSERT is a read-modify-write disk hit to update the PK. This impacts both engines, but in slightly different ways. Since your benchmark involves only a few megabytes, you probably did not hit this.

Caches

MyISAM uses the key_buffer for caching 1KB index blocks. It lets the OS cache data blocks.
InnoDB uses the buffer_pool for caching 16KB blocks for both data and index blocks.
Since the OS probably uses 4KB for the allocation unit on disk, one engine is hitting a fraction of a disk block; the other is grabbing multiple disk blocks at once. This difference translates into slightly slower are faster benchmarks, depending on the randomness of the block accesses, etc.
Improper setting of key_buffer_size and innodb_buffer_pool_size can have a devastating effect on performance. What did you set them to, and how much RAM do you have?

OFFSET

LIMIT 1000 OFFSET 100000 is very unrealistic. It says to step over 100000 rows, one at a time; then fetch 1000. Almost no "real" code uses OFFSET.
Furthermore, that OFFSET is guaranteed to read to the end of the table, then not have any rows to actually read. Again, unrealistic.

Benchmarks are a way to generate Fake News.

MyISAM read is much slower than innodb

1 Answers