How to Monitor ClickHouse With Telegraf and MetricFire

Introduction 

Monitoring your ClickHouse database is a proactive measure that helps maintain its health and ensure that it continues to meet the needs of your applications and users efficiently. It allows you to address issues before they become critical, ensuring that your database environment is secure, reliable, and performing optimally.

In this article, we'll detail how to use the Telegraf agent to collect performance metrics from your ClickHouse clusters and forward them to a data source.

Getting Started with the Telegraf Agent

Telegraf is a plugin-driven server agent built on InfluxDB and can be used for collecting and sending metrics/events from databases, systems, devices, and a range of popular technologies. Telegraf is written in Go and compiles into a single binary with no external dependencies, and requires a very minimal memory footprint. It is compatible with many operating systems and has many useful output plugins and input plugins for collecting and forwarding a wide variety of performance metrics. 

Install Telegraf (Linux/Redhat)

wget https://dl.influxdata.com/telegraf/releases/telegraf_1.21.2-1_amd64.deb

sudo dpkg -i telegraf_1.21.2-1_amd64.deb

RedHat/CentOS

wget https://dl.influxdata.com/telegraf/releases/telegraf-1.21.4-1.x86_64.rpm

sudo yum localinstall telegraf-1.21.4-1.x86_64.rpm

Configure an Output

You can configure telegraf to output to a variety of sources, like Kafka, Graphite, InfluxDB, Prometheus, SQL, NoSQL, and more.

In this example, we will configure telegraf with a Graphite output. If you're not currently hosting your own data source, you can start a 14-day free trial with Hosted Graphite by MetricFire to follow along with these next steps.

A Hosted Graphite account will provide the data source, include Hosted Grafana as a visualization tool, and offer an alerting feature.

To configure the Graphite output, you need to locate the downloaded telegraf configuration file at /etc/telegraf/telegraf.conf and open it in your preferred text editor. Then you will need to make the following changes to the file:

Locate and comment out the line:

# [[outputs.influxdb]]

Then, uncomment the line:

[[outputs.graphite]]

Next, uncomment and edit the server line to:

servers = ["carbon.hostedgraphite.com:2003"]

Finally, uncomment and edit the prefix line to:

prefix = "<YOUR_API_KEY>.telegraf"

Install and Configure ClickHouse

This article assumes that you are already using an active instance of ClickHouse. If you need to install/launch ClickHouse for testing purposes, here is a quick guide to get up and running in a Linux environment:

• add the ClickHouse repository to your package manager:
  • sudo apt-get install apt-transport-https ca-certificates dirmngr
  • sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4
  • echo "deb http://repo.clickhouse.tech/deb/stable/ main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list
  • sudo apt-get update
• Install and start clickhouse-server:
  • sudo apt-get install clickhouse-server clickhouse-client
  • sudo service clickhouse-server start
• Configure clickhouse:
  • open the config file at: /etc/clickhouse-server/config.xml
  • uncomment the listen_host::wildcard line to accept connections from everywhere:
  • <listen_host>::</listen_host>
  • save the file and restart clickhouse: sudo service clickhouse-server restart

Configure the ClickHouse Input Plugin

Telegraf has many input plugins that can collect a wide range of data from many popular technologies and 3rd party sources. In this example, we'll demonstrate how to configure the clickhouse plugin.

Within your telegraf.conf file, locate and uncomment the [[inputs.clickhouse]] line:

[[inputs.clickhouse]]

Then you can uncomment the 'URL' and credential lines, the default path is:

username = "default"
servers = ["http://127.0.0.1:8123"]

Save your changes, and then start Telegraf using the following command, to see if there are any configuration errors in the output:

telegraf --config telegraf.conf

Telegraf will now be forwarding roughly 1000 metrics per cluster that report on the block, filesystem, CPU, memory, disk, network, OS, replicas, and many types of DB events.

See the official GitHub repository for more information and configuration options for this input plugin.

Use Hosted Graphite by MetricFire to Create Custom Dashboards and Alerts

MetricFire is a monitoring platform that enables you to gather, visualize, analyze, and alert on metrics from sources such as servers, databases, networks, devices, and applications. By utilizing MetricFire, you can effortlessly identify problems and optimize resources from within your infrastructure. Hosted Graphite by MetricFire takes away the burden of self-hosting your own monitoring solution, allowing you more time and freedom to work on your most important tasks.

1. Once you have signed up for a Hosted Graphite account and used the above steps to configure your server with the Telegraf Agent, metrics will be forwarded, timestamped, ingested, and aggregated into the Hosted Graphite backend.
2. They will be sent and stored in the Graphite format of metric.name.path <numeric-value> <unix-timestamp>, which provides a tree-like data structure and makes them easy to query.
3. You can locate these metrics in your Hosted Graphite account, and use them to build custom Alerts and Grafana dashboards.

Create Dashboards in Hosted Graphite's Hosted Grafana

First, locate your metrics in the Hosted Graphite Metrics Search UI. Then, navigate to Dashboards => Primary Dashboards and select the + button to build a new panel:

Then you can use the query UI to select a graphite metric path (the default data source will be the hosted graphite backend if you are accessing Grafana through your Hosted Graphite account):

NOTE: the Hosted Graphite datasource supports wildcard (*) searches to grab all metrics that match a specified path.

Now you can apply Graphite functions to these metrics, like groupByNode() to combine values from multiple metric paths at a specified index:

Grafana has many additional options like configuring dashboard variables and annotations. You can also use different visualizations, modify the display, set the units of measurement, and much more.

Additionally Hosted Graphite has a pre-configured dashboard for Telegraf metrics located in their Dashboard Library. Once this dashboard is generated in your account, you can locate it in your Primary Dashboards to see system metrics (CPU, mem, disk) displayed. These system performance metrics come automatically with a Telegraf => Graphite configuration:

See the Hosted Graphite dashboard docs for more details.

Creating Graphite Alerts

In the Hosted Graphite UI, navigate to Alerts => Graphite Alerts to create a new alert. Name the alert, add one of your graphite metrics to the alerting metric field, and add a description of what this alert is:

Then select the Criteria tab which will set the threshold, and select a notification channel. The default notification channel is the email you used to sign up for the Hosted Graphite account, but you can easily configure a channel for Slack, PagerDuty, Microsoft Teams, and more. See the Hosted Graphite docs for more details on notification channels:

Conclusion

Monitoring ClickHouse is crucial for business operations as it ensures the database performs optimally, preventing downtime and ensuring fast, reliable access to data, which is vital for decision-making and customer satisfaction. It also aids in resource optimization and cost management by enabling proactive troubleshooting and capacity planning, ensuring the database infrastructure scales efficiently with business needs.

Using tools like dashboards and alerts complements this monitoring by providing real-time visualization, proactive identification of issues, historical trend analysis, and facilitating informed decision-making, all of which are essential for maintaining a robust and efficient network infrastructure.

Sign up for a free trial, and start monitoring your infrastructure today! You can also book a demo and talk to the MetricFire team directly about your monitoring needs.