Let’s now put the question of MySQL Storage Engine to rest. If all of your MySQL tables are using InnoDB and you don’t need convincing that InnoDB is the way to go, you’re all set. If you are unsure, however, bear we me. We have some ground to cover.

Storage Engine What?

MySQL has supported pluggable storage engines since its inception over 20 years ago, but for a very long time MyISAM was the default storage engine and many people running MySQL didn’t even know anything about the underlying storage engines. After all, MySQL was originally designed to be a small practical database for small websites and many applications got into habit of using MyISAM storage engine explicitly.

This seemed like a good idea first, but here’s the problem: MyISAM was NOT designed with highly concurrent workload, number of CPU cores and RAID arrays in mind. And it was never meant to be resilient either. So as websites started getting more traffic, they could no longer scale, because MySQL queries would spend seconds waiting on table level locks (the only locking mechanism that MyISAM supports). And they didn’t want to be loosing their business critical data on each MySQL crash either.

Meet InnoDB

What many people don’t know though is that virtually for as long as MySQL existed, MyISAM storage engine had a cousin called InnoDB. And highly concurrent workload, performance and resilience (also atomicity, consistency and isolation) was exactly where InnoDB shined.

Sure, it had a few bumps as it was growing up (most notably, scalability issues before version 5.0.30), but over the last 9 years InnoDB has been improved in all areas you can (and can’t) imagine, whereas MyISAM got virtually no attention at all.

Therefore, as of MySQL 5.5.5, InnoDB became the default storage engine and nowadays you will hardly find a sizeable MySQL installation that’s still using MyISAM and not InnoDB.

Now before we go any further, let me tell you how you can quickly get a count and a list of MyISAM tables on your system so you can start planning your migration.

Storage engines used by your database

Here’s a really cool query that shows the storage engines you are using and a number of tables using each storage engine:

You can see that this particular customer had 13 MyISAM tables holding over 7GB worth of data combined. If you find yourself in a similar situation, don’t worry, we’ll fix that soon.

To get a list of all MyISAM tables sorted by size, just run this query:

Bear in mind that changing default-storage-engine setting to InnoDB or upgrading MySQL doesn’t automagically convert all your tables to InnoDB. Far from it. You actually have to go and convert tables one by one (or have a script do it).

Now before you go on and convert “just the big ones” to InnoDB, here’s something really important:

Why small MyISAM tables matter too
Sometimes as part of migration to InnoDB, DBAs start with the large MyISAM tables to see if things will get better. Sometimes it helps, but in many cases it doesn’t. Here’s the problem: if at least one table in a join is MyISAM, the entire query is using table level locks. So having even a small MyISAM table in a large join can be very bad for concurrency.

So when you’re ready to convert, make sure to convert all MyISAM tables to InnoDB, not just the big ones.

Converting to InnoDB

I recommend that you hold off with conversion for now until you understand InnoDB configuration better, but when you’re ready, you can run the following query to get a list of queries that will convert all tables in a given schema from MyISAM to InnoDB:

Why my tables are created as MyISAM?

In the beginning I mentioned that many applications are using MyISAM explicitly. What I mean by that is that during the initialisation of the database, when all tables are created, CREATE TABLE statements often have ENGINE=MyISAM set at the end, so tables are created as MyISAM regardless of your default-storage-engine setting.

So it’s a good idea to run the query above to check for any MyISAM tables every now and then. And, if you’re using Percona Server, you may also want to set the following in my.cnf:

P.S. If you have stumbled on this post by accident and you’re not sure what other posts I am talking about, start here.

Share this Post

About the Author

Aurimas Mikalauskas

Facebook Twitter Google+

Linux hacker since the age of 0xE, deeply passionate about high performance and scalability. Former architect of scalable systems at Percona - largest independent MySQL and MongoDB consultancy company (for 9 years). Audiobook addict, piano & guitar virtuoso, scuba diver, wind surfer and a wannabe glider pilot. Learn more about the author.