Skip to content

Performance tuning

Performance on a real ETL is multifactorial — disk speed, database speed, CPU count, CPU speed, and the engine's own code quality (a moving target — we ship optimisations regularly) all contribute to how long a run takes. There is no single setting that "makes the engine fast"; the discipline is finding the current bottleneck and removing it, then repeating against the next one.

Where to look

The bottleneck is usually one of these. The order below roughly matches "easiest to verify first":

CPU on the engine host

Run top or htop while the engine is running. Two patterns to watch for:

  • All cores at 100% — the engine is CPU-bound. Tuning won't help much; faster CPUs or splitting the workload across runs is the next move.
  • Only a few cores busy with workers idle — the engine isn't fanning out. Check worker threads in tool configuration, and verify the source-row queue isn't starving (a slow source reader can leave workers waiting).

Destination database

Watch the database while a run is in flight — pg_stat_activity on PostgreSQL, SHOW PROCESSLIST on MariaDB, the equivalent on SQL Server. Common patterns:

  • High lock waits / blocked queries — the database can't keep up with parallel writers. Lower worker threads, or lower max database connections in cdm database, or both.
  • Slow individual inserts — the destination has too many indexes or constraints active during the load. Drop non-essential indexes, load, rebuild — that is often a 10× speed-up on its own.
  • WAL/redo log pressure on PostgreSQL — synchronous_commit = off for the duration of the load helps significantly. Revert afterwards.
  • Buffer-pool too small on MariaDB — innodb_buffer_pool_size set to 60-70% of available RAM is the standard tuning.

Disk

Disk I/O matters in two places:

  • Source reads when the source driver is csv and the files are on a slow disk or network mount.
  • Destination writes when the database's data directory is on the same slow disk.

iostat -x 1 shows whether disks are saturated. Move data to local NVMe if you can; failing that, the parallel-writer tuning above usually masks slow disks well enough.

CPU speed

If the run is CPU-bound and adding workers doesn't help (you've already saturated cores), single-thread performance becomes the ceiling. The engine processes per-row rule chains entirely on one worker thread per row — a faster CPU per core helps directly. If you can choose, machines optimised for single-thread performance beat machines with many slow cores.

Code quality

Engine performance is a moving target. We ship optimisations regularly, so a slow workload on one version may be much faster on the next without any configuration change. If a workload feels slower than it should, ask — we may already have an improvement in flight, or the workload itself may be exposing a hot spot worth fixing.

Configuration knobs

These are the engine-side levers, in roughly the order to reach for them:

worker threads

The single most impactful knob. Set in tool configuration.

tool configuration {
  worker threads: 10
}

Each worker pulls source rows and runs the rule chain independently. More workers = more parallelism, until CPU, the destination database, or max database connections becomes the limit.

max database connections

cdm database {
  max database connections: 4
}

Caps how many connections the engine opens to the destination. Match it to what your database can comfortably handle without piling up lock waits.

vocabulary cache size

cdm database {
  vocabulary cache size: 5000
}

Memory for the OMOP-vocabulary lookup cache. Helps when many rule chains hit the OMOP vocabulary (AssertVocabDomain, VocabMap) and the same concept ids are looked up repeatedly. Memory grows linearly with the setting.

use dedup cache

tool configuration {
  use dedup cache: true
}

Caches deduplication results across rows. Helpful when many source rows resolve to the same Normalize key. Disabled by default because the memory cost isn't worth it on workloads with low duplication.

Splitting the load

For very large ETLs, splitting the mapping across multiple Rabbit-in-a-Hat files and several engine runs is often the cleanest answer: each run is shorter, individually re-runnable, and easier to profile. The StoreIndexMap and StoreSequence rules persist state between runs — see Folder Layout → Splitting an ETL.

When to ask for help

If you've worked through the checks above and the bottleneck still isn't obvious — or it is obvious and the answer needs an engine-side fix — get in touch. Performance work on real customer data is one of the things a Paid license buys you.