Rebuilding a large search index with limited memory

For a large catalogue, use WP-CLI so the rebuild can run outside a web request and you can keep its log. On Multisite, select the correct site with --url. If its suggestions table is present and current, wp sss rebuild all --normal is the lower-resource full rebuild: it rebuilds the main search and terms indexes, keeps the suggestions table, and records the current database version when the run completes.

Choose the rebuild mode

An unqualified wp sss rebuild all follows the Force Suggestions Rebuild setting. When that setting is on, the command also runs the heavier post-title, token and suggestions pipeline. Use --normal to keep a current suggestions table or --full when suggestions need to be rebuilt.

A successful all --normal rebuild creates the columns required by enabled settings and updates sss_db_version to the version expected by the installed plugin. For example, the built-in bonus-weight and score-multiplier settings add their corresponding columns when enabled. Check the actual settings and schema rather than assuming that every optional column belongs on every site.

Prepare the run

1. Take a recoverable database backup or server snapshot. The rebuild stages a new index beside the live one, swaps it into place, and then removes the short-lived old-table backup. The plugin does not offer a command to restore that old table after the swap.
2. Confirm that the suggestions table exists and record its row count. Check the stored SSS database version and any settings that affect indexed columns.
3. Check MemAvailable, swap use, and free space on both MariaDB’s data volume and its temporary directory. If the temporary directory is a small tmpfs, it consumes RAM-backed capacity.
4. Choose a low-traffic window and prevent scheduled rebuilds from overlapping. Use screen or tmux so a lost SSH connection does not end the job.

wp --url=https://example.com option get sss_db_version
wp --url=https://example.com db prefix
wp db query 'SELECT @@tmpdir'
free -h
df -h /var/lib/mysql /tmp

wp --url=https://example.com sss rebuild all --normal > sss-rebuild.log 2>&1
echo $?

Replace example.com with the site being rebuilt. The table prefix and MariaDB paths may differ from those in the example. Keep the log and exit code. After a successful run, check that the stored DB version matches the installed plugin’s expected version, that the main index has rows and the required columns, and that the suggestions count is unchanged. Run a representative search.

What a measured large rebuild used

In two tests, a synthetic WooCommerce Multisite catalogue produced a 130,010-row main index of about 251 MiB. The site had 112,000 suggestions, and the force-suggestions setting was on. Each run started with the same index condition and used all --normal. The 2-vCPU server had 3.7 GiB RAM and 4 GiB swap. Both runs completed with the expected schema and DB version; the suggestions count and checksum were unchanged.

The disk totals include other server activity. MariaDB created 15 and 18 on-disk SQL temporary tables. The test used synthetic data without a production traffic load; its repeat run reconstructed the starting condition rather than restoring a snapshot. The figures are examples, not capacity requirements.

What happened to page response time?

I measured three full pages before, during and after a further all --normal rebuild: the homepage, a product page and a product search results page. Requests went directly to the server’s local HTTPS address with a unique query marker and Cache-Control: no-cache, bypassing the public network and any CDN. Time to first byte (TTFB) is a useful proxy for server page generation, but it also includes local HTTP and TLS work. It does not include browser rendering.

The test sent the three requests one at a time. Each page had 10 samples before and after the rebuild, and 22-23 samples wholly inside it. Every request returned HTTP 200. The table shows median and 90th-percentile TTFB in seconds.

The slower end of the response-time range rose on all three pages, even where the median barely moved. That rebuild took 89 seconds and averaged 98% CPU busy across the server’s two vCPUs while the page requests ran. The slightly lower after-test timings may reflect warm caches or ordinary variation; the rebuild itself is not a page-speed improvement.

These figures describe one request at a time against a synthetic catalogue. They do not predict the effect of many simultaneous visitors, which can compete for PHP workers, database connections, CPU and disk.

RAM, traffic and temporary disk

There is no reliable universal minimum of available RAM for a given row count. In this test, MariaDB memory and disk work dominated PHP memory. The rebuild also created SQL temporary tables and FULLTEXT indexes while the live index remained available.

Live traffic adds PHP workers, database queries and memory pressure. It can lower MemAvailable and slow the rebuild through CPU and disk contention. The test did not measure concurrent production traffic, so its memory low point is not a safe threshold for a busy server. If a site normally has only about half a GiB available, first free headroom and choose a quiet window. Temporarily increasing RAM for the rebuild is sensible when that still leaves little headroom; it need not be a permanent hosting change.

Allow space for the live and staged index together, SQL temporary data and index creation. Check MariaDB’s tmpdir separately from the data volume. Free space elsewhere on the server will not help if a small temporary mount fills.

Reducing work and handling old tables

The practical lower-resource controls are choosing --normal when suggestions are current, avoiding overlapping builds, and running during a quiet window. The CLI has no batch-size switch or separate schema-only path for the main index. Its staging and table promotion happen within the same database.

Let the rebuild manager clean up tables it recognises from abandoned generations. Do not manually drop a table just because its name contains stage, temp or old; confirm its ownership and contents first.

If a rebuild stops

Read the last completed step and the reported error. Before the main-table swap, the old live index remains in place. After the swap, the new index may be live even if a later step fails, and the old-table backup is removed shortly afterwards. wp sss rebuild cancel acts on an active generation; it cannot undo a completed swap.

Check whether a generation is active, whether the live index exists and has rows, whether the required columns are present, the suggestions count, and sss_db_version. Fix the reported cause and rerun the intended command if the database is healthy. Restore the pre-run backup if you need to return to the pre-run state. Avoid deleting build tables by guesswork.

Further reading

Related products

Super Speedy SearchSpeeds up front-end and wp-admin search

Written for Super Speedy Search 5.78.0.