clickhouse系统表日志清理

2,362 阅读2分钟

简介

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

清理

清理方式

清理这些日志表有多种方式

第1种是设置表的TTL,clickhouse是支持设置表的TTL,不管是系统表还是业务表。

第2种是使用DDL语句+DML语句删除指定范围内数据

第3种配置文件设置,在config.xml中提供了日志表的配置

image.png

字段释义:

  • 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;
#排序,查看最新10select database,table,command,create_time,is_done from mutations order by create_time desc limit 10;

image.png 表信息显示了命令发现的在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维护了

系统表日志处理是日常数据维护的一种,业务数据的清理也可以参考这些方案