Installing Clickhouse on a kubernetes cluster

Clickhouse is the fastest and most resource efficient open-source database for real-time apps and analytics. Installing clickhouse on a kubernetes cluster is not straight forward at the moment. This article should help to do this.

Kubernetes operators

Clickhouse installation on kubernetes may start simple and slowly it would get more & more complex. It’s stateful in nature. Need to operator on cluster of nodes. Should be resilient & fault tolerant from a kubernetes resource perspective. So from the beginning we will use operators to install & manage clickhouse.

Clickhouse needs Zookeeper

ZooKeeper is a centralized service for maintaining configuration information, naming, providing distributed synchronization, and providing group services. To run a clickhouse cluster, we need a coordination system between cluster of clickhouse instances.

Zookeeper brings the guarante of linearizable writes, non-linearizable reads to clickhouse cluster.

Zookeeper installation steps

pravega/zookeeper-operator — this operator seems like a good option at the time of writing this article. But I faced issue with running `linux/amd64` while installing this operator on Mac M1. They have not published docker image for arm64.

We could simplify zookeeper installation with bitnami’s zookeeper

helm install zookeeper \
    --set zookeeper.enabled=false \
    --set persistence.size=100Mi \
    --set replicaCount=1 \
    oci://registry-1.docker.io/bitnamicharts/zookeeper

Install clickhouse operator

helm repo add clickhouse-operator https://docs.altinity.com/clickhouse-operator/
helm repo update
helm install clickhouse-operator clickhouse-operator/altinity-clickhouse-operator

Install clickhouse

Here is a demo clickhouse installation manifest that uses clickhouse operator. This will spin up a cluster with 3 replicas with 3 shards.

apiVersion: "clickhouse.altinity.com/v1"
kind: "ClickHouseInstallation"
metadata:
  name: "demo"
spec:
  configuration:
    clusters:
      - name: "demo"
        layout:
          shardsCount: 3
          replicasCount: 3
        templates:
          podTemplate: clickhouse-stable
          dataVolumeClaimTemplate: clickhouse-data-volume
          serviceTemplate: svc-template
    zookeeper:
        nodes:
        - host: zookeeper-headless
          port: 2181
    users:
        demoreadonly/profile: readonly
        demo/password: demoPassword
        demo/profile: default
        demo/quota: default
        demo/networks/ip:
            - 0.0.0.0/0
            - ::/0
    profiles:
      demoreadonly/readonly: "1"
      # server level settings can be set here
      demo/data_type_default_nullable: 1 # data types in column definitions are set to Nullable by default
      demo/insert_distributed_sync: 1 # Data is inserted in synchronous mode
      demo/mutations_sync: 2 # query waits for all mutations to complete on all replicas
      demo/parallel_distributed_insert_select: 2 # SELECT and INSERT will be executed on each shard in parallel
      demo/distributed_product_mode: allow # Allows the use of these types of subqueries
    files:
      config.d/log_rotation.xml: |-
        <clickhouse>
            <logger>
                <level>information</level>
                <log>/var/log/clickhouse-server/clickhouse-server.log</log>
                <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
                <size>100M</size>
                <count>5</count>
                <console>1</console>
            </logger>
        </clickhouse>
  templates:
    podTemplates:
    - name: clickhouse-stable
      spec:
        containers:
        - name: clickhouse
          image: clickhouse/clickhouse-server:23.4.2
          resources:
            requests:
              memory: "1024Mi"
              cpu: "500m"
            limits:
              memory: "2024Mi"
              cpu: "1000m"
    volumeClaimTemplates:
      - name: clickhouse-data-volume
        spec:
          storageClassName: standard
          accessModes:
            - ReadWriteOnce
          resources:
            requests:
              storage: 2Gi

View table size information

To know how much space each table is taking, we could use following query

select parts.*,
       columns.compressed_size,
       columns.uncompressed_size,
       columns.compression_ratio,
       columns.compression_percentage
from (
         select table,
                formatReadableSize(sum(data_uncompressed_bytes))          AS uncompressed_size,
                formatReadableSize(sum(data_compressed_bytes))            AS compressed_size,
                round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes), 3) AS  compression_ratio,
                round((100 - (sum(data_compressed_bytes) * 100) / sum(data_uncompressed_bytes)), 3) AS compression_percentage

             from system.columns
             group by table
         ) columns
         right join (
    select table,
           sum(rows)                                            as rows,
           max(modification_time)                               as latest_modification,
           formatReadableSize(sum(bytes))                       as disk_size,
           formatReadableSize(sum(primary_key_bytes_in_memory)) as primary_keys_size,
           any(engine)                                          as engine,
           sum(bytes)                                           as bytes_size
    from system.parts
    where active 
    group by database, table
    ) parts on columns.table = parts.table
order by parts.bytes_size desc;

This is inspired from this public gist. It has other developers giving more improvements on top of this.

Schema migration tool

Among different schema migration tools, I like dbmate for clickhouse. Let me update this section, once I have a demo example.

Kubernetes session affinity

Clickhouse is an eventually consistent database. There are times when we like to execute a data insert or update or delete and immediately lookup for updated data. This is natural in a Transactional database like postgresql. But not a good usecase for OLAP database like Clickhouse. If there are few usecases that need to have consistent data integrity, we could use session affinity and redirect traffic from same ClientIP to same clickhouse pods.

kind: Service
apiVersion: v1
metadata:
  name: my-service
spec:
  ....
  sessionAffinity: ClientIP

• Not just the clickhouse service but all the services that is involved in the end user interaction should have sessionAffinity: ClientIP
• Let’s say, if we have a service behind kong both service and kong service should be configured to use sessionAffinity. If kong is behind an istio-ingress, we should configure sessionAffinity to istio as well
• Without configuring all the services, we may end up in sending all requests to same clickhouse pod.
• With end to end sessionAffinity, each end user will be using a different clickhouse till service.spec.sessionAffinityConfig.clientIP.timeoutSeconds . Default is 3 hours (10800 seconds). We can change it based on our needs.

Useful videos

Useful links

• ClickHouse Installation Custom Resource specification
• High Availability Architecture recommendations from altinity

Good reads

• From EBay — Our Online Analytical Processing Journey with ClickHouse on Kubernetes