Keeping your WordPress options table in check
When troubleshooting a slow WordPress site, an often overlooked culprit is the wp_options database table. This table houses a variety of crucial site data, including:
- site settings
- scheduled posts
- widget data
In fact, nearly every WordPress page—from the front end to the admin screens—executes the query
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'. If this query does not run efficiently, the results can be devastating to a site’s speed.
Why wp_options doesn’t scale
Not all WordPress database tables suffer as they grow. The wp_posts table, for instance, can be many times larger than the wp_options table without seriously impacting site performance thanks to an indexing strategy designed to accommodate very large datasets.
A database index is similar to a textbook index, which lists every mention of a term in its pages. With clearly marked page numbers, any term can be quickly found. Without this index, every page in the book would need to be flipped through to locate a specific term, every time.
If a database query doesn’t have an index on the requested field (as in our earlier example query on the options table, which looks for records where the “autoload” field is set to“yes”), MySQL will have to search every single record in the database table.
The wp_options table is not intended to hold thousands of records, so, in theory, it does not need an index for the “autoload” query. As retooling a part of WordPress core like this is a huge endeavor—as this wp_options trac ticket shows—engineers are discouraged from adding records to the wp_options table unnecessarily.
Cause & effect
And yet, this table can become bloated quite quickly and keeping its size under control is not a small effort. 10up Systems Engineers often see wp_options ballooning due to:
- Plugins installed on large WordPress sites that were designed for smaller sites. These plugins often store data in wp_options rather than tables designed for larger data sets (such as the postmeta table for post-specific data) or external platforms better suited to “big data” like analytics;
- A bug in otherwise well-designed plugins, like this issue found with Yoast’s SEO plugin;
- Theme developers adding data tracking code—E.g. ad tracking or social media counters—in the wp_options table.
In many cases, issues like these slowly bloat the table and degrade performance over time, offering no clear warning signs. Without constant monitoring of the wp_options table size, a simple theme or plugin update can be the start of a big problem. 10up uses a basic bash script running in system cron to email alerts if the wp_options table exceeds a few thousand rows.
To quantify the impact of a bloated wp_options table, 10up ran the “autoload” query against a large website whose wp_options table contained 38,654 records. Before cleaning up the table, the test query took 52.66ms. After cleaning up abandoned transients records, Yoast’s SEO sitemap cache records, and legacy ad tracking data, the wp_options table was slimmed down to 528 records, and the query took only 1.4ms. That’s 37 times faster!
Add an index
When cleaning up the table isn’t enough, adding an index to the “autoload” field can be a huge help. Many managed WordPress hosting providers do this automatically to protect against performance degradation.
The 10up Systems team performed some test scenarios on a wp_options table with a typical number of autoloaded records to show how adding an autoload index to wp_options queries can boost performance. Again, the results were dramatic.
Without an index, the time to query for the autoload options increases linearly with table size, with no limit. With an index, the query hits a slowdown ceiling around 4ms and remains there.
Many high traffic WordPress instances can mask poor performing autoload queries by using the WordPress Object Cache. With an object caching system in place (E.g. memcached, redis, APCu), WordPress will cache wp_options values with autoload set to “yes” as an array called “alloptions”. WordPress will no longer fetch options from the database on each page load, eliminating this potentially slow query. However, an excessively large wp_options table can greatly degrade the effectiveness of some caching strategies.
There are a number of WordPress caching plugins, but 10up recommends Memcached. Memcached requires some configuration to work optimally. By default, Memcached places a 1MB limit on individual values stored in the cache. A large wp_options table can create an alloptions array that easily exceeds the 1MB limit, preventing it from being cached. Every page would suffer from the large query itself, plus an added delay due to the failed Memcached call.
The simplest way to avoid the pitfalls of a large wp_options table is to keep it small—we recommend keeping this database table under 500 rows. This requires monitoring to ensure that newly installed or updated plugins don’t diverge from best practices and start storing data within wp_options.
We also set an index on the wp_options “autoload” field, protecting clients from poor performance if the table unexpectedly grows.
Memcached’s default 1MB item size is adjusted to accommodate the alloptions array as necessary. Avoid increasing the item size more than absolutely necessary, as the increased threshold requires more memory to store the same amount of data, making Memcached less efficient in it’s memory usage.
While wp_options might seem like a minor area for optimization, sites scaling to tens of millions of pageviews can see a huge difference by tuning a table queried on every single page load. While the WordPress community actively improves wp_options indexing, large WordPress site administrators need to safeguard against this common performance issue.
Knut Sparhell on
How does this recommended index on autoload look like? Just the single column or a sequence?
“ensure that newly installed or updated plugins don’t diverge from best practices and start storing data within wp_options”
So where do you propose plugins store data?
Custom tables? Because they bring their own issues – imagine if every plugin installed, even if just for a moment, added a custom table.
I suggest you back up this statement with a link, preferably to an official guideline.
Jake Goldman on
There are many other options; using the posts tables and “hidden” custom post types with post meta is a popular option for larger blocks of storage. There is a time and place for custom tables, though you’re right to urge caution. If you’re dealing with something like analytics or other high frequency data storage at any kind of scale, it simply doesn’t belong in local WordPress storage, but in a third party center, be that a hosted service like Google Analytics or a platform like Elasticsearch.
Interesting and if I’ve understood correctly, somewhat timely information for me.
I’m currently assessing a plug-in that stores all the additional data (eg. title, image path etc.) for attribute taxonomies (eg. colour, size etc) as a serialized array within a single record field in the wp_options table. I admit, this is not my main area of expertise but this just doesn’t feel right to me, surely it would be better to make use of additional fields the wp_term_taxonomy or some other table which can at least be indexed?
Dear ZACH BROWN,
Your post lights me up at some cases Im’ looking for the answer. I do face this problem as WordPress loads slowly sometimes after activate Redis or APCu.
I just wanna discuss something more. Since I’m using Multisite, my wp_options is not as large as its in Single-Site, but I still have the problem.
After spending sometime, I figure out it because of TRANSIENTS.
Iam starting thinking even wp_options of Single-Site cannot make Redis or APCu slowly as we can easily notice. Cause every tasks between HTTPD, PHP, APCu is not persistent (I think so, correct me if Im wrong). The main reason here is TRANSIENTS.
Thanks for sharing your insights here.
I am wondering what your thoughts are on plugins adding a small amount of options, but storing larger amounts of data in their values? (say 25+ KB after serialization.)
Would you see a performance impact with this as well, or is it insignificant if the site remains within the 500 row count?
Zach Brown on
Great question Adrian, this is something we see a lot. Overall I haven’t seen much degradation in performance with large serialized values in the options table. At least, not in the size you are talking about. There are some times where wp-cron isn’t functioning and the cron item in the options table gets huge and the performance of the table is impacted, but I believe that is only at extreme cases when the value gets to be several MB and the transfer of the data from MySQL to PHP adds noticeable latency. One place where this can get you in trouble, however, is if using an object-caching backend with a low limit on item size, such as Memcached. The Memcached default is to reject items larger than 1 MB in size, and depending on the object-cache.php dropin you use, it may be combining all the options together into one Memcached “alloptions” item. If you have a few plugins with giant serialized values in the options table, you can easily go over the 1 MB limit and see a performance hit.