Domain 8Data InfrastructureINTERMEDIATE

ClickHouse Architecture, Operations, and SRE Troubleshooting Guide

A comprehensive production guide to ClickHouse database administration, covering columnar storage internals, MergeTree engine mechanics, query lifecycles, system table diagnostics, operational workflows, performance tuning, and step-by-step SRE troubleshooting.

Domain: Domain 8: Data Infrastructure•Updated: 2026-11-03
#linux#sysadmin#clickhouse#database#sre

1. Overview

ClickHouse is an open-source, column-oriented Online Analytical Processing (OLAP) database management system designed for real-time analytical reporting and aggregations across billions of rows. Unlike traditional row-oriented Relational Database Management Systems (RDBMS) like PostgreSQL or MySQL—which store complete tuples together on disk—ClickHouse organizes data by column. This structural distinction delivers high data compression ratios and allows queries to read only the specific attributes requested by a query rather than scanning whole rows.

ClickHouse is not a general-purpose transactional (OLTP) database. It does not support complex multi-table ACID transactions or high-frequency point updates and deletes. Instead, it is optimized for high-throughput, append-heavy analytical workloads.

Row-Oriented Storage (OLTP: Postgres, MySQL)
+-------------------------------------------------------+
| Row 1: [ID, Timestamp, User_ID, Action, IP_Address]   |
| Row 2: [ID, Timestamp, User_ID, Action, IP_Address]   |
+-------------------------------------------------------+
--> Scanning one column requires reading the full row off disk.

Column-Oriented Storage (OLAP: ClickHouse)
+-------------------------------------------------------+
| Column ID:        [Row 1, Row 2, Row 3, ...]          |
| Column Timestamp: [Row 1, Row 2, Row 3, ...]          |
| Column User_ID:   [Row 1, Row 2, Row 3, ...]          |
+-------------------------------------------------------+
--> Scanning one column reads ONLY that specific column file off disk.

Common Production Use Cases

  • Telemetry and Log Analytics: Ingesting millions of structured log events per second for real-time security (SIEM) and SRE observability.
  • Clickstream and Web Analytics: Aggregating user actions, session metrics, and attribution funnels.
  • Financial and Time-Series Data: Analyzing tick data, transaction histories, and metrics over time windows.
  • Real-Time Dashboards: Powering business intelligence interfaces that demand sub-second aggregation responses on massive datasets.

2. Architecture and Mental Model

To operate ClickHouse reliably, a Site Reliability Engineer (SRE) or Linux System Administrator must understand how data moves from memory to disk and how queries are parsed, pruned, and executed.

flowchart TD
    Client[Client / App] -->|HTTP:8123 or TCP:9000| Listener[ClickHouse Listener]
    Listener --> Parser[Parser & Query Planner]
    
    subgraph Data Execution & Pruning
        Parser --> Pruner[Partition & Primary Key Pruner]
        Pruner -->|Select Active Parts| Parts[Data Parts on Disk]
        Parts -->|Read Columns Only| ColumnReader[Vectorized Column Reader]
        ColumnReader -->|Parallel Processing| Memory[In-Memory Aggregation Engine]
    end
    
    Memory -->|Stream Results| Client
    Memory -->|Log Execution Metrics| QueryLog[system.query_log]
    
    subgraph Background Disk Operations
        Insert[INSERT Operation] -->|Write Immutable Block| NewPart[New Immutable Data Part]
        NewPart -->|Background Threads| Merges[Background Merges Engine]
        Merges -->|Consolidate| BigPart[Merged Large Part]
    end

The MergeTree Storage Engine Mechanics

The foundation of ClickHouse analytical storage is the MergeTree table engine family.

  1. Immutable Parts: When data is inserted into a MergeTree table, ClickHouse does not append rows to existing files. Instead, every INSERT statement creates a set of new, immutable directory structures called data parts on disk.
  2. Background Merging: To prevent disk fragmentation and maintain fast search performance, ClickHouse runs asynchronous background merge threads. These background processes select several smaller data parts and merge them into a single, larger, sorted data part, deleting the original unmerged parts once the new part is active.
  3. Partitioning and Sorting Keys: Data inside each part is physically partitioned (frequently by date or month) and sorted according to the table’s primary/sorting key.
  4. Sparse Indexing: ClickHouse builds a sparse primary index instead of a dense index. It indexes one row mark for every granule (typically 8,192 rows). This design allows the entire primary index to fit inside RAM, enabling high-speed index pruning.

Query Execution Lifecycle

  1. Connection Handling: The client connects over native TCP (port 9000) or HTTP (port 8123).
  2. Parsing and Planning: The server parses the SQL, checks user privileges, and constructs an execution plan.
  3. Pruning: The engine checks the query WHERE filters against partition keys and primary key sparse index marks to discard irrelevant data parts and granules from disk reads.
  4. Columnar Extraction: The engine streams data from disk into memory only for the specific columns involved in the selection, filtering, and aggregation stages.
  5. Vectorized Execution: Processing takes place in parallel threads using SIMD CPU instructions across columnar memory arrays.
  6. Limits and Logging: If memory or time limits (max_memory_usage or max_execution_time) are breached, the engine terminates the query. Query execution metrics are flushed to system.query_log.

Replication and Consensus Architecture

Replication in ClickHouse occurs at the table level using the ReplicatedMergeTree engine family. Data replication is asynchronous and coordinated through ClickHouse Keeper (or Apache ZooKeeper).

  • ClickHouse Keeper / ZooKeeper: Holds metadata, consensus state, partition definitions, and the replication log. It does not store actual table data.
  • Replication Log: Nodes write intent logs to Keeper. Target replicas fetch missing data parts directly from peer nodes over native TCP (port 9000).
  • Read-Only Fail-Safe: If a node loses connection to Keeper or falls critically behind, it enters a readonly mode to protect data consistency.

3. Core Concepts and Terminology

  • Columnar Storage: Storing each table column in separate files on disk, enabling vectorization and high compression ratios.
  • Data Part: An immutable directory on disk containing column binary files (.bin), mark files (.mrk2), and primary index structures (primary.idx).
  • Granule: The smallest unindivisible block of data read during a query scan (default is 8,192 consecutive rows).
  • Sparse Index: An index containing one index entry per granule, allowing large datasets to be indexed in lightweight server memory.
  • MergeTree: The core table engine providing disk sorting, primary key indexing, partitioning, and asynchronous part merging.
  • Partitions: Top-level logical and physical divisions of data on disk (e.g., YYYYMM), enabling coarse data dropping and lifecycle management.
  • Mutation: An asynchronous background update or delete operation (ALTER TABLE ... UPDATE / DELETE) that rewrites data parts.
  • ClickHouse Keeper: A C++ implementation of the ZooKeeper coordination protocol built natively into ClickHouse for replication state handling.

4. Installation and Version Management

Prerequisites

  • OS: Linux x86_64 or ARM64 (Ubuntu 22.04+, Debian 11+, RHEL/AlmaLinux 8+).
  • CPU: Modern x86_64 processor supporting SSE 4.2 / AVX instruction sets.
  • RAM: Minimum 4 GB; 32 GB+ recommended for production workloads.

Official Package Manager Installation

Debian / Ubuntu (apt)

# Install dependencies and add official GPG key
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg
curl -fsSL https://packages.clickhouse.com/gpg | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg

# Add repository entry
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list

# Update repository and install server + client packages
sudo apt-get update
sudo apt-get install -y clickhouse-server clickhouse-client

RHEL / AlmaLinux / Rocky Linux (dnf)

# Add repository
sudo dnf install -y yum-utils
sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo

# Install ClickHouse packages
sudo dnf install -y clickhouse-server clickhouse-client

Verifying Installation and Version Management

# Inspect installed binary versions via CLI
clickhouse-server --version
clickhouse-client --version

# Run inline version query against local engine
clickhouse-client -q "SELECT version()"

5. Configuration Files and Directory Structure

ClickHouse uses XML or YAML files for node configuration, maintaining separation between system options and user privileges.

Directory Layout & Purpose

File / Directory Purpose Important Details
/etc/clickhouse-server/config.xml Primary server daemon configuration Controls listening IP interfaces, data directories, ports, logging paths, and memory limits.
/etc/clickhouse-server/config.d/ Drop-in directory for server configuration overrides Modular XML/YAML files loaded alphabetically after config.xml. Preferred for automated config management.
/etc/clickhouse-server/users.xml Default user accounts, profile definitions, and quotas Specifies default access profiles, query limits, and credentials.
/etc/clickhouse-server/users.d/ Drop-in directory for user configurations Contains user credential overrides and profile modifications. Protected with strict file permissions (0600).
/var/lib/clickhouse/ Primary data storage root path Contains directory trees for table data parts, metadata, and temporary processing blocks.
/var/lib/clickhouse/data/ Table data directories Houses active and detached immutable data parts grouped by database and table names.
/var/lib/clickhouse/metadata/ DDL schema metadata storage Stores .sql file representations of database schemas and table structure definitions.
/var/log/clickhouse-server/ Application log output directory Houses main operational logs (clickhouse-server.log) and error-only outputs (clickhouse-server.err.log).

Configuration Validation and Reloading

ClickHouse validates XML formatting at launch. Syntax errors cause service startup failures.

# Validate server XML configuration files without starting the daemon
clickhouse-server --config-file=/etc/clickhouse-server/config.xml --test-config

6. Service Management and Common Operations

Systemd Operations

# Check running service status
systemctl status clickhouse-server

# Start, stop, or restart the daemon
sudo systemctl start clickhouse-server
sudo systemctl stop clickhouse-server
sudo systemctl restart clickhouse-server

# Dynamic reload of configuration files (users.xml, config.d changes without restarting network sockets)
sudo systemctl reload clickhouse-server

Inspecting Listening Ports

ClickHouse listens on two primary network interfaces by default:

  • HTTP Protocol (Port 8123): Web clients, curl queries, JDBC HTTP drivers, monitoring scrapers.
  • Native TCP Protocol (Port 9000): clickhouse-client CLI, inter-server replication traffic.
# Inspect bound ports and network listeners
ss -tlnp | grep -E '8123|9000'

Server Health Checks

# Issue an HTTP health ping to test local daemon availability
curl http://127.0.0.1:8123/ping
# Expected output: Ok.

7. Common Administrative Tasks and Workflows

Task 1: Creating a Partitioned MergeTree Table

Objective

Create a telemetry event table partitioned by month and sorted by user ID and timestamp to enable fast primary key scans.

CREATE DATABASE IF NOT EXISTS telemetry;

CREATE TABLE telemetry.user_events
(
    event_date Date,
    event_timestamp DateTime,
    user_id UInt64,
    event_type String,
    payload String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (user_id, event_timestamp);

Validation

clickhouse-client -q "SHOW CREATE TABLE telemetry.user_events"

Task 2: Adding Dedicated Users via users.d/ Drop-in

Objective

Provision an analytical read-only user without modifying the core /etc/clickhouse-server/users.xml file.

  1. Create a drop-in configuration file /etc/clickhouse-server/users.d/analytics_user.xml:
<clickhouse>
    <users>
        <analytics_readonly>
            <password>SecurePassword123</password>
            <profile>readonly</profile>
            <quota>default</quota>
            <allow_databases>
                <database>telemetry</database>
            </allow_databases>
        </analytics_readonly>
    </users>
</clickhouse>
  1. Set file permissions and test access:
sudo chmod 600 /etc/clickhouse-server/users.d/analytics_user.xml
sudo chown clickhouse:clickhouse /etc/clickhouse-server/users.d/analytics_user.xml

# Connect with the new credentials
clickhouse-client -u analytics_readonly --password SecurePassword123 -q "SELECT 1"

Task 3: Triggering Table Optimization (Maintenance Window)

Objective

Force background merges across active table parts to consolidate storage before scheduled maintenance.

-- Force merge of parts across all partitions (Use with caution on large tables!)
OPTIMIZE TABLE telemetry.user_events FINAL;

8. CLI Command Reference and Cheatsheet

Core CLI Tool Usage

Command Purpose Important Flags / Arguments Practical Example Explanation
clickhouse-client Interactive native CLI client -h (host), -u (user), --password, -q (query), -m (multiline) clickhouse-client -h 127.0.0.1 -u default --password Connects interactively to local or remote ClickHouse server over native TCP port 9000.
curl HTTP endpoint query interface ?query=..., http://<host>:8123/ping curl '[http://127.0.0.1:8123/?query=SELECT+1](http://127.0.0.1:8123/?query=SELECT+1)' Executes inline SQL queries over HTTP API.
clickhouse-server Core server daemon execution & syntax tester --config-file, --test-config clickhouse-server --config-file=/etc/clickhouse-server/config.xml --test-config Tests validity of XML configuration structures without starting daemon.
systemctl Daemon process lifecycle management status, start, stop, restart, reload systemctl status clickhouse-server Controls systemd unit states.
ss Socket state verification -t (tcp), -l (listen), -n (numeric), -p (process) `ss -tlnp grep -E ’8123

Administrative SQL Commands and System Table Queries

-- 1. Inspect table part count and total disk consumption
SELECT database, table, count() AS total_parts, sum(bytes_on_disk) AS total_bytes
FROM system.parts
WHERE active = 1
GROUP BY database, table
ORDER BY total_bytes DESC;

-- 2. Inspect currently executing queries and memory consumption
SELECT query_id, user, elapsed, memory_usage, query
FROM system.processes
ORDER BY memory_usage DESC;

-- 3. Query historical slow operations from query log
SELECT type, query_duration_ms, memory_usage, read_rows, query
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 HOUR AND type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10;

-- 4. Check cluster replication lag and read-only status
SELECT database, table, is_readonly, queue_size, absolute_delay
FROM system.replicas;

-- 5. Terminate runaway query exceeding memory bounds
KILL QUERY WHERE query_id = 'c98a58f2-2b63-490b-93e1-d3080e77413a';

9. Logs, Monitoring and Diagnostics

System Log Paths

  • Primary Server Log: /var/log/clickhouse-server/clickhouse-server.log
  • Error Log: /var/log/clickhouse-server/clickhouse-server.err.log
  • Systemd Journal Stream: journalctl -u clickhouse-server -f

Diagnostic System Tables

+------------------+-------------------------------------------------------------------------+
| System Table     | Operational Diagnostic Focus                                            |
+------------------+-------------------------------------------------------------------------+
| system.parts     | Track active data parts, block sizes, and part accumulation.           |
| system.merges    | Monitor active background part merges, progress %, and memory/IO state. |
| system.processes | Real-time active queries, RAM consumption, and execution durations.     |
| system.query_log | Historical query metrics, read rows, execution times, and errors.       |
| system.replicas  | Replication lag, queue sizes, and read-only cluster node states.        |
| system.disks     | Storage volume paths, total capacity, and available free space.        |
+------------------+-------------------------------------------------------------------------+

Real-Time Diagnostic Commands

# Tail log for errors and exceptions
tail -f /var/log/clickhouse-server/clickhouse-server.log | grep -E "Error|Exception"

# Check active disk capacity across all ClickHouse storage volumes
clickhouse-client -q "SELECT name, path, free_space, total_space FROM system.disks"

10. Troubleshooting Guide and Common Failure Scenarios

Problem 1: Server Not Accepting Connections / Socket Port Closed

Symptoms

clickhouse-client or curl connections return Connection refused or time out.

curl: (7) Failed to connect to 127.0.0.1 port 8123: Connection refused

Possible Causes

  1. clickhouse-server process is stopped or crashed.
  2. listen_host in config.xml is set strictly to 127.0.0.1, blocking external network interfaces.
  3. Local firewall rules (iptables / ufw) block incoming TCP ports 8123 or 9000.

Investigation and Diagnosis

  1. Check daemon unit state:
systemctl status clickhouse-server
  1. If service is running, check bound port listeners:
ss -tlnp | grep -E '8123|9000'

Interpretation: If output displays 127.0.0.1:8123, the server will not accept external connections. 3. Inspect config.xml or drop-in XML settings for listen_host settings:

grep -rn "listen_host" /etc/clickhouse-server/

Root Cause Identification

The listener configuration restricts access to localhost, or local systemd service is inactive.

Resolution

Edit or add a drop-in file /etc/clickhouse-server/config.d/listen.xml to allow external IP interfaces:

<clickhouse>
    <listen_host>::</listen_host>
</clickhouse>

Restart the service:

sudo systemctl restart clickhouse-server

Verification

curl http://127.0.0.1:8123/ping

Prevention

Include explicit network configuration drop-in files in infrastructure-as-code automation (e.g., Ansible).


Problem 2: Query Terminated — Memory Limit Exceeded

Symptoms

Queries fail with message Code: 241. DB::Exception: Memory limit (for query) exceeded.

Possible Causes

  1. Query scans unindexed columns across large datasets, triggering massive in-memory aggregation buffers.
  2. Aggregation setting max_memory_usage is exceeded by concurrent heavy queries.

Investigation and Diagnosis

  1. Identify high-memory running queries in system.processes:
clickhouse-client -q "SELECT query_id, user, memory_usage, elapsed, query FROM system.processes ORDER BY memory_usage DESC"
  1. Inspect query execution logs in system.query_log to analyze historical memory consumption:
clickhouse-client -q "SELECT type, memory_usage, query FROM system.query_log WHERE type = 'ExceptionBeforeStart' OR type = 'QueryFinish' ORDER BY memory_usage DESC LIMIT 5"

Root Cause Identification

The query attempts to aggregate data without using index pruning, exceeding memory thresholds configured in users.xml.

Resolution

  1. Terminate the offending query:
KILL QUERY WHERE query_id = 'offending_id';
  1. Increase query memory limit for the execution profile, or enable external disk spillover for large aggregations:
SET max_memory_usage = 10000000000; -- 10GB limit
SET max_bytes_before_external_group_by = 5000000000; -- Spill aggregations to disk at 5GB

Verification

Rerun the query with disk spillover enabled and verify execution completion in system.query_log.

Prevention

Define non-zero values for max_bytes_before_external_group_by in baseline users.xml profiles to handle memory pressure gracefully.


Problem 3: Too Many Parts in Table / Insert Operations Blocked

Symptoms

INSERT queries are rejected with error Code: 252. DB::Exception: Too many parts in table.

DB::Exception: Too many parts (301) in all data parts in table telemetry.user_events. Merges are processing significantly slower than inserts.

Possible Causes

High-frequency single-row inserts create new parts faster than background merges can combine them.

Investigation and Diagnosis

  1. Check active part count per table:
clickhouse-client -q "SELECT database, table, count() AS part_count FROM system.parts WHERE active = 1 GROUP BY database, table ORDER BY part_count DESC"

Interpretation: If active part counts exceed 300 per partition, merges are falling behind. 2. Inspect active background merges:

clickhouse-client -q "SELECT database, table, elapsed, progress FROM system.merges"

Root Cause Identification

Client applications perform frequent, unbatched single-row inserts.

Resolution

  1. Change application ingest design to batch inserts (e.g., 10,000 to 100,000 rows per insert block).
  2. Issue manual optimization during off-peak maintenance window:
OPTIMIZE TABLE telemetry.user_events FINAL;

Verification

Re-query system.parts to ensure active part count drops below 50 per partition.

Prevention

Enforce client-side buffering or use buffering proxies (like Vector or Kafka Engine tables) prior to writing to ClickHouse.


Problem 4: Replication Lag or Read-Only Replica Mode

Symptoms

Replica table blocks writes with error Table is in read-only mode, or queries serve stale data.

Possible Causes

  1. Replica lost connectivity to ClickHouse Keeper cluster.
  2. Local storage on replica node is full.
  3. Replication queue holds stuck operations.

Investigation and Diagnosis

  1. Query replica status system table:
clickhouse-client -q "SELECT database, table, is_readonly, queue_size, absolute_delay FROM system.replicas"

Interpretation: is_readonly = 1 confirms loss of coordination or read-only fail-safe state. 2. Check log files for Keeper session timeouts:

tail -n 100 /var/log/clickhouse-server/clickhouse-server.log | grep -i "keeper"

Root Cause Identification

Network disruption between ClickHouse nodes and Keeper cluster caused session expiration, triggering read-only mode.

Resolution

  1. Resolve network firewall issues or restart Keeper nodes.
  2. If Keeper is healthy, reload replica state:
SYSTEM RESTART REPLICA telemetry.user_events;

Verification

Verify is_readonly returns 0 and queue_size trends toward zero.

Prevention

Deploy ClickHouse Keeper across a 3-node or 5-node dedicated quorum with isolated network connectivity.


Problem 5: Disk Space Full (No space left on device)

Symptoms

All write operations fail, and server logs report storage write failure.

DB::Exception: No space left on device (errno 28)

Possible Causes

  1. Large ingestion spikes consumed free storage space.
  2. System log tables (system.query_log) retained uncompressed data indefinitely.

Investigation and Diagnosis

  1. Inspect available space across configured volumes:
clickhouse-client -q "SELECT name, path, free_space, total_space FROM system.disks"
  1. Identify top storage-consuming directories:
du -sh /var/lib/clickhouse/data/*/*

Root Cause Identification

Historical table data or system logging tables exhausted partition space.

Resolution

  1. Drop obsolete partitions to reclaim space immediately:
ALTER TABLE telemetry.user_events DROP PARTITION 202401;
  1. Truncate system log tables if necessary:
TRUNCATE TABLE system.query_log;
SYSTEM FLUSH LOGS;

Verification

Verify free_space via system.disks or OS df -h command.

Prevention

Configure Partition TTLs (TTL event_date + INTERVAL 30 DAY) to drop expired partitions automatically.


Problem 6: Server Fails to Start After Configuration Edit

Symptoms

Executing systemctl start clickhouse-server fails, and systemd reports job failure.

Possible Causes

XML validation error caused by unclosed tags or invalid characters in /etc/clickhouse-server/config.d/ or users.d/.

Investigation and Diagnosis

  1. Run server configuration test utility:
clickhouse-server --config-file=/etc/clickhouse-server/config.xml --test-config
  1. Inspect end of main server log:
tail -n 50 /var/log/clickhouse-server/clickhouse-server.log

Root Cause Identification

XML syntax error in configuration file.

Resolution

Fix reported syntax line error, re-verify with --test-config, and start daemon:

sudo systemctl start clickhouse-server

Verification

systemctl is-active clickhouse-server

Prevention

Always run clickhouse-server --test-config in CI/CD automation prior to reloading configuration files.


Problem 7: User Authentication Failures

Symptoms

Client connections fail with Code: 193. DB::Exception: User default: authentication failed.

Possible Causes

  1. Wrong user password supplied during CLI connection.
  2. Mismatched password hash format inside users.xml or users.d/ drop-in files.

Investigation and Diagnosis

  1. Inspect defined drop-in user credentials:
grep -rn "password" /etc/clickhouse-server/users.d/
  1. Verify connection command credentials:
clickhouse-client -u default --password

Root Cause Identification

Incorrect credentials or bad user configuration drop-in file.

Resolution

Correct XML password node structure (plaintext or SHA-256 hash) in /etc/clickhouse-server/users.d/.

Verification

Connect using clickhouse-client -u <user> --password <pass>.

Prevention

Centralize user authentication using SHA-256 password hashing or LDAP/Active Directory integration.


11. Security and Best Practices

  • File Ownership & Permissions: Keep ownership of configuration paths restricted to clickhouse:clickhouse with 0600 permissions on user credentials files.
  • Network Exposure: Never bind ClickHouse to public IPs (0.0.0.0) without configuring TLS encryption and IP restrictions.
  • Least Privilege: Disable default unauthenticated access; replace default user usage with defined application users bound to explicit databases.
  • Credential Hashing: Store user passwords using SHA-256 hashes instead of plain text in XML files.

12. Performance and Resource Optimisation

  1. Batch Ingestion: Group writes into batches of 10,000 to 100,000 rows per INSERT block to minimize part creation.
  2. Primary Key Design: Select primary sorting key columns matching frequent WHERE filtering patterns to maximize sparse index pruning.
  3. Memory Tuning: Set max_bytes_before_external_group_by to force memory-intensive aggregations to spill to disk before hitting max_memory_usage.

13. Backup, Recovery and Maintenance

ClickHouse supports instantaneous, zero-overhead disk snapshots using local hardlinks.

-- Freeze all table partitions to disk snapshot directory
ALTER TABLE telemetry.user_events FREEZE;

Snapshots are written to /var/lib/clickhouse/shadow/.

Partition Maintenance

-- Detach old partition for maintenance
ALTER TABLE telemetry.user_events DETACH PARTITION 202401;

-- Attach partition back to active scanning engine
ALTER TABLE telemetry.user_events ATTACH PARTITION 202401;

14. Automation and Integration

Systemd Integration

Ensure ClickHouse is configured to auto-start on server boot:

sudo systemctl enable clickhouse-server

Ansible Drop-in Provisioning Example

Deploying configuration overrides via Ansible:

- name: Deploy ClickHouse Network Listener Drop-in
  ansible.builtin.copy:
    dest: /etc/clickhouse-server/config.d/listen.xml
    owner: clickhouse
    group: clickhouse
    mode: '0640'
    content: |
      <clickhouse>
          <listen_host>::</listen_host>
      </clickhouse>
  notify: Reload ClickHouse

15. Practical Labs and Exercises

Lab 1: Verify Installation and Ports (Beginner)

  1. Ensure clickhouse-server is active.
  2. Confirm port 8123 is listening using ss.
  3. Query server version via HTTP endpoint with curl.

Lab 2: Analyze Small Inserts vs Merges (Intermediate)

  1. Create a MergeTree test table.
  2. Execute 20 individual single-row INSERT statements.
  3. Query system.parts to observe total part creation.
  4. Run OPTIMIZE TABLE ... FINAL and note the resulting reduction in part count.

Lab 3: Troubleshoot Memory Limit Error (Advanced SRE Exercise)

  1. Run a query exceeding default profile memory bounds.
  2. Observe error MEMORY_LIMIT_EXCEEDED.
  3. Inspect system metrics in system.query_log.
  4. Set max_bytes_before_external_group_by and verify successful completion.

16. Quick Reference and Cheat Sheet

Important Configuration Directories and Paths

Path / File Description
/etc/clickhouse-server/config.xml Primary server node configuration
/etc/clickhouse-server/config.d/ XML drop-in directory for server settings
/etc/clickhouse-server/users.d/ XML drop-in directory for user credentials and profiles
/var/lib/clickhouse/ Data part storage root path
/var/log/clickhouse-server/ Log directory (clickhouse-server.log)

Operational Troubleshooting Quick Reference

Symptom Initial Diagnostic Command Primary File / System Table
Service will not start clickhouse-server --test-config /var/log/clickhouse-server/clickhouse-server.log
Port connection refused `ss -tlnp grep -E ’8123
Memory limit exceeded SELECT * FROM system.processes system.query_log
Too many parts in table SELECT count() FROM system.parts system.parts
Replica in read-only state SELECT * FROM system.replicas system.replicas

Important System Ports

Port Protocol Usage
8123 HTTP REST API, curl, web dashboards, monitoring metrics
9000 Native TCP clickhouse-client CLI, inter-server communications

17. Further Reading and References

  1. ClickHouse Official Documentation: clickhouse.com/docs
  2. MergeTree Engine Reference: clickhouse.com/docs - MergeTree
  3. ClickHouse System Tables Guide: clickhouse.com/docs - System Tables
  4. ClickHouse Replication Architecture: clickhouse.com/docs - Replication