SRE文档

深浅模式

1.介绍 ​

clickhouse会将查询日志，度量日志和堆栈采集日志记录下来，存储到自身数据库的system库中， 分别是query_log,trace_log,metric_log等5个表，如果长时间不清查，该表数据会一直累积

1.1删除方案 ​

1.配置文件 ​

vim config.xml

trace_log
query_log
vim config.xml

trace_log
query_log

2.手动删除 ​

  • 查询
sql
select count(*) from system.trace_log prewhere event_date >= '2020-01-01'
select count(*) from system.trace_log prewhere event_date >= '2020-01-01'
  • 删除
sql
#根据统计，只有几十万或者几百万的话，可以执行删除
alter table system.trace_log delete prewhere event_date >= '2020-01-01' and  event_date <= '2020-12-31';
#根据统计，只有几十万或者几百万的话，可以执行删除
alter table system.trace_log delete prewhere event_date >= '2020-01-01' and  event_date <= '2020-12-31';
  • 如果不指定时间
sql
ALTER table system.trace_log DELETE prewhere event_date is not null;
ALTER table system.trace_log DELETE prewhere event_date is not null;

3.设置ttl ​

xml
<!--
            Table TTL specification: https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#mergetree-table-ttl
            Example:
                event_date + INTERVAL 1 WEEK
                event_date + INTERVAL 7 DAY DELETE
                event_date + INTERVAL 2 WEEK TO DISK 'bbb'
-->
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
<!--
            Table TTL specification: https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#mergetree-table-ttl
            Example:
                event_date + INTERVAL 1 WEEK
                event_date + INTERVAL 7 DAY DELETE
                event_date + INTERVAL 2 WEEK TO DISK 'bbb'
-->
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
  • 设置
sql
#系统表，保留30天
ALTER table system.trace_log MODIFY TTL event_date + toIntervalDay(30);

#业务表
ALTER TABLE your_dbname.table_name MODIFY TTL time_stamp + toIntervalYear(30);
#系统表，保留30天
ALTER table system.trace_log MODIFY TTL event_date + toIntervalDay(30);

#业务表
ALTER TABLE your_dbname.table_name MODIFY TTL time_stamp + toIntervalYear(30);