Understanding CPU usage by ClickHouse Server

Shiv Iyer
Posted on February 4, 2023

ClickHouse is designed to be highly optimized for performance and scalability, with a focus on efficient use of system resources, including CPU usage. Understanding the CPU usage by a ClickHouse server can be important for optimizing performance and resolving any performance bottlenecks.

ClickHouse utilizes a parallel query execution engine that distributes workloads across multiple cores, maximizing the use of available resources. The server collects various performance metrics, including CPU usage, and displays them in the system.metrics table, providing detailed information about the performance of the server.

It is possible to monitor the CPU usage of a ClickHouse server by accessing this information through the system.metrics table, or by using various third-party tools, such as ClickHouse Monitor, for real-time monitoring and visualization of performance metrics.

To understand the CPU usage by a ClickHouse server, it is important to analyze the system.metrics table and identify any trends or patterns in CPU utilization, including any spikes or sustained high usage that may indicate performance bottlenecks. Other factors, such as network bandwidth and I/O performance, should also be considered when analyzing CPU usage and optimizing performance.

Here’s a SQL script you can use to monitor CPU usage by the ClickHouse server:

  toStartOfMinute(event_time) as minute, 
  avg(value) as avg_value
FROM system.metrics
WHERE metric = 'system.cpu.load.15'
GROUP BY minute

This script retrieves the average CPU usage in 15-minute increments, as reported by the system.cpu.load.15 metric, over the past 60 minutes. The results are sorted in descending order by the minute timestamp, so that the most recent data is displayed first.