The perils of switching from MyISAM to InnoDB
Posted on: 03 September 2012
We've been seeing a lot of contention on one of the larger tables in our database recently. This table has a few billion rows (around 300GB), and uses MyISAM. As our usage has increased, we've started seeing more SELECTs being blocked by UPDATEs, so decided to switch the table to use InnoDB to allow concurrent reads and writes.
All of a sudden, the performance of one of our queries dropped through the floor - from running sub-second to over 2 hours! There were no other processes blocking this query, and we saw this behaviour consistently over several hours, and on all queries of that form.
The EXPLAIN for our query looked fine:
+----+-------------+-------+------+-----------------------+---------------+---------+--------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------+---------------+---------+--------------------------+------+-------------+
| 1 | SIMPLE | il | ref | PRIMARY,idx_site_date | idx_site_date | 8 | const,const | 2612 | |
| 1 | SIMPLE | ilc | ref | idx_link_id | idx_link_id | 4 | dizzyhei1_seo.il.link_id | 1 | Using where |
+----+-------------+-------+------+-----------------------+---------------+---------+--------------------------+------+-------------+
So what caused the performance to drop so badly?
A bit of help from the great people over at dba.stackexchange.com got things sorted out. The second table, ilc, didn't have a primary key defined. As this was also an InnoDB table, this can cause things to perform pretty badly. It didn't explain why the performance had been fine while the il table was MyISAM, but after adding a primary key to that second table, the performance was restored to it's former sub-second glory.
Today's Moral
EXPLAINs are not everything - just because a query looks good in it's query plan doesn't neccessarily mean that it's going to perform well.
For more gory details about the whole affair, check out the full post.