How to self host using HA replicated ClickHouse

[andreeib]

Hello,

I'm using OpenMeter to collect GPU compute allocation in a hosting platform, currently it's a MVP so I self host. So far the experience with OpenMeter has been great, it just lacks slightly on docs, for example It's not clear how I can run self hosted in a high availability setup.

I installed ClickHouse and ClickHouseKeeper using altinity-clickhouse-operator and configured it with 3 replicas each:

apiVersion: "clickhouse-keeper.altinity.com/v1"
kind: ClickHouseKeeperInstallation
metadata:
  name: chk
  namespace: ${var.namespace}
spec:
  configuration:
    clusters:
      - name: ${var.cluster_name}
        layout:
          replicasCount: 3
...
---
apiVersion: clickhouse.altinity.com/v1
kind: ClickHouseInstallation
metadata:
  name: chi
  namespace: ${var.namespace}
spec:
  configuration:
    zookeeper:
      nodes:
        - host: keeper-chk
          port: 2181

    clusters:
      - name: ${var.cluster_name}
        layout:
          replicasCount: 3
...

I created a database:
CREATE DATABASE IF NOT EXISTS ${var.db_name} ON CLUSTER ${var.cluster_name};

Then installed OpenMeter Helm chart:

clickhouse = {
  enabled = false
    operator = {
      install = false
    }
}
config = {
  aggregation = {
    clickhouse = {
      address  = "${var.clickhouse_host}:${var.clickhouse_port}"
      username = var.clickhouse_username
      password = var.clickhouse_password
      database = var.clickhouse_database
     }
  }
}
...

Now as expected GET /api/v1/meters/{meterIdOrSlug}/query requests return inconsistent results based on what ClickHouse node it hits.

How can run OpenMeter with replicated ClickHouse?
Should I manually create a ReplicatedMergeTree table?

Thank you

[andreeib]

My ClickHouse knowledge is lacking but it seems I made it work like this:

Created ReplicatedMergeTree table:

CREATE TABLE om_events_local ON CLUSTER '{cluster}'
(
    `namespace` String,
    `id` String,
    `type` LowCardinality(String),
    `subject` String,
    `source` String,
    `time` DateTime,
    `data` String,
    `ingested_at` DateTime,
    `stored_at` DateTime,
    `store_row_id` String
) 
ENGINE = ReplicatedMergeTree('/clickhouse/{installation}/{cluster}/tables/{shard}/{database}/{table}', '{replica}')
PARTITION BY toYYYYMM(time)
ORDER BY (namespace, type, subject, toStartOfHour(time))
SETTINGS index_granularity = 8192;

Then a Distributed one:

CREATE TABLE om_events_dist ON CLUSTER '{cluster}' AS om_events_local
ENGINE = Distributed('{cluster}', 'openmeter', om_events_local, rand());

And configured Openmeter to use the distributed table:

aggregation = {
  clickhouse = {
    address  = "${var.clickhouse_host}:${var.clickhouse_port}"
    username = var.clickhouse_username
    password = var.clickhouse_password
    database = "openmeter"
  }
  eventsTableName = "om_events_dist"
}

Is this the right aproach?

[GAlexIHU]

Hi @andreeib, we're glad you like using OpenMeter

We do not (yet) have an officially recommended HA setup, the public helm chart can be configured to use external dependencies (for ClickHouse, PG, etc...), the default setup without tweaks is meant more for testing and dev envs rather than production workloads.

Internally (on openmeter.cloud) we use managed services for databases, for ClickHouse we use ClickHouse cloud which works a bit differently from an OSS HA setup (for instance it has custom SharedMergeTree engines instead of ReplicatedMergeTree).

If you wanted to self-host ClickHouse, the Zookeeper (or CHKeeper, as you mentioned) approach is the recommended cluster deployment, and the affinity operator is quite alright in my experience.

One thing specific to OpenMeter is that the om_events table creation query is not parameterized to use other types of engines, so as you mentioned changing this would be a manual step right now. The reason this is not an issue for us is as we use CH cloud that aliasing happens internally (automatically translates to a SharedMergeTree)

Alternatively if you'd like to contribute with a PR to make it parametrizable, i'd be happy go through it 😉

[andreeib]

Hello @GAlexIHU, thank you for the response, makes total sense. I'll provide a PR for parameterized engine.