ClickHouse轻松配置：优雅清理日志，释放磁盘空间！

1. ClickHouse开启日志的方法

　　在users.xml中，添加配置<log_queries>0</log_queries>。0表示关闭查询日志，1表示打开查询日志。

2. ClickHouse清理日志的方法

• 方式一：清理过期数据

ALTERtable`system`.trace_log DROPPARTITION2021xx;
ALTERtable`system`.query_log DROPPARTITION2021xx;
ALTERtable`system`.query_thread_log DROPPARTITION2021xx;

• 方式二：修改配置添加TTL(需要DROP旧表并重启生效）

  <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>

　　按照如下顺序操作： 先配置这个文件，配置后删除表，再重启CK。 《实际采用此方式》

• 方式三：不重启ck添加ttl策略

　　集群不方便重启，也可以采用直接在现有的系统表上添加ttl策略的形式，但需注意集群内核升级后会重建系统日志表，可能会丢失此方式配置的ttl配置

ALTER TABLE system.query_log ON CLUSTER default_cluster MODIFY TTL event_date + INTERVAL 30 DAY DELETE;
ALTER TABLE system.trace_log ON CLUSTER default_cluster MODIFY TTL event_date + INTERVAL 30 DAY DELETE;
ALTER TABLE system.query_thread_log ON CLUSTER default_cluster MODIFY TTL event_date + INTERVAL 30 DAY DELETE;

　　注意此方式执行时，先手动删除分区后再添加，不然会生成大量的ddl任务，导致集群卡顿。 此方式风险比较大，不采用。

<!-- Query log. Used only for queries with setting log_queries = 1. --> 
    <query_log> 
        <!-- What table to insert data. If table is not exist, it will be created. 
             When query log structure is changed after system update, 
              then old table will be renamed and new table will be created automatically. 
        --> 
        <database>system</database> 
        <table>query_log</table> 
        <!-- 
            PARTITION BY expr https://clickhouse.yandex/docs/en/table_engines/custom_partitioning_key/ 
            Example: 
                event_date 
                toMonday(event_date) 
                toYYYYMM(event_date) 
                toStartOfHour(event_time) 
        --> 
        <partition_by>toYYYYMM(event_date)</partition_by>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl> 
 
        <!-- Instead of partition_by, you can provide full engine expression (starting with ENGINE = ) with parameters, 
             Example: <engine>ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_time) SETTINGS index_granularity = 1024</engine> 
          --> 
 
        <!-- Interval of flushing data. --> 
        <flush_interval_milliseconds>7500</flush_interval_milliseconds> 
    </query_log> 
 
    <!-- Trace log. Stores stack traces collected by query profilers. 
         See query_profiler_real_time_period_ns and query_profiler_cpu_time_period_ns settings. --> 
    <trace_log> 
        <database>system</database> 
        <table>trace_log</table> 
 
        <partition_by>toYYYYMM(event_date)</partition_by>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl> 
        <flush_interval_milliseconds>7500</flush_interval_milliseconds> 
    </trace_log> 
 
    <!-- Query thread log. Has information about all threads participated in query execution. 
         Used only for queries with setting log_query_threads = 1. --> 
    <query_thread_log> 
        <database>system</database> 
        <table>query_thread_log</table> 
        <partition_by>toYYYYMM(event_date)</partition_by>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl> 
        <flush_interval_milliseconds>7500</flush_interval_milliseconds> 
    </query_thread_log> 
 
    <!-- Uncomment if use part log. 
         Part log contains information about all actions with parts in MergeTree tables (creation, deletion, merges, downloads). 
    <part_log> 
        <database>system</database> 
        <table>part_log</table> 
        <flush_interval_milliseconds>7500</flush_interval_milliseconds> 
    </part_log> 
    -->