search

monogon-dev / NetMeta

NetMeta is a scalable network observability toolkit optimized for performance.
https://netmeta.demo.monogon.dev/
Apache License 2.0
135 stars 6 forks source link

Use mutation cronjob for applying TTLs #56

Open leoluk opened 4 years ago

leoluk commented 4 years ago

The ClickHouse table TTL mechanism is very static - a delete_date is set whenever a part is created and won't be updated when the TTL is altered. We should use a mutation in a cronjob instead:

ALTER TABLE flows_raw DELETE WHERE Date < today() - INTERVAL $(TTL) DAY;

This is much more flexible and allows us to push #5 for a bit.

A similar approach can be used to implement max table sizes.

fionera commented 1 year ago

I couldn't find anything that describes this behaviour in the Clickhouse docs. Are you sure this is still the case?

UnamedRus commented 1 year ago

The ClickHouse table TTL mechanism is very static - a delete_date is set whenever a part is created and won't be updated when the TTL is altered.

It's not exactly true, ClickHouse unfortunately will do whole part rewrite (by default) if you do ALTER of TABLE TTL.

More on this topic https://kb.altinity.com/altinity-kb-queries-and-syntax/ttl/modify-ttl/ (starting from ~ 21.9)

Basically, i can suggest for you to do: use merge tree settings ttl_only_drop_parts=1, materialize_ttl_recalculate_only=1; Because, you do partitioning by date, it should make change of TTL pretty cheap & will remove whole parts when they will expire. (cheap TTL delete merge)