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

简介

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);