Moodle's ACID Trip - Converting #Moodle Databases from MyISAM to INNODB

Note: This started out as an email to Moodle Mayhem asking for help, but then turned into an exploratory journey. Since the process doesn't seem to be articulated anywhere in detail, such as a Moodle wiki page, I thought it might be fun to go through it and see what happens. This is the result.
Image Source: http://3.bp.blogspot.com/_GQ8xBTDqzkc/SS4fQhtoHkI/
AAAAAAAAAPo/VQskgfRbwbI/s400/AcidTrip.jpg


Anyone seen this message after upgrading a Moodle 1.9 instance to Moodle 2.0 in their admin panel? This is what I saw after upgrading to Moodle 2.0 from Moodle 1.9:

"Database tables are using MyISAM database engine, it is recommended to use ACID compliant engine with full transaction support such as InnoDB."

If you're like me, the question must have come up...This has a fascinating overview of what all that means:

And, here's a quick explanation as to why you would want to convert from MyISAM to InnoDB:

Why would you want to convert an existing MySQL database from MyISAM to InnoDB? While the MyISAM format has low overhead and generally the fastest performance among MySQL storage engines, it does not have advanced features like transactions, rollbacks, and row-level locking. InnoDB has these features and is also fully ACID-compliant (atomicity, consistency, isolation, and durability). ACID compliance is one of the touchstones of high-end database systems. I needed these features to solve my problem.

And why you wouldn't want to:

Using the InnoDB storage engine in MySQL is relatively easy, but it does come with a price. The extra features in InnoDB require more resources in terms of CPU, memory, and disk space. After conversion to InnoDB, the database in my application used triple the disk space it did as MyISAM. In addition, because multiple databases are stored in the same data file, backups and restores may be more complicated.


Also reading these Moodle points:
Moodle Tracker - http://bit.ly/fjQcBH

Move to InnoDB will be mandatory (InnoDB storage engine highly recommended) in future versions of Moodle 2.x:


So the question going around in my head is, "how do i migrate 1.9 dbases to InnoDB easily?"

Unless someone has an easier answer, I'm back to the first link mentioned in this email:

Not much information on Moodle's wiki stub page (would this step by step be worth adding?) 

TRYING IT OUT - SWITCHING FROM MyISAM to INNODB
As I was exploring what to do, I decided to take my Moodle 1.9 database and upgrade it to Moodle 2.0.For fun, I did a quick reading of the information here for relevant information then plunged in with these steps:
  1. Did a SQLdump using PHPMyAdmin of the database (View Picture)
  2. Did a find and replace of "myISAM" with "INNODB" (without quotes, of course) (View Picture)
  3. To improve speed (as recommended in the notes, I did the following:
    • Added "SET AUTOCOMMIT = 0;" without quotes to the start of the SQL dump file
    • Added "COMMIT;" without quotes to the end of the SQL dump file
  4. Imported the SQL dump file that I'd made changes to into the MySQL server. (View Picture)
  5. Success, it all worked fine. I verified it by looking 
    1. Looking at the TYPE of table, as it appears in PHPMyAdmin. (View Picture)
    2. Running SHOW ENGINES as a SQL command (View Picture)
Since it all seems to be working fine, what did I do wrong? 
;->

The only question going around in my head now is, "How do I set the INNODB type as the default when creating a database in MySQL?"


One approach might be that it's done when Moodle 2.0 installation script runs and creates the tables with INNODB. So, to adjust my question, Will Moodle 2.0 on a fresh install create tables with INNODB as the database type?

Ok, the answer to that question is, "Yes" as shown in a new Moodle 2.0 installation (View Picture). So it appears the conversion from MyISAM to INNODB need only be done for Moodle 1.9 upgrade to Moodle 2.0 databases.


Thanks for coming along on this "acid trip" with me! I'm definitely open to feedback since I don't pretend to be an expert at this process.

Comments

Helen Foster said…
I was just about to edit http://docs.moodle.org/en/Migration_from_MyISAM_to_InnoDB and add a link to your informative blog post when I noticed you'd been editing the page already! Thanks for your contribution to Moodle Docs :-)
Miguel Guhlin said…
Thanks, Helen! I figured, maybe I needed to step up and "officially" contribute rather than lurk in the shadows (smile).

I converted 12 MyISAM Moodle databases to INNODB follow the steps outlined in this blog entry. Pretty easy and I was shocked.

I was worried that conversion might cause problems, but that turned out to not be the case. I do intend to document my whole migration process for upgrading a 1.9 Moodle instance to 2.0. Good practice since I have so many instances!

With appreciation for the work you do and have done in getting M2.0 ready for Moodle amateurs like me,

Miguel

Popular posts from this blog

#Chromecast Add-Ons to Play Various Video File Formats

Free Professional Learning! Education On Air #googleedu

10 Steps to a Blended Learning Classroom #MIEexpert #MIE #tceamie1