Monitoring ClickHouse with Prometheus and Grafana: Query Performance, Mutations, and RAM - ITFROMZERO - Share tobe shared! A comprehensive guide to leveraging ClickHouse's native OpenMetrics endpoint with Prometheus and Grafana. Master critical PromQL queries to monitor QPS, Part Mutations, and RAM usage to prevent downtime.

Table of Contents

Why ClickHouse Is Fast Yet Still Demands Strict Monitoring

ClickHouse can effortlessly scan hundreds of millions of rows per second. However, when deployed to production at a scale of 500 million to several billion logs per day, operational bottlenecks inevitably arise. A single minor mistake can bring the entire server to a halt:

Unfiltered queries consuming all RAM: A developer accidentally runs a GROUP BY on a high-cardinality column without adding WHERE date >= today(). The result: the process eats up 64GB of RAM in 5 seconds. The Linux OOM Killer promptly steps in and terminates ClickHouse.
Part mutation backlogs: ClickHouse handles ALTER UPDATE/DELETE via asynchronous mutations by rewriting entire data parts. Running continuous mutations will push disk I/O to 100%, causing read query timeouts across the board.
Part overload errors ("Too many parts in all data parts in table"): When applications insert tiny batches (e.g., 100 records per insert instead of batching 50,000), ClickHouse creates thousands of small part files. Background merges cannot keep up, and the database immediately rejects new write requests to protect itself.

Previously, whenever an incident occurred, engineers had to frantically SSH into the node, launch clickhouse-client, and inspect system.processes. Today, by simply taking advantage of ClickHouse’s built-in OpenMetrics endpoint, Prometheus can scrape these metrics continuously.

Setting Up a Test Environment with Docker Compose

If your staging or production server is already running ClickHouse, feel free to skip directly to the XML configuration section below. If you want to quickly test metric scraping, you can spin up this container stack in two minutes:

# Create working directory
mkdir -p ~/clickhouse-monitor && cd ~/clickhouse-monitor

# Create docker-compose.yml file
cat << 'EOF' > docker-compose.yml
version: '3.8'
services:
  clickhouse:
    image: clickhouse/clickhouse-server:23.8
    container_name: clickhouse-server
    ports:
      - "8123:8123"   # HTTP interface
      - "9000:9000"   # Native TCP client
      - "9363:9363"   # Prometheus metrics port
    volumes:
      - ./ch-config.xml:/etc/clickhouse-server/config.d/prometheus.xml:ro
      - ch_data:/var/lib/clickhouse
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

  prometheus:
    image: prom/prometheus:v2.47.0
    container_name: prometheus-ch
    ports:
      - "9090:9090"
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml:ro

  grafana:
    image: grafana/grafana:10.1.0
    container_name: grafana-ch
    ports:
      - "3000:3000"
    environment:
      - GF_SECURITY_ADMIN_PASSWORD=admin

volumes:
  ch_data:
EOF

Enabling the Native Metrics Endpoint in ClickHouse

1. Create the XML Configuration for Prometheus

There is no need for third-party exporters like clickhouse_exporter. ClickHouse comes with built-in OpenMetrics support. Simply drop the configuration file into /etc/clickhouse-server/config.d/:

<clickhouse>
    <prometheus>
        <endpoint>/metrics</endpoint>
        <port>9363</port>
        <metrics>true</metrics>
        <events>true</events>
        <asynchronous_metrics>true</asynchronous_metrics>
        <status_info>true</status_info>
    </prometheus>
</clickhouse>

These four flags map to internal system table groups:

metrics: Instantaneous state from system.metrics (active queries, busy background threads).
events: Cumulative counters from system.events (total executed queries, disk bytes read/written).
asynchronous_metrics: Periodic snapshots from system.asynchronous_metrics (RAM RSS, unmerged parts count, CPU usage).
status_info: Overall connection states and node health status.

2. Configure the Prometheus Scrape Job

Create a prometheus.yml file. We will configure a scrape interval of 10 seconds:

global:
  scrape_interval: 10s
  evaluation_interval: 10s

scrape_configs:
  - job_name: 'clickhouse'
    static_configs:
      - targets: ['clickhouse:9363']
        labels:
          cluster: 'analytics-production'
          env: 'prod'

Start the entire stack:

docker-compose up -d

Verifying Metrics and Setting Up Dashboards

1. Verify Raw Metrics Using curl

Before spending time building dashboards in Grafana, make sure port 9363 is serving metrics:

curl -s http://localhost:9363/metrics | grep -E 'ClickHouseProfileEvents_Query|ClickHouseAsyncMetrics_MemoryTracking'

If you see metric lines with values returned in the terminal output, Prometheus can parse the data without issues.

2. Essential PromQL Queries for Production

Log in to Grafana at http://localhost:3000 (default credentials: admin/admin). Add your Data Source pointing to http://prometheus:9090, then set up these three core alert categories using PromQL:

A. Query Traffic and Disk I/O Load

This category helps detect sudden query spikes causing CPU or disk bottlenecks:

# Query rate per second (QPS)
rate(ClickHouseProfileEvents_Query[1m])

# Concurrently executing queries on the server
ClickHouseMetrics_Query

# Disk read throughput (Bytes per second - Bps)
rate(ClickHouseProfileEvents_ReadBufferFromFileDescriptorBytes[1m])

B. Part Mutations and Background Merges

This is the most critical area. If these metrics surge, the system is accumulating severe technical debt:

# Pending mutations count (if continuously > 20, pause ALTER operations immediately)
ClickHouseAsyncMetrics_NumberOfMutations

# Number of background merge and mutation tasks active in the pool
ClickHouseMetrics_BackgroundMergesAndMutationsPoolTask

# Maximum part count for a single partition (critical if exceeding 150-300 parts)
ClickHouseAsyncMetrics_MaxPartCountForPartition

C. RAM Usage and OOM Crash Prevention

While ClickHouse tracks internal allocations, you must also compare this against the actual host memory usage:

# Memory tracked internally by ClickHouse
ClickHouseMetrics_MemoryTracking

# Resident Set Size (RSS) memory occupied by the process at the OS level
ClickHouseAsyncMetrics_MemoryResident

Production Pro Tip: Configure an alert rule when ClickHouseAsyncMetrics_MemoryResident reaches 85% of total server RAM. Do not wait until 95%, as a heavy query can easily consume the remaining 10% in just 2-3 seconds.

3. Importing Pre-built Dashboards

You do not need to build every panel from scratch. The ClickHouse community provides comprehensive, production-ready dashboard templates.

In Grafana, go to Dashboards > Import and enter dashboard ID 14192 (ClickHouse Overview) or 882. You will immediately get rich visualizations covering thread pools, active connections, cache hit ratios, and ZooKeeper/Keeper synchronization latency.