max_bytes_to_merge_at_max_space_in_pool

  • This parameter determines the maximum number of bytes that can be merged during compaction when the total size of the merged data exceeds the available space in a merge pool.

  • This setting helps control the size of each merge operation and prevents merging a large amount of data at once.

old_parts_lifetime

  • In ClickHouse, data is stored in parts within each level of the LSM tree structure. Over time, as new data is ingested and compaction occurs, older data becomes obsolete and no longer needed. The old_parts_lifetime parameter allows you to define how long data parts must persist before they are considered for cleanup and removal.
  • The cleanup process removes the disk files associated with these old data parts, freeing up storage space and maintaining optimal storage utilization.
  • You can set a shorter lifetime to trigger cleanup more frequently.

cleanup_delay_period

  • When a partition is marked for deletion in ClickHouse, rather than immediately triggering the cleanup process to remove the associated data files, the system introduces a delay based on the value set for cleanup_delay_period. This delay allows for a grace period during which the deleted data can still be accessed or recovered if needed.
  • By default, ClickHouse sets a certain duration for cleanup_delay_period, typically a few minutes, to account for any accidental deletions or potential data recovery scenarios. During this delay period, the partition is still accessible for queries and the associated data files are not immediately removed.

Replacing vs dropping a partition

Replace a partition

  • New Partition Creation: ClickHouse creates a new partition to replace the existing partition. This new partition contains the updated data or modifications.
  • Background Merge: ClickHouse runs a background merge job to gradually move data from the old partition to the new partition. It performs this merge operation incrementally, in small batches, and asynchronously in the background.
  • Continuous Query Processing: While the merge job is running, ClickHouse continues to process queries and serve data from both the old and new partitions. ClickHouse automatically combines the data from both partitions to provide query results, ensuring that queries are not affected by the ongoing partition replacement.
  • Merge Completion: Once the merge job is completed, ClickHouse removes the old partition, and the data is fully consolidated in the new partition.
  • During a partition replace operation, ClickHouse ensures that the replacement of the existing partition with the new partition containing updated data is performed as an atomic operation. This guarantees that the replacement is either completed successfully or not performed at all, without leaving any intermediate or inconsistent state.
  • During a partition replace operation, the original partition is effectively replaced with the new partition containing updated data. This does not involve explicitly marking the original partition for deletion.

Dropping a partition

  • Command Execution: When you execute the DROP PARTITION command, ClickHouse receives the request to drop the specified partition(s).
  • Scheduling: ClickHouse schedules the deletion of the partition(s) in the background. The actual deletion process occurs asynchronously and does not block or pause the execution of subsequent queries or operations.
  • Continuous Query Processing: ClickHouse continues to serve queries and perform operations while the partition deletion process is ongoing. Queries can still access data from the remaining partitions and unaffected parts of the table.
  • Deletion Completion: Eventually, in the background and depending on the size and complexity of the partition, ClickHouse completes the deletion of the specified partition(s). The timing of deletion completion varies based on various factors, such as the size of the partition, system load, and available resources.
  • While dropping a partition is asynchronous, it’s important to note that once the deletion process begins, the affected partition and its data will be marked for deletion and subsequent queries will no longer include the dropped partition’s data in the results. However, depending on the size of the partition and the system load, it may take some time for the deletion process to complete.

cleanup_thread_priority

Sets the priority of the cleanup thread. A higher priority may result in more frequent cleanup but can also impact the performance of other operations. Adjust this parameter based on system resources and workload requirements.

parts_to_delay_insert

When new data is inserted into a MergeTree table, ClickHouse may delay the merge operation to optimize performance. Instead of immediately merging each inserted part, ClickHouse waits for a certain number of parts to accumulate, as specified by parts_to_delay_insert, before triggering the merge operation. This delay reduces the frequency of merge operations and helps improve the overall performance of data insertion.

clear_old_parts_interval_seconds

In ClickHouse’s MergeTree engine, as newer data is ingested and merges occur, older parts that are no longer required become eligible for removal. The cleanup process periodically checks for these old parts based on the clear_old_parts_interval_seconds value. If an eligible part is found, it is removed during the cleanup.

Monitoring

SELECT
    task_type,
    current_state,
    progress
FROM
    system.background_tasks --including the cleanup tasks.
WHERE
    task_type = 'Merge' AND current_state = 'Executing';
  • Please note that the progress value in the system.background_tasks table may not correspond directly to the total number of parts or the amount of data cleaned up. It represents the internal progress of the current merge task, and the actual cleanup progress might involve multiple merge tasks over time.
  • system.mutations: This table provides information about mutations, including partition deletions that trigger the cleanup process.