The WordPress Options API: Autoload Optimization, the alloptions Bottleneck, and Cleanup Strategies

Why the Options Table Matters More Than You Think

WordPress stores its configuration data in a single database table: wp_options. Every theme setting, plugin configuration value, transient, site URL, and cron schedule lives in this table. On a fresh WordPress install, the options table contains roughly 120 rows. On a production site that has seen two years of plugin installations, theme switches, and configuration changes, that number can balloon to 5,000, 10,000, or even 50,000+ rows.

The problem is not the row count alone. The problem is what happens on every single page load: WordPress runs a query to fetch every option row where the autoload column is set to yes. All of those rows get loaded into memory, serialized into a single cache object, and stored in the object cache (or in a PHP array if no persistent cache is present). This cached blob is called alloptions, and its size directly affects how fast your site responds to requests.

This article is a deep technical examination of how the Options API works internally, why the alloptions cache becomes a bottleneck, how plugin developers contribute to the problem, and what you can do to diagnose and fix autoload bloat on production WordPress sites.

How wp_load_alloptions() Works Internally

The function wp_load_alloptions() is one of the first things WordPress calls during its bootstrap process. It runs during wp-settings.php, before plugins are loaded, before the theme is initialized, and before any content query executes. Understanding its internals is critical for anyone serious about WordPress database performance.

Here is what happens step by step:

Step 1: Check the object cache. WordPress calls wp_cache_get( 'alloptions', 'options' ). If a persistent object cache (Redis, Memcached) is configured and the alloptions key exists, WordPress skips the database entirely and uses the cached value. This is the fast path.

Step 2: Query the database. If the cache misses, WordPress executes this query:

SELECT option_name, option_value FROM wp_options WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')

This is a full table scan filtered by the autoload column. On a well-maintained site, this returns a few hundred rows. On a bloated site, it can return thousands of rows containing megabytes of serialized data.

Step 3: Build the alloptions array. WordPress iterates over every returned row and builds an associative array keyed by option_name:

$alloptions = array();
foreach ( $results as $row ) {
    $alloptions[ $row->option_name ] = $row->option_value;
}

Step 4: Store in object cache. The entire array is then stored using wp_cache_add( 'alloptions', $alloptions, 'options' ). If you are using Redis, this single cache key now holds every autoloaded option as a serialized PHP string.

Step 5: Return the array. From this point forward, any call to get_option() for an autoloaded option simply reads from this in-memory array. No additional database queries are made.

The function get_option() checks whether the requested option exists in the alloptions array. If it does, the value is returned immediately. If it does not, WordPress assumes the option has autoload = 'no' and fires an individual SELECT query for just that one option. This is the key distinction: autoloaded options are fetched in bulk, non-autoloaded options are fetched individually on demand.

The design makes sense for small, frequently needed values like siteurl, blogname, active_plugins, and template. These are needed on literally every page load. But the design breaks down when plugins and themes dump large serialized arrays, cached API responses, and configuration blobs into autoloaded options.

The Autoload Column: A Binary Switch With Outsized Consequences

The autoload column in wp_options is a VARCHAR(20) that stores either 'yes' or 'no'. Starting with WordPress 6.6, the column also supports 'on', 'off', 'auto', and 'auto-off' to give core and plugins finer-grained control, but the majority of existing data still uses the legacy 'yes' / 'no' values.

When a developer calls add_option() or update_option(), the default value for autoload is 'yes'. This is the root cause of most autoload bloat. Plugin developers who do not explicitly set autoload to 'no' will have their options included in the alloptions query by default.

Consider a plugin that stores a settings array:

update_option( 'myplugin_settings', array(
    'api_key' => 'abc123',
    'mode'    => 'production',
), 'yes' );

That small array adds maybe 80 bytes to the alloptions cache. Now consider a plugin that caches an entire API response:

update_option( 'myplugin_cached_products', $huge_json_blob, 'yes' );

If that JSON blob is 500KB of serialized product data, the alloptions cache just grew by half a megabyte. Every single page load now fetches, deserializes, and holds that 500KB in memory, even on pages where the plugin is not active or the data is not needed.

The autoload column has no index by default in WordPress. The query WHERE autoload IN ('yes', 'on', 'auto-on', 'auto') relies on the MySQL query optimizer to decide the most efficient execution plan. On small tables, this is a trivial full scan. On tables with tens of thousands of rows, the lack of an index means MySQL reads every row to find the ones marked for autoloading.

What Gets Autoloaded on a Typical Site

On a production WooCommerce site, I have seen the alloptions cache reach 4MB. Here is a breakdown of what contributes to that size:

WordPress core options: ~15KB. These include siteurl, home, blogname, blogdescription, active_plugins, current_theme, template, rewrite_rules (this one can be large), cron, and dozens of smaller settings.

Plugin settings: varies wildly. A well-behaved plugin stores a single serialized array of 1-5KB. A poorly behaved plugin stores multiple large options, cached data, or log entries.

Theme settings: typically 5-50KB for theme mods. Themes using the Customizer store all settings in a single serialized theme_mods_{theme_name} option.

Transients without external object cache: If no persistent object cache is configured, WordPress stores transients in the options table with autoload = 'no' for the value and autoload = 'no' for the timeout. However, some plugins call set_transient() incorrectly or store non-transient data in autoloaded options that behave like transients.

Widget settings: Each widget type gets its own widget_{name} option. Sites with many widget areas can accumulate 20-30 autoloaded widget options.

The alloptions Race Condition

One of the most subtle and dangerous behaviors of the alloptions cache involves concurrent requests. This race condition can cause option values to be silently overwritten, and most site owners never realize it is happening.

Here is the scenario:

Request A loads at time T=0. It fetches alloptions from the database, getting the current state of all autoloaded options. The alloptions array is now in memory for Request A.

Request B loads at time T=0.1. It also fetches alloptions from the database, getting the same snapshot of data.

Request A updates option foo using update_option( 'foo', 'new_value_A' ). WordPress updates the database row, then updates the in-memory alloptions array, and writes the entire alloptions array back to the object cache. The cache now reflects the change to foo.

Request B updates option bar using update_option( 'bar', 'new_value_B' ). WordPress updates the database row for bar, then updates its own in-memory alloptions array (which still has the OLD value of foo), and writes the entire alloptions array back to the object cache.

The result: the object cache now has bar = 'new_value_B' but foo has been reverted to its old value in the cache. The database has both correct values, but the cache is stale for foo. Until the cache expires or is invalidated, subsequent requests will read the stale value of foo from the cache.

This race condition is documented in WordPress core trac tickets and has been a known issue for years. The practical impact depends on your caching setup:

Without persistent object cache: The alloptions array is rebuilt from the database on every request, so the race condition does not manifest in the cache layer. However, it can still cause issues if two requests are updating options simultaneously within the same PHP process (rare but possible with WP-Cron or async handlers).

With Redis or Memcached: This is where the race condition is real and dangerous. The cache persists between requests, so the stale write from Request B overwrites the fresh write from Request A in the cache.

WordPress 6.6 introduced wp_prime_option_caches() and related improvements to reduce the blast radius of this problem, but the fundamental architecture of storing all autoloaded options in a single cache key means the race condition window exists whenever two concurrent requests modify different autoloaded options.

Mitigations

There are several strategies to reduce the impact of this race condition:

Use wp_cache_delete( 'alloptions', 'options' ) after batch updates to force a fresh load from the database on the next request.

For options that are updated frequently (hit counters, last-sync timestamps), use autoload = 'no' so they are not part of the alloptions blob. Individual option updates with autoload disabled do not interact with the alloptions cache at all.

If you are running Redis, consider using the redis.maxmemory-policy setting of allkeys-lfu to ensure frequently accessed keys are retained, and monitor for unexpected evictions of the alloptions key.

Why Plugins Create Autoload Bloat

Plugin developers are the primary source of autoload bloat, and most of them do not realize it. The reasons fall into several categories.

Default Autoload Behavior

The function signatures tell the story:

add_option( $option, $value = '', $deprecated = '', $autoload = null )
update_option( $option, $value, $autoload = null )

For add_option(), the default is 'yes'. For update_option(), the autoload parameter defaults to null, which means “do not change the current autoload value.” If the option does not yet exist, update_option() internally calls add_option(), which again defaults to 'yes'.

This means that any plugin developer who writes update_option( 'myplugin_data', $data ) without a third argument gets autoload enabled. Most developers never think about this parameter because it is the third (or fourth, in the case of add_option) argument and is rarely mentioned in beginner tutorials.

Storing Large Serialized Data

Some plugins treat the options table as a general-purpose key-value store. They serialize entire configuration trees, cached remote API responses, log entries, or analytics data into a single option value. I have personally found options containing:

A 2.3MB serialized array of Google Analytics data (autoloaded)
A 1.1MB serialized WooCommerce product sync cache (autoloaded)
A 780KB serialized array of CSS rules generated by a page builder (autoloaded)
A 450KB serialized array of social media post metadata (autoloaded)

Each of these is loaded into memory on every single page request, including AJAX requests, REST API calls, and WP-Cron executions. The plugin that created the 2.3MB analytics blob only needs that data on a single admin page, but because it is autoloaded, every frontend visitor pays the memory cost.

Abandoned Options From Deactivated Plugins

WordPress has no standard mechanism for cleaning up options when a plugin is deactivated or deleted. The register_uninstall_hook() and uninstall.php file provide a way for plugins to clean up when they are deleted from the Plugins page, but many plugins do not implement either mechanism. And even when they do, users who deactivate a plugin without deleting it leave all options in the database indefinitely.

Over time, a site accumulates hundreds of orphaned options from plugins that were tried once and abandoned. These ghost options continue to be autoloaded on every request, consuming memory and cache space for data that will never be read.

Transient Abuse

Some plugins store data that should be a transient (temporary, cacheable) as a regular option. Transients have an expiration mechanism and are automatically cleaned up by WordPress core (or stored in the object cache rather than the database if a persistent cache is present). Options have no expiration. A plugin that stores a “last checked” timestamp as an autoloaded option creates permanent bloat that grows every time the plugin updates its value.

Conversely, some plugins create transients without setting an expiration, which causes WordPress to store them as autoloaded options. The function set_transient( 'my_key', $value ) without a third argument stores the transient with no expiration, which in practice means it behaves exactly like an autoloaded option.

Plugin Developer Best Practices for the Options API

If you are building a WordPress plugin, here are concrete guidelines for responsible use of the options table.

When to Use autoload = ‘yes’

Set autoload to yes only when ALL of the following are true:

The option is needed on most or all page loads (both frontend and admin).
The option value is small (under 1KB serialized).
The option is read frequently but updated rarely.

Examples: plugin activation state, basic settings like API mode (production/sandbox), feature flags, display preferences.

When to Use autoload = ‘no’

Set autoload to no when ANY of the following are true:

The option is only needed on specific admin pages.
The option value is large (over 10KB serialized).
The option is a cache of external data.
The option is only needed during cron jobs.
The option stores logs, history, or analytics data.

Examples: cached API responses, import/export data, log entries, per-page configuration, license key validation cache.

// Good: small setting, needed everywhere
add_option( 'myplugin_version', '2.1.0', '', 'yes' );

// Good: large data, only needed on settings page  
add_option( 'myplugin_cached_templates', $templates, '', 'no' );

// Good: explicitly setting autoload on update
update_option( 'myplugin_sync_log', $log_data, 'no' );

Naming Conventions

Prefix all option names with your plugin slug. This makes it trivial to find and clean up your options:

// Good
add_option( 'wpkite_smtp_host', 'mail.example.com' );
add_option( 'wpkite_smtp_port', 587 );

// Bad: generic names that collide
add_option( 'smtp_host', 'mail.example.com' );
add_option( 'version', '1.0' );

Consistent prefixing also enables cleanup queries:

DELETE FROM wp_options WHERE option_name LIKE 'myplugin_%';

Implement Proper Uninstall Cleanup

Every plugin should implement uninstall.php or register_uninstall_hook() to remove its options when the plugin is deleted:

// uninstall.php
if ( ! defined( 'WP_UNINSTALL_PLUGIN' ) ) {
    exit;
}

global $wpdb;

// Delete all plugin options
$wpdb->query(
    "DELETE FROM {$wpdb->options} WHERE option_name LIKE 'myplugin_%'"
);

// Delete all plugin transients
$wpdb->query(
    "DELETE FROM {$wpdb->options} WHERE option_name LIKE '_transient_myplugin_%'"
);
$wpdb->query(
    "DELETE FROM {$wpdb->options} WHERE option_name LIKE '_transient_timeout_myplugin_%'"
);

Use the Settings API for Grouped Options

Instead of creating 15 separate options for 15 settings, store them in a single serialized array:

// Instead of this:
add_option( 'myplugin_color', '#fff' );
add_option( 'myplugin_font_size', '16px' );
add_option( 'myplugin_layout', 'grid' );

// Do this:
add_option( 'myplugin_settings', array(
    'color'     => '#fff',
    'font_size' => '16px',
    'layout'    => 'grid',
), '', 'yes' );

This approach reduces the number of rows in the options table and the number of entries in the alloptions cache. It does mean that updating a single setting requires reading the entire array, modifying it, and writing it back, but for settings that change rarely (which is most plugin settings), the read performance gain far outweighs the slightly more involved write pattern.

Diagnosing Autoload Bloat

Before you can fix autoload bloat, you need to measure it. Here are the tools and queries I use for diagnosis.

SQL Queries for Direct Database Analysis

Total size of all autoloaded options:

SELECT 
    COUNT(*) AS autoloaded_count,
    ROUND(SUM(LENGTH(option_value)) / 1024 / 1024, 2) AS size_mb
FROM wp_options 
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto');

On a healthy site, this should return under 1MB. If you see 2MB+, you have a problem. Over 5MB is an emergency.

Top 20 largest autoloaded options:

SELECT 
    option_name,
    LENGTH(option_value) AS value_length,
    ROUND(LENGTH(option_value) / 1024, 2) AS size_kb
FROM wp_options 
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto') 
ORDER BY LENGTH(option_value) DESC 
LIMIT 20;

This query immediately reveals the worst offenders. You will typically find 3-5 options responsible for 80% of the autoload bloat.

Autoloaded options grouped by plugin prefix:

SELECT 
    SUBSTRING_INDEX(option_name, '_', 2) AS plugin_prefix,
    COUNT(*) AS option_count,
    ROUND(SUM(LENGTH(option_value)) / 1024, 2) AS total_kb
FROM wp_options 
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')
GROUP BY plugin_prefix 
ORDER BY total_kb DESC 
LIMIT 30;

This query groups autoloaded options by their prefix (assuming plugins use underscore-delimited prefixes) and shows you which plugins are the biggest consumers. The SUBSTRING_INDEX function splits the option name at the second underscore, which works well for options named like woocommerce_cart_hash or jetpack_sync_settings.

Count of autoloaded vs non-autoloaded:

SELECT 
    autoload,
    COUNT(*) AS option_count,
    ROUND(SUM(LENGTH(option_value)) / 1024 / 1024, 2) AS size_mb
FROM wp_options 
GROUP BY autoload;

WP-CLI Commands

WP-CLI provides several useful commands for options analysis.

List all autoloaded options sorted by size:

wp option list --autoload=on --orderby=size --order=desc --format=table

Note: The --autoload flag in WP-CLI uses on/off rather than yes/no.

Get the total count and size:

wp option list --autoload=on --format=count
wp db query "SELECT ROUND(SUM(LENGTH(option_value))/1024/1024, 2) AS mb FROM wp_options WHERE autoload='yes';"

Search for options from a specific plugin:

wp option list --search='woocommerce_*' --autoload=on --format=table

Check alloptions cache size (requires persistent object cache):

wp cache get alloptions options | wc -c

This pipes the cached alloptions value through wc -c to count bytes. If the output is in the millions, you have significant bloat.

Export all autoloaded options for offline analysis:

wp option list --autoload=on --fields=option_name,size_bytes --format=csv > autoloaded_options.csv

Query Monitor Plugin

The Query Monitor plugin (by John Blackbourn) provides real-time insight into the options table during page loads. After installing it:

Navigate to any page on your site.
Open the Query Monitor panel in the admin bar.
Click on the “Queries” tab.
Search for alloptions or wp_options.

You will see the exact query WordPress executed, the number of rows returned, and the execution time. On a healthy site, the alloptions query should return under 500 rows and execute in under 5ms. If you see 2,000+ rows or execution times over 20ms, autoload bloat is impacting your site.

Query Monitor also shows you every individual get_option() call that triggers a database query (meaning the option was not autoloaded and was not already cached). This helps you identify options that should potentially be autoloaded because they are read on every page.

New Relic and Application Performance Monitoring

If you use New Relic, Datadog, or a similar APM tool, look for the following signals:

Transaction traces showing significant time spent in wp_load_alloptions() during the WordPress bootstrap phase.

Memory usage that is unusually high before any plugin or theme code runs. A 5MB alloptions cache means 5MB+ of memory consumed before the first line of your theme executes.

Redis/Memcached key sizes. Monitor the size of the wp:options:alloptions key (exact key name depends on your cache prefix). Set up alerts for when this key exceeds a threshold like 2MB.

Cleanup Strategies for Bloated Options Tables

Once you have identified the sources of bloat, here are strategies for cleaning up the options table.

Identifying Orphaned Options From Deactivated Plugins

The first step is to compare your active plugins against the option prefixes in your database. Here is a SQL approach:

-- Get all unique option prefixes (first two segments)
SELECT DISTINCT 
    SUBSTRING_INDEX(option_name, '_', 2) AS prefix
FROM wp_options 
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')
    AND option_name NOT LIKE '_%'  -- Exclude internal options
ORDER BY prefix;

Compare this list against your active plugins. Any prefix that does not correspond to an active plugin or theme is a candidate for cleanup. Common orphaned prefixes include old SEO plugins (like aioseop_*, wpseo_* from a plugin you switched away from), old caching plugins, old security plugins, and analytics plugins.

For WP-CLI users, here is a more automated approach:

# Get active plugin slugs
wp plugin list --status=active --field=name

# Compare against option prefixes
wp db query "SELECT DISTINCT SUBSTRING_INDEX(option_name, '_', 1) AS prefix FROM wp_options WHERE autoload='yes' ORDER BY prefix;"

Safe Cleanup Process

Never delete options without a backup. Here is the process I follow:

Step 1: Full database backup.

wp db export pre_cleanup_backup.sql

Step 2: Export the options you plan to delete.

wp db query "SELECT option_name, option_value FROM wp_options WHERE option_name LIKE 'old_plugin_%'" > deleted_options_backup.txt

Step 3: Switch autoload to ‘no’ first. Rather than deleting options immediately, first change them to non-autoloaded. This removes them from the alloptions query without destroying data:

UPDATE wp_options SET autoload = 'no' WHERE option_name LIKE 'old_plugin_%';

Step 4: Monitor for 48 hours. Check that nothing breaks. Search your error logs for references to the affected options. If a plugin or theme was reading one of these options, it will now trigger an individual database query instead of reading from alloptions, but it will still work.

Step 5: Delete confirmed orphans.

DELETE FROM wp_options WHERE option_name LIKE 'old_plugin_%';

Step 6: Flush the object cache.

wp cache flush

Fixing Legitimate Options That Should Not Be Autoloaded

For options that belong to active plugins but should not be autoloaded (large cached data, logs, infrequent settings):

UPDATE wp_options SET autoload = 'no' WHERE option_name = 'myplugin_large_cache';
UPDATE wp_options SET autoload = 'no' WHERE option_name = 'myplugin_activity_log';

Be aware that some poorly written plugins will break if their options are not autoloaded. This is because they assume get_option() will always find their option in the alloptions array. In practice, get_option() works identically for autoloaded and non-autoloaded options from the caller’s perspective. The only difference is performance: a non-autoloaded option triggers an individual database query on first access within a request. If the plugin reads the option once per page load, this adds one small query. If the plugin reads it 50 times per page load (bad code, but it happens), the value is still cached in the WordPress internal cache after the first read.

Cleaning Up Expired Transients

Expired transients in the options table are pure waste. WordPress is supposed to clean them up lazily (when they are next read), but expired transients for keys that are never read again accumulate indefinitely.

-- Count expired transients
SELECT COUNT(*) 
FROM wp_options 
WHERE option_name LIKE '_transient_timeout_%' 
    AND CAST(option_value AS UNSIGNED) < UNIX_TIMESTAMP();

-- Delete expired transients and their timeout entries
DELETE a, b FROM wp_options a
INNER JOIN wp_options b ON b.option_name = CONCAT('_transient_timeout_', SUBSTRING(a.option_name, 12))
WHERE a.option_name LIKE '_transient_%'
    AND a.option_name NOT LIKE '_transient_timeout_%'
    AND b.option_name LIKE '_transient_timeout_%'
    AND CAST(b.option_value AS UNSIGNED) < UNIX_TIMESTAMP();

WP-CLI also provides a built-in command:

wp transient delete --expired

For sites without persistent object caches, I recommend running this command daily via cron.

The Nuclear Option: Rebuilding the Options Table

On severely bloated sites (50,000+ rows, 20MB+ autoload), sometimes the fastest path is to rebuild the table entirely:

-- Create a clean copy
CREATE TABLE wp_options_clean LIKE wp_options;

-- Copy only the rows you want to keep
INSERT INTO wp_options_clean 
SELECT * FROM wp_options 
WHERE option_name NOT LIKE '_transient_%'
    AND option_name NOT LIKE '_site_transient_%';

-- Swap tables
RENAME TABLE wp_options TO wp_options_old, wp_options_clean TO wp_options;

-- Verify everything works, then drop the old table
DROP TABLE wp_options_old;

This approach is aggressive and should only be used on staging first. Test thoroughly before running on production. The benefit is that it eliminates all transient bloat in one operation and gives MySQL a clean table with no fragmentation.

Adding a Custom Index on the Autoload Column

By default, the wp_options table has two indexes: a primary key on option_id and a unique index on option_name. There is no index on the autoload column. The alloptions query filters by autoload = 'yes', which means MySQL must examine every row in the table to find matching rows.

On small tables (under 1,000 rows), this is negligible. On tables with 10,000+ rows, the full table scan becomes measurable. Adding an index on the autoload column can improve the alloptions query performance:

ALTER TABLE wp_options ADD INDEX autoload_idx (autoload);

However, there is a nuance. The autoload column has very low cardinality: it only has two values ('yes' and 'no'). MySQL's query optimizer may decide that a full table scan is actually faster than using an index on a low-cardinality column, especially when a large percentage of rows have autoload = 'yes'.

The index is most beneficial when:

The table has many rows (10,000+).
The majority of rows are autoload = 'no' (meaning the index helps MySQL skip a large portion of the table).
The table has significant transient bloat that has not been cleaned up.

You can verify whether MySQL uses the index by running an EXPLAIN:

EXPLAIN SELECT option_name, option_value FROM wp_options WHERE autoload IN ('yes', 'on', 'auto-on', 'auto');

If the key column shows autoload_idx, the index is being used. If it shows NULL, MySQL chose a full scan instead.

A better approach for WordPress 6.6+ sites is a composite index that covers the query entirely:

ALTER TABLE wp_options ADD INDEX autoload_options (autoload, option_name, option_value(100));

This covering index allows MySQL to satisfy the alloptions query entirely from the index without touching the table data (an "index-only scan"). The option_value(100) prefix is a compromise: it covers small option values directly in the index but falls back to the table for large values. Whether this is beneficial depends on your data distribution and MySQL version. Test with EXPLAIN and actual query timing before deploying to production.

Note that WordPress core has considered adding an autoload index multiple times (see Trac tickets #24044 and #36218), but it has not been added to the default schema due to the cardinality concerns mentioned above. As of WordPress 6.6, the core team introduced performance improvements to the options loading system that reduce but do not eliminate the need for a custom index.

Building a Monitoring Dashboard for Options Table Health

For agencies and hosting providers managing dozens or hundreds of WordPress sites, manual diagnosis is not scalable. Here is how to build automated monitoring for options table health.

Custom WP-CLI Command for Automated Audits

Create a WP-CLI command that outputs a health report:

if ( defined( 'WP_CLI' ) && WP_CLI ) {
    WP_CLI::add_command( 'options-health', function() {
        global $wpdb;

        // Total autoloaded size
        $result = $wpdb->get_row(
            "SELECT COUNT(*) AS count, 
                    ROUND(SUM(LENGTH(option_value))/1024/1024, 2) AS size_mb 
             FROM {$wpdb->options} WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')"
        );
        WP_CLI::log( "Autoloaded options: {$result->count} ({$result->size_mb} MB)" );

        // Warning thresholds
        if ( $result->size_mb > 2 ) {
            WP_CLI::warning( "Autoload size exceeds 2MB threshold!" );
        }

        // Top offenders
        $top = $wpdb->get_results(
            "SELECT option_name, ROUND(LENGTH(option_value)/1024, 2) AS kb 
             FROM {$wpdb->options} 
             WHERE autoload IN ('yes', 'on', 'auto-on', 'auto') 
             ORDER BY LENGTH(option_value) DESC LIMIT 10"
        );
        WP_CLI::log( "nTop 10 largest autoloaded options:" );
        foreach ( $top as $row ) {
            WP_CLI::log( "  {$row->option_name}: {$row->kb} KB" );
        }

        // Expired transients
        $expired = $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->options} 
             WHERE option_name LIKE '_transient_timeout_%' 
             AND CAST(option_value AS UNSIGNED) < UNIX_TIMESTAMP()"
        );
        WP_CLI::log( "nExpired transients: {$expired}" );

        // Total table rows
        $total = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->options}" );
        WP_CLI::log( "Total options rows: {$total}" );
    });
}

Run it with wp options-health to get an instant health check. Schedule it in cron to generate daily reports.

Database Monitoring With Custom Queries

For sites using a monitoring stack (Grafana + Prometheus, Datadog, etc.), expose these metrics:

// Add to your theme's functions.php or a custom mu-plugin
add_action( 'wp_ajax_options_health_metrics', function() {
    if ( ! current_user_can( 'manage_options' ) ) {
        wp_die( 'Unauthorized' );
    }
    
    global $wpdb;
    
    $metrics = array();
    
    $metrics['autoload_count'] = (int) $wpdb->get_var(
        "SELECT COUNT(*) FROM {$wpdb->options} WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')"
    );
    
    $metrics['autoload_bytes'] = (int) $wpdb->get_var(
        "SELECT SUM(LENGTH(option_value)) FROM {$wpdb->options} WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')"
    );
    
    $metrics['total_count'] = (int) $wpdb->get_var(
        "SELECT COUNT(*) FROM {$wpdb->options}"
    );
    
    $metrics['expired_transients'] = (int) $wpdb->get_var(
        "SELECT COUNT(*) FROM {$wpdb->options} 
         WHERE option_name LIKE '_transient_timeout_%' 
         AND CAST(option_value AS UNSIGNED) < UNIX_TIMESTAMP()"
    );
    
    $metrics['largest_autoloaded_kb'] = (float) $wpdb->get_var(
        "SELECT ROUND(MAX(LENGTH(option_value))/1024, 2) 
         FROM {$wpdb->options} WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')"
    );
    
    wp_send_json( $metrics );
});

Hit this endpoint with your monitoring agent at regular intervals and set up alerts:

Warning when autoload_bytes exceeds 1MB.
Critical when autoload_bytes exceeds 3MB.
Warning when expired_transients exceeds 1,000.
Warning when largest_autoloaded_kb exceeds 500.

Tracking Autoload Growth Over Time

Store daily snapshots of the autoload size to detect gradual bloat. A simple approach:

// Run daily via wp-cron
add_action( 'wpkite_daily_options_audit', function() {
    global $wpdb;
    
    $size_bytes = (int) $wpdb->get_var(
        "SELECT SUM(LENGTH(option_value)) FROM {$wpdb->options} WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')"
    );
    
    $count = (int) $wpdb->get_var(
        "SELECT COUNT(*) FROM {$wpdb->options} WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')"
    );
    
    $history = get_option( 'wpkite_autoload_history', array() );
    $history[] = array(
        'date'  => current_time( 'Y-m-d' ),
        'bytes' => $size_bytes,
        'count' => $count,
    );
    
    // Keep last 90 days
    $history = array_slice( $history, -90 );
    
    update_option( 'wpkite_autoload_history', $history, 'no' );
    
    // Alert if size grew more than 20% in 7 days
    if ( count( $history ) >= 7 ) {
        $week_ago = $history[ count($history) - 7 ]['bytes'];
        $growth_pct = ( ( $size_bytes - $week_ago ) / max( $week_ago, 1 ) ) * 100;
        
        if ( $growth_pct > 20 ) {
            wp_mail(
                get_option( 'admin_email' ),
                'WPKite Alert: Autoload size grew ' . round($growth_pct) . '% in 7 days',
                "Current autoload size: " . round($size_bytes/1024) . " KBn" .
                "Size 7 days ago: " . round($week_ago/1024) . " KBn" .
                "Growth: " . round($growth_pct) . "%"
            );
        }
    }
});

Notice that the history option itself uses autoload = 'no'. We are not going to contribute to the problem we are monitoring.

Performance Benchmarks: The Real-World Impact of alloptions Size

I ran a series of benchmarks to quantify the actual performance impact of alloptions bloat. The test environment was a DigitalOcean droplet (4 vCPU, 8GB RAM) running WordPress 6.2, PHP 8.1, MariaDB 10.6, and Redis 7.0 as the persistent object cache.

Test Methodology

The baseline site had a clean WordPress installation with 23 active plugins (a realistic production configuration). I measured three metrics:

Time to First Byte (TTFB) for the homepage, averaged over 100 requests using curl.
Peak memory usage per request, measured with memory_get_peak_usage(true).
MySQL query time for the alloptions query, measured with SHOW PROFILES.

I then artificially inflated the alloptions cache by inserting autoloaded options of increasing sizes and re-measured.

Results Without Persistent Object Cache (No Redis)

Autoload Size	Row Count	TTFB (ms)	Memory (MB)	Query Time (ms)
256 KB	340	142	18.2	1.8
512 KB	380	158	22.4	2.3
1 MB	420	189	28.6	3.1
2 MB	510	245	38.1	5.4
5 MB	680	412	62.3	11.2
10 MB	890	687	98.7	22.6

The relationship between autoload size and TTFB is roughly linear up to 2MB, then accelerates as PHP memory allocation overhead and MySQL I/O compound. At 10MB, the TTFB nearly quintupled compared to the 256KB baseline.

Memory usage grows faster than the raw data size because PHP's internal representation of unserialized arrays consumes significantly more memory than the serialized string. A 5MB serialized string can expand to 15-20MB when unserialized into a nested PHP array.

Results With Redis Object Cache

Autoload Size	TTFB (ms)	Memory (MB)	Redis GET Time (ms)
256 KB	98	16.8	0.3
512 KB	108	20.1	0.5
1 MB	128	26.4	0.9
2 MB	162	35.8	1.6
5 MB	278	58.1	3.8
10 MB	498	92.4	8.1

Redis eliminates the MySQL query entirely after the first request, but it does not eliminate the cost of transferring the large cached value over the Redis socket and unserializing it in PHP. At 5MB, the Redis GET alone takes 3.8ms, and PHP still needs to unserialize the data and hold it in memory.

The key takeaway: a persistent object cache helps, but it does not fix autoload bloat. It shifts the bottleneck from MySQL to Redis+PHP unserialization. The only real fix is reducing the size of autoloaded data.

Impact on Concurrent Requests

Autoload bloat has a multiplied effect under concurrency. If each request consumes 60MB of memory (as in the 5MB autoload scenario), a server with 8GB of RAM and php-fpm configured for dynamic workers will support fewer concurrent requests before running out of memory.

At 18MB per request (256KB autoload), the server can handle roughly 400 concurrent php-fpm workers.
At 62MB per request (5MB autoload), the server can handle roughly 120 concurrent workers.
At 98MB per request (10MB autoload), the server can handle roughly 75 concurrent workers.

This is a 5x reduction in concurrency capacity from autoload bloat alone. For a WooCommerce site during a sale, this can be the difference between handling traffic and crashing.

Impact on WP-Cron and REST API

An often overlooked consequence of autoload bloat is its impact on background processes. WP-Cron executions, REST API requests, and AJAX handlers all go through the full WordPress bootstrap. Every single one of these requests loads alloptions.

A site with a 5MB alloptions cache that processes 100 WP-Cron events per hour is allocating 5MB x 100 = 500MB of memory per hour just for the alloptions data across those cron runs. If those cron events are quick tasks (sending a queued email, checking for updates), the alloptions load dominates the total execution time.

REST API endpoints are particularly affected because modern WordPress uses the REST API heavily for the block editor (Gutenberg). Every block editor heartbeat, every autosave, every block rendering preview triggers a REST API request that loads alloptions. On sites where content editors are active, this generates dozens of additional alloptions loads per minute.

WordPress 6.6 and Beyond: Core Improvements to Options Loading

The WordPress core team has been actively working on improving options performance. WordPress 6.6 (released in 2024) introduced several changes worth noting.

The wp_prime_option_caches() Function

This new function allows developers to pre-load multiple non-autoloaded options in a single query:

wp_prime_option_caches( array( 'option_a', 'option_b', 'option_c' ) );

Instead of three separate SELECT queries (one per get_option() call), this fires a single query:

SELECT option_name, option_value FROM wp_options WHERE option_name IN ('option_a', 'option_b', 'option_c')

This is useful for plugins that have multiple non-autoloaded options needed on a specific page. Load them all at once at the top of your template or hook callback.

Autoload Values: 'on', 'off', 'auto', 'auto-off'

WordPress 6.6 expanded the autoload column beyond 'yes' and 'no':

'on' and 'off' are the new equivalents of 'yes' and 'no' and behave identically.

'auto' means WordPress will dynamically decide whether to autoload the option based on usage patterns. If an option is read on multiple requests, it gets promoted to autoloaded. This is not yet fully implemented but sets the groundwork for future intelligence in the loading system.

'auto-off' means the option was previously 'auto' but WordPress determined it should not be autoloaded.

The function wp_set_option_autoload_values() allows bulk updates of autoload values:

wp_set_option_autoload_values( array(
    'myplugin_large_cache'  => 'off',
    'myplugin_settings'     => 'on',
    'myplugin_temp_data'    => 'off',
) );

Lazy Loading and Deferred Queries

Core has also begun exploring lazy loading patterns where non-critical options are not loaded until first accessed. The pre_option_{$option} filter and the option_{$option} filter provide hooks for plugins to implement their own lazy loading logic.

For plugin developers, the practical advice remains the same: explicitly set autoload values, keep autoloaded option values small, and use wp_prime_option_caches() for batch loading non-autoloaded options.

A Practical Optimization Workflow

Let me tie everything together with a step-by-step workflow for optimizing a production WordPress site's options table.

Step 1: Measure. Run the SQL queries from the diagnostics section above. Record the autoload count, total size, and top 20 offenders.

Step 2: Back up. Export the full database. Store it somewhere safe.

Step 3: Clean expired transients. Run wp transient delete --expired. This is safe and often reclaims significant space.

Step 4: Identify orphaned options. Cross-reference option prefixes against your active plugin list. Flag any prefixes from plugins that are no longer installed.

Step 5: Switch orphaned options to autoload=no. Do not delete them yet. Just disable autoloading. Monitor for 48 hours.

Step 6: Address the top offenders. For each of the top 10 largest autoloaded options, determine: Does the active plugin that owns this option actually need it on every page load? If not, switch it to autoload = 'no'. Some plugins provide a setting for this. Others require a direct database update.

Step 7: Delete confirmed orphans. After the monitoring period, delete options from plugins that are no longer installed.

Step 8: Consider adding an index. If your options table has over 10,000 rows, test the autoload_idx index on staging first, then deploy to production.

Step 9: Set up ongoing monitoring. Deploy the WP-CLI health check or monitoring endpoint described above. Schedule weekly audits.

Step 10: Educate your team. Make autoload awareness part of your plugin review process. Every new plugin installed should be checked for how many autoloaded options it creates and how large they are.

This workflow, applied to a client WooCommerce site, reduced the alloptions cache from 4.2MB to 380KB. TTFB dropped from 340ms to 125ms. Peak memory per request dropped from 52MB to 21MB. The entire process took about three hours, with most of that time spent on the 48-hour monitoring period between Steps 5 and 7.

Final Thoughts

The WordPress options table is deceptively simple. Two columns matter: option_name and autoload. The engineering decisions made around those two columns determine whether your site bootstraps in 50ms or 500ms. Whether your server handles 400 concurrent users or 75.

The alloptions pattern was designed in an era when WordPress sites had a handful of plugins and the options table had a few hundred rows. That era ended years ago. Modern production WordPress sites run 20-40 plugins, generate thousands of options, and process millions of requests per month. The alloptions loading strategy has not fundamentally changed, even as the data it carries has grown by orders of magnitude.

The good news is that the tools for diagnosis and optimization exist, and the WordPress core team is actively improving the options loading system. The bad news is that every plugin you install has the potential to add kilobytes or megabytes of autoloaded data. The solution is vigilance: measure your alloptions size regularly, audit new plugins before they ship to production, and clean up after plugins that did not clean up after themselves.

Your database will thank you. Your users will notice the difference. And your hosting bill might shrink, too.