So, we’ve been planning to roll out some database changes for a while here at eNotes. We’ve been dealing with content in a mishmash of ASCII, ISO-8859-1, Windows-1252, and UTF-8 encodings for a while now. It makes sense to standardize on UTF-8, and I wrote some migration scripts to accomplish that. We also wanted to move from MyISAM to InnoDB table types; Joe did the work to get the schemas moved to InnoDB and setup foreign keys. Last Sunday, we rolled those changes out.

What a complete nightmare. I had heard that InnoDB had performance issues before, but hadn’t experienced them firsthand. I went to a performance tuning talk at MySQL UC 2006, and I specifically asked about InnoDB performance. I was told that it’s only performance issue was with INSERT and UPDATE statements, since it has to check table constraints, and it’s read performance is comparable to MyISAM. Lies! It’s read performance is crap. Our database servers - three of them - went from load averages of around 1-2 all the way to 10-15.

So, our slaves are moved back to MyISAM (an ordeal in and of itself), the MyISAM slaves are getting most of the read traffic, load is back down to a reasonable level, and my faith in MySQL is taken down another notch.

This entry was posted on Tuesday, May 16th, 2006 at 6:42 pm and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.