MySQL 8.0: The end of MyISAM

MyISAMThis 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 on 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:

  1. Tables will be smaller on disk compared to uncompressed InnoDB tables.
  2. The count(*) is still much faster in MyISAM:

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.

Learn more about Percona Server for MySQL

Share this post

Comments (48)

  • Justin Swanhart

    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.

    October 11, 2016 at 2:31 pm
    • Morgan Tocker

      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.

      October 11, 2016 at 2:46 pm
      • Justin Swanhart

        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.

        October 11, 2016 at 2:55 pm
      • Justin Swanhart

        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.

        October 12, 2016 at 9:24 am
    • sbester

      related: https://bugs.mysql.com/bug.php?id=71507 (innodb is excessively slow to replace duplicate values in a table)

      October 12, 2016 at 12:14 am
  • Hans J Jakobsen

    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!

    October 12, 2016 at 3:36 am
    • Morgan Tocker

      Hi Hans,

      This use case can most likely be handled with InnoDB with Transportable Tablespaces + Partition Exchange without validation (MySQL 5.7).

      – Morgan

      October 12, 2016 at 8:37 am
  • Mark Callaghan

    I will miss MyISAM. It could have been something. Too bad they never made it crash safe as 10gen did with the MongoDB mmap engine. That would have been a big deal.

    October 12, 2016 at 1:56 pm
  • petre

    Well MariaDB still has Aria.

    October 12, 2016 at 2:33 pm
    • Justin Swanhart

      a) it is gamma
      b) it is too slow to compete with MyISAM 😀

      October 18, 2016 at 6:37 pm
  • datacharmer

    Another reason MyISAM will not get much usage in MySQL 8.0 is that it can’t do partitioning anymore. (https://dev.mysql.com/worklog/task/?id=8971)

    October 12, 2016 at 4:11 pm
    • Justin Swanhart

      They promised to leave HA_Partition in. I strongly disagree with this. All engines should be able to continue to use partitioning. This is not cool.

      October 18, 2016 at 9:16 pm
  • Eric

    How terribly depressing that the fast, small, efficient MyISAM engine is going away, forcing us to replace it with its bloated successor, InnoDB, which eats memory and disk resources and belches out complex, hard-to-manage, hard to backup, hard to restore, hard to copy, hard to move, databases. We have maybe 4000 run-years (400 MySQL instances running for the past 10 years) on MyISAM and we’ve been really happy with it. The great FUD factor that sold InnoDB to the world–the fear of data corruption–never materialized. InnoDB is going to force us to buy five times as much hardware for basically the same level of performance for our particular application. How very exciting.

    October 12, 2016 at 5:22 pm
    • Justin Swanhart

      You just don’t know your data is corrupted.

      October 16, 2016 at 3:06 pm
      • Eric

        Lol. It’s medical data, and is being reviewed every day by doctors and nurses, often going all the way back to a patient’s first visit years ago. It’s not corrupted. Further, we do occasional mysqlchecks just to be 100% sure. They never show any corruption. Thanks for your input.

        October 16, 2016 at 3:11 pm
        • Justin Swanhart

          LOL.. you don’t have checksums. use a real storage engine. Listen to a person that has over 20 years of experience. Or not, your loss. your customers, your data.

          Just let me know where you work, so I never step through your doors.

          October 18, 2016 at 6:33 pm
          • Justin Swanhart

            if a bit changes in a value, your precious mysqlcheck won’t find any problems. A 1 can turn into a 2 and you will have no idea. Then what happens when a medical mistake works. SHAME ON YOUR FOR PUTTING IMPORTANT DATA IN MYISAM. SHAME!

            October 18, 2016 at 6:34 pm
          • Eric

            My ‘lol’ apparently offended you, which was not my intention. MyISAM does have checksums. Just create the tables with CHECKSUM=1 and a live checksum is maintained as the table changes.

            October 18, 2016 at 7:22 pm
          • Justin Swanhart

            That checksum isn’t compared when pages are read from disk. It doesn’t do anything for you 🙂

            October 18, 2016 at 8:40 pm
          • Eric

            Sure it does. It speeds up CHECKSUM TABLE a lot.

            October 18, 2016 at 8:59 pm
          • Justin Swanhart

            and what does that give you? Do you compare that checksum to a copy of InnoDB data to make sure it has the same checksum at the same binlog position? Otherwise it is useless, no more useful than the count of the table to make sure data is OK.

            October 18, 2016 at 9:00 pm
          • Eric

            I’m not following your last comment. As I said, we use MyISAM. So I’m not sure how your question, “Do you compare that checksum to a copy of InnoDB data to make sure it has the same checksum at the same binlog position?” applies. Also, can you explain the purpose of MyISAM’s running-checksum functionality if it truly “does nothing for you” as you stated?

            December 8, 2016 at 11:49 am
          • Eric

            Also, I’d be curious to know what you think about MyRocks. It seems as if it could be a welcome alternative to the bloated, complex, hard-to-manage, grisly nightmare called InnoDB.

            December 8, 2016 at 11:55 am
      • Capsandiego

        Justin, someone (like me) put (true) performance before (possible) data corruption. Just respect this, please.

        December 10, 2016 at 10:04 am
  • Mark Callaghan

    The storage engine API has always been hard, now it is harder and there will be less storage engine diversity. I hope that MyRocks is able to keep up with the API to keep upstream from being an InnoDB-only world.

    October 12, 2016 at 5:27 pm
  • Holger Thiel

    Let me guess: only few inserts and few updates.
    And MyISAM is only performant for only one session.
    If you do not change the data afterwards, MyISAM is nice.

    BUT:
    – no ACID
    – MyISAM is for high performance not suitable.
    => not professional

    October 17, 2016 at 5:12 am
  • alan

    I run a datawarehouse. Loading millions of rows inside a transaction is slow and pointless

    October 17, 2016 at 6:42 am
    • Justin Swanhart

      you have another source to truth, so that isn’t a problem as long as your run verification queries regularly to ensure that your copy is consistent.

      October 18, 2016 at 6:36 pm
      • Mark Callaghan

        Justin – you can make your point without insulting Alan. I have been a fan of MyISAM the past few years. Maybe I root for the underdog. I was happy to learn that it worked for Alan.

        October 18, 2016 at 8:28 pm
        • Justin Swanhart

          There wasn’t anything insulting here. If you use MyISAM, there are no checksums. You must validate your data to the source of truth if you want your warehouse to return true answers. If a 1 turns into a 2, how else will you know?

          October 18, 2016 at 8:42 pm
          • Justin Swanhart

            Lesson learned at Yahoo – ask Jeremy Cole about those SuperMicro risers that corrupted data because the PCI bus has no checksums. We never would have known if we were using MyISAM. Hardware CAN NOT BE TRUSTED.

            October 18, 2016 at 8:43 pm
          • Mark Callaghan

            MySQL was weak in the past about validating input (ask a Postgres fan). Postgres didn’t have page checksums for a long time. Yet we put important data in both and both eventually got better. Regardless, you don’t need to go ALL CAPS to make your point.

            October 18, 2016 at 8:49 pm
          • Mark Callaghan

            I meant to reply to the thread with SHAME

            October 18, 2016 at 8:43 pm
          • Justin Swanhart

            Oh, well, that is the truth. Would you put important medical records in MyISAM. For the love of God I hope not.

            Turn down durability at the session level if you want. XtraDB supports that.

            October 18, 2016 at 8:46 pm
          • Justin Swanhart

            Data validation is a data quality problem, not a data corruption problem. They are orthogonal and are not SE dependent.

            October 18, 2016 at 8:51 pm
          • Mark Callaghan

            You used a DBMS with weak or no support for data validation. Doesn’t matter much whether data was corrupted by disk or an application, the result is still corruption. Do I get to write “shame on you” now?

            October 18, 2016 at 9:01 pm
          • Justin Swanhart

            Corruption means you can’t read the data you wrote. Writing an application that writes bad data isn’t my problem, and using a product that doesn’t prevent it isn’t my problem either. This is 2016. Don’t keep the source of truth in MyISAM. It is gross negligence.

            October 18, 2016 at 9:08 pm
          • Justin Swanhart

            I was a god damned Oracle DBA that was forced to use MySQL. I never used MyISAM, never suggested anyone use MyISAM, and as a consultant I converted dozens of customers from MyISAM to InnoDB. In fact, ironically, an old boss from AdBrite just wrote me an email saying he wanted help converting data to MyISAM. Do you think I said YES? No.

            October 18, 2016 at 9:10 pm
          • Justin Swanhart

            Hit CTRL-C in the middle of an update on MyISAM and watch replication break….

            October 18, 2016 at 9:13 pm
          • Justin Swanhart

            Then let MySQL crash during that update, and watch as your master has completely different rows than the slave. This is nuts. Totally nuts.

            October 18, 2016 at 9:13 pm
  • Peter Laursen

    There is no change for table-based logs (general and slow log. Docs at http://dev.mysql.com/doc/refman/8.0/en/log-destinations.html say:

    “By default, the log tables use the CSV storage engine that writes data in comma-separated values format. For users who have access to the .CSV files that contain log table data, the files are easy to import into other programs such as spreadsheets that can process CSV input.

    The log tables can be altered to use the MyISAM storage engine. You cannot use ALTER TABLE to alter a log table that is in use. The log must be disabled first. No engines other than CSV or MyISAM are legal for the log tables.”

    December 8, 2016 at 9:40 am
  • Rick James

    Tips on converting from MyISAM to InnoDB: http://mysql.rjweb.org/doc.php/myisam2innodb

    January 22, 2017 at 11:26 pm