Keeping your WordPress options table in check

on March 10, 2017 • 7 Comments

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:

• permalinks
• 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.

Object caching

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.

Our recommendations

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.