Diagnosing slow imports

June 5, 2026

When an import takes longer than expected, the fix is almost always to identify WHICH stage got slow and treat that one — not to add workers blindly or upgrade the server. SSI emits enough telemetry to do this in minutes.

Where the timing lives

Two places:

1. The CLI summary at the end of every run

Every wp ssi <id> invocation prints a table like this at the very end:

--------------------------------------------------------------------------------
Super Speedy Imports (2.53) Import Summary
--------------------------------------------------------------------------------
Stage                          Metric                     Count   CPU Time   Duration
--------------------------------------------------------------------------------
load-csv                       Rows Processed           867,990  7604.125s  1098.28s
import-taxonomies              Terms Created                  0     0.000s     0.36s
import-taxonomies              Hierarchical Terms Prop.   3,293     0.135s     0.36s
match-existing                 Posts Matched            867,990   179.738s   228.09s
update-posts                   Posts Updated            867,990    72.256s    72.26s
insert-posts                   Posts Inserted                 0    29.589s    61.96s
update-postmeta                Postmeta Updated         <count>   523.894s   523.89s
upsert-relationships           Term Relationships     2,577,210     0.000s   326.45s
fix-attributes                 Attributes Processed     867,990  5061.829s   658.18s
--------------------------------------------------------------------------------
Total Duration (s)                                                          3562.20
  • Count — what the stage actually did (rows processed, terms created, posts matched, etc.).
  • CPU Time — total CPU time across all workers. On a parallel stage with 8 workers, this can be 8× Duration.
  • Duration — wall-clock time the stage took.

The stages that took the most Duration are where to focus. Look for outliers — one stage taking 10× the rest is usually the optimisable one.

2. The import history panel (admin UI)

Every run also writes a row to wp_ssi_import_history and a row per stage to wp_ssi_import_history_stages. The admin UI’s “Import History” panel renders this as a per-run breakdown with the same data, accessible weeks later. Useful for comparing two runs to see what regressed.

Per-stage diagnosis

Match the slow stage to a likely cause:

load-csv slow

At 868k rows you should expect 5-20 minutes depending on the box. Slower than that:

  • CSV is huge per row (lots of mapped columns, or one column with multi-MB text) — the per-row work scales linearly with row content. Check du -h on the CSV and divide by row count.
  • Disk I/O is slow (the CSV lives on a network mount, or the per-import staging tables are on slow storage). iostat -x 5 on the DB host during the stage will show if disk is saturated.
  • Worker count is wrong for the box. See Choosing parallel workers per stage.

match-existing slow

Usually depends on:

  • Postmeta size. match-existing joins wp_postmeta to find the unique identifier — at 10M postmeta rows the join can take a while.
  • Missing index on _sku (or your custom UID meta key). WordPress doesn’t index meta_value by default. If your import is bigger than ~100k rows AND you use a custom UID, you may want to add a partial index on wp_postmeta(meta_key, meta_value(50)). Standard _sku matches are usually fast because the WC PA lookup table also covers them.

upsert-relationships slow

This was historically the biggest pain point. 2.47 added covering indexes; 2.51 fixed a duplicate-row explosion. If you’re on 2.51+ and this stage is still slow:

  • Many taxonomies × many posts. At 868k posts × 8 taxonomies, the stage handles ~7M relationships. Even a fast SQL has to read and write all of them. 2-5 minutes is realistic.
  • Temp table spilling to disk. The DISTINCT temp tables can exceed tmp_table_size (default 16M). The opt-in enable_db_tuning Additional Option bumps this to 512M per session. Without it, the DISTINCT spills to disk and takes minutes instead of seconds. See Database tuning for the details.
  • assign_all_hierarchy_levels is ON (default for new imports since 2.55.9). With this option enabled, each hierarchical post is assigned a relationship for EVERY level of its category path (parent + leaf), not just the leaf. That legitimately multiplies the row count this stage writes — a deeper category tree means proportionally more “Term Relationships”. A higher number than your old leaf-only runs is expected behaviour, not a bug; if you genuinely only want leaf assignments, turn the option off (or it’s already off on pre-2.55.9 imports).

Note on import-taxonomies: if that stage is the slow one, adding workers won’t help — since 2.55.8 it’s forced to a single worker. Tune hierarchy_batch_size and enable_db_tuning instead.

update-postmeta or insert-postmeta slow

Two common causes:

  • Many mapped postmeta keys. Each key is a separate bulk INSERT/UPDATE; 30 mapped keys means 30 statements. Drop unused mappings.
  • InnoDB log file too small. Each big INSERT batch can exceed the redo-log fsync threshold. Default innodb_log_file_size is 96M-100M; bump to 512M-1G for big imports. Requires a MySQL restart.

upload-remote-images slow

Almost always upstream-bound:

  • Remote server is slow (high response time per image). Check with curl -w "@curl-format.txt" -o /dev/null -s <url> — anything > 1s/image at the network level is the dominant cost.
  • Remote server is rate-limiting. Reduce worker count for this specific stage (Performance Options → per-stage override). 8 workers hammering a rate-limited CDN is slower than 2 workers staying under the limit.
  • Image files are large. Multi-MB images at 100ms latency are 10x slower than KB-sized images at the same latency. Check the size distribution: for u in $(head -100 batch_image_urls.txt); do curl -sI "$u" | grep -i content-length; done.

fix-attributes slow

This stage rewrites _product_attributes postmeta and rebuilds the WC attribute lookup table. At 868k products with multiple attributes per product, expect 5-10 minutes. Not much to tune — it’s bottlenecked on WC’s own functions.

If you don’t care about WC attribute filtering (e.g. a catalog of simple products without variation attributes), you can skip this stage entirely via the Import mode preset or a one-off CLI stage list:

wp ssi 5 load-csv,import-taxonomies,match-existing,update-posts,insert-posts,update-postmeta,insert-postmeta,upsert-relationships

Comparing two runs to find regressions

If an import suddenly got slower (you upgraded WC, added a plugin, scaled your data), compare two recent runs:

SELECT
    s.stage_name,
    s.duration AS new_duration,
    (
        SELECT duration FROM wp_ssi_import_history_stages
        WHERE stage_name = s.stage_name
        AND history_id = <OLD-HISTORY-ID>
        LIMIT 1
    ) AS old_duration,
    ROUND(s.duration / NULLIF((
        SELECT duration FROM wp_ssi_import_history_stages
        WHERE stage_name = s.stage_name
        AND history_id = <OLD-HISTORY-ID>
        LIMIT 1
    ), 0), 2) AS ratio
FROM wp_ssi_import_history_stages s
WHERE s.history_id = <NEW-HISTORY-ID>
ORDER BY s.duration DESC;

Replace <OLD-HISTORY-ID> and <NEW-HISTORY-ID> with two wp_ssi_import_history.id values. The ratio column tells you which stages got slower or faster between the two runs.

Per-taxonomy progress (since 2.49)

The upsert-relationships stage emits per-taxonomy progress during the run AND persists those numbers to the history:

Processing 8 taxonomies for term relationships:
  product_brand (hierarchical) ... deleted: 0, created: 850,000 [45.23s]
  product_status (flat)        ... deleted: 0, created: 850,000 [3.45s]
  product_feature (hierarchical) ... skipped (no source mapped)

If one taxonomy is taking 90% of the total time, that’s your target — usually an index gap or a missing default term on a hierarchical taxonomy.

When the answer is “the data really is that big”

At 1M+ rows, some stages genuinely take hours. There’s no magic fix — the time scales with your data. The right response is:

  • Run imports off-hours (overnight).
  • Use the Import mode presets to skip stages you don’t need on each run.
  • Cache image URLs so you don’t re-download every time. SSI does this automatically — once an image is uploaded, a row is added to the wp_ssi_image_lookup table; subsequent imports referencing the same URL skip the download and reuse the existing attachment. Run wp ssi resync-image-lookup to backfill the lookup table from existing attachment guids (useful after manual media-library edits, or if you’ve uploaded images outside SSI).
  • Consider sharding the import: split the CSV by product type or category and import each shard as a separate SSI import.

What’s next

  • Choosing parallel workers per stage — the upstream lever for parallel stages.
  • Database tuning — for sequential stages bottlenecked on MySQL.
  • MySQL stopped mid-import — for the failure case when the DB couldn’t keep up at all.
×
▲
▼
1/1