clickhouse（四、运维查看数据库及表容量）

mysql数据库有information_schema.tables系统表记录表相关元数据，clickhouse对应的有system.parts表。下面是查看clickhouse数据库和表大小、行数及压缩率等方法。

• 查看数据库容量，以测试数据为参考

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) "压缩率"
from system.parts;

┌──────总行数─┬─原始大小──┬─压缩大小─┬─压缩率─┐
│ 169851144587 │ 30.76 TiB │ 4.51 TiB │     15 │
└─────────────┴───────────┴──────────┴────────┘

1 rows in set. Elapsed: 1.462 sec. Processed 90.35 thousand rows, 42.81 MB (63.76 thousand rows/s., 30.60 MB/s.) 

• 查询test表，2019年10月份的数据容量

select
    table as "表名",
    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) "压缩率"
from system.parts
	-- 根据实际情况加查询条件
    where table in('test')
        and partition like '2019-10-%'
    group by table;		

┌─────总行数─┬─原始大小───┬─压缩大小──┬─压缩率─┐
│ 3015199559 │ 727.83 GiB │ 60.34 GiB │      8 │
└────────────┴────────────┴───────────┴────────┘

1 rows in set. Elapsed: 0.227 sec. Processed 3.52 thousand rows, 1.70 MB (15.48 thousand rows/s., 7.50 MB/s.) 

• 查询表字段每列的存储大小及压缩率

SELECT
    column AS `字段名`,
    any(type) AS `类型`,
    formatReadableSize(sum(column_data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(column_data_compressed_bytes)) AS `压缩大小`,
    round(sum(column_data_uncompressed_bytes) / sum(column_data_compressed_bytes), 2) AS `压缩率`,
    sum(rows) AS `行数`
FROM system.parts_columns
WHERE (database = 'test') AND (table = 'table_name')
GROUP BY column
ORDER BY column ASC

下一节 我们来看下如何查看后台进程并杀死。