clickhouse数据库磁盘空间使用率过高问题排查

一、前言

clickhouse天天触发磁盘使用率过高告警，所以需要进行排查，故将排查记录一下。

二、排查过程

1、连接上进入clickhouse

2、执行语句查看各库表使用磁盘情况

SELECT database, table, formatReadableSize(sum(bytes_on_disk)) as disk_space FROM system.parts GROUP BY database, table ORDER BY disk_space DESC;

发现个别日志表占用存储空间较大

3、如果只查看某个库的表使用空间可以执行

SELECT database, table, formatReadableSize(sum(bytes_on_disk)) as disk_space FROM system.parts where database = 'system' GROUP BY database, table ORDER BY disk_space DESC;

4、如果要查看某个特定表的磁盘占用情况，可以执行以下语句

SELECT formatReadableSize(sum(bytes_on_disk)) as disk_space FROM system.parts WHERE database='your_database' AND table='your_table';

5.查询库的容量和压缩信息可以执行

select
    sum(rows) as row,--总行数
    formatReadableSize(sum(data_uncompressed_bytes)) as ysq,--原始大小
    formatReadableSize(sum(data_compressed_bytes)) as ysh,--压缩大小
    round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) ys_rate--压缩率
from system.parts

select
    sum(rows) as row,--总行数
    formatReadableSize(sum(data_uncompressed_bytes)) as ysq,--原始大小
    formatReadableSize(sum(data_compressed_bytes)) as ysh,--压缩大小
    round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) ys_rate--压缩率
from system.parts

 三、对无用的日志表数据进行清理

truncate table  query_log；

truncate table  trace_log；

truncate table  query_thread_log；

ALTER TABLE query_thread_log  DELETE WHERE 1=1;

ALTER TABLE query_log DELETE WHERE 1=1;

ALTER TABLE trace_log DELETE WHERE 1=1;