Monitor infrastructure

Grafana integrations

Integrations reference

ClickHouse

Grafana Cloud

ClickHouse integration for Grafana Cloud

ClickHouse is an open-source column-oriented database management system that allows generating analytical data reports in real-time. This integration helps you monitor a ClickHouse cluster metrics and error logs, with an accompanying dashboard to visualize it.

This integration supports ClickHouse 22.6.8.35+.

This integration includes 4 useful alerts and 4 pre-built dashboards to help monitor and visualize ClickHouse metrics and logs.

Before you begin

ClickHouse supports multi-file configurations using either a /etc/clickhouse-server/config.xml or /etc/clickhouse-server/config.yaml. This example shows config.xml snippets required to collect metrics and logs.

Prometheus metrics are generated using the ClickHouse Prometheus instrumentation that must be configured under the Global Server Settings, within the config.xml file. Some ClickHouse installations have these commented out, else include this snippet:

    <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 error logs are scraped using a Promtail agent that targets the default /var/log/clickhouse-server/clickhouse-server.err.log path. Logs are enabled by including the target path inside the <errorlog> within the config.xml file. Some ClickHouse installations have the error log set by default, which would be located at the top of the config.xml file.

    <logger>
        <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
    </logger>

For more information please check ClickHouse logger settings.

After editing the config.xml file, restart the ClickHouse server:

sudo service clickhouse-server restart

Install ClickHouse integration for Grafana Cloud

In your Grafana Cloud stack, click Connections in the left-hand menu.
Find ClickHouse and click its tile to open the integration.
Review the prerequisites in the Configuration Details tab and set up Grafana Alloy to send ClickHouse metrics and logs to your Grafana Cloud instance.
Click Install to add this integration’s pre-built dashboards and alerts to your Grafana Cloud instance, and you can start monitoring your ClickHouse setup.

Configuration snippets for Grafana Alloy

Simple mode

These snippets are configured to scrape a single ClickHouse instance running locally with default ports.

First, manually copy and append the following snippets into your alloy configuration file.

Metrics snippets

discovery.relabel "metrics_integrations_integrations_clickhouse" {
	targets = [{
		__address__ = "localhost:9363",
	}]

	rule {
		target_label = "instance"
		replacement  = constants.hostname
	}
}

prometheus.scrape "metrics_integrations_integrations_clickhouse" {
	targets    = discovery.relabel.metrics_integrations_integrations_clickhouse.output
	forward_to = [prometheus.remote_write.metrics_service.receiver]
	job_name   = "integrations/clickhouse"
}

Logs snippets

linux

local.file_match "logs_integrations_integrations_clickhouse" {
	path_targets = [{
		__address__ = "localhost",
		__path__    = "/var/log/clickhouse-server/clickhouse-server.log",
		instance    = constants.hostname,
		job         = "integrations/clickhouse",
	}]
}

loki.process "logs_integrations_integrations_clickhouse" {
	forward_to = [loki.write.grafana_cloud_loki.receiver]

	stage.multiline {
		firstline     = "^([\\d]{4}).([\\d]{1,2}).([\\d]{1,2}) (([\\d]+):([\\d]+):([\\d]+).([\\d]+))"
		max_lines     = 0
		max_wait_time = "3s"
	}

	stage.regex {
		expression = `(?P<timestamp>([\d]{4}).([\d]{1,2}).([\d]{1,2}) (([\d]+):([\d]+):([\d]+).([\d]+))) \[ (?P<thread_id>\d+) \] \{(?P<query_id>.*)\} <(?P<level>.+?)> (?P<message>(?s:.*))$`
	}

	stage.labels {
		values = {
			level = null,
		}
	}
}

loki.source.file "logs_integrations_integrations_clickhouse" {
	targets    = local.file_match.logs_integrations_integrations_clickhouse.targets
	forward_to = [loki.process.logs_integrations_integrations_clickhouse.receiver]
}

Advanced mode

The following snippets provide examples to guide you through the configuration process.

To instruct Grafana Alloy to scrape your ClickHouse instances, manually copy and append the snippets to your alloy configuration file, then follow subsequent instructions.

Advanced metrics snippets

discovery.relabel "metrics_integrations_integrations_clickhouse" {
	targets = [{
		__address__ = "localhost:9363",
	}]

	rule {
		target_label = "instance"
		replacement  = constants.hostname
	}
}

prometheus.scrape "metrics_integrations_integrations_clickhouse" {
	targets    = discovery.relabel.metrics_integrations_integrations_clickhouse.output
	forward_to = [prometheus.remote_write.metrics_service.receiver]
	job_name   = "integrations/clickhouse"
}

To monitor your ClickHouse instance, you must use a discovery.relabel component to discover your ClickHouse Prometheus endpoint and apply appropriate labels, followed by a prometheus.scrape component to scrape it.

Configure the following properties within each discovery.relabel component:

__address__: The address to your ClickHouse Prometheus metrics endpoint.
instance label: constants.hostname sets the instance label to your Grafana Alloy server hostname. If that is not suitable, change it to a value uniquely identifies this ClickHouse instance. Make sure this label value is the same for all telemetry data collected for this instance.

If you have multiple ClickHouse servers to scrape, configure one discovery.relabel for each and scrape them by including each under targets within the prometheus.scrape component.

Advanced logs snippets

linux

local.file_match "logs_integrations_integrations_clickhouse" {
	path_targets = [{
		__address__ = "localhost",
		__path__    = "/var/log/clickhouse-server/clickhouse-server.log",
		instance    = constants.hostname,
		job         = "integrations/clickhouse",
	}]
}

loki.process "logs_integrations_integrations_clickhouse" {
	forward_to = [loki.write.grafana_cloud_loki.receiver]

	stage.multiline {
		firstline     = "^([\\d]{4}).([\\d]{1,2}).([\\d]{1,2}) (([\\d]+):([\\d]+):([\\d]+).([\\d]+))"
		max_lines     = 0
		max_wait_time = "3s"
	}

	stage.regex {
		expression = "(?P<timestamp>([\\d]{4}).([\\d]{1,2}).([\\d]{1,2}) (([\\d]+):([\\d]+):([\\d]+).([\\d]+))) \\[ \\d+ \\] \\{.*\\} <(?P<level>.+)> (?P<message>(?s:.*))$"
	}

	stage.labels {
		values = {
			level = null,
		}
	}
}

loki.source.file "logs_integrations_integrations_clickhouse" {
	targets    = local.file_match.logs_integrations_integrations_clickhouse.targets
	forward_to = [loki.process.logs_integrations_integrations_clickhouse.receiver]
}

To monitor your ClickHouse instance logs, you will use a combination of the following components:

local.file_match defines where to find the log file to be scraped. Change the following properties according to your environment:
- __address__: The ClickHouse instance address
- __path__: The path to the log file.
- instance label: constants.hostname sets the instance label to your Grafana Alloy server hostname. If that is not suitable, change it to a value uniquely identifies this ClickHouse instance. Make sure this label value is the same for all telemetry data collected for this instance.
loki.process defines how to process logs before sending it to Loki.
loki.source.file sends logs to Loki.

On Linux, you will also need to add the alloy user to the clickhouse group to get logs. Run the following command to configure the user as required:

sudo usermod -a -G clickhouse alloy

Kubernetes instructions

Before you begin with Kubernetes

Please note: These instructions assume the use of the Kubernetes Monitoring Helm chart

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

    <logger>
        <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
    </logger>

For more information please check ClickHouse logger settings.

After editing the config.xml file, restart the ClickHouse server:

sudo service clickhouse-server restart

Configuration snippets for Kubernetes Helm chart

The following snippets provide examples to guide you through the configuration process.

To scrape your Clickhouse instances, manually modify your Kubernetes Monitoring Helm chart with these configuration snippets.

Replace any values between the angle brackets <> in the provided snippets with your desired configuration values.

Metrics snippets

alloy-metrics:
    extraConfig: |-
        discovery.kubernetes "metrics_clickhouse" {
            role = "endpoints"
            selectors {
                role = "endpoints"
                label = "<endpoints label>=<endpoints label value>"
            }
        }
        
        discovery.relabel "metrics_clickhouse" {
            targets = discovery.kubernetes.metrics_clickhouse.targets
        
            rule {
                source_labels = ["__meta_kubernetes_endpoint_port_name"]
                regex = "<clickhouse service listening port>"
                action = "keep"
            }
            rule {
                source_labels = ["__meta_kubernetes_pod_label_clickhouse_cluster"]
                target_label = "clickhouse_cluster"
            }
            rule {
                source_labels = ["__meta_kubernetes_pod_name"]
                target_label = "instance"
            }
        }
        
        prometheus.scrape "metrics_clickhouse" {
            targets      = discovery.relabel.metrics_clickhouse.output
            job_name     = "integrations/clickhouse"
            honor_labels = true
            forward_to   = [prometheus.remote_write.grafana_cloud_metrics.receiver]
        }

Logs snippets

podLogs:
    extraDiscoveryRules: |-
        rule {
            source_labels = ["__meta_kubernetes_namespace", "__meta_kubernetes_pod_label_<your_clickhouse_pod_label_name>"]
            separator     = ":"
            regex         = "<your_clickhouse_namespace>:<your_clickhouse_pod_label_value>"
            replacement   = "clickhouse"
            target_label  = "integration"
        }
        
    extraLogProcessingStages: |-
        stage.match {
            selector = "{integration=\\"clickhouse\\"}"
            
            stage.multiline {
                firstline     = \`^([\\d]{4}).([\\d]{1,2}).([\\d]{1,2}) (([\\d]+):([\\d]+):([\\d]+).([\\d]+))\`
                max_lines     = 0
                max_wait_time = "0s"
            }
        
            stage.regex {
                expression = \`(?P<timestamp>([\\d]{4}).([\\d]{1,2}).([\\d]{1,2}) (([\\d]+):([\\d]+):([\\d]+).([\\d]+))) \\[ \\d+ \\] \\{.*\\} <(?P<level>.+)> (?P<message>(?s:.*))$\`
            }

            stage.template {
                source   = "instance"
                template = "{{ .namespace }}-{{ .container }}"
            }

            stage.labels {
                values = {
                    instance = "pod",
                    level = "",
                }
            }
        
            stage.static_labels {
                values = {
                    job = "integrations/clickhouse",
                    clickhouse_cluster = "<your_clickhouse_cluster_name>",
                }
            }
        }

Dashboards

The ClickHouse integration installs the following dashboards in your Grafana Cloud instance to help monitor your system.

ClickHouse latency
ClickHouse logs
ClickHouse overview
ClickHouse replica

ClickHouse Overview Dashboard 1

ClickHouse Overview Dashboard 2

ClickHouse Latency Dashboard

Alerts

The ClickHouse integration includes the following useful alerts:

Alert	Description
ClickHouseRejectedInserts	Critical: ClickHouse has too many rejected inserts.
ClickHouseReplicasInReadOnly	Critical: ClickHouse has too many replicas in read only state.
ClickHouseReplicationQueueBackingUp	Warning: ClickHouse replica max queue size backing up.
ClickHouseZookeeperSessions	Critical: ClickHouse has too many Zookeeper sessions.

Metrics

The most important metrics provided by the ClickHouse integration, which are used on the pre-built dashboards and Prometheus alerts, are as follows:

ClickHouseAsyncMetrics_OSMemoryTotal
ClickHouseAsyncMetrics_ReplicasMaxQueueSize
ClickHouseMetrics_HTTPConnection
ClickHouseMetrics_InterserverConnection
ClickHouseMetrics_MemoryTracking
ClickHouseMetrics_MySQLConnection
ClickHouseMetrics_PostgreSQLConnection
ClickHouseMetrics_ReadonlyReplica
ClickHouseMetrics_TCPConnection
ClickHouseMetrics_VersionInteger
ClickHouseMetrics_ZooKeeperRequest
ClickHouseMetrics_ZooKeeperSession
ClickHouseMetrics_ZooKeeperWatch
ClickHouseProfileEvents_AsyncInsertQuery
ClickHouseProfileEvents_DiskReadElapsedMicroseconds
ClickHouseProfileEvents_DiskWriteElapsedMicroseconds
ClickHouseProfileEvents_FailedInsertQuery
ClickHouseProfileEvents_FailedSelectQuery
ClickHouseProfileEvents_InsertQuery
ClickHouseProfileEvents_NetworkReceiveBytes
ClickHouseProfileEvents_NetworkReceiveElapsedMicroseconds
ClickHouseProfileEvents_NetworkSendBytes
ClickHouseProfileEvents_NetworkSendElapsedMicroseconds
ClickHouseProfileEvents_Query
ClickHouseProfileEvents_RejectedInserts
ClickHouseProfileEvents_ReplicatedPartChecks
ClickHouseProfileEvents_ReplicatedPartFetches
ClickHouseProfileEvents_ReplicatedPartMerges
ClickHouseProfileEvents_ReplicatedPartMutations
ClickHouseProfileEvents_SelectQuery
ClickHouseProfileEvents_ZooKeeperWaitMicroseconds
up

Changelog

# 1.1.0 - August 2024

* Add Asserts support
* Update mixin:
  - Add resolve delay for alerts
  - Fix dashboards' legends
  - Fix network panels
* Fix logs regex in the instructions


# 1.0.1 - July 2024

* Update instructions with the correct ClickHouse Prometheus instrumentation documentation link.

# 1.0.0 - November 2023

* Added Grafana Agent Operator configuration snippet to support Clickhouse in kubernetes cluster.
* Added cluster selector to dashboard for kubernetes support.
* Update Grafana Agent configuration snippets to include filtered metrics used in gauge panels.

# 0.1.2 - September 2023

* New Filter Metrics option for configuring the Grafana Agent, which saves on metrics cost by dropping any metric not used by this integration. Beware that anything custom built using metrics that are not on the snippet will stop working.
* New hostname relabel option, which applies the instance name you write on the text box to the Grafana Agent configuration snippets, making it easier and less error prone to configure this mandatory label.

# 0.1.1 - August 2023

* Add regex filter for logs datasource.

# 0.1.0 - July 2023

* Update logs
  * Move logs panel to separate dashboard.
  * Update agent logs snippet to extract level label and collect all logs.

# 0.0.2 - May 2023

* Update legends for latency metrics.

# 0.0.1 - February 2023

* Initial release.

Cost

By connecting your ClickHouse instance to Grafana Cloud, you might incur charges. To view information on the number of active series that your Grafana Cloud account uses for metrics included in each Cloud tier, see Active series and dpm usage and Cloud tier pricing.