Awhile back, I mentioned that I had some issues with the site that required a bit of database knowledge to correct. In this article, I want to briefly share my experience, so you can learn how to clean your WordPress database too.
Table of Contents
Background
You may recall that I had an issue with Jetpack, a plugin that I pay for, that caused Google Analytics to stop tracking my website traffic. Mainly, the issue boiled down to a configuration problem in my database, but I didn’t know that right away. In fact, the Jetpack team took a couple of months to troubleshoot the issue themselves.
As a result, I want to share some of what the Jetpack team asked me to do, and how it helped me learn the ins and outs of the WordPress backend. If nothing else, this will serve as great documentation for myself in the future.
Troubleshooting
When I first encountered my issue, I thought maybe I had caused it, so I did a little digging. Right around the time the issue appeared, I had changed to a new email address to reflect my name change. Naturally, I tried to track down every place where my new email address could have been missing.
When that didn’t work out, I decided to tinker with things on the Google end. I started by generating a new Google Analytics tracking code
. I figured if the new code didn’t work either, the issue was most likely on my end. When the new code didn’t work, I reached out to the Jetpack support team.
At that point, they asked me to go through a series of steps to help isolate the issue. In the following sections, we’ll take a look at a few of their tips.
Health Check
The very first step to troubleshooting for a standard WordPress website is to disable all plugins and switch to a default theme. Of course, this isn’t always practical—even for a site like mine that averages about 50 unique views a day.
As an alternative, the Jetpack team asked me to use the Health Check plugin which essentially allows you to do the same thing without taking down your site. While the plugin worked great for me, I urge you to exercise some caution. Many of the comments I read about the plugin were very scary. Some users have crashed their sites when using that plugin, so just be aware of the risks.
Unfortunately, when I used Health Check, nothing changed. Sadly, the Google Analytics tracking code was still not displaying.
WP Staging
Eventually, the Jetpack team asked me to create a staging site, so their team could do some testing without breaking my site. Unfortunately, my hosting services do not provide a staging feature, so I had to resort to yet another plugin.
Naturally, the Jetpack team recommended WP Staging which I attempted to use. Sadly, WP Staging was a bit more nefarious than Health Check. In order to duplicate my site, it used space in my existing database. While that’s fine, the plugin failed to complete the duplication on multiple occasions leaving a lot of orphaned data in my database.
Of course, I had no idea this was the issue, so I contacted the Jetpack team with a few complaints and an error log. They quickly spotted the issue and asked me to reach out my hosting provider to attempt to solve the issue.
When I contacted my hosting provider, they told me that my database was full. They then spent the next 20 minutes trying to get me to buy more hosting instead of helping me troubleshoot the issue. I think that was the first time customer service was more upset with me than I was with them.
Database Cleanup
From there, I decided to download another plugin to try to clean up junk from my database. After some searching, I learned about WP Sweep which allowed me to clean out a lot of old junk data from the database. In fact, I used the tool to clear out almost 8500 article revision which amounted to roughly 300 MB of my 1 GB database.
Unfortunately, WP Sweep wasn’t enough to clean out the orphaned copies of my site thanks to WP Staging. As a result, I had to bring out the big guns: PHP myAdmin and SQL.
From there, I found the 5 clones of my database sitting side-by-side. Since I’d never done any cleanup since launching the site, there were a lot of tables that weren’t being used; I started there.
After that, I begin deleting the duplicate tables one-by-one. When I realized how long that was going to take, I consulted Google for some SQL tips. I ended up using the following solution by Andre Miller:
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' )
AS statement FROM information_schema.tables
WHERE table_name LIKE 'myprefix_%';
This snippet allowed me to query by the prefix that each staging table used. The resulting statement could be copied and used to drop all those tables.
When everything was said and done, I was down to a comfortable 500 MB.
VaultPress
Of course, I wasn’t that excited as I could see the site was going to quickly outgrow its limited storage space. Fortunately, the Jetpack team was able to help me out. They just needed my permission to use one of my VaultPress backups.
From there, their dev team quickly discovered the error which was in my database. As an added bonus, they were able to clean out a lot of junk data from VaultPress which left my database with an exciting 850 MB of free space. How cool is that?
What Have We Learned?
If we take some time to pick apart my trajectory until now, we’ve learned a lot. We’ve learned how to troubleshoot, how to use customer support, and how to roughly clean up a database.
If we wanted to boil this article down into a few steps, here’s what they’d be:
- Determine the issue
- Attempt to find the root cause of the issue through troubleshooting
- Health Check is a great tool for isolating problems with plugins and themes
- WP Staging is another great tool if you want to play on a hidden copy of your site
- Health Check
- If all else fails, contact customer support
Always remember to back up your site before you start playing around. The last thing you want to do is make things worse. If you need to dig into your database, here’s my advice:
- Use a plugin to perform surface level database maintenance
- See WP Sweep, WP Optimize, etc.
- See WP Sweep
- If you have to, use PHP myAdmin to look inside your database
- When obvious, delete old plugin tables
- Don’t be afraid to remove duplicate tables that may have appeared thanks to WP Staging
- You can use the command I reference early to generate the DROP statement
- Feel free to clean individual records from tables if they’re no longer needed
- Ideally, plugins like WP Sweep should handle this for you (see step #1)
- Ideally, plugins like WP Sweep
In general, don’t be afraid to consult an expert. I don’t claim to be one as everything I’ve learned has been through trial and error. That said, WordPress has a massive community, so there is bound to be someone who can help.
Thanks Jetpack Team
Speaking of getting help, not only did the Jetpack team fix my Google Analytics issue, but they also gave me back some of my database space. To top it all off, they were incredibly friendly. In fact, I believe they call themselves Happiness Engineers, and I think they do a great job. I couldn’t be more thankful.
Hopefully all the help I got has been helpful to you in some way. If so, let me know in the comments. I’d love to know if these articles are having any sort of impact.
If you really enjoyed this article, why not share it with some friends. Even better, become a member and get regular updates when new articles come out. At any rate, until next time!
Recent Posts
As the semester starts to wrap up, I'm a bit busy and a bit burnt out. As a result, I thought I'd have a little fun writing about nasty little bugs I've run into over time. I'll keep it short; don't...
I find the structure of relational databases to be appealing. They allow you to store data in various tables that eliminate redundancy, reduce storage size, and combine easily. Therefore, it should...