labs/apm-workflows

Related notes (Korean): HyperDX / ClickStack 소개

A team moving off an in-house or commercial APM does not judge the replacement by its feature list. It judges it by whether the workflows it runs every day still work: finding the SQL behind a slow transaction, telling a bad deploy from a good one, seeing the top errors as issues, getting an incident write-up, handing evidence to a coding agent. This lab shows each of them on Managed ClickStack in ClickHouse Cloud, and checks each one by SQL with a positive and a negative control.

Status (2026-10-03). S1 is built and verified (below). S2 and S3 are built and not verified. S4–S7 are documentation only (design in #66).

What it shows

#	Workflow	How it is shown	Status
S1	Slow transaction → the SQL behind it — share of time in SQL, the statement, its service and table, the pod; N+1, connection wait, a slow external call and Kafka consumer lag told apart	sql/s1_diagnose.sql, the APM workflows dashboard, trace waterfall	verified
S2	Alert on it — eight tile alerts to Slack: six on traces (A1–A6), two on logs — A7 error lines per service, A8 a service that stopped logging, against the services seen in the 24 hours before	clickstack/setup.py --alerts, the APM alerts dashboard	built, not verified on this lab. A7/A8: expected rows on seeded logs in clickhouse local 26.9.6.6 (tests/test_log_alerts.py, 2026-11-06); the same two shapes fired on Managed ClickStack in labs/process-down-alerts/managed (E1, E3)
S3	Deploy comparison against 24 h and 7 d ago, JSON per service	sql/s3_deploy_compare.sql	built, not verified
S4	Errors as issues	SQL / materialized view, Event Patterns	documentation only
S5	Incident write-up from an alert	SQL	documentation only
S6	Evidence for a coding agent through MCP	ClickStack MCP, Cloud MCP	documentation only
S7	Swapping the agent	documentation only	written, never run

How it is built: everything inside the Cloud service

No application, no collector, nothing sent in from outside. Telemetry is generated in SQL by refreshable materialized views, shaped like what the stock OpenTelemetry Java agent 2.31.1 emits for Spring Boot services on HikariCP, MySQL, Redis (Lettuce) and Kafka. It is modelled, not captured: every span, attribute and metric name was read from the agent's v2.31.1 sources, but no JVM produced this data. The checks prove that the diagnosis finds a known injected cause.

ClickHouse Cloud service with Managed ClickStack
├── database apm_workflows
│   ├── topology     topo_services · topo_endpoints · topo_spans      the shop as rows: add a service = add rows
│   ├── switches     fault_events · deploy_events · lab_settings      you INSERT rows (bin/fault.py, bin/deploy.py)
│   ├── generator    gen_traces(window) ─┬─→ gen_logs(window)          views; deterministic: the same window
│   │                                    └─→ gen_metrics_*(window)     always yields the same rows
│   ├── live         rmv_traces  REFRESH EVERY 1 MINUTE, from the watermark
│   │                └─ DEPENDS ON → rmv_logs · rmv_metrics_gauge · rmv_metrics_sum · rmv_metrics_histogram
│   ├── backfill     the same views over the 8 days before install
│   └── telemetry    otel_traces · otel_logs · otel_metrics_*        OTel exporter schema, TTL 30 days
└── ClickStack
    ├── sources      APM Traces ⇄ APM Logs ⇄ APM Metrics              → apm_workflows
    └── dashboard    APM workflows — 21 SQL tiles                      clickstack/tiles/*.sql

• Switches are rows. A fault starts at the minute its fault_events row says, so every fault window is on record. Because generation is deterministic, the S1 check replays faults into a past block of the history, judges it at once, and regenerates the block clean afterwards — the row counts before and after are compared and must be equal.
• Logs and metrics are derived from the spans of the same window, so they share trace and span ids.
• The backfill is the same generator over the past 8 days, faults off, every row tagged apm.backfill=true. Live generation continues from its last minute, so 24 h-ago and 7 d-ago comparisons are real.

The modelled shop

Eleven services, two or more pods each (web-bff three), in five namespaces:

Namespace	Service	What it does · data	Base version
shop-edge	web-bff	entry point for every user request	2.8.0
shop-catalog	catalog	product pages and search; MySQL, Redis cache	3.2.1
shop-purchase	cart	the cart; Redis	1.9.4
	checkout	orchestrates a purchase	4.1.0
	pricing	prices and promotions	2.6.3
	inventory	stock reservation; MySQL	5.0.2
	payment	authorisation at an external gateway (pg.example.com)	1.12.0
	order	create, read and list orders; MySQL; publishes order.created to Kafka	3.7.1
shop-customer	customer	accounts; MySQL	2.1.0
shop-async	notification	consumes order.created, sends mail through an HTTP API (mail.example.com)	1.4.2
	fulfillment	consumes order.created, creates shipments; MySQL	1.6.0

User requests (root spans of web-bff): GET /products/{sku} 35%, GET /search 15%, POST /cart/items 15%, POST /checkout 10%, GET /orders 10%, GET /orders/{oid} 10%, GET /account 5%, at about 60 a minute on a daily curve.

One purchase, as generated — 26 spans in one trace (sql/verify_trace_tree.sql), condensed here: a client span and the server span it calls share a line:

span                                        service       kind      offset ms  duration ms
POST /checkout                              web-bff       Server        0          89.5
  POST                                      web-bff       Client        0.1        88.2
    POST /api/checkout                      checkout      Server        0.7        87.2
      GET → GET /api/cart → GET (Redis)     checkout → cart             0.9         3.6
      POST → POST /api/quote                checkout → pricing          4.8         4.0
      POST → POST /api/reservations         checkout → inventory        8.8        14.8
              HikariDataSource.getConnection, UPDATE shop.stock
      POST → POST /api/authorizations       checkout → payment         23.9        22.1
              POST  → pg.example.com
      POST → POST /api/orders               checkout → order           46.0        37.0
              HikariDataSource.getConnection, INSERT shop.orders, INSERT shop.order_items
              order.created publish                    Producer        79.9         1.3
                order.created process        notification  Consumer   139.3        40.6   → POST mail.example.com
                order.created process        fulfillment   Consumer   156.9         9.5   → INSERT shop.shipments

Under the agent's defaults the consumer's process span is a child of the producer's publish span, in the same trace (receive telemetry is off by default; read from the kafka-clients instrumentation and its tests at v2.31.1).

Faults

Each is off by default and switched by a row (python3 bin/fault.py on|off <fault> [--target <pod>]).

Fault	Where	What the SQL tells apart
slow-query	order, order history: orders WHERE customer_email = ? without its index	the statement tops time-in-SQL with its service and table; MySQL slow-log entries with rows_examined above a million
n-plus-one	order, order history: order_items WHERE order_id = ? once per order	SQL statements per request jump from 1 to ~30; one statement repeats
pool-exhaustion	one inventory pod (--target)	purchases wait for a connection on that pod only; SQL itself stays fast; Hikari pending requests > 0
downstream-latency	payment → pg.example.com	the purchase's time moves into the external call, with its address
kafka-consumer-lag	notification	publish→consume delay grows to minutes; fulfillment and the purchase itself stay fast
exception-storm	order, order detail	three exception types, every message different (S4)
bad deploy	python3 bin/deploy.py checkout 4.2.0 --regression	the new checkout calls pricing once per item, ~3% errors (S3)

Background noise and small incidents

So the shop is not unrealistically quiet, everyday failures run all the time (backfill and live): pricing timeouts that are retried, invalid cart input, MySQL deadlocks on stock that are retried, Kafka rebalances (WARN); payment-gateway timeouts, mail-API 503s, a rare search bug, duplicate order keys (ERROR). lab_settings.noise_scale scales them (0 = off). Measured by SQL over five live hours on 2026-10-03, as a share of user requests: WARN 1.8–2.7%, ERROR 0.2–0.9%, 5xx at the edge 0.0–0.3%. rmv_incidents also writes a small incident every few hours ahead of time into fault_events (run_id = auto-…; three extra kinds: mail-api-errors, pricing-timeouts, stock-deadlocks); lab_settings.incidents = 0 stops new ones. Not re-verified with the noise on: the S1 check, the incident effect and continuity were not re-run after this change.

The dashboard

APM workflows in the service's ClickStack, 21 SQL tiles, each one file in clickstack/tiles/ (header comments give its title, display type and grid position):

• user requests, 5xx/exception rate, 4xx rate, p95 user-facing latency;
• requests per service, p95 per user endpoint;
• S1: time in SQL by statement, SQL statements per user request, connection wait by pod, purchase time per step, the S1 diagnosis by endpoint and by service and pod (derived from sql/s1_diagnose.sql, so the query exists once);
• Kafka publish→consume delay and consumer records lag, external calls by address, Hikari pending requests;
• error and warning logs, WARN/ERROR by message (numbers folded), MySQL slow log, fault switches and deploys.

A server span is an error only for 5xx (OTel HTTP semantic conventions), so 4xx is shown separately.

Verification

Verified on ClickHouse 26.6.1.2191 (ClickHouse Cloud, ap-northeast-2, two replicas) with Managed ClickStack (its Help menu shows the v2.39.0 release notes; the running build is not displayed), 2026-10-03:

• bin/s1_check.py, replay mode: 44 of 44 assertions PASS, twice (runs s1-20261002T175916Z and s1-20261002T185112Z, a 60-minute block of the backfill; the first took 38 s end to end); the restore regenerated the block and the row counts of all five tables equalled the snapshot. Measured in the second run: slow-query sql_share 0.993 and 18 slow-log entries (max rows_examined 1,168,553); n-plus-one 31.8 statements per request; pool-exhaustion conn_wait_share 0.904 on the target pod and 0.000 on the other; downstream-latency 0.930 of the purchase in the call to pg.example.com; kafka-consumer-lag notification p95 327 s, fulfillment 0.033 s. The negative window had every indicator off. Thresholds are in bin/s1_check.py.
• --live once (faults on real time, 18:01–18:35 UTC): 44 of 44 PASS.
• Negative controls: the 12 cause-detecting assertions run against a window without faults all fail, as they must; a wrong expected statement turns the run into 43 PASS / 1 FAIL.
• Continuity: no minute written twice, no gap between the backfill's last minute and the first live one, no minute without requests; an injected duplicate and an injected gap are both caught.
• Service graph by SQL: exactly the 14 edges of the table above (sql/verify_graph.sql).
• The ClickStack sources and dashboard were created through the Cloud API and read back. The dashboard rendering in the UI was looked at, which is not a verification claim.
• Not run: an install on an empty service from the first step. Dropping the database was not permitted during the run, so the tables were emptied instead; bin/install.sh was run twice on the existing database and was idempotent, and the backfill and live generation then started from empty tables. bin/uninstall.sh has never been run.

Running it

You need a ClickHouse Cloud service with Managed ClickStack and a credentials file that defines CH_HOST, CH_USER and CH_PASSWORD (HTTPS port 8443) — and, for the ClickStack step only, CHC_ORG_ID, CHC_KEY_ID and CHC_KEY_SECRET (a Cloud API key). The lab writes only into database apm_workflows; lib/ch.py refuses the write statements the scripts use when they name another database — a guard against accidents, not a security boundary.

cd labs/apm-workflows
cp .env.example .env            # set CH_ENV_FILE to the credentials file
bin/install.sh                  # database, tables, topology, switches, generator views
bin/backfill.sh                 # 8 days of history; prints the plan first, refuses above 20M spans
bin/install.sh rmvs             # live generation, every minute
python3 clickstack/setup.py --test && python3 clickstack/setup.py --apply   # sources and dashboard
python3 bin/s1_check.py         # replay the faults into a past block, PASS/FAIL per assertion, restore
bin/stop.sh                     # stop live generation on every replica (data stays); --resume starts it
bin/uninstall.sh                # DROP DATABASE apm_workflows (asks first)

python3 bin/s1_check.py --live runs the same check on real time (about 40 minutes); --keep leaves the replayed block in place to look at in the UI, and --restore RUN_ID puts it back. Unit tests: python3 -m unittest discover -s tests.

What it costs on the service

	Measured 2026-10-03
Backfill	4,628,010 spans (690,082 traces) for 8 days in 185 s
Storage	about 263 MiB on disk for all lab tables; otel_traces 235 MiB (compression 15.7×)
Live generation	five refreshes a minute: about 2.0 s of CPU and 4.3 s of wall time per minute in total, at about 60 requests a minute

Operating notes

• SYSTEM STOP VIEW / START VIEW act on one replica only (no ON CLUSTER form, measured on 26.6.1.2191); bin/stop.sh repeats them until every replica reports the wanted state.
• On a multi-replica service an INSERT … SELECT from a table the previous statement just wrote can read an old state; the backfill, the replay and the views run with select_sequential_consistency = 1.
• To change the shop, edit the rows in sql/04_topology.sql, DELETE FROM the three topo_* tables and run bin/install.sh again. A topology change does not rewrite history; regenerate it with bin/backfill.sh --force.
• The Kafka lag metric the agent emits (kafka.consumer.records_lag_max) counts records, not seconds; the delay in seconds comes from the spans.

Honest limits

• Modelled data, as above.
• No transaction profiling with call stacks: OTel profiling is not in ClickStack yet. S1 stops at spans and statements.
• No email alert channel: email and messengers go through a webhook.
• Issue grouping is SQL / a materialized view (S4), not a built-in view.
• PromQL is private preview. The Cloud ClickStack MCP is OAuth only.

S7 — swapping the agent (documentation only)

Not run here: the in-database generator cannot stand in for a real JVM. What the swap is, from the sources:

• The OpenTelemetry Java agent attaches with JAVA_TOOL_OPTIONS="-javaagent:path/to/opentelemetry-javaagent.jar" and is named with OTEL_SERVICE_NAME (getting started, read 2026-10-03). Agent 2.x exports OTLP over http/protobuf to http://localhost:4318 by default (configuration, read 2026-10-03).
• On Kubernetes the usual pattern — and the OpenTelemetry Operator's (v0.160.0, internal/instrumentation/javaagent.go) — is an init container that copies the agent jar into a shared volume, and -javaagent:<mount>/javaagent.jar appended to the application container's JAVA_TOOL_OPTIONS. The Operator v0.160.0 ships agent 2.31.1.
• In-house agents are usually attached the same way, so the swap is the jar and its environment variables in the same slot. The application image does not change.
• The collector for Managed ClickStack is the ClickStack distribution (clickhouse/clickstack-otel-collector), configured by CLICKHOUSE_ENDPOINT, CLICKHOUSE_USER, CLICKHOUSE_PASSWORD and HYPERDX_OTEL_EXPORTER_CLICKHOUSE_DATABASE (read from the 2.39.1 image, 2026-10-02); extra receivers come from otel-profiles/ through CUSTOM_OTELCOL_CONFIG_FILE.

Layout

labs/apm-workflows/
├── .env.example        the path of the credentials file — never the credentials
├── bin/                install.sh · backfill.sh · fault.py · deploy.py · s1_check.py · stop.sh · uninstall.sh
├── lib/ch.py           HTTPS client; guards against writing outside apm_workflows
├── sql/                00–05 tables, switches, topology, S1 bookkeeping · 10–13 generator views and watermark ·
│                       30 the live views · backfill_*.sql · s1_*.sql (S1 diagnosis and checks) · verify_*.sql
├── clickstack/         setup.py · tiles/*.sql (one dashboard tile per file)
└── tests/              lib/ch.py, the S1 replay schedule, the dashboard tiles

---

Managed ClickStack이 있는 ClickHouse Cloud 서비스
├── 데이터베이스 apm_workflows
│   ├── 구성       topo_services · topo_endpoints · topo_spans      쇼핑몰을 행으로 정의: 서비스 추가 = 행 추가
│   ├── 스위치     fault_events · deploy_events · lab_settings      행을 INSERT (bin/fault.py, bin/deploy.py)
│   ├── 생성기     gen_traces(구간) ─┬─→ gen_logs(구간)              view. 결정적: 같은 구간은
│   │                                └─→ gen_metrics_*(구간)         항상 같은 행을 만든다
│   ├── live       rmv_traces  REFRESH EVERY 1 MINUTE, 워터마크부터
│   │              └─ DEPENDS ON → rmv_logs · rmv_metrics_gauge · rmv_metrics_sum · rmv_metrics_histogram
│   ├── 백필       같은 view로 설치 시점 이전 8일
│   └── 텔레메트리 otel_traces · otel_logs · otel_metrics_*        OTel exporter 스키마, TTL 30일
└── ClickStack
    ├── 소스       APM Traces ⇄ APM Logs ⇄ APM Metrics              → apm_workflows
    └── 대시보드   APM workflows — SQL 타일 21개                     clickstack/tiles/*.sql

span                                        service       kind      offset ms  duration ms
POST /checkout                              web-bff       Server        0          89.5
  POST                                      web-bff       Client        0.1        88.2
    POST /api/checkout                      checkout      Server        0.7        87.2
      GET → GET /api/cart → GET (Redis)     checkout → cart             0.9         3.6
      POST → POST /api/quote                checkout → pricing          4.8         4.0
      POST → POST /api/reservations         checkout → inventory        8.8        14.8
              HikariDataSource.getConnection, UPDATE shop.stock
      POST → POST /api/authorizations       checkout → payment         23.9        22.1
              POST  → pg.example.com
      POST → POST /api/orders               checkout → order           46.0        37.0
              HikariDataSource.getConnection, INSERT shop.orders, INSERT shop.order_items
              order.created publish                    Producer        79.9         1.3
                order.created process        notification  Consumer   139.3        40.6   → POST mail.example.com
                order.created process        fulfillment   Consumer   156.9         9.5   → INSERT shop.shipments

cd labs/apm-workflows
cp .env.example .env            # CH_ENV_FILE에 접속 파일 경로
bin/install.sh                  # 데이터베이스, 테이블, 구성, 스위치, 생성기 view
bin/backfill.sh                 # 8일치 이력. 먼저 계획을 출력하고, 2천만 span을 넘으면 거부
bin/install.sh rmvs             # live 생성, 1분마다
python3 clickstack/setup.py --test && python3 clickstack/setup.py --apply   # 소스와 대시보드
python3 bin/s1_check.py         # 과거 구간에 결함을 재생, 판정마다 PASS/FAIL, 되돌리기
bin/stop.sh                     # 모든 replica에서 live 생성 멈춤 (데이터는 남음). --resume으로 재개
bin/uninstall.sh                # DROP DATABASE apm_workflows (먼저 확인)

labs/apm-workflows/
├── .env.example        접속 파일 경로 — 자격 증명은 넣지 않음
├── bin/                install.sh · backfill.sh · fault.py · deploy.py · s1_check.py · stop.sh · uninstall.sh
├── lib/ch.py           HTTPS 클라이언트. apm_workflows 밖에 쓰는 실수를 막음
├── sql/                00–05 테이블·스위치·구성·S1 기록 · 10–13 생성기 view와 워터마크 ·
│                       30 live view · backfill_*.sql · s1_*.sql (S1 진단과 검사) · verify_*.sql
├── clickstack/         setup.py · tiles/*.sql (대시보드 타일 하나당 파일 하나)
└── tests/              lib/ch.py, S1 재생 일정, 대시보드 타일