It’s something that can happen to any WordPress site. Everything is running on smoothly for months and months. And then all of a sudden your hard drive runs out of space. It turns out your wp_options file has ballooned from 10mb to 13gb or more. What in the world is going on here?
What Is your WP_Options File?
The wp_options file just holds the basic options for how your WordPress blog operates. In general it should be a fairly small file with maybe 200-300 rows in it. It holds values like upload_path and db_version. It should be a reasonably small file.
One thing, though, that can start to rack up space are “transient” entries. A transient entry is a temporary entry like _transient_is_multi_author. These entries are meant to be used for a short while and then removed automatically. However, if cleanup scripts don’t run properly, they can be left behind by accident.
So the first step is to clear out all transient entries.
Removing Transient Entries from wp_options
In order to run this script, you need to either load the MySQL Workbench or get access to that MySQL table in another fashion. There are WordPress plugins which will let you do this.
First, you want to see the state of the table. Start by running:
Select count(*) from wp_options;
That will show you how many rows in general are in your table. It should be around 200-300 rows depending on how many plugins you are running.
Next run:
SELECT count(*) from wp_options where option_name like ‘%transient%’;
This will tell you how many transient entries are in your table. If things aren’t too bad you might come back with 50 entries. If things are out of control you could have hundreds or thousands of them. If you have less than a thousand, you could run this to look at them:
SELECT * from wp_options where option_name like ‘%transient%’;
Either way, though, these should be deleted. There’s never a reason to just have them in your database. To delete them, use:
delete from wp_options where option_name like ‘%transient%’;
This will reset your wp_options database into a cleaner state.
However, MySQL won’t necessarily shrink the actual file size to take into account this new reduced row count for your table. To do that, and reclaim your hard drive space, you need to take another step.
Optimizing MySQL Database Table Size
These instructions are for using the MySQL Workbench – you should be able to do this same operation with other tools.
From MySQL Workbench, click on the schema you wish to work with. Right click on that schema name and choose Schema Inspector.
You’ll now see a list of your tables in that schema with the row count for each, the file size on the disk, and so on. This is really useful for making sure your tables are in healthy shape. If you see one that needs to get its file size under control, like the wp_options table, you click on the ‘maintenance’ button at the bottom of the list.
Click on the table name that you wish to optimize. Now click on optimize. It will get that table back into a reasonable size.
Note that I highly recommend optimizing all your tables once a month in any case. For example, you probably get a steady stream of spam comments through your system that get auto-cleaned. Your tables can bump out in size, though, due to that flood, and then stay large even when the comments are removed. Optimizing the tables gets them back down to their proper size.
Ask with any questions!
Leave a Reply