max_bytes_before_external_distinct
Query memory threshold, in bytes, for spillingDISTINCT data to disk. Actual memory usage can exceed
this threshold.
0 disables this threshold. If max_bytes_ratio_before_external_distinct also provides a threshold,
the smaller is used. Set both settings to 0 to disable spilling.
See DISTINCT in external memory.
max_bytes_before_external_group_by
Cloud default value: half the memory amount per replica. Enables or disables execution ofGROUP BY clauses in external memory.
(See GROUP BY in external memory)
Possible values:
- Maximum volume of RAM (in bytes) that can be used by the single GROUP BY operation.
0—GROUP BYin external memory disabled.
If memory usage during GROUP BY operations is exceeding this threshold in bytes,
activate the ‘external aggregation’ mode (spill data to disk).The recommended value is half of the available system memory.
max_bytes_before_external_join
If set to a non-zero value andjoin_algorithm is hash, parallel_hash, default, or auto, the hash join will automatically be converted to grace hash join to enable spilling to disk when the right-side data exceeds this many bytes. Together with max_bytes_ratio_before_external_join, this is the threshold-based spill trigger for every hash-based join_algorithm, including grace_hash, which requires one of the two to be non-zero. Once a non-zero threshold makes a join spill-capable, enable_adaptive_memory_spill_scheduler can force it to spill under memory pressure before the threshold is reached. When set to 0 (default), this absolute byte threshold is disabled, but automatic spilling may still occur via max_bytes_ratio_before_external_join (which defaults to 0.5). With both settings at 0, the join never spills, so the scheduler has nothing to trigger. The exception is legacy_join_size_limits_trigger_spilling: with it on, standalone grace_hash ignores both settings and spills on max_rows_in_join / max_bytes_in_join instead. It prevents read in order through join optimization. The threshold also bounds the right side materialized by the block nested loop join, whatever join_algorithm is set to.
max_bytes_before_external_set
Query memory threshold, in bytes, for spilling the set ofIN with a subquery to disk, while the set is
being built or while the query uses it. Actual memory usage can exceed this threshold. A set that takes
less memory than this threshold or 16 MiB, whichever is smaller, stays in memory.
0 disables this threshold. If max_bytes_ratio_before_external_set also provides a threshold, the
smaller is used. Set both settings to 0 to disable spilling.
See IN in external memory.
max_bytes_before_external_sort
Cloud default value: half the memory amount per replica. Enables or disables execution ofORDER BY clauses in external memory. See ORDER BY Implementation Details
If memory usage during ORDER BY operation exceeds this threshold in bytes, the ‘external sorting’ mode (spill data to disk) is activated.
Possible values:
- Maximum volume of RAM (in bytes) that can be used by the single ORDER BY operation. The recommended value is half of available system memory
0—ORDER BYin external memory disabled.
max_bytes_before_remerge_sort
In case of ORDER BY with LIMIT, when memory usage is higher than specified threshold, perform additional steps of merging blocks before final merge to keep just top LIMIT rows.max_bytes_for_external_set_bloom_filter
Memory budget, in bytes, for the Bloom filter that each set ofIN written to disk keeps in memory. The
filter lets lookups of values that are not in the set skip reading the disk. It does not speed up lookups
of values that are in the set.
A set takes about 1 byte per key for its filter, or the budget, whichever is smaller, where the keys of a
set are its distinct values, or its distinct tuples for IN with several columns:
- If the budget is at least 1 byte per key, the set gets the full filter, and about 97% of the lookups of values that are not in the set skip the disk. For example, a set of 1 million keys takes about 1 MiB.
- If the budget is smaller, the set gets a weaker filter that fits in the budget, and fewer lookups of values that are not in the set skip the disk. For example, with a quarter of a byte per key, about 60% of such lookups skip the disk instead of 97%.
- If the budget is less than about 1/7 of a byte per key, the set gets no filter, because such a filter would let too few lookups skip the disk to pay for building it.
0 disables the filter.
The filter counts towards the memory usage of the query, such as max_memory_usage, but not towards
max_bytes_in_set. The budget applies to each set, not to the query.
See IN in external memory.
max_bytes_for_external_set_cache
Memory budget, in bytes, for the cache of blocks that each set ofIN written to disk keeps in memory. A
lookup that needs a block already read by an earlier lookup gets it from the cache instead of reading it
from disk again. The cache helps when lookups repeatedly need the same blocks, such as lookups of a few
frequent values or many lookups into a set that fits in the cache. It helps little when lookups spread
evenly over a set much larger than the cache. 0 disables the cache.
The budget applies per set. The cache counts towards the memory usage of the query, such as
max_memory_usage, but not towards max_bytes_in_set.
See IN in external memory.
max_bytes_for_lazy_final
Maximum number of bytes in the set for lazy FINAL optimization. If exceeded, falls back to normal FINAL.max_bytes_in_distinct
The maximum number of bytes of the state (in uncompressed bytes) in memory, which is used by a hash table when using DISTINCT.max_bytes_in_join
The maximum size in bytes of the right-side data structure (typically a hash table) used when joining tables. This setting applies to SELECT … JOIN operations and the Join table engine. If a query contains multiple joins, ClickHouse checks this setting for every intermediate result. It is a hard cap for every hash-basedjoin_algorithm: when the limit
is reached the query throws or breaks according to
join_overflow_mode.
It never makes a join spill to disk — that decision belongs to
max_bytes_before_external_join
and
max_bytes_ratio_before_external_join.
Because it is a cap rather than a trigger, setting it at or below the spill
threshold normally makes the query fail before the join can spill at all —
unless the join is spill-capable and enable_adaptive_memory_spill_scheduler
forces a spill first, or
legacy_join_size_limits_trigger_spilling turns this limit back into a spill
trigger for the part of a join that already runs on disk.
The limit counts what the hash tables hold, so a join that spilled reaches it as
each bucket is loaded rather than while the right side is read: it can read more
of the right side before stopping than an in-memory hash join would.
When an ON section determines no join key at all, there is no algorithm to choose: the limit
applies to the right side materialized by the
block nested loop join,
spilled blocks included, and the action on overflow is
join_overflow_mode.
Possible values:
- Positive integer.
- 0 — Memory control is disabled.
max_bytes_in_set
The maximum number of bytes (of uncompressed data) used by a set in the IN clause created from a subquery.max_bytes_ratio_before_external_distinct
Fraction of available server or user memory used to calculate the externalDISTINCT threshold at
the start of execution. For example, 0.5 uses half of the available memory.
Values must be at least 0 and less than 1. 0 disables this threshold. Without an applicable
server or user memory limit, the ratio has no effect.
max_memory_usage does not affect this calculation. To configure spilling relative to that limit,
use max_bytes_before_external_distinct, leaving room for additional memory usage.
max_bytes_ratio_before_external_group_by
The ratio of available memory that is allowed forGROUP BY. Once reached,
external memory is used for aggregation.
For example, if set to 0.6, GROUP BY will allow using 60% of the available memory
(to server/user/merges) at the beginning of the execution, after that, it will
start using external aggregation.
max_bytes_ratio_before_external_join
The ratio of available memory that is allowed forJOIN. Once reached, the hash join will be converted to grace hash join to spill the right-side data to disk.
For example, if set to 0.6, JOIN will allow using 60% of the available memory (to server/user/merges) for the right-side hash table at the beginning of the execution; after that, it starts spilling to disk.
If both max_bytes_before_external_join and max_bytes_ratio_before_external_join are set, the smaller resulting threshold is used. If the ratio is 0, only the absolute setting applies.
Has effect for every hash-based join_algorithm, including grace_hash, provided a temporary data path is configured.
max_bytes_ratio_before_external_set
Fraction of available server or user memory used to calculate the threshold for spilling the set ofIN
with a subquery to disk, at the start of execution. For example, 0.5 uses half of the available memory.
Values must be at least 0 and less than 1. 0 disables this threshold. Without an applicable
server or user memory limit, the ratio has no effect.
max_memory_usage does not affect this calculation. To configure spilling relative to that limit,
use max_bytes_before_external_set, leaving room for additional memory usage.
max_bytes_ratio_before_external_sort
The ratio of available memory that is allowed forORDER BY. Once reached, external sort is used.
For example, if set to 0.6, ORDER BY will allow using 60% of available memory (to server/user/merges) at the beginning of the execution, after that, it will start using external sort.
Note, that max_bytes_before_external_sort is still respected, spilling to disk will be done only if the sorting block is bigger then max_bytes_before_external_sort.
max_bytes_to_read
The maximum number of bytes (of uncompressed data) that can be read from a table when running a query. The restriction is checked for each processed chunk of data, applied only to the deepest table expression and when reading from a remote server, checked only on the remote server.max_bytes_to_read_leaf
The maximum number of bytes (of uncompressed data) that can be read from a local table on a leaf node when running a distributed query. While distributed queries can issue a multiple sub-queries to each shard (leaf) - this limit will be checked only on the read stage on the leaf nodes and will be ignored on the merging of results stage on the root node. For example, a cluster consists of 2 shards and each shard contains a table with 100 bytes of data. A distributed query which is supposed to read all the data from both tables with settingmax_bytes_to_read=150 will fail as in total it
will be 200 bytes. A query with max_bytes_to_read_leaf=150 will succeed since
leaf nodes will read 100 bytes at max.
The restriction is checked for each processed chunk of data.
This setting is unstable with
prefer_localhost_replica=1.max_bytes_to_sort
The maximum number of bytes before sorting. If more than the specified amount of uncompressed bytes have to be processed for ORDER BY operation, the behavior will be determined by thesort_overflow_mode which by default is set to throw.