Installing ClickHouse

The following commands were run on a fresh install of Ubuntu 20.04 LTS on a system with 16 GB of RAM.

$ sudo apt-key adv \
    --keyserver hkp://keyserver.ubuntu.com:80 \
    --recv E0C56BD4
$ echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | \
    sudo tee /etc/apt/sources.list.d/clickhouse.list
$ sudo apt update

I'll install ClickHouse.

$ sudo apt install \
    clickhouse-client \
    clickhouse-server-common

The following will enable Prometheus metrics exposure in ClickHouse's config.

$ sudo vi /etc/clickhouse-server/config.xml

Remove the XML comments around the <prometheus> tags. It should look like the following:

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

$ sudo systemctl restart clickhouse-server

TCP port 9363 should expose an HTTP interface with metrics provided by ClickHouse.

$ curl -s 127.0.0.1:9363/metrics 2>/dev/null \
    | grep -v '^#' \
    | head

ClickHouseProfileEvents_Query 142
ClickHouseProfileEvents_SelectQuery 142
ClickHouseProfileEvents_InsertQuery 0
ClickHouseProfileEvents_FailedQuery 0
ClickHouseProfileEvents_FailedSelectQuery 0
ClickHouseProfileEvents_FailedInsertQuery 0
ClickHouseProfileEvents_QueryTimeMicroseconds 4771631
ClickHouseProfileEvents_SelectQueryTimeMicroseconds 4771631
ClickHouseProfileEvents_InsertQueryTimeMicroseconds 0
ClickHouseProfileEvents_FileOpen 19671

The above endpoint exposes over 320 metrics with the version and configuration combination I have running.

Installing Prometheus

I found a packaged version of Prometheus maintained by a developer at Yandex.

$ sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys EA8AECDE414187DB
$ sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys A57ED69D49D1012A
$ echo "deb https://packagecloud.io/the_asten/prometheus/ubuntu/ focal main" \
    | sudo tee /etc/apt/sources.list.d/prometheus.list
$ sudo apt update
$ sudo apt install prometheus

I'll append the targets list to include the endpoint for ClickHouse's Prometheus metrics.

$ sudo vi /etc/prometheus/prometheus.yml

static_configs:
    - targets: ['localhost:9090', 'localhost:9363']

The following will restart Prometheus.

$ sudo systemctl restart prometheus

Open http://127.0.0.1:9090/targets and you should see two targets listed, one of which is the ClickHouse endpoint.

Open http://127.0.0.1:9090/graph and type "ClickHouseAsyncMetrics_MemoryResident" into the large query box at the top, hit the "Execute" button to the right and then select the "Graph" tab below. You should see a line graph of the resident memory metric reported by ClickHouse.

Installing Grafana

The following will install Grafana.

$ wget -qO- https://packages.grafana.com/gpg.key | sudo apt-key add -
$ echo "deb https://packages.grafana.com/oss/deb stable main" \
    | sudo tee /etc/apt/sources.list.d/grafana.list
$ sudo apt update
$ sudo apt install grafana

The following will install a data source plugin for ClickHouse. This plugin can communicate with ClickHouse directly, including the ability to write SQL statements and chart them using Grafana.

$ sudo grafana-cli plugins install vertamedia-clickhouse-datasource

Under the [server] section of Grafana's configuration file, change the network interface binding to 127.0.0.1 so the service is only accessible locally.

$ sudo vi /etc/grafana/grafana.ini

http_addr = 127.0.0.1

Restart Grafana for the changes to take effect.

$ sudo systemctl restart grafana-server

Open http://127.0.0.1:3000/ and give the default username and password of admin and admin. You'll be prompted to change the password after this initial login.

Scroll to the bottom of http://127.0.0.1:3000/datasources/new and click on ClickHouse. Set the HTTP URL to http://127.0.0.1:8123 and then scroll to the bottom and click "Save & test".

Scroll to the bottom of http://127.0.0.1:3000/datasources/new again and click on Prometheus. Set the HTTP URL to http://127.0.0.1:9090/ and then scroll to the bottom and click "Save & test".

Plotting ClickHouse Metrics

There are two sources for ClickHouse Metrics, the first is direct via the ClickHouse data source. The following is an example plot you can set up.

Open http://127.0.0.1:3000/dashboard/new and add an empty panel. At the bottom of the page, change the following:

• Data source to ClickHouse
• -- database -- to system
• -- table : col -- to metric_log
• --dateTime : col-- to event_time

Click "Go to query". You should see a panel load up with a line graph plotting the event count. Click "Apply" in the top right of the screen.

With the above example, switch the data source to "Prometheus", type "ClickHouseAsyncMetrics_MemoryResident" into the Metric browser search box and click "Apply" in the top right of the screen. You should now see a plot being populated from metrics collected by Prometheus.

Setting up Alerts

Alerts can be built by first selecting any panel you've created on a dashboard. Click on the panel title and then click "edit". In the middle left of the interface, you'll see three tabs, "Query", "Transform" and "Alert". Click "Alert" and then with that tab selected, click the blue "Create Alert" button. Under the conditions, there should be a default one saying "WHEN avg() OF query(A, 5m, now) IS ABOVE", next to that, type in a threshold value. Then scroll to the bottom of the page and click "Test rule".

You should then be presented with a model showing the rule with syntax highlighting. Close the modal and then click "Apply" in the top right of the screen.