max_bytes_* session settings

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

max_bytes_before_external_group_by

Cloud default value: half the memory amount per replica.

Enables or disables execution of GROUP 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 BY in external memory disabled.

max_bytes_before_external_join

If set to a non-zero value and join_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. 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 of ORDER 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 BY in 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. When the limit is reached, the action depends on the chosen join_algorithm — see that setting for the per-algorithm behavior (spill, re-partition, switch, or throw/break per 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_group_by

The ratio of available memory that is allowed for GROUP 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 for JOIN. 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 only when join_algorithm is hash, parallel_hash, default, or auto and a temporary data path is configured.

max_bytes_ratio_before_external_sort

The ratio of available memory that is allowed for ORDER 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 setting max_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.

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 the sort_overflow_mode which by default is set to throw.

max_bytes_to_transfer

The maximum number of bytes (uncompressed data) that can be passed to a remote server or saved in a temporary table when the GLOBAL IN/JOIN section is executed.