一、前言

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

selectsum(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;