merge_tree_* session settings

These settings are available in system.settings and are autogenerated from source.

merge_tree_coarse_index_granularity

When searching for data, ClickHouse checks the data marks in the index file. If ClickHouse finds that required keys are in some range, it divides this range into merge_tree_coarse_index_granularity subranges and searches the required keys there recursively.

Possible values:

• Any positive even integer.

merge_tree_compact_parts_min_granules_to_multibuffer_read

Only has an effect in ClickHouse Cloud. Number of granules in stripe of compact part of MergeTree tables to use multibuffer reader, which supports parallel reading and prefetch. In case of reading from remote fs using of multibuffer reader increases number of read request. A part is read with a single buffer when it contains both fewer granules than this threshold and fewer granules than the number of columns being read, because in that case the single buffer reader issues fewer read requests than the multibuffer reader.

merge_tree_determine_task_size_by_prewhere_columns

Whether to use only prewhere columns size to determine reading task size.

merge_tree_generic_exclusion_search_max_steps

When a filter cannot be evaluated as a single continuous range of the primary key, for example when it uses key columns other than the first one, ClickHouse runs an iterative generic exclusion search algorithm over the index marks. The same algorithm is used for the analysis of the text index. This setting limits the number of steps (index checks) the algorithm spends on each data part.

The budget is spent on the largest remaining mark ranges first. When it is exhausted, the ranges that were not fully analyzed are accepted as a whole, so the query stays correct but may read more granules than an unlimited search would select. A lower budget speeds up index analysis at the cost of reading more data. The limit is approximate rather than a strict cap on the analysis cost: the search can exceed it by roughly one round of splitting, and when the part is already divided into many ranges (for example, by the query condition cache), each of them is checked at least once regardless of the limit.

The number of steps the search made for each data part is reported in the trace level log messages of the query, and the IndexGenericExclusionSearchStepLimitReached and TextIndexGenericExclusionSearchStepLimitReached profile events count how many times the budget was exhausted.

The (default) value 0 means unlimited steps.

Possible values:

• 0 for unlimited steps, or any positive integer.

merge_tree_max_bytes_to_use_cache

If ClickHouse should read more than merge_tree_max_bytes_to_use_cache bytes in one query, it does not use the cache of uncompressed blocks.

The cache of uncompressed blocks stores data extracted for queries. ClickHouse uses this cache to speed up responses to repeated small queries. This setting protects the cache from trashing by queries that read a large amount of data. The uncompressed_cache_size server setting defines the size of the cache of uncompressed blocks.

Possible values:

• Any positive integer.

merge_tree_max_rows_to_use_cache

If ClickHouse should read more than merge_tree_max_rows_to_use_cache rows in one query, it does not use the cache of uncompressed blocks.

The cache of uncompressed blocks stores data extracted for queries. ClickHouse uses this cache to speed up responses to repeated small queries. This setting protects the cache from trashing by queries that read a large amount of data. The uncompressed_cache_size server setting defines the size of the cache of uncompressed blocks.

Possible values:

• Any positive integer.

merge_tree_min_bytes_for_concurrent_read

If the number of bytes to read from one file of a MergeTree-engine table exceeds merge_tree_min_bytes_for_concurrent_read, then ClickHouse tries to concurrently read from this file in several threads.

Possible value:

• Positive integer.

merge_tree_min_bytes_for_concurrent_read_for_remote_filesystem

The minimum number of bytes to read from one file before MergeTree engine can parallelize reading, when reading from remote filesystem. We do not recommend using this setting.

Possible values:

• Positive integer.

merge_tree_min_bytes_for_seek

If the distance between two data blocks to be read in one file is less than merge_tree_min_bytes_for_seek bytes, then ClickHouse sequentially reads a range of file that contains both blocks, thus avoiding extra seek.

Possible values:

• Any positive integer.

merge_tree_min_bytes_per_read_stream

Per-stream overhead cost expressed as byte-equivalent. Controls the maximum number of read streams created for ordinary local unordered MergeTree scans. Ordered, FINAL, layered, and parallel-replica reads are not changed. The volume-based cap is ceil(sqrt(estimated_read_bytes / this_setting)). ClickHouse also preserves at least 16 streams and at least one quarter of the streams requested before this cap, so the effective stream count is min(requested_streams, max(volume_based_cap, 16, floor(requested_streams / 4))). These lower bounds preserve downstream parallelism for CPU-heavy expressions and aggregations, whose cost is not represented by estimated_read_bytes.

estimated_read_bytes is the uncompressed size of the columns being read over the mark ranges left after index and partition pruning. Uncompressed size is used because per-stream overhead is traded against the work done per stream, which scales with the number of values processed rather than with how well they compress.

The cap is not applied when the read volume cannot be estimated conservatively, for example when an old part needs other physical columns to evaluate a newly added DEFAULT column, or when a selected range reads only part of a variable-width column.

The default of 64 KB is derived from the cost model T(N) = W/N + F + V·N, where W is useful work, F is fixed pipeline overhead, and V is per-stream variable cost. The optimal stream count is sqrt(W/V). Expressing W in bytes via throughput gives C = throughput · V ≈ 400 MB/s · 0.17 ms ≈ 64 KB. Increasing this value reduces the number of streams (more conservative); decreasing it allows more streams. Set to 0 to disable this optimization.

Possible values:

• Non-negative integer.

merge_tree_min_bytes_per_task_for_remote_reading

Aliases: filesystem_prefetch_min_bytes_for_single_read_task

Min bytes to read per task.

merge_tree_min_read_task_size

Hard lower limit on the task size (even when the number of granules is low and the number of available threads is high we won’t allocate smaller tasks

merge_tree_min_rows_for_concurrent_read

If the number of rows to be read from a file of a MergeTree table exceeds merge_tree_min_rows_for_concurrent_read then ClickHouse tries to perform a concurrent reading from this file on several threads.

Possible values:

• Positive integer.

merge_tree_min_rows_for_concurrent_read_for_remote_filesystem

The minimum number of lines to read from one file before the MergeTree engine can parallelize reading, when reading from remote filesystem. We do not recommend using this setting.

Possible values:

• Positive integer.

merge_tree_min_rows_for_seek

If the distance between two data blocks to be read in one file is less than merge_tree_min_rows_for_seek rows, then ClickHouse does not seek through the file but reads the data sequentially.

Possible values:

• Any positive integer.

merge_tree_prefetch_json_shared_data_substreams

Enables prefetching of JSON shared data substreams in Wide parts that are read by seeking to a mark. Such a prefetch reads from the beginning of the granule, which is usually not the position the substream is read from, so it can be wasted. Disable to skip these prefetches.

merge_tree_read_split_ranges_into_intersecting_and_non_intersecting_injection_probability

For testing of PartsSplitter - split read ranges into intersecting and non intersecting every time you read from MergeTree with the specified probability.

merge_tree_storage_snapshot_sleep_ms

Inject artificial delay (in milliseconds) when creating a storage snapshot for MergeTree tables. Used for testing and debugging purposes only.

Possible values:

• 0 - No delay (default)
• N - Delay in milliseconds

merge_tree_use_const_size_tasks_for_remote_reading

Whether to use constant size tasks for reading from a remote table.

merge_tree_use_deserialization_prefixes_cache

Enables caching of columns metadata from the file prefixes during reading from remote disks in MergeTree.

merge_tree_use_prefixes_deserialization_thread_pool

Enables usage of the thread pool for parallel prefixes reading in Wide parts in MergeTree. Size of that thread pool is controlled by server setting max_prefixes_deserialization_thread_pool_size.

merge_tree_use_v1_object_and_dynamic_serialization

When enabled, V1 serialization version of JSON and Dynamic types will be used in MergeTree instead of V2. Changing this setting takes affect only after server restart.