InnoDB or MyISAM database engine for global tables?

Hi all,

I'm wondering why the MyISAM database engine is chosen on creation of global database tables of plugins like MarketPress and Anti-Splog (and others?).

Our server is running MySQL 5.6 so the InnoDB engine is default. New blog tables are all set to use InnoDB. Still, new tables created by WPMUDEV plugins seem to prefer MyISAM. Is there a reason for this?

I've had a MySQL server crash and restarting the service revealed warnings in the error.log:

2014-11-29 12:55:05 22243 [ERROR] /usr/sbin/mysqld: Table './aufildudoux_fr/wp_mp_products' is marked as crashed and should be repaired
2014-11-29 12:55:05 22243 [Warning] Checking table:   './aufildudoux_fr/wp_mp_products'
2014-11-29 12:55:05 22243 [ERROR] /usr/sbin/mysqld: Table './aufildudoux_fr/wp_ust' is marked as crashed and should be repaired
2014-11-29 12:55:05 22243 [Warning] Checking table:   './aufildudoux_fr/wp_ust'

Checking the DB, I noticed that these tables are among the few to use the MyISAM engine. No idea if this is related at all (no definitive errors found that explain these crashes) but I decided to switch these tables over to InnoDB.

No problems so far but just to make sure: can anyone tell me more about why MyISAM is chosen on table creation?

Thanks :slight_smile:

  • aristath
    • Recruit

    Hello there @RavanH, I hope you're well today!

    ALL hosts support MyISAM, however not all of them have InnoDB support.
    That's why it is the default option there. it's simply considered safer.
    Of course you can convert them to Inno if you wish, no problem there. :slight_smile:

    I hope that helps!

    Cheers,
    Ari.

  • Paul
    • New Recruit

    There's another reason to use myisam instead of innodb: future proofing.

    Correct me if I am wrong, but innodb is still tied to the code now essentially owned by Oracle, who is temporarily playing nice but does not "have to" in the future. The myisam engine exists also in mariadb (same developers as mysql which quit some time after Oracle but Sun which included the original company. Monty Widenius has stated that the mariadb will never be sold.

  • aristath
    • Recruit

    Hello there @Paul!

    I agree that MariaDB is preferable... in fact that's what I use on most my VPSs. I also use Percona on a couple of them... :wink:

    MariaDB does support InnoDB, and in addition the Aria engine which is something between MyISSAM and Inno.

    The point is that MyISSAM is universal. No matter what your server has! It works on MySQL, MariaDB, Percona and even MSSQL (yeah... unfortunately there are people that use that as well!). That's why we use that for the custom tables.

    Cheers,
    Ari.

  • RavanH
    • The Crimson Coder

    Hi guys, thanks for the explanations :slight_smile:

    A follow-up question: since it apparently does not matter to these plugins which engine is used, why is ENGINE=MyISAM specified at all on table creation? WordPress itself does not do that on install or update...

    Not specifying the engine leaves it up to the server default, according to the webmasters own preference.

    About MariaDB: is WordPress fully stable on it? What about plugins like W3TC? It certainly sounds like a great alternative :slight_smile:

  • aristath
    • Recruit

    Hello again @RavanH,

    A follow-up question: since it apparently does not matter to these plugins which engine is used, why is ENGINE=MyISAM specified at all on table creation? WordPress itself does not do that on install or update...

    To be honest I don't know... I'll ask the developers about that.

    About MariaDB: is WordPress fully stable on it? What about plugins like W3TC? It certainly sounds like a great alternative :slight_smile:

    Both MariaDB and Percona are drop-in replacements for MySQL and 100% compatible.
    WordPress works on them without any issues and they are both completely stable.
    Performance-wise, both Percona and MariaDB are faster than MySQL.
    If I were you, I'd prefer using MariaDB 10.
    If version 10 is not available for some reason to you, then my next choice would be Percona 5.x, then MariaDB 5.x, and MySQL would be my last choice.

    Cheers,
    Ari.

  • Paul
    • New Recruit

    Ari, one thing that most folks don't know how to do, or how to ask their hosting provider to do is to move their "mysql" instances to mariaDB, etc., some services don't even offer the option.

    Development wise on my own machine it wasn't that hard, but I also don't have a good link for folks to follow in terms of the "how to" on Linux machines, etc. If there's a tutorial you suggest, perhaps you could put a link to it here?

  • aristath
    • Recruit

    Hello again!

    MariaDB was developed by the guys that originally built MySQL, but then got sick of Oracle's policy and decided to do it better and stay open-source.
    In MySQL there's an "Enterprise" edition, and MariaDB is as close to that as you can get while staying open-source.
    The good thing is that MariaDB is a drop-in replacement for MySQL.
    Upgrading from MySQL to MariaDB is as simple as it can get. First uninstall MySQL, then install MariaDB. That's all there is to it! The only 2 guides I found offer no details, but that's only because no details are necessary!
    Official guide on mariadb.com: https://mariadb.com/kb/en/mariadb/documentation/getting-started/upgrading/upgrading-from-mysql-to-mariadb/
    DigitalOcean's guide: https://www.digitalocean.com/community/tutorials/switching-to-mariadb-from-mysql

    You shouldn't have any issues... just remember to backup your dbs (just in case)

    Cheers,
    Ari.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.