I recently got in trouble with my hosting company for being a resource hog. I get hit by a lot of spammers at WCF to begin with, but I didn’t realize that the size of my database made it such that it became taxing on the server. I had noticed things were sluggish on this site for quite some time, but I chalked it up to my site’s use of some couple dozen plugins or so running with a bbPress forum. So how big was my database? Almost 700 mb. Yikes!
The culprit: wp_options
Before I spoke with my hosting company’s tech support, I assumed that there was little I could do if my site was frequently targeted by spammers or even legitimate spiders. But after speaking with tech support, I learned that this seemed to be problematic only because the size of my database was excessively large. When visitors would visit my site, it was more taxing on the server than it would have been had the same number of visitors visited a site with a leaner database. Tech support mentioned that wp_options in my WordPress database was bloated, and said I might need to get in touch with a developer to remedy it.
How bloated was wp_options? Bloated wasn’t really the right word. Bloated is when you drink too much water so it’s sitting in your stomach. My wp_options table was morbidly obese. It was more like Jabba the Hut; so large and cumbersome that all it could do was pretty much sit in the same place and flap its flappers around and laugh with that deep laugh. It was time to go Luke Skywalker on my own database.
The solution
I spent some time figuring out what had to be done. I disabled and deleted a few plugins that I thought I could live without. I then started researching how to reduce the size of my WordPress database even more, and I found a post discussing how to do this using a plugin called Advanced Database Cleaner. The free version of the plugin really limits what you can do (you can’t really hunt down orphans all that well), but I followed the instructions, cleaned parts of my database here and there, and optimized the database.
Still no luck. As you can see, after doing all of this, I noticed that my database was still abnormally large at 684.57 mb.
I then came across the Young Cow site which explained how to specifically focus on wp_options to clean out the mess. There is one simple SQL query that needs to be run on the database. Young Cow mentioned that it worked for his database, removing around 220,000 rows! He then said that removing the rows didn’t change the table size, so a repair and optimize command had to be run afterwards.
Anyway, I ran the SQL query, and after what seemed like forever (I was worried it stalled out), I got this return:
Wowzer – are you kidding me? That’s 3.9 million rows deleted! I refreshed the view of my database stats. Although the total options had shrunk by a massive amount, the database size hadn’t really changed at all:
The next step was to run the database optimizer included in the Advanced Database Cleaner plugin. After doing so, I refreshed the database stats once more and got the news I was hoping for:
Bingo! After optimization, it reduced the database size to less than 10% of its former size (from 685 mb down to 62 mb). I was actually worried at first that doing this had to have broken my site somehow, so I clicked through my site a bit to make sure everything was in working order. So far, everything looks to be good and operating as normal. Better still, my site finally seems to be working faster!
This is great news for me and my site. Western Civ Forum has been around for about 10 years now and has evoluved from a Phpbb site to an SMF site to a WordPress site, so I can understand why there would be some extra baggage hanging on along the way. I’m just glad it got cleaned out and was so easy to do. I’m imagining (“wishfully thinking of”) a kind of WCF renaissance of sorts now. Faster page loads, more Googlebot visits, better indexing. The world is my (site’s) oyster!
Update 6/19/2017
It’s been almost two weeks since I cleaned my database, and my site has been working properly. However, I noticed something else strange going on with the database as the screenshot of the analysis shows.
The size of the database has grown by about 11 megabytes, and the number of “Total options” has ballooned from 634 to 52,357. Something clearly is afoot – the same something that must have caused the nearly four million options in my database earlier this month. I took a peek at wp_options in my database using PhpMyAdmin and noticed these kinds of rows being generated:
More transients, and they were being generated by Jetpack. Why these are being generated is unclear to me. I might ask around in the support board for Jetpack on WordPress.org, but at this point I just wanted to fix the problem. Fortunately, there are a few different transient deletion plugins in the WordPress repository, so I don’t need to run SQL queries every time I want to clean my database. The plugin I chose was called Transients Manager.
After deleting all expired transients, I got a timeout error because the process took over 30 seconds. Refreshing my page, I saw that the number of transients left was down to about 3400. I deleted expired transients again, and this time it successfully completed and left only 40 (unexpired) transients remaining. I then ran the database optimizer, leaving me with this:
So my database is back where it should be – with only around 650 options and a database that is under 65 mb.
In the future, I may decide to make transient cleaning a regular activity. I don’t know how to stop the increase of Jetpack transients, so I can only hope to contain them. I noticed that another WordPress plugin, Transient Cleaner, gives the option for daily cleaning of transients, so I may give that a try.
