Simple Settings for a Saner MySQL – InnoDB

Within MySQL, there’s a piece called a storage engine that reads and writes to disk on your behalf when you execute a query. It controls the way that your data is stored on disk. With MySQL, you can change what storage engine you use, which is helpful since every engine has different advantages and downsides, and you can select which engine to use based on your workload. There are two main engines: MyISAM and InnoDB. MyISAM was the default engine before MySQL 5.5 and it’s been there since the beginning. It’s also not crash-proof, it doesn’t have foreign keys, and it’s not transactional. InnoDB, on the other hand, has all these features.

What happens if you use MyISAM

No Safety Belt

Grooveshark (a popular music site) in 2008 had their MySQL database crash – except they didn’t know it. MySQL restarted for an unknown reason. Their InnoDB tables were perfectly fine and recovered without manual intervention, but their MyISAM tables did not fare so well. This caused the site to go offline for three hours while they tried to locate their problem! Jay Paroline, a Grooveshark developer, finally looked at the MySQL logs where he noticed the problem. He then repaired the tables and the site came back up. After this incident, they moved their tables over to InnoDB and haven’t had an issue since.

I should note that Grooveshark probably lost data during the repair (if repair wasn’t risky, why wouldn’t MyISAM just perform the repair automatically?) I’ve also had crashes that damaged MyISAM table – it’s just one of those things that happens to almost everyone who uses MySQL with MyISAM.

Short Term Memory Loss

Transactions are another big win for InnoDB. It’s really hard to develop an application on a database without them. Imagine that you have suffered a major car accident that caused memory loss, leaving you in a condition where you could black out at any time, remembering nothing of the past few minutes. Now think of trying to buy a meal at McDonalds in this condition. You order and pay up with your credit card, but before you get your food, you forget you ordered. Since you’re hungry and in McDonalds, you go ahead and order and pay again.

A database has the same condition: at any moment, the server could restart and have no idea what it was doing. A transaction is the method you use to tell MySQL to save a record if and only if it also saves this other record. You are instructing MySQL to perform several actions as if they were one action. With MyISAM, this isn’t possible. This means that you as the developer have to keep track of transactions inside the application – you’ll be creating your own database system on top of the database. Why do that? Just use InnoDB.

Big Ol&rquo; Locks

When you build a web site (or any app with multiple users), you need to support many people writing to your database at one time, and more than likely, there will be several high-contention tables or tables that everyone wants to use at the same time. MyISAM locks at the table level, which means that only one person can save a row into a table at a time, and someone who’s saving a record will block someone who’s reading the table, and vice versa. This is a scalability nightmare.

With InnoDB, writers only block other writers, and writers lock at the row level, allowing many people to write to the same table at the same time. This makes InnoDB much better for those workloads that involve users creating content, changing settings, sending message, etc.

Data Domestication with Foreign Keys

Foreign keys tell your database how tables are related. They help validate your data, restricting a column to only those values they reference in the other table. They also make sure you don’t orphan data, putting your database into a bad state. You don’t want the possibility of deleting a company’s account but leaving all their users present in the system.

You can use foreign keys in InnoDB, but not in MyISAM. Once again, you’re losing out on a fundamental feature of relational databases when you use MyISAM.

Converting to InnoDB

If you’re using a MySQL version before 5.5, you can set InnoDB as the default storage engine:

default-storage-engine = innodb

After MySQL 5.5, InnoDB is the default storage engine, so no need to set it for the last two versions of MySQL.

To convert a table to InnoDB, use this command:

Alter Table table_name Engine = InnoDB;

Limitations, Addendums, Exclusions, and Other Fine Print

There’s two limitations with InnoDB. First, fulltext indexes are not supported on InnoDB tables before version 5.6, so if you’re on 5.5 or before and using fulltext, you can’t convert. Second, you cannot, ever, convert the tables in the mysql database to InnoDB. They must be MyISAM. Your database server will not start if you convert them to InnoDB.

Here’s the full guide to migration.

Want to hear more about what I’ve learned about MySQL? Well, I’m writing a book about it! You can subscribe below and I’ll send you updates. I’ll also send out stuff on MySQL like tips and tutorials.

 No spam

Tagged with:
Posted in Databases, IT

Leave a Reply

Your email address will not be published. Required fields are marked *