Tuning ClickHouse for High-velocity Data Ingestion in Distributed Tables

Introduction

In ClickHouse, which is an open-source columnar database management system, the parameters you’ve mentioned are related to the configuration of distributed tables and how data is handled within a distributed environment. A distributed table in ClickHouse is spread across multiple physical servers, and these parameters help control various aspects of data insertion, synchronization, and monitoring.

Key ClickHouse ingestion parameters

Let’s break down each parameter:

fsync_after_insert:

1. • This parameter determines whether a file-level fsync operation should be performed after an asynchronous insert into a distributed table.
   • When an insert is performed, this setting causes ClickHouse to ensure that the operating system flushes the entire inserted data to a file on the initiator node’s disk.
   • Guarantees data durability and persistence by syncing the written data to disk.
2. fsync_directories:
   • This parameter controls whether an fsync operation should be performed on directories.
   • After operations related to asynchronous inserts on a distributed table (like insert and sending data to shards), ClickHouse will use this setting to make sure the directory metadata is refreshed and updated.
   • Helps maintain accurate directory information in the file system.
3. bytes_to_throw_insert:
   • If the pending number of compressed bytes for asynchronous inserts exceeds this threshold, an exception will be thrown.
   • This can be useful to prevent an excessive backlog of unprocessed data, which might indicate issues in the system or configurations.
   • The default value is 0, meaning exceptions won’t be thrown based on this condition.
4. bytes_to_delay_insert:
   • If the pending number of compressed bytes for asynchronous inserts exceeds this threshold, the insertion query will be delayed.
   • Delaying insert queries can help regulate the rate at which data is being processed, preventing overwhelming the system with too much unprocessed data.
   • The default value is 0, meaning queries won’t be delayed based on this condition.
5. max_delay_to_insert:
   • Specifies the maximum delay in seconds for inserting data into a distributed table when there are a significant number of pending bytes for asynchronous sending.
   • If data accumulates and causes delays beyond this threshold, it can indicate potential performance or capacity issues.
   • The default value is 60 seconds.
6. monitor_batch_inserts, monitor_split_batch_on_failure, monitor_sleep_time_ms, monitor_max_sleep_time_ms:
   • These parameters are related to monitoring the behavior of batch inserts in distributed tables.
   • They control aspects like the number of batch inserts to monitor, whether to split a batch on failure, the sleep time for monitoring, and the maximum sleep time for monitoring.
   • These settings help fine-tune the monitoring behavior of distributed insert operations.

These parameters collectively provide control over the behavior and performance of asynchronous inserts and monitoring processes in a distributed ClickHouse setup, ensuring data durability, preventing overwhelming backlogs, and optimizing system performance.

Example:

Let’s say we have a ClickHouse setup with a distributed table named distributed_logs that spans across three physical servers (nodes: Node1, Node2, and Node3). The goal is to insert log data asynchronously into this distributed table while controlling various insertion-related parameters.

-- Distributed table definition
CREATE TABLE distributed_logs
(
    timestamp DateTime,
    log_message String
)
ENGINE = Distributed('cluster_name', 'default', 'logs', rand())
SETTINGS
    fsync_after_insert=0,
    fsync_directories=0;

-- Inserting data asynchronously
INSERT INTO distributed_logs (timestamp, log_message)
VALUES ('2023-08-20 12:00:00', 'Log entry 1'),
       ('2023-08-20 12:05:00', 'Log entry 2'),
       ('2023-08-20 12:10:00', 'Log entry 3');

Now, let’s consider how the parameters you mentioned come into play:

1. fsync_after_insert:
   • If set to 1, after each asynchronous insert, ClickHouse ensures that the OS flushes the entire inserted data to a file on the initiator node’s disk for durability.
2. fsync_directories:
   • If set to 1, ClickHouse performs an fsync operation on directories after asynchronous inserts. This refreshes the directory metadata and helps maintain accurate file system information.
3. bytes_to_throw_insert:
   • Let’s assume this is set to 1000000 bytes (1MB). If the total size of pending compressed data for asynchronous inserts exceeds this threshold, an exception will be thrown. This can help alert you to excessive backlog situations.
4. bytes_to_delay_insert:
   • Suppose this is set to 500000 bytes (500KB). If the pending compressed data size crosses this threshold, insert queries will be delayed to regulate the rate of data ingestion.
5. max_delay_to_insert:
   • If set to 60 seconds, the system will delay insert queries for a maximum of 60 seconds when there is a significant backlog of pending data.
6. Monitoring Parameters:
   • These parameters control the monitoring behavior of batch inserts. For example, you can set the number of batch inserts to monitor (monitor_batch_inserts), decide whether to split a batch on failure (monitor_split_batch_on_failure), control the sleep time between monitoring attempts (monitor_sleep_time_ms), and set the maximum sleep time for monitoring (monitor_max_sleep_time_ms).

Remember, the exact impact of these parameters will depend on your specific use case, system resources, and the amount of data being inserted. You would adjust these parameters based on your desired trade-off between data ingestion speed, system stability, and resource utilization.

Conclusion

Managing data ingestion within a distributed ClickHouse environment requires a thoughtful approach to balance speed, stability, and resource efficiency. By delving into the intricacies of insertion parameters, we’ve uncovered the mechanisms that govern how data is processed, synchronized, and monitored in a distributed table setup.

From fsync_after_insert and fsync_directories ensuring data durability and maintaining metadata accuracy, to bytes_to_throw_insert and bytes_to_delay_insert controlling data backlogs, each parameter plays a vital role in maintaining a healthy data ingestion pipeline. The max_delay_to_insert parameter acts as a safeguard against potential bottlenecks, preventing excessive delays during peak data loads.

Additionally, the monitoring parameters shed light on how batch inserts are supervised, enabling administrators to fine-tune these settings to match their infrastructure’s capabilities and requirements.

To know more about High-performance ingestion in ClickHouse, do read the following articles:

• Boosting ClickHouse Ingestion Performance by Disabling Foreign Key Checks
• ClickHouse Server Configuration for High-volume Data Ingestion
• Ingesting JSON Data in ClickHouse
• Configuring Asynchronous Inserts in ClickHouse for High Performance Data Loading

References:

https://clickhouse.com/docs/en/engines/table-engines/special/distributed