Skip to content
Maintained by AxonOps — production-grade documentation from engineers who operate distributed databases at scale Get Cassandra Help Get Kafka Help

Storage

The storage virtual tables provide visibility into disk usage, partition sizes, snapshots, and running SSTable operations like compaction.

disk_usage

Shows disk space used per table on this node.

Schema

VIRTUAL TABLE system_views.disk_usage (
    keyspace_name text,
    table_name text,
    mebibytes bigint,
    PRIMARY KEY ((keyspace_name, table_name))
)
Column Type Description
keyspace_name text Keyspace name
table_name text Table name
mebibytes bigint Disk space used (MiB)

Example Queries

-- Disk usage by table
SELECT keyspace_name, table_name, mebibytes
FROM system_views.disk_usage;

-- Tables over 10 GB (10240 MiB)
SELECT keyspace_name, table_name, mebibytes
FROM system_views.disk_usage
WHERE mebibytes > 10240;

max_partition_size

Shows the largest partition size per table. Critical for identifying oversized partitions.

Schema

VIRTUAL TABLE system_views.max_partition_size (
    keyspace_name text,
    table_name text,
    mebibytes bigint,
    PRIMARY KEY ((keyspace_name, table_name))
)
Column Type Description
keyspace_name text Keyspace name
table_name text Table name
mebibytes bigint Largest partition size (MiB)

Example Queries

-- Tables with large partitions
SELECT keyspace_name, table_name, mebibytes
FROM system_views.max_partition_size
WHERE mebibytes > 100;

Large Partition Impact

Size Status Impact
< 100 MiB Normal Healthy
100-500 MiB Warning May cause GC pressure, slow repairs
500 MiB - 1 GiB Critical Performance degradation likely
> 1 GiB Severe Requires data model redesign

max_sstable_size

Shows the largest SSTable size per table.

Schema

VIRTUAL TABLE system_views.max_sstable_size (
    keyspace_name text,
    table_name text,
    mebibytes bigint,
    PRIMARY KEY ((keyspace_name, table_name))
)

Example Queries

-- Tables with large SSTables
SELECT keyspace_name, table_name, mebibytes
FROM system_views.max_sstable_size
WHERE mebibytes > 1000;

max_sstable_duration

Shows the age of the oldest SSTable per table. Useful for identifying compaction issues.

Schema

VIRTUAL TABLE system_views.max_sstable_duration (
    keyspace_name text,
    table_name text,
    max_sstable_duration bigint,
    PRIMARY KEY ((keyspace_name, table_name))
)
Column Type Description
max_sstable_duration bigint Age of oldest SSTable (milliseconds)

Example Queries

-- Tables with old SSTables (potential compaction issues)
SELECT keyspace_name, table_name,
       max_sstable_duration / 86400000 AS days_old
FROM system_views.max_sstable_duration
WHERE max_sstable_duration > 604800000;  -- > 7 days

Old SSTables

Old SSTables may indicate: - Compaction falling behind - TWCS windows not expiring - Data written once and never updated

snapshots

Lists all snapshots on this node with size information.

Schema

VIRTUAL TABLE system_views.snapshots (
    name text,
    keyspace_name text,
    table_name text,
    created_at timestamp,
    ephemeral boolean,
    expires_at timestamp,
    size_on_disk bigint,
    true_size bigint,
    PRIMARY KEY (name, keyspace_name, table_name)
) WITH CLUSTERING ORDER BY (keyspace_name ASC, table_name ASC)
Column Type Description
name text Snapshot name
keyspace_name text Keyspace
table_name text Table
created_at timestamp Creation timestamp
true_size bigint Actual data size (bytes)
size_on_disk bigint Disk space used (may differ due to hardlinks)
expires_at timestamp Auto-expiration time (if TTL set)
ephemeral boolean Auto-cleanup snapshot

Equivalent nodetool command: nodetool listsnapshots

Example Queries

-- All snapshots
SELECT name, keyspace_name, table_name, created_at, true_size
FROM system_views.snapshots;

Filter by created_at in application to find old snapshots for cleanup.

sstable_tasks

Shows currently running SSTable operations (compaction, cleanup, scrub, etc.).

Schema

VIRTUAL TABLE system_views.sstable_tasks (
    keyspace_name text,
    table_name text,
    task_id timeuuid,
    completion_ratio double,
    kind text,
    progress bigint,
    total bigint,
    sstables int,
    target_directory text,
    unit text,
    PRIMARY KEY (keyspace_name, table_name, task_id)
) WITH CLUSTERING ORDER BY (table_name ASC, task_id ASC)
Column Type Description
keyspace_name text Keyspace
table_name text Table
task_id timeuuid Unique task identifier
kind text Operation type (lowercase: compaction, cleanup, scrub, etc.)
progress bigint Bytes/rows processed
total bigint Total bytes/rows to process
completion_ratio double Progress percentage (0.0-1.0)
sstables int Number of SSTables involved
unit text Unit of progress (bytes, keys, etc.)
target_directory text Output directory

Equivalent nodetool command: nodetool compactionstats

Example Queries

-- All running tasks
SELECT keyspace_name, table_name, kind, completion_ratio, sstables
FROM system_views.sstable_tasks;

-- Compaction progress (note: kind values are lowercase)
SELECT keyspace_name, table_name, kind,
       progress, total,
       completion_ratio * 100 AS percent_complete
FROM system_views.sstable_tasks
WHERE kind = 'compaction';

-- Large compaction operations
SELECT keyspace_name, table_name, sstables, total / 1073741824 AS total_gb
FROM system_views.sstable_tasks
WHERE total > 10737418240;  -- > 10 GB

Alerting Rules

Large Partitions

-- Alert: Partitions exceeding threshold
SELECT keyspace_name, table_name, mebibytes
FROM system_views.max_partition_size
WHERE mebibytes > 100;

Old Snapshots

-- Alert: Snapshots older than 7 days
SELECT name, created_at
FROM system_views.snapshots
WHERE created_at < toTimestamp(now()) - 604800s;

Stalled Compactions

-- Alert: Long-running compactions (> 1 hour with < 50% progress)
-- Requires application logic to track over time
SELECT keyspace_name, table_name, kind, completion_ratio
FROM system_views.sstable_tasks
WHERE kind = 'compaction';

Disk Usage Growth

-- Current disk usage for trending
SELECT keyspace_name, table_name, mebibytes
FROM system_views.disk_usage;

Monitoring Dashboard Query

-- Disk usage by table
SELECT keyspace_name, table_name, mebibytes
FROM system_views.disk_usage;

-- Largest partitions by table
SELECT keyspace_name, table_name, mebibytes
FROM system_views.max_partition_size;

-- Largest SSTables by table
SELECT keyspace_name, table_name, mebibytes
FROM system_views.max_sstable_size;

Filter out system keyspaces in application if needed.