Storage and Disk Usage Analysis

Diagnose disk usage, compression efficiency, part sizes, and storage bottlenecks.

Quick Diagnostics

1. Disk Space Audit

sql

select
    name as disk_name,
    path,
    type,
    formatReadableSize(total_space) as total,
    formatReadableSize(free_space) as free,
    formatReadableSize(unreserved_space) as unreserved,
    round(100.0 * (total_space - free_space) / total_space, 1) as used_pct,
    multiIf(used_pct > 90, 'Critical', used_pct > 85, 'Major', used_pct > 80, 'Moderate', 'OK') as severity
from system.disks
order by used_pct desc

2. Storage by Database

sql

select
    database,
    count() as tables,
    sum(total_rows) as total_rows,
    formatReadableSize(sum(total_bytes)) as total_size,
    formatReadableSize(sum(total_bytes) / nullIf(sum(total_rows), 0)) as avg_row_size
from system.tables
where engine like '%MergeTree%'
group by database
order by sum(total_bytes) desc

3. Top Tables by Size

sql

select
    database,
    name,
    engine,
    formatReadableSize(total_bytes) as size,
    formatReadableSize(total_rows) as rows,
    formatReadableSize(total_bytes / nullIf(total_rows, 0)) as avg_row_size,
    (select count() from system.parts where database = t.database and table = t.name and active) as parts
from system.tables t
where engine like '%MergeTree%'
order by total_bytes desc
limit 30

4. Disk Usage by Path

sql

select
    substr(path, 1, position(path, '/store/')) as disk_path,
    formatReadableSize(sum(bytes_on_disk)) as used,
    count() as parts,
    uniq(database, table) as tables
from system.parts
where active
group by disk_path
order by sum(bytes_on_disk) desc

Compression Analysis

Overall Compression Ratio

sql

select
    formatReadableSize(sum(data_compressed_bytes)) as compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) as uncompressed,
    round(sum(data_uncompressed_bytes) / nullIf(sum(data_compressed_bytes), 0), 2) as ratio
from system.columns
where database not in ('system', 'INFORMATION_SCHEMA', 'information_schema')

Compression by Table

sql

select
    database,
    table,
    formatReadableSize(sum(data_compressed_bytes)) as compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) as uncompressed,
    round(sum(data_uncompressed_bytes) / nullIf(sum(data_compressed_bytes), 0), 2) as ratio,
    if(ratio < 2, 'Poor', if(ratio < 5, 'OK', 'Good')) as compression_quality
from system.columns
where database not in ('system', 'INFORMATION_SCHEMA', 'information_schema')
group by database, table
having sum(data_compressed_bytes) > 100000000  -- > 100MB
order by sum(data_compressed_bytes) desc
limit 30

Columns with Poor Compression

sql

select
    database,
    table,
    name as column,
    type,
    compression_codec,
    formatReadableSize(data_compressed_bytes) as compressed,
    formatReadableSize(data_uncompressed_bytes) as uncompressed,
    round(data_uncompressed_bytes / nullIf(data_compressed_bytes, 0), 2) as ratio
from system.columns
where database not in ('system', 'INFORMATION_SCHEMA', 'information_schema')
  and data_compressed_bytes > 100000000  -- > 100MB
  and data_uncompressed_bytes / nullIf(data_compressed_bytes, 0) < 2
order by data_compressed_bytes desc
limit 30

Solutions for poor compression:

•Add explicit codec: CODEC(ZSTD(3))
•For sequential integers: CODEC(Delta, ZSTD)
•For timestamps: CODEC(DoubleDelta, ZSTD)
•For low-cardinality strings: LowCardinality(String)

Part Size Analysis

Part Size Distribution

sql

select
    database,
    table,
    count() as parts,
    formatReadableSize(min(bytes_on_disk)) as min_part,
    formatReadableSize(median(bytes_on_disk)) as median_part,
    formatReadableSize(max(bytes_on_disk)) as max_part,
    formatReadableSize(sum(bytes_on_disk)) as total_size
from system.parts
where active and database not in ('system', 'INFORMATION_SCHEMA', 'information_schema')
group by database, table
order by sum(bytes_on_disk) desc
limit 30

Small Parts Detection

sql

select
    database,
    table,
    countIf(bytes_on_disk < 1000000) as tiny_parts_under_1mb,
    countIf(bytes_on_disk < 10000000) as small_parts_under_10mb,
    count() as total_parts,
    round(100.0 * countIf(bytes_on_disk < 10000000) / count(), 1) as small_pct
from system.parts
where active and database not in ('system', 'INFORMATION_SCHEMA', 'information_schema')
group by database, table
having small_pct > 50 and count() > 10
order by small_pct desc
limit 30

Wide vs Compact Parts

sql

select
    database,
    table,
    countIf(part_type = 'Wide') as wide_parts,
    countIf(part_type = 'Compact') as compact_parts,
    countIf(part_type = 'InMemory') as memory_parts,
    formatReadableSize(sumIf(bytes_on_disk, part_type = 'Wide')) as wide_size,
    formatReadableSize(sumIf(bytes_on_disk, part_type = 'Compact')) as compact_size
from system.parts
where active
group by database, table
having wide_parts > 0 or compact_parts > 0
order by wide_parts + compact_parts desc
limit 30

IO Analysis

Disk IO Metrics

sql

select
    metric,
    value,
    description
from system.asynchronous_metrics
where metric like '%Disk%' or metric like '%IO%' or metric like '%Read%' or metric like '%Write%'
order by metric

Recent IO Activity from Query Log

sql

select
    toStartOfFiveMinutes(event_time) as ts,
    count() as queries,
    formatReadableSize(sum(read_bytes)) as read_bytes,
    formatReadableSize(sum(written_bytes)) as written_bytes,
    sum(read_rows) as read_rows,
    sum(written_rows) as written_rows
from system.query_log
where type = 'QueryFinish'
  and event_time > now() - interval 1 hour
group by ts
order by ts desc

Queries with High IO

sql

select
    query_id,
    user,
    formatReadableSize(read_bytes) as read_bytes,
    formatReadableSize(written_bytes) as written_bytes,
    round(query_duration_ms / 1000, 1) as duration_sec,
    substring(query, 1, 80) as query_preview
from system.query_log
where type = 'QueryFinish'
  and event_date = today()
order by read_bytes + written_bytes desc
limit 20

System Logs Disk Usage

sql

with
    sum(bytes_on_disk) as log_bytes,
    (select arrayMin([free_space, unreserved_space]) from system.disks where name = 'default' limit 1) as free_bytes,
    log_bytes / (log_bytes + free_bytes) as ratio
select
    table,
    formatReadableSize(sum(bytes_on_disk)) as size,
    count() as parts,
    round(100.0 * sum(bytes_on_disk) / log_bytes, 1) as pct_of_logs
from system.parts
where database = 'system' and table like '%_log' and active
group by table
order by sum(bytes_on_disk) desc

Check: If system logs > 5% of disk, add TTL or reduce retention.

Detached Parts

sql

select
    database,
    table,
    reason,
    count() as count,
    formatReadableSize(sum(bytes_on_disk)) as size
from system.detached_parts
group by database, table, reason
order by sum(bytes_on_disk) desc

Common reasons:

•broken - Data corruption
•noquorum - Replication quorum not reached
•unexpected - Orphaned after failed operation
•clone - Leftover from ATTACH

Storage Policies

sql

select
    policy_name,
    volume_name,
    volume_priority,
    disks,
    max_data_part_size,
    move_factor
from system.storage_policies
order by policy_name, volume_priority

Tables by Storage Policy

sql

select
    storage_policy,
    count() as tables,
    formatReadableSize(sum(total_bytes)) as total_size
from system.tables
where engine like '%MergeTree%'
group by storage_policy
order by sum(total_bytes) desc

Problem Investigation

Disk Filling Up

sql

-- What's growing fastest?
select
    database,
    table,
    count() as new_parts,
    formatReadableSize(sum(bytes_on_disk)) as new_data
from system.parts
where modification_time > now() - interval 1 hour
  and active
group by database, table
order by sum(bytes_on_disk) desc
limit 20

Slow Disk Detection

sql

-- Check merge speeds as proxy for disk performance
select
    database,
    table,
    count() as merges,
    round(avg(duration_ms)) as avg_ms,
    formatReadableSize(sum(size_in_bytes)) as merged_bytes,
    round(sum(size_in_bytes) / nullIf(sum(duration_ms), 0) * 1000) as bytes_per_sec,
    formatReadableSize(bytes_per_sec) as speed
from system.part_log
where event_type = 'MergeParts'
  and event_date = today()
group by database, table
having count() > 5
order by bytes_per_sec asc
limit 20

Ad-Hoc Query Guidelines

Required Safeguards

sql

-- Always limit results
limit 100

-- For part_log
where event_date >= today() - 1

Key Tables

•system.disks - Disk configuration
•system.parts - Part storage details
•system.columns - Column compression
•system.storage_policies - Tiered storage
•system.detached_parts - Orphaned parts

Cross-Module Triggers

Finding	Load Module	Reason
Poor compression	`altinity-expert-clickhouse-schema`	Codec recommendations
Many small parts	`altinity-expert-clickhouse-merges`	Merge backlog
High write IO	`altinity-expert-clickhouse-ingestion`	Batch sizing
System logs large	`altinity-expert-clickhouse-logs`	TTL configuration
Slow disk + merges	`altinity-expert-clickhouse-merges`	Merge optimization

Settings Reference

Setting	Notes
`min_bytes_for_wide_part`	Threshold for Wide vs Compact parts
`min_rows_for_wide_part`	Row threshold for Wide parts
`max_bytes_to_merge_at_max_space_in_pool`	Max merge size
`prefer_not_to_merge`	Disable merges (emergency)