ch-workload

ch-workload creates repeatable mixed read/write load on ClickHouse clusters through the official Python client, clickhouse-connect. It reuses the declarative profile, editable project, desired-state scheduler, resource guard, and rotating-log model from pg-workload; no clickhouse-client, psql, or pgbench subprocess is used for database operations.

The first bundled profile targets the eight-server stand created by ch_stand as 4 shards × 2 replicas.

The PyPI distribution and command are named ch-workload; the Python import package is ch_workload. The wheel contains the immutable profile template and public ch_workload/v1 JSON Schema. Initialized profiles, generated state, logs, and database rows are runtime data and are not included in distributions.

What the first profile exercises

imdb_cluster is a synthetic movie analytics workload. It creates:

Three insert jobs continuously write to different distributed tables. Four query workers choose from 38 weighted SQL scenarios and generate typed parameter values for country, genre, studio, event type, time window, year, threshold, and result limit. The catalog, event, rating, and daily metric tables are sharded by title_id, user_id, title_id, and (country, genre) respectively, while their physical *_local tables replicate within each shard. Queries fan out through the Distributed tables and cover shard-aware scans, joins, funnels, quantiles, window functions, and cross-source aggregations.

This utility generates workload for development, diagnostics, and training. It does not calculate or publish benchmark scores.

Installation

Install from PyPI

python3 -m venv .venv
.venv/bin/pip install ch-workload
.venv/bin/ch-workload --version

Install from source

git clone https://github.com/O2eg/ch_workload.git
cd ch_workload
python3 -m venv .venv
.venv/bin/pip install -e '.[dev]'
.venv/bin/ch-workload --version

Start the matching ch-stand cluster

From an initialized ch_stand project:

ch-stand -c configs/sharded-replicated-8.yaml up
ch-stand -c configs/sharded-replicated-8.yaml cluster status
ch-stand -c configs/sharded-replicated-8.yaml connection

The bundled topology exposes the first node at 127.0.0.1:18160 over HTTP and defines the cluster as ch_stand_4s2r. ch-workload defaults match those values.

ch_stand stores its generated ClickHouse credentials in .ch_stand/credentials/clickhouse.json. Pass that file without copying the password into shell history:

export CH_WORKLOAD_PASSWORD_FILE=/absolute/path/to/ch_stand-project/.ch_stand/credentials/clickhouse.json

Alternatively, set CH_WORKLOAD_PASSWORD or CLICKHOUSE_PASSWORD.

Initialize, validate, and install

ch-workload init --directory local-workload
ch-workload validate --root local-workload
ch-workload doctor --root local-workload

ch-workload install \
  --root local-workload \
  --profile imdb_cluster \
  --recreate

--recreate drops only the configured workload database (ch_workload by default) on the selected cluster. Omit it to keep existing tables and append another deterministic seed set.

At scale 1.0, installation creates approximately 5,000 catalog rows, 100,000 viewer events, 30,000 rating updates, and 5,760 daily metric rows. For a smoke test:

ch-workload install --root local-workload --profile imdb_cluster --recreate --scale 0.01

Run mixed load

All four jobs start concurrently:

ch-workload run --root local-workload --profile imdb_cluster --duration 60

Run only analytics or bound a deterministic number of operations:

ch-workload run \
  --root local-workload \
  --profile imdb_cluster \
  --job analytics \
  --operations 100

Each job creates one independent clickhouse-connect session per worker. Insert jobs use typed column batches with insert_distributed_sync=1; query jobs send ClickHouse server-side parameters such as {days:UInt16} and {country:String}. A JSON summary reports operations, rows, throughput, and p50/p95 latency. Per-job logs are written under data/imdb_cluster/log/.

Useful global overrides include --host, --port, --user, --database, --cluster, --workers, --batch-size, --scale, and --seed.

Desired-state scheduler

The scheduler model is compatible with the workflow used by pg-workload:

ch-workload enable imdb_cluster --root local-workload --interval 60
ch-workload state --root local-workload
ch-workload start --root local-workload --run-immediately
ch-workload status --root local-workload
ch-workload stop --root local-workload

Individual jobs can be controlled with --job. Scheduler children inherit credentials through the environment, never through process arguments.

ch_play machine interface

ch_play invokes this utility through the hidden ch_play/component/v1 interface. Component capabilities and a deterministic workload plan can be inspected without connecting to ClickHouse:

ch-workload --machine --request-id probe --component-capabilities
ch-workload --machine --request-id plan-1 plan \
  --operation run \
  --root local-workload \
  --profile imdb_cluster \
  --duration 60

The returned plan_hash binds the resolved connection metadata, selected profile files and jobs, scale, seed, duration, workers, and resource guard. install and run accept --plan-hash and refuse execution if the current deterministic plan differs. The password value is accepted only through an environment variable or --password-file; it is not included in the plan or machine response.

Machine output is one JSON object with the exact envelope fields contract_version, component, component_version, command, request_id, status, result, artifacts, warnings, and error. These flags are intentionally absent from the primary human help.

Profile contract

Profiles live under data/<name>/profile.yml after initialization and use api_version: ch_workload/v1. A profile declares the required cluster topology, schema file, Python row-generator module, and insert/query jobs. Query SQL remains separate and editable; value parameters are generated from choice or bounded int declarations.

The public schema is copied to schema/ch_workload-v1.schema.json. ch-workload validate also checks unknown fields, identifiers, local paths, job contracts, parameter generators, and duplicate names.

Development and release checks

python -m pip install -e '.[dev]'
python -m ruff format --check .
python -m ruff check .
python -m pytest -q
python -m build
python -m twine check dist/*

The real 4 shards × 2 replicas integration test is opt-in locally. With the matching ch_stand cluster running:

CH_WORKLOAD_INTEGRATION=1 \
CH_WORKLOAD_PASSWORD_FILE=/absolute/path/to/ch_stand-project/.ch_stand/credentials/clickhouse.json \
python -m pytest -q tests/test_ch_stand_integration.py

CI verifies Python 3.10 and 3.12, Ruff, unit tests, the pinned ch_stand integration, sdist/wheel metadata, and an installed-wheel smoke test covering --version, init, profiles, and validate. A v<version> tag starts the release workflow, which rejects a tag that differs from pyproject.toml and publishes the already-tested artifacts through PyPI Trusted Publishing. No PyPI API token is stored in the repository.