Disk Usage SQL

Disk Info

SELECT 
    name,
    path,
    formatReadableSize(free_space) AS free,
    formatReadableSize(total_space) AS total
FROM system.disks;

┌─name────┬─path─────────────────┬─free───────┬─total──────┐
│ default │ /var/lib/clickhouse/ │ 225.87 GiB │ 491.95 GiB │
└─────────┴──────────────────────┴────────────┴────────────┘

Database Size

List databases order by size desc

SELECT 
    database,
    formatReadableSize(sum(bytes)) as size,
    sum(rows) as total_rows,
    count(distinct table) as tables_count
FROM system.parts
WHERE active
GROUP BY database
ORDER BY sum(bytes) DESC
LIMIT 10;

Total size

SELECT
    formatReadableSize(sum(bytes_on_disk)) AS total_disk_usage
FROM system.parts
WHERE active;

Table Size

All Tables

Order by size desc

SELECT 
    database,
    table,
    formatReadableSize(sum(bytes)) as size,
    sum(rows) as rows,
    sum(bytes) as bytes_size,
    max(modification_time) as latest_modification
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY bytes_size DESC
LIMIT 10;

Specify Table

SELECT 
    database,
    table,
    formatReadableSize(sum(bytes)) as size,
    sum(rows) as rows,
    count() as parts
FROM system.parts
WHERE database = 'system' 
    AND table = 'part_log'
GROUP BY database, table;

Partition Size

All Tables

Order by size desc

SELECT 
    database,
    table,
    partition,
    formatReadableSize(sum(bytes)) as size,
    sum(rows) as rows,
    sum(bytes) as bytes_size,
    max(modification_time) as latest_modification
FROM system.parts
WHERE active
GROUP BY database, table, partition
ORDER BY bytes_size DESC
LIMIT 10;

ORDER BY database, table, partition asc

SELECT 
    database,
    table,
    partition,
    formatReadableSize(sum(bytes)) as size,
    sum(rows) as rows,
    sum(bytes) as bytes_size,
    max(modification_time) as latest_modification
FROM system.parts
WHERE active
GROUP BY database, table, partition
ORDER BY database, table, partition
LIMIT 10;

Specify Table

Order by size desc

SELECT 
    database,
    table,
    partition,
    formatReadableSize(sum(bytes)) AS size,
    sum(rows) AS rows,
    sum(bytes) AS bytes_size,
    max(modification_time) AS latest_modification
FROM system.parts
WHERE active
    AND table = 'part_log'
    AND database = 'system'
GROUP BY database, table, partition
ORDER BY bytes_size DESC
LIMIT 10;

Order by partition asc

SELECT 
    database,
    table,
    partition,
    formatReadableSize(sum(bytes)) AS size,
    sum(rows) AS rows,
    sum(bytes) AS bytes_size,
    max(modification_time) AS latest_modification
FROM system.parts
WHERE active
    AND table = 'part_log'
    AND database = 'system'
GROUP BY database, table, partition
ORDER BY partition
LIMIT 10;

Partition Ratio

WITH total AS (
    SELECT sum(bytes) as total_bytes
    FROM system.parts
    WHERE database = 'system' AND table = 'part_log'
)
SELECT 
    partition,
    formatReadableSize(sum(bytes)) as size,
    round(sum(bytes) / total_bytes * 100, 2) as percentage,
    sum(rows) as rows,
    count() as parts
FROM system.parts, total
WHERE database = 'system' 
    AND table = 'part_log'
GROUP BY partition, total_bytes
ORDER BY sum(bytes) DESC;

Drop Partition

Quick and Safe

ALTER TABLE journal.otel_logs DROP PARTITION '2025-04-04';

macOS

DB_NAME="journal"
TABLE_NAME="otel_logs"
START_DATE="2025-01-01"
END_DATE="2025-01-10"

current=$START_DATE
while [[ "$current" < "$END_DATE" || "$current" == "$END_DATE" ]]; do
  echo "ALTER TABLE ${DB_NAME}.${TABLE_NAME} DROP PARTITION '$current';"
  current=$(date -j -v +1d -f "%Y-%m-%d" "$current" "+%Y-%m-%d") # BSD date
done

Linux

DB_NAME="journal"
TABLE_NAME="otel_logs"
START_DATE="2025-01-01"
END_DATE="2025-01-10"

current=$(date -I -d "$START_DATE")
while [[ "$current" < "$END_DATE" || "$current" == "$END_DATE" ]]; do
  echo "ALTER TABLE ${DB_NAME}.${TABLE_NAME} DROP PARTITION '$current';"
  current=$(date -I -d "$current + 1 day") # GNU date
done

Compression Ratio

SELECT 
    database,
    table,
    formatReadableSize(sum(data_uncompressed_bytes)) as uncompressed_size,
    formatReadableSize(sum(data_compressed_bytes)) as compressed_size,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) as compression_ratio
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY compression_ratio DESC;

Compression Info

describe table default.otel_logs

Inactive Size

SELECT 
    database,
    table,
    formatReadableSize(sum(bytes)) as size
FROM system.parts
WHERE active = 0
GROUP BY database, table
LIMIT 10;

Release Inactive Size

DANGER

危险操作

OPTIMIZE TABLE default.jaeger_span FINAL;

Notes

• ClickHouse 默认使用 LZ4 压缩算法。
• 在 ClickHouse 中，数据只以压缩后形式存储在磁盘上。
• 未压缩大小仅是用于统计和查询优化的参考信息，而不实际存储未压缩的数据。
• 压缩大小（bytes）：
  • 数据经过压缩后在磁盘上实际占用的空间。
    • 这是查询中返回的默认大小。
• 未压缩大小（data_uncompressed_bytes）：
  • 数据以原始格式加载到内存时的大小，反映了解压后的数据量。
• 可以为每列指定压缩算法，通过 CODEC 设置。
• 常见算法比较：
  • LZ4（默认）：速度快，压缩比中等。
    • ZSTD（推荐）：压缩比高，解压速度快，适合冷数据。
    • NONE：不压缩，适用于查询频繁的临时数据。