Note: This post was written a couple of months ago, and never posted, because I always wanted to add more to it. All testing was done while the FeedLounge alpha was on a single server, and FeedLounge has moved on from that. Seeing as I might be switching away from MySQL altogether, I thought I should post this for anyone else running into these types of issues…

I was thinking that the MySQL MyISAM engine’s table level locking was killing the FeedLounge application and user experience in the past week, since the feed update daemon does a large amount of writes to the database almost constantly. I wanted to test the InnoDB storage engine, to see if the row-level locking would offset the fact that I use GUIDs as primary keys, which make primary key inserts not in sort order. So, I started on my journey..

First step, get the data over to the test server. This step was fairly simple and straightforward, as I do a nightly database dump from the alpha server, and rsync it over to the test server, to have a geographically diverse near-online backup. This was just a matter of doing this again to have the latest. Total time: about 25 minutes.

Step two, configure the test server for InnoDB. This was the easiest step, just setting a few variables in the my.cnf file, then restarting mysql. Total time: 10 minutes.

Step three, load the data into the test database. I dropped the existing test database, and then wrote a quick sed script to change the engine type in the massive SQL backup file. I then started the load. A dump takes about 20 minutes now, and a load on the alpha box took about an hour, so I figured it would be in the one to two hour range. Boy was I wrong! The 1.9 GB SQL file ended up taking about 14 hours to load on the test box, but that wasn’t the worst thing.

On the alpha server, using MyISAM, the entire database took about 2.4GB of disk space, including the indices. Once the load was done on the test server, the InnoDB files totaled over 10.6 GB!!! You’ve got that right, the FeedLounge grew to take up almost 5x the space just by changing database storage engines! This obviously throws our disk space calculations out the window. The bin logs on the test box total 1.7GB, so the data seemed to be correct.

So, now I am over an entire day into this testing, but the main question is, can I live with the 5x growth in data size? Doesn’t that seem a little like overkill? Is this stated anywhere? Is this just another one of those ‘known things’ that you are supposed to know as a MySQL DBA?

We are now on InnoDB, and the lockinng/contention issues are gone. The only issue left is the speed of count() queries on InnoDB, which is a known issue, and even documented in Zawodny’s excellent book. I am working on refactoring the code to remove the counting queries, to free up the database to do real work.

Popularity: 72%