In addition to the Internet connectivity issues I had earlier this month, I got zapped by a major website issue. My hosting service claimed that my WordPress blog had a problem either with its mySQL databases, or with malformed queries that resulted in an abnormally-heavy server load. They said it was so bad that they had to shut down my account completely for about 4 hours on December 23rd to keep the server running. I took a look at the queries, and didn’t see any problems with them, and a brief inspection of the mySQL databases didn’t show anything wrong. But to make sure that was the case, and to keep my hosting company happy, I took a deeper look at my blog to see if there was anything I could do to remove any cruft that had built up in my blogfiles since I switched to WordPress in June 2007. This isn’t exactly geography-related, but I thought that some WordPress users might find a description of what I did useful.
1. First, I de-activated unneeded plugins. I’ve tried numerous plugins in the year-and-a-half since switching to WordPress, and had pretty much already settled on a core group of eight useful plugins. But there were a couple that weren’t completely necessary anymore, and de-activating them was likely to lessen the server load.
2. Next, I backed up the mySQL databases before trying to work with them, in case something got screwed up. Many hosting companies offer access to control panels like phpMyAdmin that lets you do this. But if you don’t have this access, or if it’s too complicated, there’s a free WordPress plugin called WP-DBManager that lets you create DB backups from the WordPress dashboard, and restore them if necessary (along with some other useful functions).
3. When a WordPress site loads, it reads the wp_options database for configuration data for both the basic site, and plugins. There should be no more than a few hundred entries in this table at most, and the bigger it gets, the longer a site takes to load. Here’s the story of one site owner who discovered that there were over 19000 entries in this table, most of them unnecessary RSS hashes; another source of unneeded entries are from plugins that created them when installed, but didn’t remove them when de-activated. There’s a plugin called Clean Options that does a good job of finding most unneeded options entries based on whether there are any active plugins that use them. Running this plugin reduced the number of wp_options entries on my site from 355 to about 250. But it also found an erroneous blank entry, but didn’t offer a way to remove it. Once you’re finished with this plug-in, it’s OK to deactivate it until you need it again.
4. Going into phpMyAdmin and manually browsing the wp_options table, I found and deleted the blank entry. But there were also about 50 additional table entries for deleted plugins that Clean Options hadn’t found, so those were deleted as well.
5. The wp_posts table contains individual entries for all the posts, pages and images on the blog. The total number of entries was 2700, which seemed high since there’s fewer than 600 posts on this blog to date. An examination of the entries by title showed why – there were numerous duplicated title entries for many of the posts. A bit of research turned up the reason. WordPress 2.6 introduced post versioning, which seemed like a good idea to me. But with every revision to a blog post, even a minor tweak, a new database entry was created for that revision. This was making the wp_posts database table grow a lot bigger than it really needed to be.
WordPress knows about this, but decided that the feature was so useful, and the overhead associated with the larger table so minimal, that they didn’t include the option to turn off versioning, nor to delete unneeded versions. Fortunately, there’s a plugin called Disable Revisions that not only disables revision versioning, but deletes all the revision entries in the wp_posts and wp_postmeta tables. Make sure you backup your database first before activating the plugin, as it does the deletion as soon as it’s activated. Installing and running this plugin decreased the table size by about a third, from around 2700 entries to roughly 1800.
6. I also found a number of database tables created by plugins that were no longer present, and dropped those. The default WordPress installation only has 10 database tables:
Any other tables were most likely created by plugins. Usually the name is enough to let you figure out which plugin created it, but if not, just Google the table name. If it was created by a plugin that’s no longer installed, you can probably drop it (but always backup first).
7. I then checked and optimized the database tables using phpMyAdmin; the WP-DBManager plugin can do that as well.
8. Finally, I found useful suggestions at this site and this one for speeding up page loading. For example, in the WordPress theme header and footer sections, php code is used to insert web addresses for style sheets, RSS feeds and coding. But these addresses are normally static, and you can speed up the loading process by replacing the php code that pulls up the address with just the address text itself. For example, the original code for specifying the CSS sheet for this blog was:
<link rel=”stylesheet” type=”text/css” media=”screen” href=”<?php bloginfo(‘stylesheet_url’); ?>” />
By opening my blog’s home page, and using the View => Page Source option, I found that the stylesheet’s static URL was
Replacing the php code with this static URL, I get:
<link rel=”stylesheet” type=”text/css” media=”screen” href=”http://freegeographytools.com/wp-content/themes/K2/style.css” />
Because the php code doesn’t have to be interpreted to retrieve the URL, the page loads a bit faster for every case where you can do this. I also dropped several unneeded lines of this type, such as the one that retrieves the WordPress version number and inserts it into a meta tag. Getting rid of this not only speedsup page loading, but improves your site’s security. If a security flaw is found in a particular version of WordPress, the version meta tag can tell an intruder instantly whether your site is vulnerable. If the tag is missing, it’s not as obvious whether your site still has this vulnerability or not.
Did all this solve my site problems? Don’t know yet; I’m getting fewer mysql_slow_query log entries, but haven’t gotten rid of them entirely. But I suspect that’s a server-related issue, which I can’t do anything about. I can say that my blog pages load in about half the time they used to in Firefox, and a third of the time in Internet Explorer, so presumably that reflects a reduction in my site’s load on the server.
Hopefully now I’m done with all these issues, and can get back to geographically-oriented posts on a more-normal schedule.