How to Audit wp_usermeta for Capability Bloat That Slows Every Admin Request

wp_usermeta is where WordPress keeps everything it knows about a user that doesn’t fit the wp_users columns: capabilities, session tokens, admin screen state, and whatever plugins decide to hang off each account. It’s the quieter sibling of wp_options — same meta_key/meta_value design, LONGTEXT values, no per-key schema — and it inherits the priming behavior that makes bloat expensive: the first get_user_meta() call for a user loads every row that user has, not just the key you asked for. Capabilities live here under {prefix}capabilities. Role definitions live one table over, in the {prefix}user_roles option. And WP_User stitches the two together on every request that carries a logged-in session — which is why this audit belongs in your runbook. An eleven-year-old install with one heavy editor account can make every wp-admin screen feel like it’s rendering over dial-up while anonymous visitors see nothing wrong at all.

The symptom: slow admin, fast front end

The signature is easy to miss because it hides behind “the admin is slow,” which everyone says about everything. The tells are more specific:

• Anonymous front-end requests are fine. Logged-in requests are not, and the slowness follows the account, not the page.
• The worst offender is usually the longest-serving account — the founding editor, the person who’s been there since the first theme change.
• DevTools puts the lag in TTFB, not in assets. No slow scripts, no oversized images, just server time.
• Every admin screen is equally mediocre. No single broken screen to blame.

Open Query Monitor on wp-admin/index.php as that user and sort the query list by time. You’re looking for one query that carries a suspicious amount of weight for something called “meta cache priming”:

Query Monitor — wp-admin/index.php, logged in as user 1
Total query time: 0.31 s (of 0.62 s page generation)

1. SELECT user_id, meta_key, meta_value
   FROM wp_usermeta
   WHERE user_id IN (1)
   ORDER BY umeta_id ASC
   Caller: update_meta_cache() → get_metadata_raw() → get_user_meta()
   Component: core   Rows: 214   Time: 0.0213 s

On a client’s install last spring, that query moved roughly 1.3 MB for the founding editor on every single admin request — 214 rows of accumulated session tokens, screen state, and plugin per-user arrays. Twenty-one milliseconds doesn’t sound like much until you notice that PHP also has to unserialize the payload, every value of it, and that the same tax applied to every user on every logged-in request. After pruning, the same user primed 46 rows / 88 KB and the query ran in under 2 ms. Same host, same PHP version, same plugins.

The trace: one query you pay on every logged-in request

Here’s the full path. The fix only makes sense once you’ve seen why the cost is structural rather than accidental:

1. wp-settings.php builds the current user during bootstrap — after plugins are loaded, before init fires.
2. wp_get_current_user() constructs a WP_User, whose init() calls for_site().
3. for_site() sets $this->cap_key to get_blog_prefix( $site_id ) . 'capabilities' and calls get_role_caps().
4. get_role_caps() reads the capabilities row — get_caps_data() → get_user_meta( $this->ID, $this->cap_key, true ).
5. get_metadata_raw() finds no user_meta:{id} cache entry and calls update_meta_cache( 'user', array( $id ) ).
6. That runs the query above — every row for the user, all keys, no filter.
7. Every value gets unserialized into the object cache. Later per-key reads are in-process and cheap. The damage was done in step 6.

The WP_User class reference and the update_meta_cache() source are worth reading once, end to end; the priming behavior is load-bearing for this whole audit. Two consequences fall out of it:

The cost is the row set, not the table. A 2 GB wp_usermeta table is only a disk problem. The per-request problem is the current user’s slice: total bytes fetched, plus PHP unserialization of values you mostly never asked for. Multisite makes it worse in a non-obvious way — wp_2_capabilities through wp_40_capabilities all ride along in the same priming query, because the query filters on user_id, not on key prefix.

Screens that touch many users multiply the payload. update_meta_cache() accepts a list of IDs. An admin screen that pulls per-user plugin meta for the 20 users in a list primes all 20 full row sets in one go. If your users.php or author-heavy views are slow, this is usually why.

The options twin: {prefix}user_roles

Capabilities have a second home. wp_roles() hydrates the global WP_Roles instance from the {prefix}user_roles option, which is autoloaded by default — so role definitions are read on every request, including anonymous front-end ones. A membership plugin that registers six roles with per-post-type capability grants can quietly push that option past 100 KB. I’ve seen one at 170 KB; nobody on that team could name the plugin that wrote it. So “capability bloat” lives in two places, and an audit that only checks wp_usermeta is half an audit.

What capability bloat actually looks like

The database description in the WordPress documentation will tell you the schema: umeta_id, user_id, meta_key (VARCHAR 191, indexed), meta_value (LONGTEXT). It will not tell you what accumulates. This table will. Every row listed as “in the priming payload” is fetched and unserialized on every request that user makes.

One subtlety worth writing down: a dead role slug in wp_capabilities is not just noise. Core merges the caps array into allcaps after role caps, so a stale slug like s:12:"old_editor";b:1; becomes a “capability” named old_editor. Plugins that check current_user_can( 'old_editor' ) — a discouraged but surviving pattern — will pass. And if a new plugin ever registers a role with the same slug, every legacy holder regains it instantly. Stale slugs are a security question wearing a performance costume.

The audit: five queries and a few CLI commands

Run everything below read-only first. Replace wp_ with your actual prefix — wp db prefix if you don’t know it.

Step 0 — baseline and backup

wp db prefix
wp db size --tables
wp db export pre-usermeta-audit.sql

If you can run this on staging instead of production, do. The deletes come later; the census comes first.

Step 1 — key census

Which keys own the table, in bytes rather than row counts. A thousand 40-byte rows are irrelevant; forty 40 KB rows are your problem.

SELECT meta_key,
       COUNT(*) AS row_count,
       SUM(LENGTH(meta_value)) AS total_bytes,
       ROUND(AVG(LENGTH(meta_value))) AS avg_bytes
FROM wp_usermeta
GROUP BY meta_key
ORDER BY total_bytes DESC
LIMIT 25;

Read the output top-down and ask one question per key: does a current, active writer own this key? If nothing on the install claims it, it’s a candidate for removal — after Step 2 confirms who’s carrying it.

Step 2 — heaviest users

SELECT u.ID, u.user_login,
       COUNT(um.umeta_id) AS row_count,
       SUM(LENGTH(um.meta_value)) AS total_bytes
FROM wp_users u
JOIN wp_usermeta um ON um.user_id = u.ID
GROUP BY u.ID, u.user_login
ORDER BY total_bytes DESC
LIMIT 15;

If your slow-admin complaint has a name attached to it, this query usually finds that name at the top. The founding editor with 214 rows isn’t a coincidence; row count tracks account age almost perfectly, because screen-state keys and plugin arrays accrue and nothing retires them.

Step 3 — role census and dead slugs

SELECT meta_value, COUNT(*) AS user_count
FROM wp_usermeta
WHERE meta_key = 'wp_capabilities'
GROUP BY meta_value
ORDER BY user_count DESC;

Grouping on the serialized value works cleanly for single-role users and fragments for multi-role users, so treat it as a quick census, not gospel. Then list the roles that actually exist:

wp role list --format=csv
wp user meta get 1 wp_capabilities

Compare the slugs in the census against wp role list. Anything present in usermeta but absent from the roles option is a dead slug: residue from a removed plugin, a membership system you migrated off, or a role someone deleted via wp role delete without cleaning up the users. The prune script in the fixes section handles the comparison for you.

Step 4 — orphaned rows

SELECT COUNT(*) AS row_count,
       IFNULL(SUM(LENGTH(um.meta_value)), 0) AS total_bytes
FROM wp_usermeta um
LEFT JOIN wp_users u ON u.ID = um.user_id
WHERE u.ID IS NULL;

Zero is the correct answer. Anything else means someone deleted users with raw SQL or a broken importer, because wp_delete_user() cleans meta properly. Orphans don’t slow requests — nothing reads them — but they bloat the table, the backups, and any future migration, and they make this audit’s numbers lie.

Step 5 — the autoloaded roles option

SELECT option_name, LENGTH(option_value) AS total_bytes, autoload
FROM wp_options
WHERE option_name LIKE '%user_roles'
ORDER BY total_bytes DESC;

SELECT option_name, LENGTH(option_value) AS total_bytes
FROM wp_options
WHERE autoload = 'yes'
ORDER BY total_bytes DESC
LIMIT 20;

Anything over roughly 100 KB in an autoloaded option deserves scrutiny, and {prefix}user_roles is the one capability-adjacent value that taxes anonymous traffic too.

The fixes

Strip dead role slugs

remove_role() and wp role delete edit only the {prefix}user_roles option. The users keep the slug in their caps array, indefinitely. Strip it deliberately, with a dry run first:

<?php
/**
 * prune-dead-roles.php — strips role slugs from {prefix}capabilities
 * that no longer exist in {prefix}user_roles.
 * Usage: wp eval-file prune-dead-roles.php
 * DRY_RUN defaults to true. Flip to false only after reviewing the log.
 */
global $wpdb;

define( 'PRUNE_DRY_RUN', true );

$cap_key = $wpdb->get_blog_prefix() . 'capabilities';
$live    = array_keys( wp_roles()->get_names() );

// 1. Census every slug present in capabilities rows.
$seen = array();
$rows = $wpdb->get_col(
    $wpdb->prepare(
        "SELECT DISTINCT meta_value FROM {$wpdb->usermeta} WHERE meta_key = %s",
        $cap_key
    )
);
foreach ( $rows as $serialized ) {
    $caps = maybe_unserialize( $serialized );
    if ( ! is_array( $caps ) ) {
        continue;
    }
    foreach ( array_keys( $caps ) as $slug ) {
        $seen[ $slug ] = ( $seen[ $slug ] ?? 0 ) + 1;
    }
}

$dead = array_diff( array_keys( $seen ), $live );
if ( array() === $dead ) {
    WP_CLI::log( 'No dead role slugs. Nothing to do.' );
    return;
}
WP_CLI::log( 'Dead slugs: ' . implode( ', ', $dead ) );

// 2. Strip them per user, and only those keys.
foreach ( get_users( array( 'fields' => 'ID' ) ) as $user_id ) {
    $caps = get_user_meta( $user_id, $cap_key, true );
    if ( ! is_array( $caps ) ) {
        continue;
    }
    $pruned = array_diff_key( $caps, array_fill_keys( $dead, true ) );
    if ( $pruned === $caps ) {
        continue;
    }
    if ( PRUNE_DRY_RUN ) {
        WP_CLI::log( sprintf(
            'DRY user=%d would drop: %s',
            $user_id,
            implode( ', ', array_keys( array_diff_key( $caps, $pruned ) ) )
        ) );
    } else {
        update_user_meta( $user_id, $cap_key, $pruned );
        clean_user_cache( $user_id );
    }
}
WP_CLI::log( PRUNE_DRY_RUN ? 'Dry run complete. No rows written.' : 'Done.' );

The script removes only keys that are provably dead — slugs absent from the roles option — so legacy individual capabilities stored in the same array survive. On multisite, run it once per site with --url=, since the cap key is per-site.

Delete orphaned rows

After the backup from Step 0, and after confirming the count with Step 4:

DELETE um FROM wp_usermeta um
LEFT JOIN wp_users u ON u.ID = um.user_id
WHERE u.ID IS NULL;

This is the one query in the article that cannot break a live request, because no live user owns those rows. It can break your rollback, though, if you skipped the export. Don’t skip the export.

Compact session tokens and shorten cookie life

Nothing in core sweeps session_tokens on a schedule; the row gets rewritten when sessions are created or destroyed, not before. For heavy accounts:

wp eval 'WP_Session_Tokens::get_instance( 42 )->destroy_all();'

That logs user 42 out everywhere — tell them first. To slow re-accumulation, tighten the remembered-cookie window:

add_filter( 'auth_cookie_expiration', function ( $expires, $user_id, $remember ) {
    return $remember ? WEEK_IN_SECONDS : DAY_IN_SECONDS * 2;
}, 10, 3 );

Default is fourteen days remembered, two days not. The tradeoff is real: editors on shared machines will grumble about logging in weekly. That grumble is cheaper than a 60 KB session row riding every request.

Retire stale screen-state keys

DELETE FROM wp_usermeta WHERE meta_key LIKE 'closedpostboxes_%';
DELETE FROM wp_usermeta WHERE meta_key LIKE 'metaboxhidden_%';
DELETE FROM wp_usermeta WHERE meta_key LIKE 'manageedit-%columnshidden';

Yes, _ is a wildcard in LIKE; for these prefixes it happens to match exactly the keys you mean. The rows regenerate as users rearrange their screens — good news, nothing is lost permanently; bad news, your editors will re-collapse the same boxes and one of them will file a ticket about it. Run these deletes when the rows reference screens that no longer exist (post types you deleted, plugins you removed), not on a schedule.

On multisite, delete per-site capability pairs only for sites that no longer exist. Enumerate live IDs with wp site list --field=id, then remove the specific dead keys (wp_3_capabilities, wp_3_user_level, and so on) by name. Don’t pattern-match your way through this one.

What not to delete

• wp_user_level — legacy plugins and older themes still read it, and core rewrites it on the next role change anyway. Deleting it is pointless busywork.
• Live wp_capabilities values — the audit’s job is to slim these, not remove them.
• session_tokens for users mid-session, unless destroying their sessions is the point.
• Plugin keys whose writers are still installed. The Step 1 census tells you which keys are heavy; the plugins list tells you whether the writer still exists. Delete only when both answers line up.

Verification: measure it twice

Re-run the per-user metric for every account you touched:

wp db query "SELECT COUNT(*) AS row_count, SUM(LENGTH(meta_value)) AS total_bytes FROM wp_usermeta WHERE user_id = 1;"

Then compare Query Monitor captures on the same screen, same user, before and after. The client install from the opening section, for the record:

Before: priming query 214 rows / 1.3 MB / 0.0213 s
        total query time 0.31 s, page generation 0.62 s
After:  priming query  46 rows /  88 KB / 0.0018 s
        total query time 0.09 s, page generation 0.34 s

Numbers, not adjectives. Then verify capability behavior, because a cleanup that breaks logins is not a cleanup:

wp eval 'wp_set_current_user( 7 ); var_dump( current_user_can( "edit_posts" ) );'
wp cap list editor | head -3
wp eval 'var_dump( array_keys( get_userdata( 7 )->caps ) );'

Log in as one pruned user from each affected role and click through the admin. If anything regressed, the dry-run log from the prune script tells you exactly which keys were dropped from which accounts, and the export from Step 0 puts them back.

Keeping the table honest

Three habits keep the bloat from coming back:

• A deactivation checklist. When you remove a plugin that registered roles, run wp role delete <slug>, then run the prune script the same day. Residue never gets the chance to age into “mystery data.”
• A census after every uninstall. Re-run the Step 1 query and diff it against the last census. Plugin per-user arrays are the heaviest category in the table above, and uninstalls are when they turn into orphans.
• Know what a persistent object cache does and doesn’t do. Redis or Memcached removes the SQL round trip after the first prime, but the cached payload still crosses the process boundary and unserializes on every request — and any update_user_meta() invalidates the whole user_meta:{id} entry, so frequently written keys keep the hit rate poor. A cache hides the symptom. It does not shrink the row set.

If you’re building this discipline on a fresh install, the triage order in what to fix first when a new WordPress site says nothing found is the day-one companion — capability bloat is a three-year problem, and it’s cheaper never to accrue it than to audit it. This piece is the second entry in a series on table-level failure modes; the next one applies the same priming analysis to wp_postmeta, where the payload math gets considerably worse.

FAQ

Does a large wp_usermeta table slow the site for anonymous visitors?

No. Meta priming is per logged-in user; an anonymous request builds a WP_User with ID 0 and never queries wp_usermeta for it. The one exception is the {prefix}user_roles option, which is autoloaded and therefore read on every request, anonymous ones included. Table size alone is a disk and backup cost, not a per-request one — the current user’s row set is the per-request cost.

Is it safe to delete the wp_user_level meta rows?

No, and it’s pointless anyway. wp_user_level is maintained by core on every role change for backward compatibility, and legacy plugins and older themes still read it. Delete it and the next role update recreates it. Leave it alone; it’s one small row per user.

Will a persistent object cache fix capability bloat?

It masks the SQL but not the payload. With Redis or Memcached, the priming query runs once and the result is served from cache — but the full meta array still crosses the wire and gets unserialized on every request, and any usermeta write invalidates the entire cached entry for that user. Treat a cache as latency relief, then prune the row set anyway.

How much usermeta is too much for one user?

A working editor on a healthy install carries tens of rows and well under 100 KB. Hundreds of rows, or megabytes, means something is writing per-user data without a retirement plan — usually a plugin storing read-state or preference arrays. The number that matters for performance is the heaviest daily user’s total bytes, which Step 2 of the audit measures directly.

Why does removing a role leave data in wp_usermeta?

remove_role() — and its CLI wrapper wp role delete — edits only the {prefix}user_roles option. The users’ {prefix}capabilities rows keep the slug forever, because core has no cleanup path for role-to-user assignments. That residue is harmless until a role with the same slug gets registered again, at which point every legacy holder regains it. Hence the prune script.