当我们把存储在clickhouse中的数据全部清除后,为何磁盘空间没有恢复最初状态呢?其实有一点,我们忽略了,当磁盘存储或查询,本身运行都会存在一些日志,而数据清除了,但是日志却还在,占用磁盘空间,所以需要把日志也给清除,才能完全释放空间。
首先我们来查看一下,磁盘使用情况
select
partition,
database,
table,
sum(rows) as row,
formatReadableSize(sum(bytes_on_disk)) as used_disk,
formatReadableSize(sum(data_uncompressed_bytes)) as before_compress,
formatReadableSize(sum(data_compressed_bytes)) as after_compress,
round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) compress_rate
from system.parts
--and database = 'system'
group by table,database,partition
order by row desc
partition|database|table |row|used_disk |before_compress|after_compress|compress_rate|
---------+--------+----------------+---+----------+---------------+--------------+-------------+
202111 |system |query_thread_log|477|128.25 KiB|480.02 KiB |95.78 KiB | 20.0|
202111 |system |query_log |207|140.90 KiB|268.39 KiB |97.48 KiB | 36.0|
202111 |system |trace_log | 84|15.29 KiB |23.18 KiB |10.40 KiB | 45.0|
由于初始化,分区是按年的分区的,我们可以看到我们存储的数据文件不在了,然而日志还在,我们需要清除掉
alter table system.query_thread_log drop partition '202111';
alter table system.query_log drop partition '202111';
alter table system.trace_log drop partition '202111';
然后再看磁盘空间是不是已经释放了呢?
查看磁盘所用数据,占用情况如下
SELECT
sum(rows) AS `总行数`,
formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
FROM system.parts;

