MySQL 8.0: The end of MyISAM
This blog discusses the gradual end of MyISAM in MySQL.
The story that started 20 years ago is coming to its end. I’m talking about the old MyISAM storage engine that was the only storage provided by MySQL in 1995, and was available in MySQL for 20+ years. Actually, part of my job as a MySQL consultant for 10+ years was to discover MyISAM tables and advise customers how to convert those to InnoDB.
(Check your MySQL installation, you may still have MyISAM tables).
MySQL 5.7 still used MyISAM storage for the system tables in the MySQL schema.
In MySQL 8.0 (DMR version as of writing), the MyISAM storage engine is still available. But in a very limited scope:
- After introducing the new data dictionary, the MyISAM tables are gone from the system schema (“mysql” db).
- Working with MyISAM is harder now (and discouraged): you can’t just copy MyISAM tables into a running MySQL server, they will not be discovered (unlike InnoDB, where you can use “ALTER TABLE … IMPORT TABLESPACE”)
- However, you can create a table engine=MyISAM, and it will work as before
InnoDB implemented all the older, missing features:
| Feature | MyISAM | InnoDB |
| Full Text Indexes | yes | Since MySQL 5.6 |
| Portable tables (tablespaces) | yes | Since MySQL 5.6 |
| Spatial Indexes/RTREE (GIS) | yes | Since MySQL 5.7 |
| Last update for table | yes | Since MySQL 5.7
(http://dev.mysql.com/worklog/task/?id=6658) |
| Suitable for temp tables | yes | Since MySQL 5.7
Also complex selects uses InnoDB ondisk temp tables |
| Faster count(*) | yes | *Faster in MySQL 5.7 but does not store counter |
So the only MyISAM advantages left are:
- Tables will be smaller on disk compared to uncompressed InnoDB tables.
- The count(*) is still much faster in MyISAM:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select count(*) from a_myisam; +----------+ | count(*) | +----------+ | 6291456 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from a_innodb; +----------+ | count(*) | +----------+ | 6291456 | +----------+ 1 row in set (2.16 sec) |
I would not use MyISAM unless there is a specific case, and for well-known reasons (MyISAM are non-transactional, table level locks, with no crash recovery, etc.)
My colleague Laurynas Biveinis also suggested converting MyISAM to an optional storage engine plugin.
MyISAM still does WAY better on the hot row problem, such as an increment of the same value over and over, as in a sequence. I did a significant number of tests on this, when I worked at Percona. They are probably still on the internal “experts” mailing list (they would be replies to Jay). I’d like to see some way to get InnoDB perform like MyISAM in such scenarios, as MyISAM exceeds the performance of InnoDB significantly in this particular use case.
Sam Lambert has previously written about a workaround for this problem:
http://samlambert.com/posts/mysql-slotted-counter
Depends on the use case of course how viable it is – but since we are all moving in the direction of semi-sync and GR, hot rows just becomes a hard problem due to Callaghan’s Law.
This is one reason why autonomous/anonymous transactions would be useful (retrying a one row trx is easy to do compared to a general group commit conflict). Regardless, there are data stores that are more suited to counters and such. MySQL is not always the best tool for the job.
InnoDB heap tables would be nice, for a number of different workloads. PG is missing IOT, and MySQL InnoDB is missing heap tables. It would be nice to have the best of both worlds.
related: https://bugs.mysql.com/bug.php?id=71507 (innodb is excessively slow to replace duplicate values in a table)
Merge table still missing.
Have a case where some collectors have a table that are updated. Every 5 min. the tables are moved to to a central machine and merge table gives the full view.
The rows are (relative) stable(2M+) There are 20M+ updates/hour.
I am considering to centralize DB(innodb or maybe NDB) But a perfomance test have to be done!
Hi Hans,
This use case can most likely be handled with InnoDB with Transportable Tablespaces + Partition Exchange without validation (MySQL 5.7).
– Morgan