My job sent me to a class on scaling, optimizing and troubleshooting MySQL this week. I’ve been digging around a bit on some test databases at work, but of course as someone running a self-hosted WordPress blog, I had another MySQL server to practice on right here — one with real-world data and (admittedly low) load, but where I was only accountable to myself if I messed anything up.

Unfortunately, DreamHost’s MySQL VPS doesn’t give you much control over the server, and of course when you’re working with a third-party application, there’s only so much you can change the database without breaking compatibility. But I found some interesting surprises:

1: Everything was using the older MyISAM engine, because DreamHost is running an older version of MySQL that uses it as the default. Switching to the newer InnoDB (and back) is simple and safe enough that I figured it was worth a try.

2: There was a lot of junk left over from old plugins that I haven’t used in years.

• FULLTEXT indexes from Spam Karma, two different related-posts plugins, and WP Greet Box.
• Leftover tables from several plugins, including one on Speed Force that I had forgotten installing.
• A duplicate index on the users table on this site that I can only assume was either a renamed index that didn’t get cleaned up in an upgrade, or some ancient attempt at naive optimization.

I didn’t even look for leftover post_meta items or options.

3: Some plugins create a LOT of queries. Co-Authors Plus, for instance, just completely balloons anything that looks at authors. WordPress SEO runs an update for each post shown on the dashboard, because it’s recalculating SEO stats. The weirdest, and one I’m going to have to dig into, is WordPress HTTPS, which frequently updates a long list of URLs.

4: WordPress scans the entire options table on every page load looking for autoload options. Indexing won’t help this case, and scans are one of the things that InnoDB is slower at than MyISAM. On the plus side, it’s run often enough that the table should stay in memory most of the time. Still, it’s going to be worth clearing out old options when I get a chance.

5: Generally speaking, MySQL isn’t the problem on slow pages. Unless the profiling plugins I used were totally off, it rarely accounted for more than 200ms and was typically closer to 100ms. Even switching from MyISAM to InnoDB didn’t change much. It’s an interesting exercise, but tuning PHP and re-examining my plugin list will probably help me more than tuning MySQL at this point, unless I want to start digging into code.

6. Bulk comment operations timed out at first after switching to InnoDB. The first couple of times I tried to empty the spam folder, it only got through about 10 comments and then timed out after 30 seconds. Then I tried again later and it deleted hundreds in about half a second. My suspicion is that this was related to memory usage, but it’s something else I’m going to have to keep an eye on.

Dashboard performance does feel a bit faster after the switch to InnoDB, but (a) I did toss a little more RAM at it while testing, which I haven’t dialed back yet, and (b) the numbers don’t look that different (plus see #5 above), so I imagine it’s mostly imaginary improvement.