Database tuning for Super Speedy Imports

June 5, 2026

Importing 100k+ rows hits MySQL / MariaDB differently than serving web traffic. The same buffers that need to be small-per-connection for 200 concurrent web requests need to be large for one big sequential JOIN + DISTINCT. SSI splits the problem into two halves:

  1. Per-connection buffers that ONLY help the import worker — applied at SET SESSION scope so concurrent web connections never see the bump. These are opt-in per import via a checkbox.
  2. Server-wide settings that can’t be set per-connection — left to the admin to configure in my.cnf / 50-server.cnf. This doc gives a recommended baseline for a host that serves both web traffic and runs SSI imports.

The opt-in lives in Additional Options → Optimise DB session settings for slow stages.

Part 1 — Session-scoped settings SSI applies

Set only when enable_db_tuning is checked, only for stages that register tuning, and only for the lifetime of the import worker’s MySQL connection. Zero impact on any other connection.

Currently registered for: upsert-relationships. Other heavy stages can be added via the ssi_session_tuning_for_stage filter (see Part 3).

Setting MariaDB / MySQL default SSI sets to Why
tmp_table_size 16M 512M The per-taxonomy INSERT … SELECT DISTINCT JOIN materialises ~(post_id, term_taxonomy_id) pairs at ~16 bytes each. At 500k+ batch rows that’s ~10MB — right at the spill-to-disk threshold. Disk spill turns a seconds-long in-RAM op into a minutes-long I/O-bound one.
max_heap_table_size 16M 512M MySQL takes MIN(tmp_table_size, max_heap_table_size) for in-memory temp tables. Both must be raised together.
sort_buffer_size 2M (MariaDB) / 256K (MySQL) 8M DISTINCT and GROUP BY use this when sorting.
join_buffer_size 256K 32M For BNL (Block Nested Loop) fallback when an index can’t fully drive the join. With the 2.47 covering indexes this is rarely the limiting factor, but the buffer is allocated per-join so keeping it generous protects worst-case plans.
read_buffer_size 128K 4M Sequential reads from the batch table during the join.
read_rnd_buffer_size 256K 16M Index-driven random reads back into the post/term tables.

Peak RAM cost: ~600MB on the single import worker connection during the tuned stage. Released when the stage completes its queries (the SESSION variable persists for the connection’s lifetime, but the buffers themselves are allocated per-query on-demand).

Why this is opt-in and not “always on”

Some users run SSI on shared hosting with 1-2GB total RAM. A 600MB-per-connection bump is fine for one worker but disastrous if accidentally applied globally to a 100-connection web pool. Opt-in default-OFF protects those users; the recommendation is turn it ON for any import of 100k+ rows on a host with 4GB+ RAM.

Why it’s safe even with the checkbox ON

SET SESSION only affects the connection that issued it. SSI imports run in their own PHP processes (CLI workers via proc_open, or one PHP-FPM process for AJAX-driven imports), each holding their own MySQL connection. Web traffic, cron jobs, other plugins, your own DB clients — none of them see the bumped values.

The session variables ALSO persist only for that connection’s lifetime. When the import process exits, the connection closes and every byte the buffers held is released back to the OS.

Part 2 — Server-wide settings (admin configures in my.cnf)

These cannot be set at session scope. They affect every connection — but unlike the SSI session bumps, they’re either one-time allocations (buffer pool) or per-query costs that scale with the workload, not 100-fold worst-case multipliers. Pick a baseline below that matches your server class and put it in your config.

Where to put these: typically /etc/mysql/mariadb.conf.d/50-server.cnf (MariaDB) or /etc/mysql/mysql.conf.d/mysqld.cnf (MySQL) under [mysqld]. Restart the daemon to apply (most of these can also be set at runtime via SET GLOBAL ... but won’t survive a restart unless also written to config).

Check your current values:

SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'innodb_buffer_pool_size',
  'innodb_buffer_pool_instances',
  'innodb_log_file_size',
  'innodb_log_buffer_size',
  'innodb_flush_log_at_trx_commit',
  'innodb_flush_method',
  'innodb_io_capacity',
  'innodb_io_capacity_max',
  'innodb_autoinc_lock_mode',
  'max_connections',
  'thread_cache_size',
  'table_open_cache',
  'table_definition_cache',
  'max_allowed_packet',
  'query_cache_type',
  'query_cache_size'
);

InnoDB core

Setting Default Recommended (web + import mix) Notes
innodb_buffer_pool_size 128M 50–70% of total RAM on dedicated DB host. On a single-box LAMP setup (Apache + PHP-FPM + MariaDB), 25–40% of RAM — leave room for PHP workers and the OS file cache. e.g. 16GB box → 6G buffer pool. The single biggest tunable. Holds InnoDB data + indexes in RAM. If your dataset fits, queries effectively become RAM-speed. SSI’s upsert-relationships benefits hugely when the entire wp_term_relationships, wp_term_taxonomy, and the per-import batch + hierarchies tables all fit.
innodb_buffer_pool_instances 8 (MariaDB 10.5+) / 1 (older) 8 when pool ≥ 1G; 1 when pool < 1G More instances = less contention on the buffer pool mutex under concurrent traffic.
innodb_log_file_size 96M (MariaDB 10.5+) / smaller older 512M–1G The redo log. Larger files = fewer fsyncs during heavy writes (SSI inserts/updates). Costs disk space and slightly longer crash recovery. For an import-heavy server, the speedup is worth it.
innodb_log_buffer_size 16M 64M–128M Buffers redo records in memory before flushing. Larger = fewer flushes during multi-MB transactions, which SSI generates constantly.
innodb_flush_log_at_trx_commit 1 (full ACID — fsync at every commit) 1 for normal operation; 2 during import-only periods if you want max speed At 1, every commit fsyncs to disk — durable but slow. At 2, the OS buffers the log writes and only loses ~1 second of writes on a crash. SSI’s parallel workers commit constantly; flipping to 2 can 2-3× import throughput. Set as SET GLOBAL before the import, set back after. Don’t leave at 2 permanently on a production WordPress site.
innodb_flush_method fsync (Linux) O_DIRECT on SSD/NVMe Bypasses the OS page cache for InnoDB data files (the buffer pool already caches at the DB level — double-caching wastes RAM). No-op on HDD; significant memory saving on SSD.
innodb_io_capacity 200 SSD: 1000–2000. NVMe: 5000–10000 The default assumes spinning rust. SSDs can do 10× more IOPS; tell InnoDB so its background flushing keeps up with write bursts.
innodb_io_capacity_max 2000 SSD: 4000. NVMe: 20000 Burst capacity ceiling. Set 2× of innodb_io_capacity.
innodb_autoinc_lock_mode 2 in MySQL 8 / MariaDB 10.3+ 2 “Interleaved” — lets multiple parallel INSERTs allocate auto-increment IDs without serialising. Critical for SSI’s 8 parallel load-csv workers all inserting into the batch table concurrently. If your version still shows 1, your parallel inserts are bottlenecked by a single table-level auto-inc lock. Verify with SHOW GLOBAL VARIABLES LIKE 'innodb_autoinc_lock_mode';.
innodb_file_per_table ON (MariaDB 10+ / MySQL 5.6+) ON One .ibd file per table — lets you reclaim space when a table is dropped. SSI creates per-import tables and drops them; with this OFF the shared tablespace grows forever.

Connections

Setting Default Recommended Notes
max_connections 151 200–500 SSI imports use up to 9 connections (1 main + 8 workers by default) on top of your normal web traffic. Bump enough that a running import never starves web connections.
thread_cache_size 0 (MySQL) / 200 (MariaDB 10.5+) max_connections / 4 Caches threads instead of creating + destroying them per connection. Reduces connection-setup latency on the WordPress side.
table_open_cache 2000 4000–8000 Each open table costs a file descriptor + memory. WP installs with many plugins (postmeta variants, custom tables, multisite) easily exceed 2000 distinct tables × connections × 2 (per-connection cache).
table_definition_cache 400 2000–4000 Same as above for table definitions (.frm parsed metadata).

Per-statement limits

Setting Default Recommended Notes
max_allowed_packet 4M (MariaDB) / 64M (MySQL 8) 64M–256M Caps a single SQL statement’s wire size. SSI’s bulk INSERTs and large serialized postmeta blobs hit this. If you see “Got a packet bigger than ‘max_allowed_packet’ bytes” during an import, this is why.

Query cache (legacy)

Setting Default Recommended Notes
query_cache_type OFF (MariaDB 10.3+) OFF The query cache serialises invalidation on every write. On a mixed workload — WP frontend reads + import writes hammering the same tables — invalidation contention can cost more than the cache saves. Removed entirely from MySQL 8.
query_cache_size 0 (often) / 1M 0 If query_cache_type = OFF, set this to 0 to free the memory.

Part 3 — Overriding / extending SSI’s defaults

Apply tuning to other stages

add_filter('ssi_session_tuning_for_stage', function($tuning, $stage_name, $template) {
    if ($stage_name === 'import-taxonomies') {
        $tuning['tmp_table_size']      = 268435456; // 256M
        $tuning['max_heap_table_size'] = 268435456;
        $tuning['sort_buffer_size']    =   4194304; // 4M
    }
    return $tuning;
}, 20, 3); // priority 20 > SSI's default 10, so this runs after

Change SSI’s per-stage values

Same filter, higher priority — array_merge semantics mean your keys overwrite the bundled defaults:

add_filter('ssi_session_tuning_for_stage', function($tuning, $stage_name, $template) {
    if ($stage_name === 'upsert-relationships') {
        $tuning['tmp_table_size']      = 1073741824; // 1G instead of 512M
        $tuning['max_heap_table_size'] = 1073741824;
    }
    return $tuning;
}, 20, 3);

Turn tuning off without unchecking the box

add_filter('ssi_session_tuning_for_stage', '__return_empty_array', 999, 3);

Inspect what SSI is applying

When tuning runs, the CLI prints a line like:

  DB session tuning applied: tmp_table_size=512.0M, max_heap_table_size=512.0M, sort_buffer_size=8.0M, join_buffer_size=32.0M, read_buffer_size=4.0M, read_rnd_buffer_size=16.0M

If you don’t see this, either (a) the checkbox is off, or (b) the stage doesn’t have any registered tuning.

In --show-sql mode, the SET SESSION statements are printed but not applied, so you can preview without committing.

Part 4 — Per-size reference configs

Three tiers covering the range of host sizes SSI is realistically run on. Each one is complete — paste the [mysqld] block into your config, set the SSI-side options below it, restart MariaDB. Each tier accounts for everything competing for RAM during an import: the SSI PHP workers (~150-300MB each), the orchestrator PHP process, GridPane / cPanel / Plesk agents, the web stack, the OS itself.

The RAM budget below each tier shows where it goes — if your server runs unusually heavy / light workloads alongside, slide the buffer-pool size up or down accordingly.

4GB host — small / hobbyist / GridPane “Bronze” sized box

This tier is the floor. Imports work but you’re trading wall-clock time for memory headroom. Anything smaller than 4GB will swap-thrash. For imports >500k rows, an 8GB box pays for itself in the first run.

[mysqld]
# InnoDB — sized for the available RAM, not the workload
innodb_buffer_pool_size         = 768M
innodb_buffer_pool_instances    = 1     # 1 instance is correct below 1GB pool
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 32M
innodb_flush_log_at_trx_commit  = 1     # set to 2 ONLY during import; revert to 1 after
innodb_flush_method             = O_DIRECT
innodb_io_capacity              = 2000
innodb_io_capacity_max          = 4000
innodb_autoinc_lock_mode        = 2
innodb_file_per_table           = ON

# Connections — small box doesn't need many
max_connections                 = 50
thread_cache_size               = 16
table_open_cache                = 2000
table_definition_cache          = 1000

# Per-statement
max_allowed_packet              = 128M

# Query cache off
query_cache_type                = OFF
query_cache_size                = 0

SSI-side: – Performance Options → Workers per stage: 2 (NOT 8) – Additional Options → “Optimise DB session settings for slow stages”: OFF Why off: the per-connection buffers SSI bumps add ~600MB during upsert-relationships. That plus the 768M buffer pool won’t fit alongside the PHP workers on a 4GB box.

RAM budget (during peak import): – MariaDB (buffer pool + connection overhead + daemon): ~1.0GB – 2 SSI PHP workers × ~200MB: 400MB – Orchestrator PHP + WP-CLI: 200MB – Web stack (nginx + PHP-FPM): 400MB – OS + GridPane agents + cron: 500MB – Total: ~2.5GB used, ~1.5GB headroom for the OS file cache

Will hit GridPane MEM_RESTART threshold? No — MariaDB stays under 1.2GB so the 1953MB GridPane trigger is safe.

8GB host — sweet spot for serious imports

The point where everything fits comfortably. Recommended size for production imports of 100k–1M rows.

[mysqld]
# InnoDB
innodb_buffer_pool_size         = 2G
innodb_buffer_pool_instances    = 4
innodb_log_file_size            = 512M
innodb_log_buffer_size          = 64M
innodb_flush_log_at_trx_commit  = 1
innodb_flush_method             = O_DIRECT
innodb_io_capacity              = 2000
innodb_io_capacity_max          = 4000
innodb_autoinc_lock_mode        = 2
innodb_file_per_table           = ON

# Connections
max_connections                 = 100
thread_cache_size               = 32
table_open_cache                = 3000
table_definition_cache          = 1500

# Per-statement
max_allowed_packet              = 128M

# Query cache off
query_cache_type                = OFF
query_cache_size                = 0

SSI-side: – Performance Options → Workers per stage: 4 – Additional Options → “Optimise DB session settings for slow stages”: ON The 600MB session-tuning bump fits — 2GB buffer pool + 600MB during the tuned stage = 2.6GB MariaDB peak, leaves plenty of room for PHP workers.

RAM budget (during peak import): – MariaDB (buffer pool + session-tuning bump + overhead): ~2.8GB – 4 SSI PHP workers × ~200MB: 800MB – Orchestrator PHP: 200MB – Web stack: 600MB – OS + agents: 600MB – Total: ~5.0GB used, ~3.0GB headroom for OS file cache (which speeds reads further)

Will hit GridPane MEM_RESTART threshold? Yes — MariaDB will exceed 1953MB during upsert-relationships. Either raise the GridPane threshold to 4GB+ or disable the MEM_RESTART monitor on this server. See the GridPane section below.

16GB host — production-grade import server

For imports >1M rows or where wall-clock matters. The original tier the KB was sized for.

[mysqld]
# InnoDB
innodb_buffer_pool_size         = 6G
innodb_buffer_pool_instances    = 8
innodb_log_file_size            = 1G
innodb_log_buffer_size          = 128M
innodb_flush_log_at_trx_commit  = 1
innodb_flush_method             = O_DIRECT
innodb_io_capacity              = 2000
innodb_io_capacity_max          = 4000
innodb_autoinc_lock_mode        = 2
innodb_file_per_table           = ON

# Connections
max_connections                 = 300
thread_cache_size               = 75
table_open_cache                = 4000
table_definition_cache          = 2000

# Per-statement
max_allowed_packet              = 128M

# Query cache off
query_cache_type                = OFF
query_cache_size                = 0

SSI-side: – Performance Options → Workers per stage: 8 – Additional Options → “Optimise DB session settings for slow stages”: ON

RAM budget (during peak import): – MariaDB (buffer pool + session-tuning + overhead): ~7.0GB – 8 SSI PHP workers × ~250MB: 2.0GB – Orchestrator PHP: 300MB – Web stack: 1.0GB – OS + agents: 700MB – Total: ~11GB used, ~5GB headroom for OS file cache (massive speedup for repeat reads)

Will hit GridPane MEM_RESTART threshold? Definitely — buffer pool alone exceeds it. Disable the monitor or raise the threshold to 8GB+. See the GridPane section below.

Sizing rule of thumb

If you don’t fit cleanly into one of the three tiers above, use this allocation:

Component Share of total RAM
MariaDB buffer pool ~50% (single-purpose import server) or ~30% (mixed web + import)
MariaDB connection / log buffers reserve another ~10%
SSI PHP workers (~250MB each, planned worker count) calculate explicitly
Web stack + OS + agents reserve 1.0–1.5GB minimum
OS file cache headroom ≥20% of total RAM unused — InnoDB benefits from this even with O_DIRECT

If your calculated “MariaDB-able” RAM is less than 512MB, you’re on the wrong server class — get more RAM. SSI’s smallest aux-table CREATE alone needs ~200MB to be comfortable during a 100k-row import.

GridPane-specific note

GridPane bundles a MySQL memory monitor (/usr/local/bin/gpworker) with hardcoded thresholds:

  • MEM_HIGH warning: MySQL using ≥1757MB for 20 minutes
  • MEM_RESTART action: MySQL using ≥1953MB for 40 minutes → systemctl restart mariadb (kills any running import)

These thresholds are tuned for cookie-cutter WordPress hosts where MySQL shouldn’t grow large. They’re incompatible with the 8GB and 16GB tiers above — the recommended innodb_buffer_pool_size alone exceeds the threshold.

For an import server, either:

  1. Disable the MySQL memory monitor in GridPane’s UI / CLI (preferred — survives gpupdate).
  2. Raise the thresholds in /usr/local/bin/gpworker — works but each gpupdate may overwrite it (the cron self-update runs every ~15-30 mins).
  3. Pause the gpworker cron entry for the duration of the import: bash sudo crontab -l | sed 's|^.*gpworker.*$|# &|' | sudo crontab - # … run import … sudo crontab -e # uncomment the gpworker lines when done

Without one of these, the GridPane monitor will kill MariaDB mid-import on any tier above 4GB.

Same applies to similar memory-watchdog features in cPanel / Plesk / RunCloud — check your control panel for “MySQL auto-restart on high memory” toggles before launching a big import.

×
▲
▼
1/1