Configuring TTL for the ClickHouse System Table

Scenario

ClickHouse does not provide built-in TTL for the system table, and historical data will use up disk space.

This topic guides you to configure a TTL for the system table to retain only the data of the last 30 days.

This topic is available for versions earlier than MRS 3.2.0 only.

Procedure

1. Log in to the client node as a user who has permission to modify the ClickHouse system table, and then connect to the ClickHouse server. For details, see ClickHouse Client Practices.
2. Clear the historical system table data.

   truncate table system.query_log on cluster default_cluster;

   truncate table system.query_thread_log on cluster default_cluster;

   truncate table system.trace_log on cluster default_cluster;

3. Configure the TTL for the system table to retain data of the last 30 days.

   alter table system.query_log on cluster default_cluster modify TTL event_time + INTERVAL 30 day;

   alter table system.query_thread_log on cluster default_cluster modify TTL event_time + INTERVAL 30 day;

   alter table system.trace_log on cluster default_cluster modify TTL event_time + INTERVAL 30 day;