Table of Contents
So your WordPress is slow and you’ve already done the needful. You’ve checked your plugins and themes, you’ve put caching in place, you’ve checked for hacks, but it’s still slow, especially on the back end of WordPress?
It may be your database.
More specifically it may be your
wp_options table. When your options table gets very, very large, it gets very, very slow. WordPress regularly queries that when you’re logged in, and it’s not indexed. DB indexes are used to locate data fast, without searching every row in the tables. This sounds sensible in many ways, but we don’t
Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. We don’t use ’em in the options table for a variety of reasons, but mostly that it slows things down.
So in lieu of making indexes on your own, what can and should you do to debug things?
Optimize Your Database Optimize Your Database
wp db optimize
Otherwise you can use phpMyAdmin to check tables with overhead and clean ’em up. I don’t use a plugin, I think asking WP to optimize itself is a little weird, but you certainly can. The point here is to keep it clean.
Cache that Database! Cache that Database!
Caching makes things better. Right? Kind of right. Mostly right. I use memcacheD (which I often typo as memecached) and that plus an object-cache.php file can cache your DB calls so they’re faster, which is great. Unless your
wp_optimize table is too big.
Your cache has two major issues. First, there’s going to be data you don’t want to cache (like private, sensitive information), but also when your cache is too big, or it’s trying to save data bigger than it is, it can make things slower by trying to cache, crashing, and repeating that over and over. That gets worse when we talk about the temp data generated by _transient entries in your database.
Check The Size Check The Size
Your options table really shouldn’t be large. My biggest, busiest, site is only using 142K. It’s a site that’s old (8+ years now), it’s had many iterations and themes and plugins. You’d think it would be filthy with leftover code, because we all know plugins don’t always clean up. Nope. I did rebuild the DB once, in 2009, when I rebuilt the entire site from scratch, but that was 5 years ago and a lot has changed since then with plugins and themes. The next biggest site, a Multisite Network, has a wp_options of 100k. The biggest I’ve had is one of 500kb and that’s on a test site where I install and delete plugins daily.
You get the point I trust. These things should be small. At most, I’m going to have a lot of _transient entries. But that’s actually issue here.
Clean The Transients Clean The Transients
There’s a story here about why WordPress doesn’t really clean your transients. Why? Well as the name implies, transient data is meant to be transient. It should be temporary data, saved and used briefly, and then it should go away. A lot of developers, as it happens, were storing it for long term caches. Like checking when the last upgrade ran, or when a cron kicked off. So while for one, glorious, month we did nuke them all on upgrade, now we only delete expired transients, which doesn’t help as much as it could. As Nacin said:
This leaves much to be desired, but we don’t want a core update to be blamed for breaking a site that incorrectly assumes transients aren’t transient.
Basically people doing things wrong in a way we couldn’t adjust for.
If you want to go whole hog, there’s a command to clean the whole thing out with SQL:
DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%')
Of course you want to run a db optimize afterwards to actually flush out the rows.
As always WP-CLI has features like
wp transient delete-expired and
wp transient delete-all.
What if that doesn’t help? What if that doesn’t help?
Then you should check the Database to see exactly what the biggest value is. In this case, I ran
wp db cli to leap into the database and then this:
mysql> SELECT option_name , length (option_value) AS blah FROM wp_options ORDER BY blah DESC LIMIT 5; +--------------------------------------------------+----------+ | option_name | blah | +--------------------------------------------------+----------+ | cron | 12194468 | | _transient_feed_d117b5738fbd35bd8c0391cda1f2b5d9 | 223838 | | _transient_feed_ac0b00fe65abe10e0c5b588f3ed8c7ca | 98184 | | _transient_is_cached_instagram_images_self | 97315 | | mytheme_storage | 18354 | +--------------------------------------------------+----------+ 10 rows in set (0.02 sec)
CRON is 12 megs. That would be the problem. Of course the only way I know of to fix that would be to totally trash cron and let it start over.
Is That It? Is That It?
When you see a ginormous
wp_options table, what do you do?