Finding
Unbucketed telemetry partition key and STCS compaction push LOCAL_QUORUM p99 to 74ms
Querying system.large_partitions exposed 19 partitions exceeding 350 MiB (2.1M rows each) pinned to Seastar Shard 3. Meanwhile, default STCS merged 90-day TTL SSTables with fresh writes. Bucketing by day + shard salt and switching to TWCS cut p99 to 1.1ms.
1. CQL diagnostic query & refactored Tablet + TWCS schema with partition bucketing
cqlsh · large partition audit & bucketed TWCS table
-- 1. Identify partitions > 100 MB causing Seastar reactor stalls
SELECT keyspace_name, table_name, partition_key, partition_size, rows
FROM system.large_partitions
WHERE partition_size > 104857600 ALLOW FILTERING;
-- 2. Enable ScyllaDB 6.x Raft Tablets on the keyspace across 3 AZ racks
CREATE KEYSPACE IF NOT EXISTS telemetry_prod
WITH replication = {
'class': 'NetworkTopologyStrategy',
'us-east-1': 3
} AND tablets = {'enabled': true, 'initial': 128};
-- 3. Composite (tenant_id, day_bucket, shard_salt) key + TWCS 1-day window
CREATE TABLE telemetry_prod.device_events (
tenant_id uuid,
day_bucket int, -- e.g. 20261109
shard_salt tinyint, -- 0..15 deterministic hash mod
event_ts timestamp,
device_id text,
payload blob,
PRIMARY KEY ((tenant_id, day_bucket, shard_salt), event_ts, device_id)
) WITH CLUSTERING ORDER BY (event_ts DESC, device_id ASC)
AND compaction = {
'class': 'TimeWindowCompactionStrategy',
'compaction_window_unit': 'DAYS',
'compaction_window_size': 1
}
AND default_time_to_live = 7776000
AND gc_grace_seconds = 86400;2. Workload Prioritization service levels (OLTP vs Spark analytics)
cqlsh · Seastar service level isolation
CREATE SERVICE LEVEL sl_realtime_oltp WITH shares = 1000 AND timeout = 500ms AND workload_type = 'interactive'; CREATE SERVICE LEVEL sl_spark_batch WITH shares = 100 AND timeout = 10s AND workload_type = 'batch'; ATTACH SERVICE LEVEL sl_realtime_oltp TO app_checkout_role; ATTACH SERVICE LEVEL sl_spark_batch TO spark_etl_role;







