今天出现了生产环境clickhouse 清理业务表数据磁盘并未下降的问题
  搜索相关文档后得知system表内会继续各种操作日志

一、查询各个表占用空间信息

select concat(database, '.', table) as table,
formatReadableSize(sum(bytes)) as size,
sum(rows) as rows,
max(modification_time) as latest_modification,
sum(bytes) as bytes_size,
any(engine) as engine,
formatReadableSize(sum(primary_key_bytes_in_memory)) as primary_keys_size
from system.parts
where active
group by database, table
order by bytes_size desc
limit 10;

二、清理system 系统自带的日志表数据

2.1 根据指定数据库清理SQL

alter table system.query_thread_log delete where current_database ='xxx' ;

2.2 根据指定数据库和时间清理SQL

alter table system.query_thread_log delete where current_database ='xxx' and event_date='2023-02-05';