Solving Database Bloat
The Hidden WordPress Ailment
Is your WordPress blog or site over 2 years old?
WordPress database bloat may be the hidden cause of a number of issues your blog may be experiencing!
(And even if it’s not causing you trouble yet, heading off trouble is a good-hygiene plan for a healthy blog.)
Safety Notice: We’re going to discuss changes to the database. These are not minor things. Never even look at your database funny without a backup. When in doubt, hire a professional such as myself to assist you with this type of work.
Recently, I was servicing a blog that was having a number of odd and hard to place issues.
Recommended for YouWebcast: Growth at a Scale Up: How to Grow When You're No Longer a Startup
It was reaching “that age” where a blog is just more fussy than it was in the beginning and requires more TLC and limited use of plugins to keep it fast, stable, and within hosting guidelines.
As I was working on the database, I had a minor w.t.f. moment when I realized the database backup size was nearly 100 MB compressed! And was over 1.6 Gigs extracted! Wowsers!
This isn’t uncommon, or hard to resolve, but it’s not a good state for the blog to be in as it sets the blog up for a wide number of issues including failed backups.
This is a look at the phpMyAdmin screenshot:
I know that’s small so I’ve linked the image to itself if you want to have a closer look.
Make a database backup before implementing any tactic below.
How To Fix WordPress Database Bloat
First Cause of Database Bloat: Extraneous Unused Plugin Tables
On the left hand panel, you see the names of the database tables.
If you knew the blog, you’d quickly remark that many of the tables have names that indicate that they are from plugins that are no longer in use. This is sloppy housekeeping on the part of the plugin coder. Sometimes it’s important, but many times plugins just leave their stuff hanging around and have no actual way to get it removed.
It is okay to see a lot of tables but be sure that they either belong to WordPress core framework itself or that they belong to plugins you are actually using!
The solution is to manually “DROP” these tables from the database. This can be done using a plugin that can access the database (security risk) or manually using phpMyAdmin which is part of your CPanel.
Second Cause of Database Bloat: Large Log Files
It’s hard to see it in this image, but the very bottom line in the screenshot belongs to wp_redirection_logs and has a size of 130,773. Wowsers! That size is bigger than many blogs whole database!
As is the case with many plugins that create (important) logs, the Redirection plugin has a feature that lets you limit the size of the log and clean (expire) the log after a certain point (or did last time I checked). Most logging plugins give you some manner by which to control for this.
The solution for this is to go into the plugin and alter its settings so that it clears out its log. Then recheck that it actually did what it claimed to do. If it did not, then the plugin needs removed and the database table for the plugin may need removed manually (see above).
Third Cause of Database Bloat: Post Revisions
Post revisions, which are different than auto-saves, are a feature that helps WordPress enable you to not only survive a browser crash but also to recover from massive changes to a post.
In this screenshot, you will see that wp_posts is 214,792 (compare that to my site which wp_posts is 9,142) and you quickly see an issue!
WP_Posts will always grow some as your site increases in the number of posts and pages present.
However, unfortunately, Post Revisions are not deleted and hang around forever and create tons of bloat.
The solution has 2 parts. I’ll talk more about this in an upcoming post. First, delete all current post revisions. Then, tell WordPress that it’s allowed to make only a specified number of revisions per post. Both can be done via plugin but it’s always better to do this manually when possible.
Organizing The Database House
Once you have made manual adjustments on the database, it’s recommended to do a repair and optimize on the database for health and stability of the site.
This is something that can be done via plugin but still requires a manual change to the wp-config so remains preferable to do manually as it’s built into WordPress. (Post on that soon).
Optimizing your database is a “good hygiene” technique for any established blog that can be done as often as once a month and helps ensure that things stay in good working order. (Minor database corruption – with data loss sometimes occurring – is very common and an optimization helps to head that off.)
If your blog is more than 2 years old, all of these are factors that you need to consider even if your feel that your traffic is low or your site is not that busy. These are things things that come about as a product of age rather than a product of traffic.
I’ll talk more about all three of these more technical items in another post, so be sure to leave me a comment and let me know what questions you may have!
Have you ever optimized your blog’s database?
PS: Outsourcing this is about an hour of billable time for a USA/UK/Aussie based WordPress specialist team. That means it will run you anywhere from $75 to $150 on average to be done that way. If you’d like to discuss my services, and having my team taking care of this for you, simply open a support ticket at http://support.just-ask-kim.com