Performance

Diagnosing slow imports

Published · Last updated

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.

The time shown against each stage in the browser’s Running Import dialog is the same recorded stage time, so the dialog, the completion screen and History agree. Stage times add up to less than the elapsed clock, because the time between requests belongs to no stage.

On a slow server a browser load-csv request returns after 20 seconds with the rows it has loaded, and History records Segment returned early: 20s time budget reached ... against the stage. Many of those lines mean the server is taking far longer per row than usual, which is worth investigating in its own right.

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

Common causes:

  • 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 (the default for new imports). 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.

Note on import-taxonomies: if that stage is the slow one, adding workers won’t help — it always runs with a single worker. Tune hierarchy_batch_size and enable_db_tuning instead. If the import brings in term images (paid edition), check the stage’s Term Images line first: it counts the term image URLs resolved and carries the time spent downloading them, which is often most of the stage. Term Descriptions and Term Meta Rows carry only the time to write those values.

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

post-import-jobs slow

Each After Import task is recorded as its own line, so the slow one is named. For the WooCommerce product lookup tables, Rebuild it during the import (bulk SQL, scoped to this import) is the default for new imports and is the faster choice on a large catalogue; Let WooCommerce rebuild it runs WooCommerce’s own whole-catalogue rebuild. When WooCommerce’s rebuild runs, Super Speedy Imports first refreshes the database’s table statistics (Product lookup: refresh optimiser statistics), so the rebuild is not planned from statistics left stale by the import’s bulk writes.

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

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.
  • Fixing Slow WooCommerce Imports — the hub guide, including the fixes that work whichever importer you use.

Written for Super Speedy Imports 3.40.0.

Comments

▲
▼
1/1