I host my clients’ WordPress sites on a pretty beefy 4GB Linode. There has been one site, though, that has always confused me as to why it’s causing problems. MySQL would crash about once every week or two, and I could never figure out why.
The Usual Suspects
After dealing with this site for a long time, I had pretty much ruled out the usual suspects. I tried all sorts of caching: memcache, object caching, plugins like WP Super Cache, W3 Total Cache, and even WP-FFPC (Fast Full Page Cache). Although they all kept the site zipping along, they didn’t keep it from occasionally crashing.
I have literally been trying to chase down this rare occurrence for years, but it’s just been so random and odd that I didn’t even know where to start.
Formatting the DB
I often export and import the databases from the WordPress sites I work on, so that I can load them on my local MAMP install, or reload them on the Live Linode Server. I recently thought that since the site in question is a very large e-commerce site with lots of transactions, perhaps I should try to optimize the DB in Sequel Pro.
When I tried to do that, the error messages gave me another clue. Some of the tables could be optimized / repaired, but some of them were in the wrong format to be repaired (MyISAM). The tables that were in InnoDB format were able to be repaired quickly.
Since I use Easy Engine to manage the Nginx setup for my sites, I figured it was setting up some of the tables in MyISAM and some of them in InnoDB.
It turns out the format of your database tables is set by whatever the default format is on the current server.
This makes sense, because I’ve worked with this site for a long time, and another agency rebuilt the site recently on different hosting, causing some of the newer tables to be created with they MyISAM format.
Once I realized why there were multiple formats of tables in the database, I set out to fix them, with the help of this Easy Engine article.
Better Performance and Stability
From my research about MyISAM vs InnoDB, I learned that InnoDB is capable of taking advantage of multiple cores, whereas MyISAM can only use one core. This means loads in a 4-core server like my Linode. InnoDB also has other features like being able to recover better from a crash, and being more stable in general.
Once I switched the format of all database tables to InnoDB, my overall CPU load on the server went down, the sites got massively faster, and there hasn’t been a single crash yet. I’m still on the lookout, but for now, I’ve accepted that InnoDB is the better format for WordPress databases.
Keep in mind that WordPress does not decide what format the databases will take. Your server defaults determine the database table format.