How to delete automatically data of clickhouse shard

[secuofme]

I managed coroot by helm chart.
I installed like this.

helm install coroot --namespace coroot coroot/coroot -f coroot-values.yaml

coroot-values.yaml

opentelemetry-collector:
  resources:
    limits:
      memory: 4Gi
      cpu: 3
    requests:
      memory: 2Gi
      cpu: 2
  config:
    processors:
      batch:
        timeout: 5s
        send_batch_size: 100000
    exporters:
      clickhouse:
        ttl_days: 3
clickhouse:
  shards: 3
  replicaCount: 2
  clusterName: prod-clickhouse-cluster
  persistence:
    size: 500Gi
  resources:
    requests:
      cpu: 2
      memory: 4Gi
    limits:
      cpu: 4
      memory: 8Gi
  config:
    clickhouse.yml: |
      query_log:
        ttl: 'event_date + INTERVAL 5 DAY DELETE'
        flush_interval_milliseconds: 8000
node-agent:
  resources:
    limits:
      cpu: 1.5
    requests:
      cpu: 0.5

but Click house shard disk size is almost full.
I don't know exactly, how to remove automatically click house shard data.

Could you help me?

[apetruhin]

@secuofme, please check the TTL setting of the otel_traces table:

SHOW CREATE TABLE otel_traces;

CREATE TABLE default.otel_traces
(
    `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
...
 INDEX idx_duration Duration TYPE minmax GRANULARITY 1
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SpanName, toUnixTimestamp(Timestamp), TraceId)
TTL toDateTime(Timestamp) + toIntervalDay(7)
...

Changing opentelemetry-collector.config.exporters.clickhouse.ttl_days doesn't affect the otel_traces table if it already exists.

You can alter the table yourself:

ALTER TABLE otel_traces MODIFY TTL toDateTime(Timestamp) + toIntervalDay(3);

[secuofme]

I am operating 3 shard on clickhouse. I need to execute alter table each shard?

[secuofme]

I checked Thank you. @apetruhin. I am wondering Why didn't change ttl value by helm values file?
is it not working? or my configuration is wrong?

[secuofme]

And I have problem when I executed ALTER like below.

PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SpanName, toUnixTimestamp(Timestamp), TraceId)
TTL toDateTime(Timestamp) + toIntervalDay(7)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.003 sec.

coroot-clickhouse-shard0-0.coroot-clickhouse-headless.coroot.svc.cluster.local :) ALTER TABLE otel_traces MODIFY TTL toDateTime(Timestamp) + toIntervalDay(3);

ALTER TABLE otel_traces
    MODIFY TTL toDateTime(Timestamp) + toIntervalDay(3)

Query id: 97ae0f99-8a83-4023-96cb-3e1991583188





Timeout exceeded while receiving data from server. Waited for 300 seconds, timeout is 300 seconds.
Cancelling query.

[secuofme]

ALTER TABLE has too long. So, it's canceled. how to fix it? please advice for me.

[apetruhin]

I think the ALTER operation is not actually canceled.
ClickHouse should have freed up some space by now. Please check its volume.

[secuofme]

your right it's working it applied.
And Could you check on my helm values file?
Why didn't applied below configuration?

I am wrong it?

    exporters:
      clickhouse:
        ttl_days: 3

  config:
    clickhouse.yml: |
      query_log:
        ttl: 'event_date + INTERVAL 5 DAY DELETE'
        flush_interval_milliseconds: 8000

[apetruhin]

Your config is okay.

Unfortunately, the clickhouse exporter of opentelemetry-collector does not support changing the ttl_days setting for now.

The value of the setting is used only the first time when the exporter creates the otel_traces table.