Multisite Option Storage, and What Multiplies by the Number of Sites

The network was running slow, so somebody opened the main site and started auditing wp_options. The numbers were fine. Small autoload set, no rows over 40 KB, nothing worth a ticket. Two days later the actual complaint turned out to be on site 47, where a forms plugin had been rewriting an 800 KB entry on every submission since a release three months earlier.

That is the shape of most multisite option work. The audit is not wrong, it is just pointed at one site out of however many exist, and the site you happened to point it at is the one site nobody visits.

Four minutes against four seconds, and only the slow one tells you anything. Measured on a 200-site network with WP-CLI 2.10, WordPress 6.6.2 and PHP 8.2, with a persistent object cache configured, the loop below takes about four minutes. The single-site check takes four seconds.

Two storage facts make this worse. First, an option written on a network is not in the same table as an option written on a site, and the two APIs do not fall back to each other. Second, any per-site table a plugin creates is duplicated once per site in the network, which turns a routine schema change into two hundred of them.

Three tables hold options, and they never look at each other

A multisite install stores per-site options in wp_options (or wp_2_options, wp_3_options, and so on), network-wide options in wp_sitemeta, and per-user settings in wp_usermeta. A plugin that reads with get_option( 'myplugin_api_key' ) will never see a value written with update_site_option( 'myplugin_api_key' ), even though the key matches. There is no merge step and no shadow copy.

get_site_option() and get_network_option() are the same read under two names; the network functions take an explicit network ID as their final argument, which is the only reason the longer name exists. Both read wp_sitemeta through the site-options cache group. That group is a different key space from the options group that holds alloptions, so flushing one does nothing to the other, and a cache warm-up script that only primes alloptions leaves every network read to hit the database.

There is a fourth table worth knowing about because its absence causes design mistakes. WordPress 5.1 added wp_blogmeta, which sounds like the per-site equivalent of wp_usermeta. Core barely uses it and offers no real plugin-facing API for it, so it is not a home for your data. Practically, a plugin on a network has two options: a table per site, or one network table with a blog_id column. Choosing "I will just call get_option() everywhere and figure out scope later" is not one of them.

switch_to_blog() is a cache operation before it is a query operation

The reason a per-site audit costs four minutes instead of four seconds is that switch_to_blog() does more than assign a new value to $blog_id. Internally it calls $wpdb->set_blog_id(), which rewrites $wpdb->prefix and the rest of the table properties, and the switch also resets the object cache entries that are scoped to the previous site. The next get_option() then rebuilds alloptions for the site you just moved into.

On a network where no persistent object cache is configured, that rebuild is a real query each time. On a network where Redis or Memcached is present, the rebuild is a network round trip each time. Either way, one audit pass over 200 sites pays the cost 200 times, and an audit script that switches blogs inside a loop over options pays it thousands of times.

Two habits keep this manageable. Read from the current site and discard results as you go, rather than accumulating every site's data in one array for a summary at the end. And never nest a switch inside a loop that already runs per record. A network audit should have one switch per site, which on a 200-site network is 200 rebuilds and about four minutes of wall clock.

The audit has to run per site, and WP-CLI makes that a loop

Since WordPress 6.6 the autoload column distinguishes an explicit on from values core sets on its own, so the predicate depends on the version you are targeting. The snippet below assumes 6.6 or newer; on 6.5 and older the same query needs autoload = 'yes'.

Write the per-site reader as a file. Inlining SQL in wp eval means fighting two layers of quoting, and the file is the thing you keep and run again next quarter.

// network-autoload-audit.php: one site, called once per URL.
<?php
global $wpdb;

$rows = $wpdb->get_results(
    "SELECT option_name, LENGTH(option_value) AS bytes
     FROM {$wpdb->options}
     WHERE autoload IN ( 'on', 'auto-on', 'auto' )
     ORDER BY bytes DESC"
);

$bytes = 0;
foreach ( $rows as $r ) {
    $bytes += (int) $r->bytes;
}

$top = $rows ? $rows[0]->option_name . ':' . $rows[0]->bytes : '-';

printf( "%s|%d|%d|%s\n", home_url(), count( $rows ), $bytes, $top );

printf '%s\n' 'url|rows|bytes|largest'

wp site list --field=url --archived=0 --spam=0 --deleted=0 \
  | while read -r url; do
      wp --url="$url" --skip-plugins --skip-themes eval-file ./network-autoload-audit.php \
        || printf '%s|error\n' "$url"
    done

Run it against a 200-site network and the output sorts itself by eye within a minute. What you are looking for is not the network total, which is meaningless, but the shape of the distribution: most sites sitting under 100 KB, a handful in the megabyte range, and a long tail of sites that nobody inspects. The tail is where the plugin that writes too much on every request lives.

Three details in that loop matter more than they look. --skip-plugins and --skip-themes keep third-party code from loading while you measure, so a plugin cannot change what it writes mid-audit. home_url() rather than the loop variable ends up in the output, because --url can normalise or redirect a URL and you want to know which site actually produced the row. And the single wp process per site is deliberate: batching sites into one process means batching switches, which is exactly the pattern that makes the audit slow and the results ambiguous.

A per-site audit also means a per-site remedy. Demoting an autoloaded row on the main site has no effect on site 47, and a mu-plugin that fixes the flag globally needs to run on every site, which is a different deployment question. If you take one thing from the loop above, take the row count: it tells you whether you are fixing one site or running a network-wide policy.

Per-site or network-wide is decided at activation, not later

A plugin that needs a custom table has to answer one question at activation time: does each site own its rows, or does the network own them. The two answers produce different schemas, and moving between them after the fact means writing a data migration for every site and keeping both code paths alive until each one runs.

Activation is where this gets decided, and activation is not a single event on a network. A plugin activated network-wide runs its activation hook once for the network, and then each site runs it again the first time that site is visited and on every subsequent network upgrade. Code that creates its table inside that hook is fine on the sites that have been visited and missing on the sites that have not. The request that discovers the gap is a visitor's, not yours, and the error it produces will not look like a plugin bug at first glance. Verify the table exists on a sample of sites after activation rather than trusting the hook.

The network table with a blog_id column is usually the right answer for anything administrative. The per-site table is right when a site's data should disappear with the site. What is not right is the third pattern I keep finding in the wild: a per-site table created on activation, plus a network admin screen that reads it with a raw query against $wpdb->prefix after a switch_to_blog() that never runs. That screen shows the main site's rows and calls it network-wide reporting.

The limit: every schema change gets multiplied by N

Here is the number that decides whether the table shape above was a good idea. On a 200-site network, adding one index to a per-site custom table is 200 statements, executed by 200 code paths that can each fail on their own schedule. Some sites will be on the current schema, some will be one version behind because their upgrade routine timed out, and the dashboard that reports schema versions becomes a support tool.

That is a real cost against a real benefit, and it is fair to pay when the data genuinely belongs to individual sites. It is not fair to pay for a plugin whose rows are only ever counted at the network level, which is the case I see most often. Per-site tables are for per-site data. If your queries would all begin with "sum across every site", the table should not be per site.

Two things this advice does not solve. It does not tell you what to do when a plugin you did not write already shipped 200 tables, which is a migration project with a schedule, not an audit. And it does not help with the performance character of switch_to_blog() loops that live in front-end code rather than in an audit script. A theme that loops over all sites to build a footer is paying a cache rebuild per site on every page view, and no option-level fix addresses that.

What to do on Monday

1. Run the WP-CLI loop and save the output as a dated file. Dated files are the only baseline you get, because nobody remembers what the numbers were last quarter.
2. Sort by bytes, not by row count, and fix the worst site first. One site with an 800 KB autoloaded row costs more than fifty sites at 10 KB.
3. Audit wp_sitemeta separately with wp network meta list 1. It is a different table with a different cache group, and nothing in a per-site report will show it to you.
4. Re-run the loop after the fix and store the second file next to the first. Growth between two dated runs is the signal worth alerting on.
5. For any custom table you own, write down per-site or network-wide in the plugin readme before the next schema change. The decision costs an hour now and a migration later.