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, 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 withmax_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; 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 and spills on max_rows_in_join / max_bytes_in_join instead. 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); set both to 0 to fully disable automatic spilling. It prevents read in order through join optimization.
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_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.
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_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.