MySQL soon to store system tables in InnoDB

In the MySQL team, we are changing the system tables currently located in the mysql schema from MyISAM to InnoDB.

Looking at this historically:

  • MyISAM was the default storage engine up until MySQL 5.5.
  • In 5.5 almost 4 years ago, the default storage engine changed to InnoDB, however system tables used for features such as storing privileges and timezones remained as MyISAM.

Unlike MyISAM, InnoDB is an ACID compliant storage engine, with the behaviour that once a transaction commits, modifications are able to survive power-loss or other failures. This is a solid foundation to build applications on, since developers will need to handle fewer failures. To use an example:

  1. Customer places an order
  2. A confirmation email is sent
  3. Power is lost

Without durability, (2) could occur with no record of (1) occurring! Durability is a great feature. However, we do not currently offer this for the system tables which use MyISAM. To use an example:

  1. A DBA revokes a user’s privilege to MySQL (the command returns success)
  2. Power loss occurs
  3. Upon restore, the revoke never applied.

By switching to InnoDB we are improving the experience of system-related tasks by ensuring durability that ACID provides.

This change will have the effect that InnoDB will be required for all MySQL installations, and the configuration setting --skip-innodb will no longer make sense. Users will still be able to use the MyISAM storage engine, and MyISAM-heavy installations can continue to configure the InnoDB buffer pool to as low as 5MB – taking up very little memory.

This is a great step forward for MySQL, and I am personally very excited to see this change. Many in the MySQL community have been requesting this change for years, and we’re happy to now be working on it. If you have any thoughts, please leave a comment, or email me!

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.

  • Hartmut Holzgraefe

    This will break a lot of libmysqld embedded stuff though, I’ve seen quite a few cases where mysql_server_init() is called with “–skip-innodb”, and even if that option is just ignored with a warning embedded applications may not necessarily be ready to deal with the extra InnoDB tablespace and log files getting auto-created …

    • The minimum log file size is 1MB * 2. ibdata1 minimum is now 12MB = 14MB.

  • Congratulations! This is a long time coming, and will make hot backup much more “clean.”

    ETA? Is this a change that we’ll see in MySQL 5.7? (Subject, of course, to the usual disclaimers that nothing is set in stone until GA.)

    • The DMR ‘release train’ model requires for features to be stable before being merged, rather than releases holding for specific features. So I don’t want to get ahead of myself here for work that is in early development. Soon 😀

  • Mike Lischke

    Hartmut, only if people simply switch to the new server version without checking if their app is ready, no? Or is there a guarantee that everything has to run the same forever?

  • dabestone

    This is a good feature, but if possible please make it possible to have an option between MyISAM and InnoDB engine for MySQL system tables.

    • Hartmut Holzgraefe

      Or actually make it fully engine-agnostic … AFAIR there was earlier work on the MySQL Cluster side to allow for ndb_cluster system tables, too …

      • Sergei

        It is fully agnostic in MariaDB. We even have a test case (since 5.1) that InnoDB can be used for system tables.

        And I’ve just tried it with TokuDB (but it had to be statically linked into the server) — that worked too.

        Basically, one can store system tables in almost any engine — it’s just one ALTER TABLE away.

        • Hartmut Holzgraefe

          And now I’m feeling embarrassed for not knowing this (and not having checked this first before replying here at all) …

    • One of the things we’re trying to do with this is to make the usual setup as fully transactional as we can, including things like user data, to avoid a wide range of bugs, inconsistencies and inconveniences. Trying to get rid of issues at their source so we don’t have to explain workarounds or catches.

      For us that means InnoDB, partly because it’s there and partly because it’s what just about everyone we see already uses it anyway so the consistency is a nice extra benefit. We could have provided an option to use Cluster instead of InnoDB but for us it didn’t seem worthwhile.

      But why do you want MyISAM and the risk of losing things? What penalties does having it safely stored in InnoDB cost you other than a little disk space? Maybe you could tell us more about why and how much it hurts so we can better understand why it’s better for your case to use MyISAM? I don’t think it can change what we’re planning in 5.7 but if people come up with really good reasons why it’s too painful to use InnoDB that would be helpful to us in planning what to do.

      From the Support side of things I was very keen on this because it’ll reduce the number of problems that people have. I like trying to get rid of problems before people even see them.

      James Day, MySQL Senior Principal Support Engineer, Oracle

      • dabestone

        Having transactional system tables is good, especially if privileges are changed frequently, but not for all use cases. It would be best if power users would have an option to choose which engine is used for MySQL system tables.

        Example case 1: For database instances where we only use MyISAM (we use it for read-only compressed tables), we completely disable Innodb and would prefer to keep it that way. There is no need for Innodb on those instances.

        Example case 2: Sometimes we manage user privileges and other system tables by copying them directly via file system. Having them in Innodb, would complicate things as we would not be able to copy directly at file system level. Simple example: if we would like to quickly restore a production database to a non-production environment, we just copy all the innodb files from backup (the backup is a filesystem level copy of production) into the non-production instance, but we keep non-production mysql system tables and thus preserve the privileges that were set up specifically for non-production use. Since MySQL 5.6 introduces a few more things that it now stores in system tables, things such as server UUID, we can selectively copy and restore what we need or exclude from the restore things we don’t need, as long as the system tables are MyISAM.

        • Thanks! As it happens I and others from Oracle’s MySQL support team have also been asking the devs to look more at making it easier to move InnoDB tablespaces around at the OS level and that’s been getting better. See for example Todd Farmer’s post here: http://mysqlblog.fivefarmers.com/2012/11/07/smarter-innodb-transportable-tablespace-management-operations/ .

          There’s still more we can do to improve this, like making the tablespaces fully self-describing – full create table info moving around with the tablespace so a destination server can flawlessly load a tablespace on first sight. My main driver for this is data recovery issues, so you can easily get at all undamaged info just by copying files around, then do the harder parts, but it has plenty of other uses.

          We generally assume that innodb_file_per_table is enabled so that it’s easy to do single table copies.

          Do these sorts of things help with your second reservation?

          James Day

          • dabestone

            Yes, being able to move or copy InnoDB tablespaces at OS level will help with the second case. I hope that there will not be any restrictions for system tables.

            Correction: Server UUID in MySQL 5.6 is actually stored in auto.cnf file, not a table. I was thinking of UUID created by MySQL Enterprise – it does go into mysql.inventory table.

  • Roberto Spadim

    maybe aria engine too at mariadb? 🙂 i like aria, ok no acid but a nice myisam with features

  • Peter Zaitsev

    Wonderful! Though probably about 10years late 🙂

  • Pingback: MySQL se tourne encore plus vers InnoDB Qui sera utilisé pour les tables systèmes pour bénéficier des propriétés ACID | Maria DB - Database()

  • Jameson

    So soon?