How to Monitor & Troubleshoot Log Contention in ClickHouse

Troubleshooting Log Contention Happening to ClickHouse


Log contention in ClickHouse occurs when multiple write operations compete for the same log file, causing performance issues and potentially leading to data corruption. Here are some steps you can take to troubleshoot and resolve these issues in ClickHouse:

  1. Monitor ClickHouse server metrics: Use ClickHouse’s built-in system tables to monitor server metrics, such as the number of write operations, disk usage, and query performance. You can use tools like Grafana or Prometheus to visualize these metrics and identify any trends or anomalies that may be contributing to log contention.
  2. Check ClickHouse server configuration: Review the ClickHouse server configuration to ensure that the log settings are optimized for your workload. You may need to adjust settings such as log_max_size, log_rotation_age, and log_rotation_size to reduce the likelihood of log contention. You can also consider increasing the number of log files to reduce contention.
  3. Check hardware resources: Check the hardware resources of the server hosting ClickHouse to ensure that it has enough CPU, memory, and disk I/O capacity to handle the workload. If resources are limited, consider adding more resources or upgrading to a more powerful server.
  4. Check disk I/O performance: Check the disk I/O performance of the server hosting ClickHouse to ensure that it can handle the write workload. You may need to upgrade the disk hardware or configure the operating system to optimize disk I/O performance.
  5. Monitor ClickHouse server logs: Check the ClickHouse server logs for any error messages or warnings related to log contention. You can use tools like tail or grep to filter the logs for relevant messages.
  6. Monitor network traffic: Check the network traffic between the ClickHouse server and any clients or other servers to ensure that it is not causing contention or performance issues. You can use tools like tcpdump or wireshark to capture and analyze network traffic.
  7. Consider alternative storage engines: If log contention continues to be an issue, consider using an alternative storage engine that is better suited to your workload. ClickHouse supports several storage engines, including MergeTree, CollapsingMergeTree, and SummingMergeTree, each of which has its own strengths and weaknesses.

Monitoring log contention happening to ClickHouse

To monitor log contention in ClickHouse, you can create an SQL script that queries the system tables and displays relevant metrics. Here’s an example SQL script that you can use:

  sum(if(metric_name='log_bytes_written', metric_value, 0)) as total_log_bytes_written,
  sum(if(metric_name='log_write_time', metric_value, 0)) as total_log_write_time,
  sum(if(metric_name='log_writes', metric_value, 0)) as total_log_writes,
  sum(if(metric_name='log_syncs', metric_value, 0)) as total_log_syncs
FROM system.metrics
WHERE metric_name IN ('log_bytes_written', 'log_write_time', 'log_writes', 'log_syncs')

This script queries the system.metrics table and aggregates metrics related to log writes and syncs, including the total number of bytes written to the log, the total time spent writing to the log, the total number of log writes, and the total number of log syncs.

You can run this script periodically to monitor log contention in real-time and detect any trends or anomalies that may be indicative of contention. Additionally, you can use this script in conjunction with other monitoring tools and techniques to gain a more comprehensive understanding of log contention issues in ClickHouse.

What are reasons for log contention in ClickHouse and how to address log contention proactively?

Log contention in ClickHouse can occur due to several reasons, including high write throughput, inefficient log file rotation, and limited disk I/O capacity. Here are some reasons for log contention and how to address them proactively:

  1. High write throughput: If your workload involves a high volume of write operations, it can lead to log contention. To address this, you can consider sharding your data across multiple servers or using a distributed file system such as HDFS to spread the write load across multiple nodes.
  2. Inefficient log file rotation: ClickHouse’s default log file rotation settings may not be optimized for your workload, leading to contention. To address this, you can adjust the log_max_size, log_rotation_age, and log_rotation_size settings to better suit your workload. You can also increase the number of log files to reduce contention.
  3. Limited disk I/O capacity: If the disk hosting the log files cannot keep up with the write throughput, it can lead to contention. To address this, you can consider using a high-performance SSD or NVMe disk or upgrading to a more powerful server with more disk I/O capacity.
  4. Inefficient use of ClickHouse features: Certain features of ClickHouse, such as the buffer pool and the write-ahead log, can impact log contention. To address this, you can optimize your queries to make efficient use of the buffer pool and minimize the number of write operations performed.


To address log contention proactively, you can also monitor server metrics using tools such as Grafana or Prometheus to detect any trends or anomalies that may be indicative of contention. Additionally, you can regularly review the ClickHouse server configuration and adjust settings as needed to optimize performance and reduce contention.

To read more about the Troubleshooting in ClickHouse, do consider reading the below article

About Shiv Iyer 225 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.