clickhouse系统表日志清理方式详解


    目录
  • 简介
  • 清理
    • 清理方式
  • 实际操作
    • 方案选择
    • 删除数据
    • 查看任务执行情况
    • 单机操作
    • 设置TTL
  • 总结

    简介
    

    clickhouse会将查询日志，度量日志和堆栈采集日志记录下来，存储到自身数据库的system库中， 分别是query_log,query_thread_log,trace_log,metric_log等5个表，如果长时间不清查，该表数据会一直累积。
    清理
    清理方式
    清理这些日志表有多种方式
    第1种是设置表的TTL，clickhouse是支持设置表的TTL，不管是系统表还是业务表。
    第2种是使用DDL语句+DML语句删除指定范围内数据
    第3种配置文件设置，在config.xml中提供了日志表的配置
    
    字段释义:
    
  • database – 数据库名
  • table – 日志存放的系统表名
  • partition_by — 系统表分区键，如果定义了 engine 则不能使用
  • engine -系统表 表引擎，如果定义了 partition_by 则不能使用
  • flush_interval_milliseconds – 将数据从内存的缓冲区刷新到表的时间间隔。
  • ttl 保存时间

    图中没有ttl的配置，可以自行添加, event_date是表中的时间字段,格式是: 2020-01-01
    
<!--
            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>

    实际操作
    方案选择
    第3种改配置文件方案适合在集群搭建的时候，后期再改就需要重启服务，因服务不能随意重启，需要配置维护期间操作，所以不具灵活性，排除掉了。
    第1种设置TTL的方案也是可以的，但是现在不合适，因为已经累积了很长时间的数据，数据量比较大，直接设置会消耗比较多的资源
    所以会使用第2种+第1种组合方案，先分批删除历史的日志，然后再使用第1种方案设置TTL。这样后期就不需要维护了
    删除数据
    使用DDL+DML语句删除指定日期内的数据
    先用小一点的表操作一下，通常trace_log比较小，query_log表比较大
    
use system;
#统计一下2020年有多少条数据
select count(*) from trace_log where event_date >= '2020-01-01' and  event_date <= '2020-12-31';
#根据统计，只有几十万或者几百万的话，可以执行删除
alter table trace_log delete where event_date >= '2020-01-01' and  event_date <= '2020-12-31';

    clickhouse的delete,update等操作是异步的，当sql语句一提交，就会有反馈，这个时候是将任务提交到排队执行了
    如果表非常小，不想选定时间范围。也可以一次性清完
    
ALTER table `system`.trace_log DELETE where event_date is not null;

    查看任务执行情况
    队列数据在mutations表中
    
#统计历史次数
select count(*) from mutations;
#排序，查看最新10条
select database,table,command,create_time,is_done from mutations order by create_time desc limit 10;

    
    表信息显示了命令发现的在system库，表名已经执行命令的时间，判断sql是正在排还是已经执行完成的是is_done字段。1表示执行完成，0表示正在排队
    单机操作
    clickhouse是支持创建集群库，集群表的，但是system库属于系统自带的，需要查看库和表的创建信息
    
show create database system;
show create table trace_log;

    因为system属于系统自带的库，集群建好时已存在，但是创建该库的sql文件确没有,所以报错。
    但是查看clickhouse元数据文件时发现 data/system/trace_log文件在变小，说明本节点删除成功了，如果换个节点执行sql语句又能查出来。
    所以需要在每个节点上执行删除数据语句
    在元数据存储的目录可以看到有个mutations_xxxx.log的文件，是删除语句的记录日志 data/system/trace_log
    元数据的配置
    
<!-- Path to data directory, with trailing slash. -->
<path>xxxxx/clickhouse/data/</path>

    设置TTL
    大部分数据删除完成之后，就可以设置TTL了
    
#系统表
ALTER table `system`.trace_log MODIFY TTL event_date + toIntervalDay(30);
#业条表
ALTER TABLE `test`.app_version MODIFY TTL time_stamp + toIntervalYear(30);

    总结
    通过删除表数据+设置TTL的方式，后期就可以0维护了
    系统表日志处理是日常数据维护的一种，业务数据的清理也可以参考这些方案
相关文章！