Database Monitoring for Self-Hosted ClickHouse

Atatus Database Monitoring provides deep visibility into your ClickHouse databases by collecting key system metrics, query summary statistics, active query samples, completed query events, query errors, EXPLAIN plans, and MergeTree parts/merges health.

To enable Database Monitoring, the Atatus Infra Agent connects to ClickHouse as a dedicated user with read access to the relevant system.* tables. This guide walks you through the required prerequisites and setup process for self-hosted ClickHouse instances.

Setup Database Access for the Agent
Configure Atatus Infrastructure Agent
Verify

Before You Begin

Component	Supported Versions / Requirements
ClickHouse	23.x and later (23.x, 24.x, 25.x). Recommended: 23.8 LTS+
Atatus Infra Agent	4.3.0 or higher

Setup Database Access for the Agent

Connect to your ClickHouse instance using clickhouse-client as a user with privileges to create users and grant access:

copy

clickhouse-client --host 127.0.0.1 --port 9000 --user default --password

Create the atatus user:

copy
```
CREATE USER atatus IDENTIFIED BY '<UNIQUEPASSWORD>';
```

Grant SELECT access on the system.* tables the Agent reads:

copy

GRANT SELECT ON system.metrics TO atatus;
GRANT SELECT ON system.events TO atatus;
GRANT SELECT ON system.asynchronous_metrics TO atatus;
GRANT SELECT ON system.errors TO atatus;
GRANT SELECT ON system.processes TO atatus;
GRANT SELECT ON system.query_log TO atatus;
GRANT SELECT ON system.parts TO atatus;
GRANT SELECT ON system.detached_parts TO atatus;
GRANT SELECT ON system.mutations TO atatus;
GRANT SELECT ON system.replicas TO atatus;
GRANT SELECT ON system.replication_queue TO atatus;
GRANT SELECT ON system.dictionaries TO atatus;

Note:

The system.processes and system.query_log grants power query activity, query summary, query completions, query errors, and EXPLAIN collection. The system.parts*, system.mutations, system.replicas, and system.replication_queue grants power MergeTree storage health.

Configure Atatus Infrastructure Agent

Install the Atatus Infrastructure agent on a host that can connect to your ClickHouse server.
Copy the ClickHouse example configuration file:

copy
```
cd /etc/atatus-infra-agent/conf.d/clickhouse.d/
sudo cp clickhouse.yml.template clickhouse.yml
```
Update the ClickHouse configuration file:

Add the following configuration to /etc/atatus-infra-agent/conf.d/clickhouse.d/clickhouse.yml:

copy
```
metrics:
  - hosts: ["clickhouse://127.0.0.1:9000"]
    username: atatus
    password: <UNIQUEPASSWORD>
    database: default
    dbm: true

logs:
  - type: log
  - type: error
```
Use clickhouse://host:port for the native protocol (default port 9000) or http://host:8123 for the HTTP protocol.
Restart the Atatus Infra Agent:

copy
```
sudo service atatus-infra-agent restart
```

Verify

Confirm the atatus user can connect and read the core system.* tables:

copy

clickhouse-client --host 127.0.0.1 --port 9000 --user atatus --password \
  --query "SELECT 1" \
  && echo -e "\e[0;32mClickHouse connection - OK\e[0m" \
  || echo -e "\e[0;31mCannot connect to ClickHouse\e[0m"

clickhouse-client --host 127.0.0.1 --port 9000 --user atatus --password \
  --query "SELECT count() FROM system.query_log" \
  && echo -e "\e[0;32mClickHouse system.query_log read OK\e[0m" \
  || echo -e "\e[0;31mCannot read from system.query_log\e[0m"

clickhouse-client --host 127.0.0.1 --port 9000 --user atatus --password \
  --query "SELECT count() FROM system.processes" \
  && echo -e "\e[0;32mClickHouse system.processes read OK\e[0m" \
  || echo -e "\e[0;31mCannot read from system.processes\e[0m"

Enabling TLS (Optional)

If your ClickHouse server requires TLS (for example, when exposing the native protocol on port 9440), enable the tls block:

copy

metrics:
  - hosts: ["clickhouse://clickhouse.example.com:9440"]
    username: atatus
    password: <UNIQUEPASSWORD>
    database: default
    dbm: true
    tls:
      enabled: true
      verification_mode: "certificate"     # certificate | none
      certificate_authorities:
        - /etc/ssl/clickhouse/ca.pem

Use verification_mode: "none" only for self-signed certificates in test environments.

Tuning DBM Feature Toggles (Optional)

Each Database Monitoring feature can be turned on or off individually under dbm_clickhouse_options. By default, every feature is enabled once dbm: true (except query_explain, which can be opted out via enabled: false).

copy

metrics:
  - hosts: ["clickhouse://127.0.0.1:9000"]
    username: atatus
    password: <UNIQUEPASSWORD>
    database: default
    dbm: true
    dbm_clickhouse_options:
      health:
        enabled: true             # Connection status, ping latency, uptime
      metrics:
        enabled: true             # system.metrics + system.events + asynchronous_metrics + errors
      query_summary:
        enabled: true             # Aggregated per-query stats from system.query_log
      query_activity:
        enabled: true             # Currently-running queries from system.processes
      query_completions:
        enabled: true             # Per-event completed query samples
      query_errors:
        enabled: true             # ExceptionBeforeStart/ExceptionWhileProcessing rows
      query_explain:
        enabled: true             # EXPLAIN PLAN collection for completed SELECTs
      parts_and_merges:
        enabled: true             # Parts, merges, mutations, replication queue

Query Log Tunables

The following options apply to query_summary, query_completions, query_errors, and query_explain:

copy

    dbm_clickhouse_options:
      query_log_table: "system.query_log"
      internal_user_exclude_pattern: "%-internal"      # LIKE pattern: matching users are skipped
      max_samples_per_collection: 1000                  # LIMIT per query_log scan
      seen_samples_cache_maxsize: 10000                 # Capacity of the seen-samples cache
      query_completions_samples_per_hour_per_query: 15  # Per-(db, query_signature) rate cap
      query_errors_samples_per_hour_per_query: 60       # Errors are rarer per signature

EXPLAIN Tunables

copy

    dbm_clickhouse_options:
      explained_queries_per_hour_per_query: 60
      explained_queries_cache_maxsize: 5000
      query_explain_lookback_seconds: 60
      query_explain_max_rows_per_fetch: 200

Parts and Merges Tunables

copy

    dbm_clickhouse_options:
      max_parts_rows: 500
      max_detached_parts_rows: 1000
      max_mutations_rows: 200
      max_replication_queue_rows: 1000
      stalled_merge_elapsed_threshold_seconds: 3600    # Merges past this are tagged stalled
      stuck_replication_num_tries: 3                   # Replication tasks past this are tagged stuck
      table_metrics_include_partition_tag: false       # High cardinality — opt in deliberately
      table_metrics_max_tables: 200                    # Cap gauges to the top N tables

Collecting Logs (Optional)

To collect ClickHouse server logs alongside metrics, enable the default log paths or provide custom paths:

copy

logs:
  - type: log              # /var/log/clickhouse-server/clickhouse-server.log
  - type: error            # /var/log/clickhouse-server/clickhouse-server.err.log

If you have custom log paths, define them explicitly:

copy

logs:
  - type: log
    paths:
      - /var/log/clickhouse-server/clickhouse-server.log*
    source: clickhouse
    service: clickhouse

  - type: error
    paths:
      - /var/log/clickhouse-server/clickhouse-server.err.log*
    source: clickhouse
    service: clickhouse

Overriding the Reported Hostname (Optional)

To report a custom hostname instead of the resolved host:port:

copy

metrics:
  - hosts: ["clickhouse://127.0.0.1:9000"]
    username: atatus
    password: <UNIQUEPASSWORD>
    database: default
    dbm: true
    reported_hostname: "clickhouse-prod-01"