The MySQL MyISAM and InnoDB engines and a grocery checkout
There’s no shortage of resources comparing the MyISAM and InnoDB storage engines. You’ll quickly see it isn’t a black-and-white decision after reading through various discussions debating MyISAM and InnoDB.
Why is the decision so hard?
- Setting up your database is one of the first steps when building a web application. You probably don’t have a good idea on the database activity at this point, so you may have little data to work with.
- The ordering and number of statements can have a big impact on database performance. It’s difficult to simulate until you have real users.
However, there is a one case where choosing the wrong table type can be crippling.
The Grocery Checkout
Many web applications primarily issue READ queries – SELECT statements. Both the MyISAM and InnoDB table types can execute these concurrently. There isn’t a single queue – you’re checking out at the cash register with 10 possible lines. One long query won’t stop another from running:
UPDATE statements are another story
For the MyISAM engine, an UPDATE can only execute if the other checkout lines are empty. All of the other cashiers must stop working. This is table-level locking.
Normally, this isn’t a problem. However, what if there is long 10 second SELECT query, then an update, and then several other SELECT statements?
The additional SELECT statements must wait unit the UPDATE query finishes:
This means that the 3 queries after the UPDATE statement will take a minimum of 10 seconds to run. The execution time of these queries may be very small – perhaps 0.001 seconds, but it doesn’t matter – they have to wait for the UPDATE to finish, which is waiting for the long SELECT statement to finish.
InnoDB doesn’t have this restriction as it uses row-level locking – only the updated rows are locked. SELECT queries can continue to execute.
This seems like a fatal flaw. Why would MySQL make MyISAM the default storage engine?
In a well-optimized web application, the table type may not matter:
- Most web applications primarily issue reads, not writes, so table locks don’t occur frequently.
- There should be few (if any queries) that take a 1/2 second or more.
However, new web applications are anything but optimized. You’re more likely to have missing indexes that result in slow queries. You don’t know if you’ll be doing reporting, which often involves slow queries.
There are a lot of unknowns, so it’s often worthwhile to go with the safe choice. This can be a difficult problem to diagnose, and during the post-launch phase, you’ll have plenty of other things to worry about.
More on MySQL
I’ll be following up this post with a walk-thru of several tools for monitoring MySQL performance.
Subscribe to our RSS feed or follow us on Twitter for part 2.
