Categories
How To

Slow Site Troubleshooting: Database Edition

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

Got WP-CLI?

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.

Top ↑

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.

Top ↑

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.

Top ↑

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.

There are plugins like Delete Expired Transients and Transient Cleaner (which has the cutest header image). Those can be used to clean out your old transients.

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.

Top ↑

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.

Top ↑

Is That It? Is That It?

When you see a ginormous wp_options table, what do you do?

6 replies on “Slow Site Troubleshooting: Database Edition”

This was particularly helpful 🙂

Whenever I have a problem I come and look through your articles… and often, if its a WP issue I find some good reading material (& you even cover non-WP issues too!).

Cheers, Max

This might be exactly why one of my multisite networks is so slow on the backside! I just took a peek at the size of the wp_options table, and it’s 238kb. With just a glance, I see some stuff obviously related to old themes & plugins no longer in use. I’m assuming that I can just drop those rows, right?

Does the wp_options table in multisite have theme related data for subsites too that I should be careful not to drop? For instance, I see rows in wp_options related to an old parent theme no longer used on the main site, but still being used on a subsite or two. It looks like the subsite options table (wp_3_options, for instance) seems like it has it’s own theme info.

Comments are closed.