This documentation is available to agents as Markdown at https://docs.synthesized.io/tdk/latest/user_guide/080_troubleshooting/performance.md. For the complete documentation index, see llms.txt.

Performance Troubleshooting

Most slow runs come from one of four places: the network between the worker and the databases, the databases themselves, memory, or the configuration. This page helps you find which, and what to change.

For sizing, the working directory and reducing the data a run processes, see Improving Performance.

Find the bottleneck

Open the run in Governor: it shows each stage and how long it took. Then compare with the signs below.

Signs Likely cause See

Reading and writing take most of the time, and the worker’s CPU is low

Network latency between the worker and the databases

Network

The databases' CPU or disk is busy during the run

Database load: missing indexes, slow filters, small database memory

Database

OutOfMemoryError, the container is killed, or long pauses

Not enough memory for the run

Memory

Transformation takes most of the time

Expensive transformers, such as scripts that run for every row

Transformers

Runs wait in the queue

Too few workers

More runs at once

Network

Every row crosses the network twice: from the input database to the worker, and from the worker to the output database.

  • Run the worker in the same network or cloud region as the databases. See Synthesized Workers.

  • Allow more connections to each database with TDK_DB_MAXIMUM-POOL-SIZE (default 20), if the database can take them. See Connection pool.

Database

  • Index the columns you filter on. Filters become WHERE clauses, so a filter on a column without an index reads the whole table.

  • Prefer filters to subsetting on very large tables: a filter such as "the last six months" reads less than a sample of the whole table. See Prefer filtering on large datasets.

  • Give the databases enough memory for the load, for example shared_buffers on PostgreSQL or innodb_buffer_pool_size on MySQL.

  • Check for locks from other work on the output database during the run.

Memory

Give the Java runtime more memory:

export JAVA_TOOL_OPTIONS="-Xmx8g"

JAVA_TOOL_OPTIONS passes options to the Java runtime of the CLI or a container. In Docker or Kubernetes, raise the container’s memory limit too, so the heap fits inside it.

To use less memory per run:

  • Reduce the insert batch size, the number of rows written per batch. The default is 1000:

    default_config:
  insert_batch_size: 500

  • Keep the default fetch type, streaming, which uses the least memory. If you set fetch.type: chunking with parallelism above 1, which only MySQL supports, lower parallelism:

default_config:
  fetch:
    type: chunking
    parallelism: 1

  • Use the working directory for subsetting runs, so the platform keeps working data on disk instead of in memory. See Enabling working directory.

Larger batches are faster but use more memory; smaller ones add overhead. Change insert_batch_size in steps, and compare runs.

Transformers

Built-in transformers are faster than scripts. A script runs for every row, so use one only when no built-in transformer does the job.

Faster, with a built-in generator:

transformations:
  - columns: ["email"]
    params:
      type: person_generator
      column_templates: ["${email}"]

Slower, with a script:

transformations:
  - columns: ["email"]
    params:
      type: scripting_transformer
      language: JAVASCRIPT
      script:
        code: |
          (ctx, originalRecord) => originalRecord.get("email").split("").reverse().join("")

Settings that the platform works out from the data, such as a column’s statistics or categories, also take time. Set them in the configuration to skip that step. See Optimising effective configuration.

More runs at once

Each worker runs workflows independently, so more workers run more workflows at once. See Multiple workers. In Docker Compose, use its overlay: scaling the worker service directly fails, because every replica binds host port 8083.

Two runs at the same time need different output databases.

Related pages