clickhouse-operations

What this skill does

# ClickHouse Operations & Production Management

## When to Use This Skill

Use when asked to:
- Diagnose ClickHouse performance problems ("query is slow", "out of memory")
- Set up production monitoring and alerting
- Scale ClickHouse vertically (more resources) or horizontally (clustering)
- Implement backup and disaster recovery procedures
- Troubleshoot replication lag, disk space, or resource issues
- Plan capacity for growing data volumes

Do NOT use when:
- Optimizing specific queries (use clickhouse-query-optimization instead)
- Building materialized views (use clickhouse-materialized-views instead)
- Initial ClickHouse installation (this is for operations, not setup)

## Table of Contents

- [Purpose](#purpose)
- [Quick Start](#quick-start)
- [Key Operations](#key-operations)
  - [Step 1: Set Up Essential Monitoring](#step-1-set-up-essential-monitoring)
  - [Step 2: Diagnose Issues Using System Tables](#step-2-diagnose-issues-using-system-tables)
  - [Step 3: Implement Vertical Scaling (Single Server)](#step-3-implement-vertical-scaling-single-server)
  - [Step 4: Implement Horizontal Scaling (Cluster)](#step-4-implement-horizontal-scaling-cluster)
  - [Step 5: Set Up Backup & Disaster Recovery](#step-5-set-up-backup--disaster-recovery)
  - [Step 6: Tune Performance at Multiple Levels](#step-6-tune-performance-at-multiple-levels)
  - [Step 7: Troubleshoot Common Issues](#step-7-troubleshoot-common-issues)
  - [Step 8: Manage Resources Effectively](#step-8-manage-resources-effectively)
  - [Step 9: Production Deployment Checklist](#step-9-production-deployment-checklist)
- [Examples](#examples)
  - [Emergency Query Memory Pressure](#example-1-emergency-query-thats-using-too-much-memory)
  - [Disk Space Crisis](#example-2-disk-space-running-out-critical)
  - [Monitoring Setup](#example-3-setting-up-monitoring-for-new-cluster)
  - [Backup Implementation](#example-4-implementing-backup-with-30-day-retention)
- [References & Resources](#references--resources)
  - [Complete Query Reference](./references/queries.md) - Diagnostic and monitoring queries
  - [Extended Troubleshooting Guide](./references/troubleshooting.md) - Root cause analysis and solutions
  - [Scaling Case Studies](./examples/scaling-case-studies.md) - Real-world scaling decisions
  - [Monitoring Setup Examples](./examples/monitoring-setup.md) - Production-ready Prometheus/Grafana configs
- [Requirements](#requirements)

## Purpose

This skill provides comprehensive operational guidance for managing ClickHouse in production environments. It covers monitoring strategies, system diagnostics, scaling approaches, backup procedures, performance tuning, troubleshooting, and resource management to help DevOps and SRE teams maintain reliable, performant ClickHouse deployments.

## Quick Start

**Monitor current cluster health in 30 seconds:**

```bash
# 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;
"
```

## Key Operations

This section walks through the 9 essential steps for managing ClickHouse in production.

### Step 1: Set Up Essential Monitoring

ClickHouse requires monitoring of 9 critical metrics. Use these queries with your monitoring system (Prometheus, Grafana, Datadog, etc.):

**Query Latency & Throughput:**
```sql
-- 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 Rate & Memory Usage:**
```sql
-- 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 Usage & Background Operations:**
```sql
-- 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;
```

**Alert Thresholds:**
- Query latency p95 > 5 seconds
- Insert rate drops > 20% without load change
- Free disk < 20% of total
- Active parts > 1000 per table
- Memory usage > 80% of limit
- Replication lag > 60 seconds
- Failed query rate > 1%

### Step 2: Diagnose Issues Using System Tables

When problems occur, use these targeted queries to pinpoint root causes:

**Find Slow Queries:**
```sql
-- 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;
```

**Identify Problematic Queries:**
```sql
-- 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;
```

**Track Failed Queries:**
```sql
-- 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;
```

**Monitor Table Growth:**
```sql
-- 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;
```

### Step 3: Implement Vertical Scaling (Single Server)

Scale up individual ClickHouse servers by increasing CPU, memory, or disk resources:

**CPU Optimization:**
```sql
-- 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>
```

**Memory Management:**
```sql
-- 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