ClickHouse Monitoring: How to add ClickHouse to Percona Monitoring & Management

Introduction

Percona Monitoring and Management (PMM) is a powerful tool that simplifies database monitoring, providing valuable insights into the performance of your database servers. 

If you’re using ClickHouse, an open-source columnar database management system, integrating it with PMM can help you keep a close eye on its performance metrics and ensure its optimal functioning.

In this step-by-step guide, we will walk you through the process of adding ClickHouse to the PMM monitoring setup. By the end of this tutorial, you’ll have a comprehensive monitoring solution that allows you to monitor both your ClickHouse and other database systems from a single PMM dashboard.

Runbook to add ClickHouse to Percona Monitoring & Management (PMM)

Step 1: Install PMM Server

Start by installing the PMM server by running the following command: Follow Quick Start: https://www.percona.com/software/pmm/quickstart

curl -fsSL https://www.percona.com/get/pmm | /bin/bash

This straightforward easy installation script will take care of checking and installing any missing software components. Ensure you run this command as a user with sudo privileges or as the root user.

root@docker:~# curl -fsSL https://www.percona.com/get/pmm | /bin/bash
Gathering/downloading required components, this may take a moment

Checking docker installation - installed.

Starting PMM server...
Created PMM Data Volume: pmm-data
Created PMM Server: pmm-server
    Use the following command if you ever need to update your container by hand:
    docker run -d -p 443:443 --volumes-from pmm-data --name pmm-server --restart always percona/pmm-server:2

PMM Server has been successfully setup on this system!

You can access your new server using one of the following web addresses:
    https://172.30.0.1:443/
    https://172.19.0.1:443/
    https://172.77.0.1:443/
    https://172.50.0.1:443/
    https://172.20.0.1:443/
    https://172.22.0.1:443/
    https://172.17.0.1:443/
    https://10.0.6.24:443/
    https://127.0.0.1:443/

The default username is 'admin' and the password is 'admin' :)
Note: Some browsers may not trust the default SSL certificate when you first open one of the urls above.
If this is the case, Chrome users may want to type 'thisisunsafe' to bypass the warning.

Enjoy Percona Monitoring and Management!

To get started, open the provided link. Ensure that the required port (default is 443) is open in your security group settings. You’ll be prompted for credentials; use ‘admin’ for both the username and password. After your initial login, it’s recommended to change your password for added security.

Check the Dashboard for Linux metrics.

Step 2: Install PMM Client

Ubuntu: Install PMM Client

Execute the below steps on the database server that you want to add to monitoring.

1. Download Percona Repo Package

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb

2. Install Percona Repo Package

sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb

3. Update apt cache

sudo apt-get update

4. Install Percona Monitoring and Management Client

sudo apt-get install pmm2-client

Step 3: Connect Client to Server

Register Percona Monitoring and Management client with the server

## sudo pmm-admin config --server-insecure-tls --server-url=https://admin:<password>@pmm.example.com

root@clickhouse1:~# sudo pmm-admin config --server-insecure-tls --server-url=https://admin:admin123@10.0.6.24:443

Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm2/config/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.
Checking local pmm-agent status...
pmm-agent is running.

Clickhouse1 Is getting monitored by the PMM server ( As of now, System Metrics only )

Step 4: Load the ClickHouse Dashboard

Import the clickhouse dashboard to the PMM2. Click the + add dashboard, and import the ClickHouse dashboard with id(https://grafana.com/grafana/dashboards/14192). Load it and save.

[This is only for the initial setup of the dashboards.]

Step 5: Add ClickHouse DB monitoring on PMM

# vim /etc/clickhouse-server/config.d/prometheus.xml 
<clickhouse>
  <prometheus>
    <endpoint>/metrics</endpoint>
    <port>8001</port>
    <metrics>true</metrics>
    <events>true</events>
    <asynchronous_metrics>true</asynchronous_metrics>
  </prometheus>
</clickhouse>

Restart ClickHouse database

# systemctl restart clickhouse-server

Integrate an external service into PMM from the ClickHouse node, where the PMM agent is set up to export system and ClickHouse metrics. Use the following command:

pmm-admin add external --service-name=clickhouse-db01 --listen-port=8001 --metrics-path=/metrics --scheme=http

To know more about ClickHouse Monitoring, do consider reading the following articles: 

• Real-Time Performance Monitoring for ClickHouse
• ClickHouse Monitoring: Disk I/O Metrics
• Monitoring ClickHouse Server Load