Skip to content

ClickHouse

Last updated

View as Markdown

Monitor ClickHouse 25.8 or later that you run yourself, on virtual machines or in containers.

For a cluster run by the Altinity Kubernetes Operator, use ClickHouse on Kubernetes. For NetApp Instaclustr managed ClickHouse, use Instaclustr ClickHouse.

  • Check the version on any server. The collector supports 25.8 and later.

    SELECT version();
  • Ensure that you have the query log enabled. ClickHouse typically has it enabled by default.

  • Ensure that you have DBGorilla collector 0.17.0 or later

  • Ensure that the collector can reach the clickhouse server on port 8123, or 8443 with TLS.

  • Ensure the collector can reach otlp.dbgorilla.com and auth.dbgorilla.com on port 443. See What the collector is in Choose your setup.

  • Install Docker on the machine that will run the collector.

  • Add a collector in the DBGorilla web admin interface and record the values provided to include in your collector config file.

Create the user as an account that may manage users.

CREATE USER dbg_collector IDENTIFIED WITH sha256_password BY '<strong password>';
GRANT SHOW ON *.* TO dbg_collector;
GRANT SELECT ON system.* TO dbg_collector;

ClickHouse stores users on each server. On a cluster, run all three statements on every server. If the cluster has ClickHouse Keeper or ZooKeeper, you can run them once with ON CLUSTER:

CREATE USER dbg_collector ON CLUSTER '<cluster>' IDENTIFIED WITH sha256_password BY '<strong password>';
GRANT ON CLUSTER '<cluster>' SHOW ON *.* TO dbg_collector;
GRANT ON CLUSTER '<cluster>' SELECT ON system.* TO dbg_collector;
GrantWhat it addsWithout it
SHOW ON *.*The topology: the names and definitions of databases, tables, columns and dictionaries.Only the cluster, its servers and their settings arrive.
SELECT ON system.*Server metrics, the workload from the query log, and the queries that are running now.The cluster does not appear. The collector log says Not enough privileges and names the grant it needs on system.clusters.

Write collector.toml. This is everything a cluster that serves plain HTTP on port 8123 needs:

[dbgorilla]
agent_id = "<agent id from the app>"
tenant_id = "<tenant id from the app>"
secret = "${DBG_SERVER_SECRET}"
[[component]]
name = "events-cluster"
engine = "clickhouse"
[component.provider]
type = "self_hosted"
[component.auth]
method = "password"
user = "dbg_collector"
password = { env = "CLICKHOUSE_COLLECTOR_PASSWORD" }
[component.connect]
host = "ch-1.internal.example.com"

host is any one server of the cluster. Give the address of a single server, such as one pod’s DNS name, and not a load balancer in front of several.

Run the collector from the directory that contains both files:

Terminal window
docker run -d --name dbg-collector --restart unless-stopped \
-v "$PWD/collector.toml:/etc/dbg-collector/collector.toml:ro" \
--env-file secrets.env \
dbgorillapublic.azurecr.io/dbg-collector:0.17.0 \
--config-file /etc/dbg-collector/collector.toml

If the HTTP interface uses TLS, set the HTTPS port and an ssl_mode. The default port is 8123 and the default ssl_mode is disable.

[component.connect]
host = "ch-1.internal.example.com"
port = 8443
ssl_mode = "verify_full"
ca_cert = "/etc/dbgorilla/clickhouse-ca.pem"
ssl_modeWhat it checks
disableNothing. The collector uses plain HTTP, which a TLS port refuses.
requireEncrypts, but does not check the certificate.
verify_fullThe certificate chain, and that the certificate names the host the collector connects to.

The collector connects to each server at the host name in the cluster definition. With verify_full, every server’s certificate needs that name.

ca_cert is the CA that signed the server certificates, as a path inside the container. Leave it out when a public CA signed them. For a private CA, mount the file at that path:

Terminal window
docker run -d --name dbg-collector --restart unless-stopped \
-v "$PWD/collector.toml:/etc/dbg-collector/collector.toml:ro" \
-v "$PWD/clickhouse-ca.pem:/etc/dbgorilla/clickhouse-ca.pem:ro" \
--env-file secrets.env \
dbgorillapublic.azurecr.io/dbg-collector:0.17.0 \
--config-file /etc/dbg-collector/collector.toml

By default the collector monitors every cluster that the server in host belongs to. You may specify a single cluster directly.

[component.connect]
host = "ch-1.internal.example.com"
cluster = "main"

By default the collector monitors every non-system database. databases monitors only the ones listed. exclude_databases leaves databases out of the topology, metrics and workload, and wins over databases.

[component.connect]
host = "ch-1.internal.example.com"
databases = ["shop", "billing"]
exclude_databases = ["scratch"]

RPC Commands allow DBGorilla agents to run commands on your database. By default commands are disabled.

[commands]
enabled = true
allowed = ["explain", "execute_query"]
CommandAgents can run?What it does
explainYesReturns the plan estimate of one SELECT without query execution.
execute_queryNoRuns one read statement and returns at most 1,000 rows. It accepts SELECT, WITH, SHOW, DESCRIBE, EXPLAIN and EXISTS.

ClickHouse checks the user’s grants for both commands, so grant SELECT on each database they may reach:

GRANT SELECT ON shop.* TO dbg_collector;

A command answer larger than 1 MiB fails with a message that says so. To change the limit, set it in bytes:

[engine.clickhouse.commands]
max_response_bytes = 4194304

For CPU, memory and disk metrics, run Prometheus node_exporter on each server on its default port 9100. The collector finds it there with no change to collector.toml. See node_exporter in Self-managed server.

Read the collector log:

Terminal window
docker logs dbg-collector 2>&1 | grep -E 'opamp handshake complete|discovered component|snapshot serialized|WARN|ERROR'

A healthy start has these three messages:

opamp handshake complete
discovered component
topology snapshot serialized for OTLP post

The first discovered component line carries "engine":"clickhouse" and a "nodes" count. The count is the number of servers in the cluster. One topology scrape failed; retrying next tick warning can appear as the collector starts. It sends the topology again on the next pass.

Then open Home in DBGorilla. The cluster appears under the name you gave the component, marked Monitored, as ClickHouse with its version. Its databases, tables, views, materialized views and dictionaries arrive first, and server metrics within a minute. The workload arrives within a few minutes of the cluster taking queries. Each workload entry is one statement, with its call count and time.

If nothing arrives, the log names the cause:

Log lineFix
is not supported: 25.8 or later is requiredUpgrade ClickHouse to 25.8 or later.
unsupported combination (engine=clickhouse, provider=self_hosted, auth=password)The collector is older than 0.17.0. Use 0.17.0 or later.
Authentication failedCheck the user name and CLICKHOUSE_COLLECTOR_PASSWORD, and that the user exists on every server.
Not enough privilegesRun the grants in Prepare the cluster on every server.
unknown ssl_modeUse disable, require or verify_full.
open ca_certThe path in ca_cert does not exist in the container. Mount the certificate.
invalid peer certificate: UnknownIssuerSet ca_cert to the CA that signed the server certificates.
Connection reset by peerThe port uses TLS. Set ssl_mode.

Change the tag in the docker run command to the new release, remove the container with docker rm -f dbg-collector, and run the command again. The collector reconnects under the same agent ID.

Remove the container with docker rm -f dbg-collector. To delete the cluster from Home, follow Removing the database from DBGorilla in Manage collectors. Then drop the user:

DROP USER dbg_collector;