ClickHouse with multiple replicas and shards

[vittoriocanilli]

Hello, I am setting up Plausible on a Kubernetes cluster, where PostgreSQL is hosted on an Azure instance and ClickHouse is hosted on the same Kubernetes cluster, having multiple replicas and shards:

• 2 Plausible replicas
• 3 ClickHouse replicas with 2 shards, corresponding to 6 pods, each with a 8GBi PVC
• 3 ZooKeeper replicas, each with a 8GBi PVC

I am using the BitNami ClickHouse chart and I have set the headless service's endpoint http://plausible_user:plausible_password@plausible-analytics-clickhouse-headless.<namespace>:8123/plausible_events_db as CLICKHOUSE_DATABASE_URL and everything seems to work.

When I try to delete one of the ClickHouse pods to simulate what would happen during a rescheduling through the Kubernetes cluster's nodes, the pod gets recreated correctly, but the data on the sites on my Plausible dashboard is no more consistent as before, when I refresh the page: sometimes the graphs have data, sometimes they are empty, sometimes they are not rendered because of a HTTP 500 error.

I have found a discussion that highlights that Plausible does not support clusters. I am quite new with ClickHouse: does that actually mean that Plausible can only run with ClickHouse if it has one shard and one replica?

Just to understand if it is a limitation of Plausible or if I am doing something wrong with my setup. Thanks in advance.

[kakaroto]

Hi, I don't know if you find a solution.
I don't know much about clickhouse or sharding, but I found this article which explains how to get plausible in self-hosted to work in high availability mode: https://harrytang.xyz/blog/high-availability-self-hosted-plausible-analytics-k8s
I haven't tried it yet but I will probably give it a try next week.

The SQL query they share might need to be updated to match the latest DB structure from plausible's repo (https://github.com/plausible/analytics/blob/master/priv/ingest_repo/structure.sql)
My (quick glance) understanding is that plausible uses things like:

ENGINE = VersionedCollapsingMergeTree(sign, events)

and replacing it with:

ENGINE = ReplicatedVersionedCollapsingMergeTree('/clickhouse/{cluster}/tables/{shard}/{database}/{table}', '{replica}', sign, events)

and adding a ON CLUSTER to the query is all that's needed to tell clickhouse to replicate the data between replicas instead of keeping it local to the local server only..