ClickHouseのディスク使用量を確認するSQL集

はじめに

ClickHouseでtableのサイズなどの確認を行いたいことがあり、調べた結果をまとめたものです。

サンプルデータ

以下のサンプルを適当に入れています。

全ディスクサイズ

SELECT * FROM system.disks;

clickhouse :) SELECT * FROM system.disks;

SELECT *
FROM system.disks

Query id: f7bbb8d5-666d-4edf-a574-f925b9b4db27

┌─name────┬─path─────────────────┬───free_space─┬──total_space─┬─keep_free_space─┬─type──┐
│ default │ /var/lib/clickhouse/ │ 187382165504 │ 269427478528 │               0 │ local │
└─────────┴──────────────────────┴──────────────┴──────────────┴─────────────────┴───────┘

1 rows in set. Elapsed: 0.003 sec. 

clickhouse :) 

各テーブルの圧縮サイズ

SELECT
   table,
   formatReadableSize(sum(data_compressed_bytes)) AS compressed
FROM system.parts
WHERE active AND (table LIKE '%')
GROUP BY table

uk_price_paid は最初に紹介したsampleデータです。

Query id: 40e8323c-5827-49de-a3a5-7e4ac42c9709

┌─table───────────────────┬─compressed─┐
│ part_log                │ 2.35 KiB   │
│ metric_log              │ 3.24 MiB   │
│ trace_log               │ 8.87 MiB   │
│ query_thread_log        │ 102.02 KiB │
│ uk_price_paid           │ 170.95 MiB │
│ query_log               │ 66.53 KiB  │
│ session_log             │ 14.42 KiB  │
│ asynchronous_metric_log │ 27.52 MiB  │
└─────────────────────────┴────────────┘

8 rows in set. Elapsed: 0.004 sec. 

clickhouse :) 

指定したテーブルの圧縮サイズ

SELECT
   table,
   formatReadableSize(sum(data_compressed_bytes)) AS compressed
FROM system.parts
WHERE active AND (table LIKE 'uk_%')
GROUP BY table

Query id: ce9c9567-7176-44fb-8d92-3fbeb0aaad25

┌─table─────────┬─compressed─┐
│ uk_price_paid │ 170.95 MiB │
└───────────────┴────────────┘

1 rows in set. Elapsed: 0.003 sec. 

clickhouse :) 

Database 毎

SELECT
    disk_name,
    database,
    formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed,
    sum(data_compressed_bytes) AS compressed_raw,
    formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed,
    sum(data_uncompressed_bytes) AS uncompressed_raw,
    round(usize / size, 2) AS compr_rate,
    sum(rows) AS rows,
    count() AS part_count
FROM system.parts
WHERE (active = 1)
GROUP BY
    disk_name,
    database
ORDER BY size DESC;

Query id: 55428673-7e19-4f7a-913e-0d13958a7ff3

┌─disk_name─┬─database─┬─compressed─┬─compressed_raw─┬─uncompressed─┬─uncompressed_raw─┬─compr_rate─┬─────rows─┬─part_count─┐
│ default   │ default  │ 170.95 MiB │      179251704 │ 770.11 MiB   │        807517463 │        4.5 │ 28205961 │          1 │
│ default   │ system   │ 39.09 MiB  │       40988839 │ 1.48 GiB     │       1584508932 │      38.66 │ 43726807 │         22 │
└───────────┴──────────┴────────────┴────────────────┴──────────────┴──────────────────┴────────────┴──────────┴────────────┘

2 rows in set. Elapsed: 0.005 sec. 

clickhouse :) 

全Databaseの全テーブル毎

SELECT
    disk_name,
    database,
    table,
    formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed,
    round(usize / size, 2) AS compr_rate,
    sum(rows) AS rows,
    count() AS part_count
FROM system.parts
WHERE (active = 1) AND (table LIKE '%') AND (database LIKE '%')
GROUP BY
    disk_name,
    database,
    table
ORDER BY size DESC;

┌─disk_name─┬─database─┬─table───────────────────┬─compressed─┬─uncompressed─┬─compr_rate─┬─────rows─┬─part_count─┐
│ default   │ default  │ uk_price_paid           │ 170.95 MiB │ 770.11 MiB   │        4.5 │ 28205961 │          1 │
│ default   │ system   │ asynchronous_metric_log │ 26.86 MiB  │ 970.89 MiB   │      36.14 │ 42410341 │          8 │
│ default   │ system   │ trace_log               │ 8.69 MiB   │ 319.29 MiB   │      36.73 │  1048334 │          6 │
│ default   │ system   │ metric_log              │ 3.16 MiB   │ 212.68 MiB   │      67.31 │    87456 │          2 │
│ default   │ system   │ query_thread_log        │ 93.20 KiB  │ 1.24 MiB     │      13.66 │     1508 │          1 │
│ default   │ system   │ query_log               │ 56.95 KiB  │ 789.79 KiB   │      13.87 │      746 │          1 │
│ default   │ system   │ session_log             │ 12.71 KiB  │ 103.09 KiB   │       8.11 │      715 │          1 │
│ default   │ system   │ part_log                │ 2.35 KiB   │ 11.20 KiB    │       4.76 │       63 │          1 │
└───────────┴──────────┴─────────────────────────┴────────────┴──────────────┴────────────┴──────────┴────────────┘

8 rows in set. Elapsed: 0.005 sec. 

clickhouse :) 

参考