Posted on 7 Comments

Would you prefer InnoDB to be the default storage engine?

I’ve created a new community poll: Would you prefer InnoDB to be the default storage engine?, as I’m just curious how the community currently feels about this.

Of course, anyone can put --default-storage-engine=InnoDB (and --sql_mode=NO_ENGINE_SUBSTITUTION) in their my.cnf to accomplish the same, but… as a friend of mine has been asking me for years, it would make MySQL “ACID compliant out of the box” (e.g., by default) with no silent ignoring of transactions or foreign key constraints.

You can always specify the desired engine explicitly with CREATE TABLE ... ENGINE=...

One could say that it’s about usability and ease-of-use for new users who don’t yet know much about MySQL or its configuration. Did you know that the config wizard on Windows actually already sets the default storage engine to InnoDB if you select that you might be using transactions? It’s not explicitly noted that that’s the consequence, but it is what happens.

So, please put in your vote, and possibly a comment here explaining your opinion? Great!

Posted on 7 Comments

7 thoughts on “Would you prefer InnoDB to be the default storage engine?

  1. Disclaimer: I think InnoDB is great and I have it set as default in my configurations on all my MySQL servers.

    I just don’t think it’s wise to put a 3rd party storage engine as the default. Also, with Maria vs. Falcon battling to be the new default storage engine, I think this is going to cause some cry foul from within.

    Plus, how would that work if I wanted to use the plugin InnoDB instead? What if the plugin didn’t initialize correctly. In it’s current state, if InnoDB doesn’t start, the database starts and any tables you create default back to MyISAM. So even if you set InnoDB as your default table type, if that’s broken it _still_ defaults to MyISAM.
    The bottom line is… if you want InnoDB to be the absolute default, it MUST always work, all the time.

    haaseg

  2. For any option in a database, in general it makes sense to me that the most conservative option should be the default. If people want to add risk they should take that on themselves, which is why I agree that that the innodb doublewrite buffer should remain enabled by default, and why the innodb flush at trx should remain at 1.

    In the case of InnoDB vs MyISAM, the doublewrite buffer, crash recovery, and transaction support make InnoDB clearly the more conservative option. If for certain applications users want to take on more risk to get the relative benefits of MyISAM then it makes sense to explicitly choose that storage engine.

    In reality though, I don’t expect any change to be made for 5.1. And in 6.0 they have already stated that Falcon will be the default transactional engine. I’m not sure what that actually means, except I do think we can gather that InnoDB is not going to be a default then. So then it’s unlikely and probably even a bit confusing to switch the default engine now even though for the time being it would probably make sense.

  3. I’d guess that the MYISAM replacement MARIA (crashsafe MyISAM) will become the default storage engine at some point down the road. Probably at about the same point that FK constraints for all storage engines will be implemented. There is also the issue of licensing. InnoDB isn’t included in some commercial versions of MySQL (classic & CGE). Despite the licensing issues and confusion which may be caused by the change, it’d just be pretty galling to make an Oracle owned engine the default.

  4. Not quite. If InnoDB is the default, and there is an initialisation failure, the server will stop. And thus you know to check the error log to see and fix the problem. If InnoDB is not the default, you will end up with a “running” server but some inaccessible (InnoDB) tables. I personally prefer the former. Plugin or build-in doesn’t matter.

    I’m intrigued you’re bringing up non-technical factors; I don’t believe they should be the primary concern – at least not to us, in the community. We just want to deal best with our production systems, right?

    But either way… how do you feel about it already being the defacto default on most Windows installations? That may generally not be production, but it’s still over 40% of the installed base…

  5. It’s as GPL as the rest of the server, so what’s the difference?

    What non-GPL customers of Sun/MySQL use is an entirely different and IMHO separate matter, right? Different builds.
    Most people use MySQL under GPL, anyway. And even some non-GPL licensees do have InnoDB.

  6. Interesting argumentation. thanks for that!

    Re Falcon (or Maria), they new. People don’t trust their livelyhood on engines that have not been battle-tested for a number of years. Generally takes 5-8 years, going on past observation.
    One can “announce” anything, but that doesn’t make it something that actual people in the real world want.
    I think Falcon is interesting – as is PBXT by the way! – but they will need time “out there” before bugs and performance/scaling issues can be weeded out. It’s sitting in 6.0 right now, how many people are even trying it?
    If it were a 5.1 plugin, it would get there quicker.

  7. I love InnoDB and for master database servers and most other situations where data loss is not allowed it is a must. For small applications or database slaves MyISAM may be a better choice. The statistics on MyISAM tables are more predictable on slaves and tables often consume less disk space.

    MyISAM has some nice features too, like the ability to easily copy databases and tables from place to place. DML performance may be better on MyISAM especially if the default InnoDB behavior is to sync the transaction log at commit.

    Most applications don’t really need the robustness of InnoDB and most certainly they don’t need the extra complexity. MyISAM is the only supported storage engine for the ‘mysql’ database and tables need to be easily repaired. A corrupted innodb tablespace file could result in a completely unusable database.

    So I vote ‘no’. Hopefully MARIA will be a suitable successor to MyISAM.

Comments are closed.