Configuring TTL for the ClickHouse System Table

Scenarios

By default, the time to live (TTL) mechanism is not configured for ClickHouse system tables. If these tables are used over an extended period without a manually defined expiration policy, historical data in the tables will accumulate continuously. This can lead to excessive disk space usage and may negatively impact cluster performance and storage availability.

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

Notes and Constraints

This section applies only to versions earlier than MRS 3.2.0-LTS.

Procedure

1. Log in to FusionInsight Manager.

   For details, see Accessing MRS Manager (Versions Earlier Than MRS 3.x).

2. Create a ClickHouse user who has permissions to modify system tables. For details, see ClickHouse User and Permission Management.
3. Log in to the client node as the user created in 2 and connect to the ClickHouse server. For details, see Using the ClickHouse Client.
4. 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;

   In the preceding command, default_cluster indicates the cluster name.

5. 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;

Helpful Links

For more information about TTL, visit https://clickhouse.com/docs/guides/developer/ttl#ttl-syntax.