How Do I Add a Periodic Deletion Policy to Prevent Large ClickHouse System Table Logs?

Symptom

ClickHouse logs generated in the system table is too large. Deleting logs at a time takes a long time.

Handling Procedure

• For MRS 3.3.0 and later versions, on FusionInsight Manager, choose Cluster > Services > ClickHouse > Configurations > All Configurations. You can set query_log.ttl and trace_log.ttl to configure the data clearing period, the default value is 90 days.
• In versions earlier than MRS 3.3.0, run the following statement on the ClickHouse client to modify the TTL of a table in ClickHouse:

  alter table system.Table name modify TTL event_date + INTERVAL Number of days for storing data day;

  

  • This statement is used only on a SQL node to configure the TTL of the system table. If the TTL needs to be configured on all nodes, run this statement on each node. Do not run the statement with on cluster on each node. Otherwise the ClickHouse will keep running.
  • Run the preceding statement during off-peak hours. This operation may take a long time if there is a large amount of data.