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

Log in to FusionInsight Manager.

For details, see Accessing FusionInsight Manager.
Create a ClickHouse user who has permissions to modify system tables. For details, see Creating a User with ClickHouse Permissions.
Log in to the client node as the user created in 2 and connect to the ClickHouse server. For details, see ClickHouse Client Practices.

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.

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;