# Siphon with ClickHouse HA Overview: 1. A local single-box Kubernetes setup for testing Siphon against a replicated (HA) ClickHouse cluster (sections 1-4). 2. Consistency / HA findings and recommendations for a real production multinode cluster (sections 5-7). ## 1. Environment summary | Piece | Detail | |---|---| | Host | Debian 12, single physical node, 12 cores / 31Gi RAM | | Kubernetes | k3s `v1.36.2+k3s1`, pinned to NIC `eno1` | | LoadBalancer | k3s built-in servicelb (assigns the node's LAN IP to `LoadBalancer` services; no MetalLB) | | Ingress | Traefik (bundled with k3s) on `:80/:443` | | cert-manager | `v1.21.0` (hard prerequisite for the operator's webhooks) | | CH operator | ClickHouse-Inc operator `v0.0.6` (`github.com/ClickHouse/clickhouse-operator`), NOT the Altinity one. CRDs: `clickhouse.com/v1alpha1` (`ClickHouseCluster`, `KeeperCluster`). Namespace `clickhouse-operator-system` | | ClickHouse | server `26.6.1`, 1 shard x 3 replicas (`replicas: 3`), 1-node Keeper for coordination | | Storage | `local-path` (k3s default), 10Gi per CH replica, 1Gi for Keeper | | CH resources | per replica: `requests` 500m CPU / 1Gi, `limits` 4 CPU / 5Gi | The 3 ClickHouse replicas + Keeper all run on the single physical node (default anti-affinity is off). ## 2. Access - Provisioned the `gitlab` user as per [documentation](https://docs.gitlab.com/integration/clickhouse/?tab=HA+ClickHouse+for+GitLab+Self-Managed), see caveat 1. - Provisioned the `siphon` user as per [comment](https://gitlab.com/gitlab-org/orbit/knowledge-graph/-/work_items/369#note_3191238186) ## 3. Kubernetes configuration ### Install steps ```bash # 1. k3s (on the host, pinned to eno1) curl -sfL https://get.k3s.io | sudo sh -s - \ --write-kubeconfig-mode 644 --node-ip $$$NODE_IP_HERE$$$ --flannel-iface eno1 # 2. cert-manager (prereq) kubectl apply -f https://github.com/cert-manager/cert-manager/releases/download/v1.21.0/cert-manager.yaml kubectl -n cert-manager rollout status deploy/cert-manager-webhook # 3. ClickHouse operator kubectl apply --server-side --force-conflicts \ -f https://github.com/ClickHouse/clickhouse-operator/releases/download/v0.0.6/clickhouse-operator.yaml # 4. namespace + default-user password secret kubectl create namespace clickhouse kubectl -n clickhouse create secret generic ch-default-password --from-literal=password=<PASSWORD> # 5. cluster manifest (below) kubectl apply -f clickhouse-cluster.yaml ``` ### clickhouse-cluster.yaml ```yaml apiVersion: clickhouse.com/v1alpha1 kind: KeeperCluster metadata: name: ch namespace: clickhouse spec: replicas: 1 dataVolumeClaimSpec: resources: requests: storage: 1Gi --- apiVersion: clickhouse.com/v1alpha1 kind: ClickHouseCluster metadata: name: ch namespace: clickhouse spec: replicas: 3 dataVolumeClaimSpec: resources: requests: storage: 10Gi keeperClusterRef: name: ch # Memory/CPU per replica. NOTE: field is spec.containerTemplate.resources, # NOT spec.resources (see caveat 2). containerTemplate: resources: requests: cpu: "500m" memory: "1Gi" limits: cpu: "4" memory: "5Gi" settings: defaultUserPassword: passwordType: password secret: name: ch-default-password key: password --- # LAN access: the operator only makes a headless Service, so we add our own # LoadBalancer. servicelb assigns the node's LAN IP. apiVersion: v1 kind: Service metadata: name: ch-lan namespace: clickhouse spec: type: LoadBalancer selector: app: ch-clickhouse ports: - name: http port: 8123 targetPort: 8123 - name: native port: 9000 targetPort: 9000 ``` ## 4. Caveats ### 1. Creating users/roles: do NOT use `ON CLUSTER` This operator stores users, roles, grants, quotas, and row policies in a Keeper-backed `replicated` access store shared by all 3 replicas. Running `CREATE USER ... ON CLUSTER default` (as per our current documentation) fans the DDL out to every node, so after the first node writes the entity to the shared store, the other two fail with: ``` Code: 493. DB::Exception: user `gitlab`: cannot insert because user `gitlab` already exists in `replicated`. (ACCESS_ENTITY_ALREADY_EXISTS) ``` Fix: create access entities **once, without `ON CLUSTER`**: ```sql CREATE USER gitlab IDENTIFIED WITH sha256_password BY 'gitlab'; ``` (Tables/databases are the opposite: use `ReplicatedMergeTree` / the `Replicated` database engine. `ON CLUSTER` is optional there since the `default` database is a `Replicated` engine and auto-propagates DDL.) Documentation gap: depending on the ClickHouse setup, we might need to omit or append the `ON CLUSTER` clause. This needs to be clearly documented. ### 2. Memory limit exceeded on a fresh cluster The operator defaults CH pods to a `512Mi` memory limit, so ClickHouse caps its server memory at ~0.9x that (~458 MiB) and table creation queries fail with: ``` Code: 241. DB::Exception: (total) memory limit exceeded: ... maximum: 458.85 MiB. (MEMORY_LIMIT_EXCEEDED) ``` Fix: raise the pod memory limit; ClickHouse auto-scales its server limit to match. The field is `spec.containerTemplate.resources` (a bare `spec.resources` is rejected as an unknown field). Setting `resources` replaces the operator defaults wholesale, so include CPU too. ```bash kubectl -n clickhouse patch clickhousecluster ch --type merge -p '{ "spec": {"containerTemplate": {"resources": { "requests": {"cpu": "500m", "memory": "1Gi"}, "limits": {"cpu": "4", "memory": "5Gi"} }}}}' ``` Current: pods limited to 5Gi, CH server memory cap ~5 GiB. ### 3. Primary key rendered as a tuple: `no primary key columns found in the source stream` **Symptom:** consumer NAKs messages for tables *not* in `dedup_config` (e.g. `banned_users`, `users`, `organizations`, `namespaces`, `ci_runners`, `duo_workflows_workflows`, `sbom_component_versions`) with: ``` no primary key columns found in the source stream ``` Root cause (siphon code bug, surfaced by this cluster, fixed here: https://gitlab.com/gitlab-org/analytics-section/siphon/-/merge_requests/496+): the consumer resolves a table's PK by reading `system.tables.primary_key` and splitting it on commas (`pkg/clickhouse/metadata.go`). On this cluster (ClickHouse `26.6.1`, tables created with an explicit `PRIMARY KEY (user_id)` and `ReplicatedReplacingMergeTree`) that column renders **with tuple parentheses**: `(user_id)`. The parser kept the parens and looked for a column literally named `(user_id)` in the CDC event, which never matches -> the error. Version-dependent: a local single-node ClickHouse `26.1.3.52` renders the same PK as `user_id` (no parens), which is why it only broke against the HA cluster. Tables in `dedup_config` are unaffected because they use the config-provided `dedup_by` column names and bypass the `system.tables.primary_key` path entirely. Fix: strip the wrapping tuple parens before splitting, in `pkg/clickhouse/metadata.go` (`fetchMetadata`): ## 5. Consistency & HA (findings for production multinode) Target: self-managed multinode ReplicatedMergeTree cluster behind a load balancer. Context: Siphon writes DELETEs as tombstones by reading the row's PK back from CH (`SETTINGS select_sequential_consistency = 1`) and re-inserting with `_siphon_deleted=true`. If that read lands on a lagging replica and misses the insert, the tombstone is dropped and the delete is lost. ### Settings applied in Siphon `select_sequential_consistency=1` + `insert_quorum=auto` + `insert_quorum_parallel=0` + `async_insert=0` are set together on the consumer insert batch and on the tombstone/refresh/dedup query paths (`pkg/consumers/`). All are ignored on CH Cloud (SharedMergeTree), so the Cloud path is unchanged. - `insert_quorum=auto` = majority; self-adjusts with replica count (= 2 at 3 replicas). `auto` guarantees majority overlap (the invariant `select_sequential_consistency` relies on) and tracks scaling; a fixed number stops being a majority once you add replicas. - `insert_quorum_parallel=0` is required for `select_sequential_consistency` to take effect on self-managed RMT. It serializes quorum inserts per table, so it trades write throughput for correctness. - `async_insert=0` is mandatory: `insert_quorum` + `insert_quorum_parallel=0` + async insert is rejected by ClickHouse (`code 49`). `async_insert` defaults on on some servers (it does on this cluster). ### Consistency test plan 1. **No quorum, induced lag:** `SYSTEM STOP FETCHES` on replica B; default INSERT on replica A; read B with and without `select_sequential_consistency=1`. 2. **Quorum, induced lag:** same, but INSERT with `insert_quorum=auto` + `insert_quorum_parallel=0` + `async_insert=0`; read a quorum member and the lagging replica with `select_sequential_consistency=1`. 3. **Sustained mixed workload:** random INSERT/UPDATE/DELETE, then reconcile PG live rows == CH `FINAL` non-deleted rows on every replica. ### Consistency results (CH 26.6.1, ReplicatedMergeTree) | Test | Config | Read on lagging replica | |---|---|---| | 1 | `select_sequential_consistency=1` only | stale data, returned silently | | 2 | `insert_quorum=auto` + `insert_quorum_parallel=0` + `select_sequential_consistency=1` | `Code 289 REPLICA_IS_NOT_IN_QUORUM` (fail-loud); quorum member returns correct row | ### Failure tests executed (3-replica, 1-Keeper, single box) | Test | Method | Result | |---|---|---| | Quorum unavailable | stop fetches on 2 of 3, insert with `insert_quorum=auto` | insert failed (majority unreachable); succeeded after restore | | Idempotency | insert same `(id, ver)` twice | 1 row (ReplicatedMergeTree block dedup) | | Version ordering | insert `ver` 1..5 out of order | `FINAL` keeps latest (`ver=5`) | | Replica kill / write availability | delete a replica pod, insert on a survivor with quorum | insert succeeded (majority still reachable) | | Replica catch-up | killed replica rejoins | synced all rows incl. the one written while down; all 3 replicas agree | ### End-to-end through Siphon (PG load + CH disruption) Sustained random INSERT/UPDATE/DELETE on `notes` via the live consumer, disrupting ClickHouse mid-load, then reconcile PG live rows == CH `FINAL` non-deleted on every replica. | Test | Method | Result | |---|---|---| | Regression caught | first run with `insert_quorum_parallel=0`, `async_insert` still on | 20/21 inserts failed `code 49` -> pipeline broke | | Replica kill under load | delete a replica pod mid-load | writes continued on survivors; no consumer error | | Below quorum under load | stop fetches on 2/3 during load | writes blocked then recovered on restore; no data-affecting error | | Reconcile | after disruptions + drain | PG live == CH `FINAL` non-deleted, exact match on all 3 replicas | The regression above is why `async_insert=0` is now set (by default async insert may be enabled). Collateral during the broken window: a delete was lost (the tombstone read found no row because the insert hadn't landed). After adding `async_insert=0` and re-running, consistency held. ### Recommendations (self-managed production HA) - Writes: `insert_quorum=auto` + `insert_quorum_parallel=0` + `async_insert=0`. - Reads (tombstone / dedup lookups): `select_sequential_consistency=1`. - On `REPLICA_IS_NOT_IN_QUORUM` (289): retry on another replica. - Retry is handled by Siphon and NATS consistently. Opened https://gitlab.com/gitlab-org/analytics-section/siphon/-/work_items/256+ to deal with the config changes.