MySQL 5.7.8 – Now featuring super_read_only and disabled_storage_engines

I wanted to highlight two new features that are making their way into MySQL 5.7 via the not-yet-released 5.7.8-rc2:

  • A new system variable super_read_only allows a more strict definition of ‘read-only’ which also applies to super users.
  • A new disabled_storage_engines setting offers a way to prevent an enumerated list of storage engines from being used. For example, a DBA may wish to enforce an InnoDB-only policy to simplify common operations such as backups, but it’s possible MyISAM may sneak back in via new code-deployments. This setting allows more active enforcement.

These features are the fruits of our engineering team meeting with our users at Percona Live this year. Thank you to Percona for once again hosting a great conference, and in particular thank you to @isamlambert (and the GitHub Engineering team), @John_Cesario, @denshikarasu & Rob Wultsch for specifically requesting these two features 🙂

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I’ve also worked at Percona and InPowered.

  • Peter Zaitsev

    Morgan,
    For disable storage engines I have 2 thoughts. First – I think you often what to Whitelist rather than Blacklist (you seems to be chosen) ie I know I only want Innodb it is clear. Disabling MyISAM is tricky as what if there was also CVS ARCHIVE or FEDERATED available ?

    Also I do not know whenever it will be still needed in 5.7 too; in 5.6 and below I often find a need to allow MyISAM but only as temporary table. Allowing different list for temporary tables might be good idea.

    • Hi Peter,

      We arrived at blacklist after some discussion. If a user specified ARCHIVE or FEDERATED, there may be a specific reason, but MyISAM is the main use case where it sneaks in from restores from previous versions.

      5.7.8 also has punch hole compression, which reduces the use case for MyISAM further (smaller disk footprint). There have been a number of optimizations for temporary tables in InnoDB, and the optimizer has already switched to using them internally.

      The remaining use-cases for MyISAM should be quite narrow in 5.7 🙂

      • Justin Swanhart

        Well, now you are forcing a user to edit their dump.

        So either mysqlimport should offer an option to change the storage engine (other tools be damned) or it should be a rename option:
        set global rewrite_engine=”myisam=innodb,archive=innodb,federated=innodb”

      • Justin Swanhart

        Can you elaborate on sparse compression? A filesystem block is only sparse if the whole block is punched. Is the idea to reduce the footprint of big blocks of deleted rows?

        • The punch-hole compression is page oriented – and quite simple. As the page is written, it is compressed. any remaining space (minus some alignment factor to filesystem block) is marked free.

          • Justin Swanhart

            I’ve discussed similar techniques on Facebook and the Fastbit mailing lists in regards to the sparse bitmap compression and also data compression for my column store. Neat.

  • Federico Razzoli

    Several choices you made recently make me think that Oracle doesn’t want to support anything except for InnoDB in a future. Is this correct?

    Please consider that the ability to choose is a feature, not a bug. There are cases when using other engines is important. For example, Drupal uses MyISAM for logs – any other choice would not be reliable, because logs should never rollback. Another example: a customer of my company uses MySQL on a non-dedicated server, and InnoDB needs too much memory, so they need MyISAM.

    There are more examples out there. Please, listen your users even when they say something that you don’t like: removing storage engines from MySQL is like removing alcol from beer.

    • Federico, to clarify:
      The default value of disabled_storage_engines is empty. This is a feature requested by our users, and the use case is a very valid one (MyISAM tables block backups).

      You are free to continue to use MyISAM for logs. InnoDB is required for system tables (and soon data dictionary), so the low memory scenario is probably less suitable. But InnoDB *can* still initialize a buffer pool in 5M, which should be reasonable.

  • Giuseppe Maxia

    I Think a parallel “enabled_storage_engines” could be as useful as the blacklist.