Optimize ScyllaDB performance for time-series data workloads with advanced configuration tuning

#!/usr/bin/env bash

set -euo pipefail

# ScyllaDB Time-Series Performance Optimization Script
# Configures ScyllaDB for optimal time-series workloads

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

print_status() { echo -e "${GREEN}[INFO]${NC} $1"; }
print_warning() { echo -e "${YELLOW}[WARN]${NC} $1"; }
print_error() { echo -e "${RED}[ERROR]${NC} $1"; }

usage() {
    echo "Usage: $0 [OPTIONS]"
    echo "Options:"
    echo "  -m, --memory-gb MEMORY    Total memory in GB (default: auto-detect)"
    echo "  -d, --datacenter NAME     Datacenter name (default: datacenter1)"
    echo "  -r, --replication NUM     Replication factor (default: 3)"
    echo "  -h, --help               Show this help"
    exit 1
}

cleanup() {
    if [ $? -ne 0 ]; then
        print_error "Script failed. Check logs and manual configuration may be required."
    fi
}
trap cleanup ERR

# Parse arguments
MEMORY_GB=""
DATACENTER="datacenter1"
REPLICATION=3

while [[ $# -gt 0 ]]; do
    case $1 in
        -m|--memory-gb) MEMORY_GB="$2"; shift 2 ;;
        -d|--datacenter) DATACENTER="$2"; shift 2 ;;
        -r|--replication) REPLICATION="$2"; shift 2 ;;
        -h|--help) usage ;;
        *) print_error "Unknown option: $1"; usage ;;
    esac
done

# Check prerequisites
if [[ $EUID -ne 0 ]]; then
    print_error "This script must be run as root"
    exit 1
fi

# Detect distribution
if [ -f /etc/os-release ]; then
    . /etc/os-release
    case "$ID" in
        ubuntu|debian) 
            PKG_MGR="apt"
            PKG_INSTALL="apt install -y"
            SCYLLA_CONFIG="/etc/scylla/scylla.yaml"
            ;;
        almalinux|rocky|centos|rhel|ol|fedora) 
            PKG_MGR="dnf"
            PKG_INSTALL="dnf install -y"
            SCYLLA_CONFIG="/etc/scylla/scylla.yaml"
            ;;
        amzn) 
            PKG_MGR="yum"
            PKG_INSTALL="yum install -y"
            SCYLLA_CONFIG="/etc/scylla/scylla.yaml"
            ;;
        *) 
            print_error "Unsupported distribution: $ID"
            exit 1
            ;;
    esac
else
    print_error "Cannot detect distribution"
    exit 1
fi

# Auto-detect memory if not provided
if [ -z "$MEMORY_GB" ]; then
    MEMORY_MB=$(free -m | awk 'NR==2{print $2}')
    MEMORY_GB=$((MEMORY_MB / 1024))
    print_status "Auto-detected ${MEMORY_GB}GB of system memory"
fi

# Calculate memory settings
MEMTABLE_HEAP=$((MEMORY_GB * 1024 * 3 / 4 / 2))
MEMTABLE_OFFHEAP=$((MEMORY_GB * 1024 * 3 / 4 / 2))

print_status "[1/8] Checking ScyllaDB installation..."
if ! systemctl is-enabled scylla-server >/dev/null 2>&1; then
    print_error "ScyllaDB is not installed or not enabled. Please install ScyllaDB first."
    exit 1
fi

print_status "[2/8] Applying system-level optimizations..."
# Apply kernel parameters
sysctl -w vm.swappiness=1
sysctl -w vm.dirty_background_ratio=5
sysctl -w vm.dirty_ratio=10
sysctl -w net.core.rmem_max=134217728
sysctl -w net.core.wmem_max=134217728

# Make persistent
cat > /etc/sysctl.d/99-scylla-timeseries.conf << 'EOF'
vm.swappiness = 1
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
net.core.rmem_max = 134217728
net.core.wmem_max = 134217728
EOF
chmod 644 /etc/sysctl.d/99-scylla-timeseries.conf

print_status "[3/8] Configuring I/O optimizations..."
# Set I/O scheduler for all block devices
for dev in /sys/block/sd*; do
    if [ -d "$dev" ]; then
        device=$(basename "$dev")
        echo deadline > "/sys/block/$device/queue/scheduler" 2>/dev/null || true
        echo 4096 > "/sys/block/$device/queue/read_ahead_kb" 2>/dev/null || true
    fi
done

# Make I/O settings persistent
cat > /etc/udev/rules.d/60-scylla-timeseries.rules << 'EOF'
ACTION=="add|change", KERNEL=="sd[a-z]", ATTR{queue/scheduler}="deadline"
ACTION=="add|change", KERNEL=="sd[a-z]", ATTR{queue/read_ahead_kb}="4096"
EOF
chmod 644 /etc/udev/rules.d/60-scylla-timeseries.rules

print_status "[4/8] Backing up ScyllaDB configuration..."
cp "$SCYLLA_CONFIG" "${SCYLLA_CONFIG}.backup.$(date +%Y%m%d_%H%M%S)"

print_status "[5/8] Optimizing ScyllaDB memory and performance settings..."
# Update ScyllaDB configuration
python3 << EOF
import yaml

config_file = "$SCYLLA_CONFIG"
try:
    with open(config_file, 'r') as f:
        config = yaml.safe_load(f) or {}
except:
    config = {}

# Memory settings
config['memtable_heap_space_in_mb'] = $MEMTABLE_HEAP
config['memtable_offheap_space_in_mb'] = $MEMTABLE_OFFHEAP
config['row_cache_size_in_mb'] = 512
config['key_cache_size_in_mb'] = 256

# Write performance
config['concurrent_writes'] = 128
config['concurrent_counter_writes'] = 128
config['memtable_flush_writers'] = 4

# Compaction settings
config['compaction_throughput_mb_per_sec'] = 256
config['compaction_large_partition_warning_threshold_mb'] = 1000
config['concurrent_compactors'] = 8

# Batch and timeout settings
config['batch_size_warn_threshold_in_kb'] = 64
config['batch_size_fail_threshold_in_kb'] = 640
config['write_request_timeout_in_ms'] = 10000
config['range_request_timeout_in_ms'] = 20000

# Monitoring
config['prometheus_port'] = 9180
config['prometheus_address'] = '0.0.0.0'

with open(config_file, 'w') as f:
    yaml.dump(config, f, default_flow_style=False)
EOF

chown scylla:scylla "$SCYLLA_CONFIG"
chmod 644 "$SCYLLA_CONFIG"

print_status "[6/8] Installing required packages..."
case "$PKG_MGR" in
    apt)
        apt update
        $PKG_INSTALL python3-pip
        ;;
    dnf|yum)
        $PKG_INSTALL python3-pip
        ;;
esac

print_status "[7/8] Restarting ScyllaDB service..."
systemctl restart scylla-server
sleep 10

# Wait for ScyllaDB to be ready
print_status "Waiting for ScyllaDB to be ready..."
for i in {1..30}; do
    if cqlsh -e "DESCRIBE KEYSPACES" >/dev/null 2>&1; then
        break
    fi
    if [ $i -eq 30 ]; then
        print_error "ScyllaDB failed to start properly"
        exit 1
    fi
    sleep 2
done

print_status "[8/8] Creating optimized keyspace and table schema..."
cqlsh -e "CREATE KEYSPACE IF NOT EXISTS timeseries_data 
WITH replication = {
  'class': 'NetworkTopologyStrategy',
  '$DATACENTER': $REPLICATION
} AND durable_writes = true;"

cqlsh -e "CREATE TABLE IF NOT EXISTS timeseries_data.metrics (
  metric_name text,
  time_bucket timestamp,
  timestamp timestamp,
  value double,
  tags map<text,text>,
  PRIMARY KEY ((metric_name, time_bucket), timestamp)
) WITH CLUSTERING ORDER BY (timestamp ASC)
AND compaction = {
  'class': 'TimeWindowCompactionStrategy',
  'compaction_window_unit': 'DAYS',
  'compaction_window_size': 1
}
AND gc_grace_seconds = 86400
AND default_time_to_live = 2592000;"

print_status "Verifying configuration..."
if systemctl is-active scylla-server >/dev/null 2>&1; then
    print_status "✓ ScyllaDB service is running"
else
    print_error "✗ ScyllaDB service is not running"
    exit 1
fi

if cqlsh -e "DESCRIBE KEYSPACE timeseries_data" >/dev/null 2>&1; then
    print_status "✓ Time-series keyspace created successfully"
else
    print_warning "⚠ Could not verify keyspace creation"
fi

echo -e "${GREEN}ScyllaDB time-series optimization complete!${NC}"
echo "Configuration backup saved to: ${SCYLLA_CONFIG}.backup.*"
echo "Prometheus monitoring available on port 9180"
echo "Time-series keyspace 'timeseries_data' created with optimized settings"
echo ""
echo "Next steps:"
echo "1. Configure your application to use the 'timeseries_data.metrics' table"
echo "2. Set up Prometheus to scrape metrics from port 9180"
echo "3. Monitor performance and adjust settings as needed"