一、前言
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.pa服务器托管网rts 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 tablequery_log;
truncate tabletrace_log;
truncate tablequery_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;
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
效果: 代码: 弹出框 .icon { width: 1em; height: 1em; vertical-align: -1.17em; margin-left: 165px; fill: currentColor; overflow:服务器托管网 hidd…