dawiddutoit/custom-claude/clickhouse-operations

# SSH to ClickHouse server
clickhouse-client --query "
SELECT
    formatReadableSize(total_space) as total_disk,
    formatReadableSize(free_space) as free_disk,
    round(free_space / total_space * 100, 2) as free_percent
FROM system.disks;
 
-- Check running queries
SELECT query_id, user, elapsed, memory_usage / 1024 / 1024 as memory_mb
FROM system.processes
LIMIT 5;
 
-- Check recent errors
SELECT name, value as count, last_error_time
FROM system.errors
WHERE value > 0
ORDER BY last_error_time DESC
LIMIT 5;
"

-- p95 latency (replace 0.95 for p99 = 0.99)
SELECT quantile(0.95)(query_duration_ms) as p95_latency_ms
FROM system.query_log
WHERE event_date >= today()
  AND type = 'QueryFinish'
  AND query NOT LIKE '%system.%';
 
-- Queries per second (current)
SELECT COUNT() / max(CAST(elapsed as Float64)) as qps
FROM system.processes
WHERE query NOT LIKE '%system.%';

-- Insert throughput (rows/second)
SELECT
    table,
    SUM(rows) / SUM(CAST(query_duration_ms as Float64)) * 1000 as rows_per_second
FROM system.query_log
WHERE query LIKE 'INSERT%'
  AND type = 'QueryFinish'
  AND event_date >= today()
GROUP BY table;
 
-- Memory usage
SELECT
    formatReadableSize(value) as current_memory,
    formatReadableSize(80000000000) as target_limit  -- Adjust to 80% of your RAM
FROM system.metrics
WHERE metric = 'MemoryTracking';

-- Disk utilization
SELECT
    formatReadableSize(free_space) as free,
    formatReadableSize(total_space) as total,
    round(free_space / total_space * 100, 2) as free_percent
FROM system.disks;
 
-- Merge pressure (high pending merges = slow inserts)
SELECT database, table, COUNT() as parts_count
FROM system.parts
WHERE active = 1
GROUP BY database, table
HAVING parts_count > 1000
ORDER BY parts_count DESC;

-- Slowest queries (last 24 hours)
SELECT
    event_time,
    query_duration_ms,
    read_rows,
    formatReadableSize(read_bytes) as bytes_read,
    formatReadableSize(memory_usage) as peak_memory,
    query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date >= today() - 1
  AND query NOT LIKE '%system.%'
ORDER BY query_duration_ms DESC
LIMIT 20;

-- Most resource-intensive queries
SELECT
    query,
    COUNT() as exec_count,
    AVG(query_duration_ms) as avg_duration_ms,
    MAX(memory_usage) / 1024 / 1024 as peak_memory_mb,
    SUM(read_bytes) / 1024 / 1024 / 1024 as total_gb_read
FROM system.query_log
WHERE event_date >= today() - 7
  AND type = 'QueryFinish'
GROUP BY query
ORDER BY total_gb_read DESC
LIMIT 20;

-- Error breakdown
SELECT
    exception,
    COUNT() as count,
    MAX(event_time) as last_error
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing'
  AND event_date >= today() - 1
GROUP BY exception
ORDER BY count DESC;

-- Largest tables and growth rate
SELECT
    database,
    name as table,
    SUM(rows) as total_rows,
    formatReadableSize(SUM(bytes_on_disk)) as disk_size,
    formatReadableSize(SUM(data_compressed_bytes)) as compressed,
    round(SUM(data_uncompressed_bytes) / SUM(data_compressed_bytes), 1) as compression_ratio
FROM system.parts
WHERE active = 1
  AND database NOT IN ('system', 'information_schema')
GROUP BY database, name
ORDER BY bytes_on_disk DESC;

-- Check current thread setting
SELECT value FROM system.settings WHERE name = 'max_threads';
 
-- Optimize for your hardware
SET max_threads = 16;  -- Set to CPU core count (or slightly less)
 
-- Permanent setting in /etc/clickhouse-server/config.xml
-- <max_threads>16</max_threads>

-- Current server memory limit
SELECT value FROM system.settings WHERE name = 'max_server_memory_usage';
 
-- Set per-query limit (10 GB example)
SET max_memory_usage = 10000000000;
 
-- Production config (/etc/clickhouse-server/config.xml):
-- <max_server_memory_usage>64000000000</max_server_memory_usage>  <!-- 64 GB = 80% of 80GB RAM -->
-- <max_concurrent_queries>100</max_concurrent_queries>
 
-- Monitor current usage
SELECT formatReadableSize(value) as memory_usage
FROM system.metrics
WHERE metric = 'MemoryTracking';

<!-- In /etc/clickhouse-server/config.xml -->
<merge_tree>
    <!-- Enable direct I/O for large table scans -->
    <min_bytes_to_use_direct_io>10485760</min_bytes_to_use_direct_io>  <!-- 10 MB -->
</merge_tree>
 
<!-- Filesystem recommendations: Use NVMe SSD for best performance -->
<!-- Separate data and logs to different disks if possible -->

<!-- Add to /etc/clickhouse-server/config.xml on all nodes -->
<remote_servers>
    <production_cluster>
        <shard>
            <replica>
                <host>node1.example.com</host>
                <port>9000</port>
            </replica>
            <replica>
                <host>node2.example.com</host>
                <port>9000</port>
            </replica>
        </shard>
        <shard>
            <replica>
                <host>node3.example.com</host>
                <port>9000</port>
            </replica>
            <replica>
                <host>node4.example.com</host>
                <port>9000</port>
            </replica>
        </shard>
    </production_cluster>
</remote_servers>

-- Step 1: Create local table on each shard
CREATE TABLE events_local ON CLUSTER 'production_cluster' (
    user_id UInt32,
    event_type String,
    timestamp DateTime,
    properties String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
ORDER BY (user_id, timestamp)
PARTITION BY toDate(timestamp);
 
-- Step 2: Create distributed table (insert point)
CREATE TABLE events ON CLUSTER 'production_cluster' AS events_local
ENGINE = Distributed('production_cluster', default, events_local, rand());
 
-- Step 3: Use distributed table for inserts
INSERT INTO events VALUES (123, 'purchase', now(), '{"amount": 99.99}');
 
-- Step 4: Query across all shards
SELECT COUNT() as total_events FROM events;
SELECT topK(10)(event_type) FROM events;  -- Top 10 event types

-- Check replication status
SELECT
    database,
    table,
    total_replicas,
    active_replicas,
    absolute_delay
FROM system.replicas
ORDER BY absolute_delay DESC;
 
-- Verify all nodes responding
SELECT hostname(), version(), uptime() as uptime_seconds
FROM clusterAllReplicas('production_cluster', system.one);

#!/bin/bash
# backup_clickhouse.sh - Daily automated backup
 
BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups/clickhouse/$BACKUP_DATE"
CH_DATA_DIR="/var/lib/clickhouse"
 
mkdir -p "$BACKUP_DIR"
 
# Optional: Stop ClickHouse for consistent backup
# sudo systemctl stop clickhouse-server
 
# Create backup with rsync (preserve hard links)
rsync -av --hard-links "$CH_DATA_DIR/" "$BACKUP_DIR/"
 
# Optional: Restart ClickHouse
# sudo systemctl start clickhouse-server
 
# Keep only last 30 days of backups
find /backups/clickhouse -maxdepth 1 -type d -mtime +30 -exec rm -rf {} \;
 
echo "Backup completed to $BACKUP_DIR"

-- Backup single table
BACKUP TABLE events TO Disk('backups', 'events_' || toString(today()) || '.zip');
 
-- Backup entire database
BACKUP DATABASE default TO Disk('backups', 'db_' || toString(today()) || '.zip');
 
-- Restore table
RESTORE TABLE events FROM Disk('backups', 'events_20240115.zip');
 
-- Restore database
RESTORE DATABASE default FROM Disk('backups', 'db_20240115.zip');

#!/bin/bash
# Restore complete ClickHouse instance
 
set -e
 
NEW_SERVER="new.example.com"
BACKUP_DATE="20240115"
 
echo "Starting disaster recovery..."