WordPress Multisite Database Sharding: Scaling and Pitfalls

Multisite is a good fit for many brands, faculties or regions on one codebase. Its database layout is simple: one set of shared network tables, plus a full set of content tables per site. That simplicity is also the scaling limit. This article covers where the limit shows up, what to tune before sharding, and how to shard without creating a new class of bugs.

How Multisite lays out the database

Each new site gets its own prefixed copy of the content tables: wp_2_posts, wp_2_postmeta, wp_2_options, wp_2_comments, wp_2_commentmeta, wp_2_terms, wp_2_termmeta, wp_2_term_taxonomy, wp_2_term_relationships and wp_2_links. That is ten tables per site. A network of 5,000 sites carries around 50,000 tables before any plugin adds its own per-site tables.

A smaller set of tables is shared by the whole network: wp_users, wp_usermeta, wp_blogs, wp_blogmeta, wp_site, wp_sitemeta, wp_signups and wp_registration_log. Every site reads them, and they grow with the network.

Where it actually hurts

Table cache before sharding

MySQL keeps an open-table cache sized by table_open_cache. The MySQL manual says to check the Opened_tables status variable. If it is large or climbing quickly, the cache is too small. The manual also warns that raising it can run into the operating system’s file-descriptor limit, so open_files_limit has to rise with it.

SHOW GLOBAL STATUS LIKE 'Opened_tables';
SHOW GLOBAL VARIABLES LIKE 'table_open_cache';
SHOW GLOBAL VARIABLES LIKE 'open_files_limit';

On a large network, that tuning alone often makes a bigger difference than moving tables between servers. There is no fixed site count at which sharding becomes mandatory. Measure, then decide.

The object cache does most of the work

Options, user capabilities and post lookups that come from a warm persistent object cache never reach MySQL. On Multisite, drop-ins prefix non-global cache groups with the site ID, so per-site data stays separate automatically. The leaks come from custom groups wrongly registered as global, and from several networks or environments sharing a cache server without a unique WP_CACHE_KEY_SALT. The backend choice is covered in Redis vs Memcached for WordPress.

Sharding with LudicrousDB

When one server cannot carry the tables, the established approach is a db.php drop-in that routes each query to a database based on the table it touches. Automattic’s HyperDB started this. Its WordPress.org listing was last updated in October 2022 and carries the “not tested with the latest 3 major releases” warning. LudicrousDB began as a distribution of HyperDB and is actively maintained: its most recent tag is 5.3.1, with commits as recently as October 2026. It keeps the same configuration model, so HyperDB examples mostly carry over.

The model is datasets: named groups of tables that live together, each mapped to one or more servers. Global tables stay in the default global dataset. A callback decides which dataset each per-site table belongs to.

// ABSPATH/db-config.php (LudicrousDB). Credentials come from the environment.
$wpdb->add_database( array(
	'host'     => getenv( 'DB_GLOBAL_HOST' ), // use host:port if not 3306
	'user'     => getenv( 'DB_USER' ),
	'password' => getenv( 'DB_PASSWORD' ),
	'name'     => 'wp_global',
	// No 'dataset': defaults to 'global' (users, blogs, sitemeta, site 1).
) );

foreach ( array( 'shard_a', 'shard_b' ) as $shard ) {
	$wpdb->add_database( array(
		'host'     => getenv( 'DB_' . strtoupper( $shard ) . '_HOST' ),
		'user'     => getenv( 'DB_USER' ),
		'password' => getenv( 'DB_PASSWORD' ),
		'name'     => 'wp_' . $shard,
		'dataset'  => $shard,
	) );
}

$wpdb->add_callback( 'acme_dataset_for_table' );

function acme_dataset_for_table( $query, $wpdb ) {
	// Per-site tables look like wp_123_posts; global tables do not match.
	$pattern = '/^' . preg_quote( $wpdb->base_prefix, '/' ) . '(\d+)_/';
	if ( ! preg_match( $pattern, $wpdb->table, $m ) ) {
		return null; // Fall through to 'global'.
	}

	// Explicit ranges, not modulo: adding a shard must never move existing sites.
	// The last range is open-ended so new sites always have a home.
	static $ranges = array(
		2500        => 'shard_a',
		PHP_INT_MAX => 'shard_b',
	);
	foreach ( $ranges as $max_blog_id => $dataset ) {
		if ( (int) $m[1] <= $max_blog_id ) {
			return $dataset;
		}
	}
}

Three changes from the original HyperDB example in this draft matter:

• Routing is by callback, not by listing tables. add_table() suits a handful of fixed tables. Thousands of per-site tables need a rule.
• The map is explicit. blog_id % shards looks tidy until you add a shard and every site’s home changes. Ranges, or a lookup table for moved sites, keep placement stable.
• New sites must route before their tables exist. WordPress creates a site’s tables when the site is initialised. If the callback cannot place a new ID, the tables land in the wrong database. The open-ended last range guarantees a destination.

Concurrency pitfalls after sharding

Cross-dataset joins stop working

A query can only run on one server. SQL that joins a per-site table to a global table, such as wp_5_posts to wp_users, worked when everything shared a database and fails once they are apart. Search custom code and plugins for raw SQL that mixes $wpdb->posts with $wpdb->users or $wpdb->usermeta, and rewrite it as two queries joined in PHP.

Read-after-write and replica lag

With replicas, a read straight after a write can hit a replica that has not caught up. LudicrousDB sends reads for a table to the primary once that table has been written in the same request. That protects the current request. It does not protect the next request. Show a user their own change from the primary or from cache, not from a replica they may beat. LudicrousDB also exposes callbacks for supplying replica-lag data, so lagging replicas can be skipped.

Network-wide operations

Anything that iterates sites, such as upgrades, cron, search indexing and migrations, should batch per site and tolerate interruption. wp core update-db --network upgrades every site’s tables. For cron, disable WP-Cron and run due events per site:

# wp-config.php: define( 'DISABLE_WP_CRON', true );
wp site list --field=url --archived=0 --deleted=0 \
  | xargs -P 4 -I {} wp cron event run --due-now --url={} --quiet

Keep parallelism modest and per shard, so one busy database is not hit by every worker at once. The batching and throttling habits are covered in WP-CLI at Scale.

Backups and restores

Each shard is backed up separately, so a “point in time” now spans several servers. Restoring one shard to an earlier time than the global database can leave sites whose content refers to users or settings that no longer match. Document a restore procedure that names which databases move together, and test it.

A decision checklist

1. Is a persistent object cache in place and hitting well? If not, start there.
2. Is Opened_tables climbing? Tune the table cache and file limits before moving tables.
3. Is the database server the bottleneck after that? Add replicas for reads before splitting writes.
4. Still saturated? Shard per-site tables with LudicrousDB, using an explicit map, after auditing cross-table SQL.
5. Do some sites need their own lifecycle, plugins or legal separation? That is an argument for separate installs, not for more shards.

Hosted platforms change the picture. On WordPress VIP, the database layer is managed and your job is query discipline, covered in Engineering for WPVIP.

Questions people actually ask

When should we choose Multisite over separate WordPress installs?

When sites share a theme, plugin set, users and release cycle, such as faculty sites or a multi-brand portal. If sites need different plugins, independent deployments or strict data separation, separate installs are simpler and safer, and they scale horizontally without sharding.

Is HyperDB still maintained?

Its WordPress.org listing was last updated in October 2022 and is flagged as untested with recent WordPress releases. LudicrousDB, which began as a HyperDB distribution and keeps its configuration model, is actively maintained and is the better starting point today.

How do database upgrades work on a sharded network?

The same as on any network: wp core update-db --network walks every site, and the drop-in routes each site’s queries to its shard. On large networks, run it during low traffic and watch replica lag, because it is a burst of schema writes.