Usage: /usr/bin/clickhouse-benchmark [options] < queries.txt Allowed options: --help produce help message -q [ --query ] arg query to execute -c [ --concurrency ] arg (=1) number of parallel queries -d [ --delay ] arg (=1) delay between intermediate reports in seconds (set 0 to disable reports) --stage arg (=complete) request query processing up to specified stage: complete,fetch_columns ,with_mergeable_state,with_mergeable_st ate_after_aggregation,with_mergeable_st ate_after_aggregation_and_limit -i [ --iterations ] arg (=0) amount of queries to be executed -t [ --timelimit ] arg (=0) stop launch of queries after specified time limit -r [ --randomize ] randomize order of execution -h [ --host ] arg list of hosts --port arg list of ports --roundrobin Instead of comparing queries for different --host/--port just pick one random --host/--port for every query and send query to it. --cumulative prints cumulative data instead of data per interval -s [ --secure ] Use TLS connection -u [ --user ] arg (=default) --password arg --quota_key arg --database arg (=default) --stacktrace print stack traces of exceptions --confidence arg (=5) set the level of confidence for T-test [0=80%, 1=90%, 2=95%, 3=98%, 4=99%, 5=99.5%(default) --query_id arg --query_id_prefix arg --max-consecutive-errors arg (=0) set number of allowed consecutive errors --ignore-error continue testing even if a query fails --reconnect establish new connection for every query --client-side-time display the time including network communication instead of server-side time; note that for server versions before 22.8 we always display client-side time --dialect arg Which dialect will be used to parse query --min_compress_block_size arg For [MergeTree](../../engines/table-eng ines/mergetree-family/mergetree.md) tables. In order to reduce latency when processing queries, a block is compressed when writing the next mark if its size is at least `min_compress_block_size`. By default, 65,536. The actual size of the block, if the uncompressed data is less than `max_compress_block_size`, is no less than this value and no less than the volume of data for one mark. Let's look at an example. Assume that `index_granularity` was set to 8192 during table creation. We are writing a UInt32-type column (4 bytes per value). When writing 8192 rows, the total will be 32 KB of data. Since min_compress_block_size = 65,536, a compressed block will be formed for every two marks. We are writing a URL column with the String type (average size of 60 bytes per value). When writing 8192 rows, the average will be slightly less than 500 KB of data. Since this is more than 65,536, a compressed block will be formed for each mark. In this case, when reading data from the disk in the range of a single mark, extra data won't be decompressed. :::note This is an expert-level setting, and you shouldn't change it if you're just getting started with ClickHouse. ::: --max_compress_block_size arg The maximum size of blocks of uncompressed data before compressing for writing to a table. By default, 1,048,576 (1 MiB). Specifying a smaller block size generally leads to slightly reduced compression ratio, the compression and decompression speed increases slightly due to cache locality, and memory consumption is reduced. :::note This is an expert-level setting, and you shouldn't change it if you're just getting started with ClickHouse. ::: Don't confuse blocks for compression (a chunk of memory consisting of bytes) with blocks for query processing (a set of rows from a table). --max_block_size arg In ClickHouse, data is processed by blocks, which are sets of column parts. The internal processing cycles for a single block are efficient but there are noticeable costs when processing each block. The `max_block_size` setting indicates the recommended maximum number of rows to include in a single block when loading data from tables. Blocks the size of `max_block_size` are not always loaded from the table: if ClickHouse determines that less data needs to be retrieved, a smaller block is processed. The block size should not be too small to avoid noticeable costs when processing each block. It should also not be too large to ensure that queries with a LIMIT clause execute quickly after processing the first block. When setting `max_block_size`, the goal should be to avoid consuming too much memory when extracting a large number of columns in multiple threads and to preserve at least some cache locality. --max_insert_block_size arg The size of blocks (in a count of rows) to form for insertion into a table. This setting only applies in cases when the server forms the blocks. For example, for an INSERT via the HTTP interface, the server parses the data format and forms blocks of the specified size. But when using clickhouse-client, the client parses the data itself, and the 'max_insert_block_size' setting on the server does not affect the size of the inserted blocks. The setting also does not have a purpose when using INSERT SELECT, since data is inserted using the same blocks that are formed after SELECT. The default is slightly more than `max_block_size`. The reason for this is that certain table engines (`*MergeTree`) form a data part on the disk for each inserted block, which is a fairly large entity. Similarly, `*MergeTree` tables sort data during insertion, and a large enough block size allow sorting more data in RAM. --min_insert_block_size_rows arg Sets the minimum number of rows in the block that can be inserted into a table by an `INSERT` query. Smaller-sized blocks are squashed into bigger ones. Possible values: - Positive integer. - 0 — Squashing disabled. --min_insert_block_size_bytes arg Sets the minimum number of bytes in the block which can be inserted into a table by an `INSERT` query. Smaller-sized blocks are squashed into bigger ones. Possible values: - Positive integer. - 0 — Squashing disabled. --min_insert_block_size_rows_for_materialized_views arg Sets the minimum number of rows in the block which can be inserted into a table by an `INSERT` query. Smaller-sized blocks are squashed into bigger ones. This setting is applied only for blocks inserted into [materialized view](../../sql-reference /statements/create/view.md). By adjusting this setting, you control blocks squashing while pushing to materialized view and avoid excessive memory usage. Possible values: - Any positive integer. - 0 — Squashing disabled. **See Also** - [min_insert_block_size_rows](#min_ins ert_block_size_rows) --min_insert_block_size_bytes_for_materialized_views arg Sets the minimum number of bytes in the block which can be inserted into a table by an `INSERT` query. Smaller-sized blocks are squashed into bigger ones. This setting is applied only for blocks inserted into [materialized view](../../sql-reference /statements/create/view.md). By adjusting this setting, you control blocks squashing while pushing to materialized view and avoid excessive memory usage. Possible values: - Any positive integer. - 0 — Squashing disabled. **See also** - [min_insert_block_size_bytes](#min_in sert_block_size_bytes) --min_external_table_block_size_rows arg Squash blocks passed to external table to specified size in rows, if blocks are not big enough. --min_external_table_block_size_bytes arg Squash blocks passed to the external table to a specified size in bytes, if blocks are not big enough. --max_joined_block_size_rows arg Maximum block size for JOIN result (if join algorithm supports it). 0 means unlimited. --min_joined_block_size_bytes arg Minimum block size for JOIN result (if join algorithm supports it). 0 means unlimited. --max_insert_threads arg The maximum number of threads to execute the `INSERT SELECT` query. Possible values: - 0 (or 1) — `INSERT SELECT` no parallel execution. - Positive integer. Bigger than 1. Cloud default value: from `2` to `4`, depending on the service size. Parallel `INSERT SELECT` has effect only if the `SELECT` part is executed in parallel, see [max_threads](#max_thr eads) setting. Higher values will lead to higher memory usage. --max_insert_delayed_streams_for_parallel_write arg The maximum number of streams (columns) to delay final part flush. Default - auto (1000 in case of underlying storage supports parallel write, for example S3 and disabled otherwise) --max_final_threads arg Sets the maximum number of parallel threads for the `SELECT` query data read phase with the [FINAL](/sql-refere nce/statements/select/from#final-modifi er) modifier. Possible values: - Positive integer. - 0 or 1 — Disabled. `SELECT` queries are executed in a single thread. --max_threads_for_indexes arg The maximum number of threads process indices. --max_threads arg The maximum number of query processing threads, excluding threads for retrieving data from remote servers (see the 'max_distributed_connections' parameter). This parameter applies to threads that perform the same stages of the query processing pipeline in parallel. For example, when reading from a table, if it is possible to evaluate expressions with functions, filter with WHERE and pre-aggregate for GROUP BY in parallel using at least 'max_threads' number of threads, then 'max_threads' are used. For queries that are completed quickly because of a LIMIT, you can set a lower 'max_threads'. For example, if the necessary number of entries are located in every block and max_threads = 8, then 8 blocks are retrieved, although it would have been enough to read just one. The smaller the `max_threads` value, the less memory is consumed. --use_concurrency_control [=arg(=1)] Respect the server's concurrency control (see the `concurrent_threads_so ft_limit_num` and `concurrent_threads_s oft_limit_ratio_to_cores` global server settings). If disabled, it allows using a larger number of threads even if the server is overloaded (not recommended for normal usage, and needed mostly for tests). --max_download_threads arg The maximum number of threads to download data (e.g. for URL engine). --max_parsing_threads arg The maximum number of threads to parse data in input formats that support parallel parsing. By default, it is determined automatically --max_download_buffer_size arg The maximal size of buffer for parallel downloading (e.g. for URL engine) per each thread. --max_read_buffer_size arg The maximum size of the buffer to read from the filesystem. --max_read_buffer_size_local_fs arg The maximum size of the buffer to read from local filesystem. If set to 0 then max_read_buffer_size will be used. --max_read_buffer_size_remote_fs arg The maximum size of the buffer to read from remote filesystem. If set to 0 then max_read_buffer_size will be used. --max_distributed_connections arg The maximum number of simultaneous connections with remote servers for distributed processing of a single query to a single Distributed table. We recommend setting a value no less than the number of servers in the cluster. The following parameters are only used when creating Distributed tables (and when launching a server), so there is no reason to change them at runtime. --max_query_size arg The maximum number of bytes of a query string parsed by the SQL parser. Data in the VALUES clause of INSERT queries is processed by a separate stream parser (that consumes O(1) RAM) and not affected by this restriction. :::note `max_query_size` cannot be set within an SQL query (e.g., `SELECT now() SETTINGS max_query_size=10000`) because ClickHouse needs to allocate a buffer to parse the query, and this buffer size is determined by the `max_query_size` setting, which must be configured before the query is executed. ::: --interactive_delay arg The interval in microseconds for checking whether request execution has been canceled and sending the progress. --connect_timeout arg Connection timeout if there are no replicas. --handshake_timeout_ms arg Timeout in milliseconds for receiving Hello packet from replicas during handshake. --connect_timeout_with_failover_ms arg The timeout in milliseconds for connecting to a remote server for a Distributed table engine, if the 'shard' and 'replica' sections are used in the cluster definition. If unsuccessful, several attempts are made to connect to various replicas. --connect_timeout_with_failover_secure_ms arg Connection timeout for selecting first healthy replica (for secure connections). --receive_timeout arg Timeout for receiving data from the network, in seconds. If no bytes were received in this interval, the exception is thrown. If you set this setting on the client, the 'send_timeout' for the socket will also be set on the corresponding connection end on the server. --send_timeout arg Timeout for sending data to the network, in seconds. If a client needs to send some data but is not able to send any bytes in this interval, the exception is thrown. If you set this setting on the client, the 'receive_timeout' for the socket will also be set on the corresponding connection end on the server. --tcp_keep_alive_timeout arg The time in seconds the connection needs to remain idle before TCP starts sending keepalive probes --hedged_connection_timeout_ms arg Connection timeout for establishing connection with replica for Hedged requests --receive_data_timeout_ms arg Connection timeout for receiving first packet of data or packet with positive progress from replica --use_hedged_requests [=arg(=1)] Enables hedged requests logic for remote queries. It allows to establish many connections with different replicas for query. New connection is enabled in case existent connection(s) with replica(s) were not established within `hedged_connection_timeout` or no data was received within `receive_data_timeout`. Query uses the first connection which send non empty progress packet (or data packet, if `allow_changing_replica_until_first_dat a_packet`); other connections are cancelled. Queries with `max_parallel_replicas > 1` are supported. Enabled by default. Disabled by default on Cloud. --allow_changing_replica_until_first_data_packet [=arg(=1)] If it's enabled, in hedged requests we can start new connection until receiving first data packet even if we have already made some progress (but progress haven't updated for `receive_data_timeout` timeout), otherwise we disable changing replica after the first time we made progress. --queue_max_wait_ms arg The wait time in the request queue, if the number of concurrent requests exceeds the maximum. --connection_pool_max_wait_ms arg The wait time in milliseconds for a connection when the connection pool is full. Possible values: - Positive integer. - 0 — Infinite timeout. --replace_running_query_max_wait_ms arg The wait time for running the query with the same `query_id` to finish, when the [replace_running_query](#repla ce_running_query) setting is active. Possible values: - Positive integer. - 0 — Throwing an exception that does not allow to run a new query if the server already executes a query with the same `query_id`. --kafka_max_wait_ms arg The wait time in milliseconds for reading messages from [Kafka](/engines/table-engines/integrat ions/kafka) before retry. Possible values: - Positive integer. - 0 — Infinite timeout. See also: - [Apache Kafka](https://kafka.apache.o rg/) --rabbitmq_max_wait_ms arg The wait time for reading from RabbitMQ before retry. --poll_interval arg Block at the query wait loop on the server for the specified number of seconds. --idle_connection_timeout arg Timeout to close idle TCP connections after specified number of seconds. Possible values: - Positive integer (0 - close immediately, after 0 seconds). --distributed_connections_pool_size arg The maximum number of simultaneous connections with remote servers for distributed processing of all queries to a single Distributed table. We recommend setting a value no less than the number of servers in the cluster. --connections_with_failover_max_tries arg The maximum number of connection attempts with each replica for the Distributed table engine. --s3_strict_upload_part_size arg The exact size of part to upload during multipart upload to S3 (some implementations does not supports variable size parts). --azure_strict_upload_part_size arg The exact size of part to upload during multipart upload to Azure blob storage. --azure_max_blocks_in_multipart_upload arg Maximum number of blocks in multipart upload for Azure. --s3_min_upload_part_size arg The minimum size of part to upload during multipart upload to S3. --s3_max_upload_part_size arg The maximum size of part to upload during multipart upload to S3. --azure_min_upload_part_size arg The minimum size of part to upload during multipart upload to Azure blob storage. --azure_max_upload_part_size arg The maximum size of part to upload during multipart upload to Azure blob storage. --s3_upload_part_size_multiply_factor arg Multiply s3_min_upload_part_size by this factor each time s3_multiply_parts_count_threshold parts were uploaded from a single write to S3. --s3_upload_part_size_multiply_parts_count_threshold arg Each time this number of parts was uploaded to S3, s3_min_upload_part_size is multiplied by s3_upload_part_size_mu ltiply_factor. --s3_max_part_number arg Maximum part number number for s3 upload part. --s3_allow_multipart_copy [=arg(=1)] Allow multipart copy in S3. --s3_max_single_operation_copy_size arg Maximum size for single-operation copy in s3. This setting is used only if s3_allow_multipart_copy is true. --azure_upload_part_size_multiply_factor arg Multiply azure_min_upload_part_size by this factor each time azure_multiply_parts_count_threshold parts were uploaded from a single write to Azure blob storage. --azure_upload_part_size_multiply_parts_count_threshold arg Each time this number of parts was uploaded to Azure blob storage, azure_min_upload_part_size is multiplied by azure_upload_part_size_mu ltiply_factor. --s3_max_inflight_parts_for_one_file arg The maximum number of a concurrent loaded parts in multipart upload request. 0 means unlimited. --azure_max_inflight_parts_for_one_file arg The maximum number of a concurrent loaded parts in multipart upload request. 0 means unlimited. --s3_max_single_part_upload_size arg The maximum size of object to upload using singlepart upload to S3. --azure_max_single_part_upload_size arg The maximum size of object to upload using singlepart upload to Azure blob storage. --azure_max_single_part_copy_size arg The maximum size of object to copy using single part copy to Azure blob storage. --s3_max_single_read_retries arg The maximum number of retries during single S3 read. --azure_max_single_read_retries arg The maximum number of retries during single Azure blob storage read. --azure_max_unexpected_write_error_retries arg The maximum number of retries in case of unexpected errors during Azure blob storage write --s3_max_unexpected_write_error_retries arg The maximum number of retries in case of unexpected errors during S3 write. --s3_max_redirects arg Max number of S3 redirects hops allowed. --s3_max_connections arg The maximum number of connections per server. --s3_max_get_rps arg Limit on S3 GET request per second rate before throttling. Zero means unlimited. --s3_max_get_burst arg Max number of requests that can be issued simultaneously before hitting request per second limit. By default (0) equals to `s3_max_get_rps` --s3_max_put_rps arg Limit on S3 PUT request per second rate before throttling. Zero means unlimited. --s3_max_put_burst arg Max number of requests that can be issued simultaneously before hitting request per second limit. By default (0) equals to `s3_max_put_rps` --s3_list_object_keys_size arg Maximum number of files that could be returned in batch by ListObject request --s3_use_adaptive_timeouts [=arg(=1)] When set to `true` than for all s3 requests first two attempts are made with low send and receive timeouts. When set to `false` than all attempts are made with identical timeouts. --azure_list_object_keys_size arg Maximum number of files that could be returned in batch by ListObject request --s3_truncate_on_insert [=arg(=1)] Enables or disables truncate before inserts in s3 engine tables. If disabled, an exception will be thrown on insert attempts if an S3 object already exists. Possible values: - 0 — `INSERT` query creates a new file or fail if file exists and s3_create_new_file_on_insert is not set. - 1 — `INSERT` query replaces existing content of the file with the new data. See more details [here](/integrations/s 3#inserting-data). --azure_truncate_on_insert [=arg(=1)] Enables or disables truncate before insert in azure engine tables. --s3_create_new_file_on_insert [=arg(=1)] Enables or disables creating a new file on each insert in s3 engine tables. If enabled, on each insert a new S3 object will be created with the key, similar to this pattern: initial: `data.Parquet.gz` -> `data.1.Parquet.gz` -> `data.2.Parquet.gz`, etc. Possible values: - 0 — `INSERT` query creates a new file or fail if file exists and s3_truncate_on_insert is not set. - 1 — `INSERT` query creates a new file on each insert using suffix (from the second one) if s3_truncate_on_inser t is not set. See more details [here](/integrations/s 3#inserting-data). --s3_skip_empty_files [=arg(=1)] Enables or disables skipping empty files in [S3](../../engines/table-engin es/integrations/s3.md) engine tables. Possible values: - 0 — `SELECT` throws an exception if empty file is not compatible with requested format. - 1 — `SELECT` returns empty result for empty file. --azure_create_new_file_on_insert [=arg(=1)] Enables or disables creating a new file on each insert in azure engine tables --s3_check_objects_after_upload [=arg(=1)] Check each uploaded object to s3 with head request to be sure that upload was successful --azure_check_objects_after_upload [=arg(=1)] Check each uploaded object in azure blob storage to be sure that upload was successful --s3_allow_parallel_part_upload [=arg(=1)] Use multiple threads for s3 multipart upload. It may lead to slightly higher memory usage --azure_allow_parallel_part_upload [=arg(=1)] Use multiple threads for azure multipart upload. --s3_throw_on_zero_files_match [=arg(=1)] Throw an error, when ListObjects request cannot match any files --hdfs_throw_on_zero_files_match [=arg(=1)] Throw an error if matched zero files according to glob expansion rules. Possible values: - 1 — `SELECT` throws an exception. - 0 — `SELECT` returns empty result. --azure_throw_on_zero_files_match [=arg(=1)] Throw an error if matched zero files according to glob expansion rules. Possible values: - 1 — `SELECT` throws an exception. - 0 — `SELECT` returns empty result. --s3_ignore_file_doesnt_exist [=arg(=1)] Ignore absence of file if it does not exist when reading certain keys. Possible values: - 1 — `SELECT` returns empty result. - 0 — `SELECT` throws an exception. --hdfs_ignore_file_doesnt_exist [=arg(=1)] Ignore absence of file if it does not exist when reading certain keys. Possible values: - 1 — `SELECT` returns empty result. - 0 — `SELECT` throws an exception. --azure_ignore_file_doesnt_exist [=arg(=1)] Ignore absence of file if it does not exist when reading certain keys. Possible values: - 1 — `SELECT` returns empty result. - 0 — `SELECT` throws an exception. --azure_sdk_max_retries arg Maximum number of retries in azure sdk --azure_sdk_retry_initial_backoff_ms arg Minimal backoff between retries in azure sdk --azure_sdk_retry_max_backoff_ms arg Maximal backoff between retries in azure sdk --s3_validate_request_settings [=arg(=1)] Enables s3 request settings validation. Possible values: - 1 — validate settings. - 0 — do not validate settings. --s3_disable_checksum [=arg(=1)] Do not calculate a checksum when sending a file to S3. This speeds up writes by avoiding excessive processing passes on a file. It is mostly safe as the data of MergeTree tables is checksummed by ClickHouse anyway, and when S3 is accessed with HTTPS, the TLS layer already provides integrity while transferring through the network. While additional checksums on S3 give defense in depth. --s3_retry_attempts arg Setting for Aws::Client::RetryStrategy, Aws::Client does retries itself, 0 means no retries --s3_request_timeout_ms arg Idleness timeout for sending and receiving data to/from S3. Fail if a single TCP read or write call blocks for this long. --s3_connect_timeout_ms arg Connection timeout for host from s3 disks. --enable_s3_requests_logging [=arg(=1)] Enable very explicit logging of S3 requests. Makes sense for debug only. --s3queue_default_zookeeper_path arg Default zookeeper path prefix for S3Queue engine --s3queue_migrate_old_metadata_to_buckets [=arg(=1)] Migrate old metadata structure of S3Queue table to a new one --s3queue_enable_logging_to_s3queue_log [=arg(=1)] Enable writing to system.s3queue_log. The value can be overwritten per table with table settings --hdfs_replication arg The actual number of replications can be specified when the hdfs file is created. --hdfs_truncate_on_insert [=arg(=1)] Enables or disables truncation before an insert in hdfs engine tables. If disabled, an exception will be thrown on an attempt to insert if a file in HDFS already exists. Possible values: - 0 — `INSERT` query appends new data to the end of the file. - 1 — `INSERT` query replaces existing content of the file with the new data. --hdfs_create_new_file_on_insert [=arg(=1)] Enables or disables creating a new file on each insert in HDFS engine tables. If enabled, on each insert a new HDFS file will be created with the name, similar to this pattern: initial: `data.Parquet.gz` -> `data.1.Parquet.gz` -> `data.2.Parquet.gz`, etc. Possible values: - 0 — `INSERT` query appends new data to the end of the file. - 1 — `INSERT` query creates a new file. --hdfs_skip_empty_files [=arg(=1)] Enables or disables skipping empty files in [HDFS](../../engines/table-eng ines/integrations/hdfs.md) engine tables. Possible values: - 0 — `SELECT` throws an exception if empty file is not compatible with requested format. - 1 — `SELECT` returns empty result for empty file. --azure_skip_empty_files [=arg(=1)] Enables or disables skipping empty files in S3 engine. Possible values: - 0 — `SELECT` throws an exception if empty file is not compatible with requested format. - 1 — `SELECT` returns empty result for empty file. --hsts_max_age arg Expired time for HSTS. 0 means disable HSTS. --extremes [=arg(=1)] Whether to count extreme values (the minimums and maximums in columns of a query result). Accepts 0 or 1. By default, 0 (disabled). For more information, see the section "Extreme values". --use_uncompressed_cache [=arg(=1)] Whether to use a cache of uncompressed blocks. Accepts 0 or 1. By default, 0 (disabled). Using the uncompressed cache (only for tables in the MergeTree family) can significantly reduce latency and increase throughput when working with a large number of short queries. Enable this setting for users who send frequent short requests. Also pay attention to the [uncompressed_cache_si ze](/operations/server-configuration-pa rameters/settings#uncompressed_cache_si ze) configuration parameter (only set in the config file) – the size of uncompressed cache blocks. By default, it is 8 GiB. The uncompressed cache is filled in as needed and the least-used data is automatically deleted. For queries that read at least a somewhat large volume of data (one million rows or more), the uncompressed cache is disabled automatically to save space for truly small queries. This means that you can keep the 'use_uncompressed_cache' setting always set to 1. --replace_running_query [=arg(=1)] When using the HTTP interface, the 'query_id' parameter can be passed. This is any string that serves as the query identifier. If a query from the same user with the same 'query_id' already exists at this time, the behaviour depends on the 'replace_running_query' parameter. `0` (default) – Throw an exception (do not allow the query to run if a query with the same 'query_id' is already running). `1` – Cancel the old query and start running the new one. Set this parameter to 1 for implementing suggestions for segmentation conditions. After entering the next character, if the old query hasn't finished yet, it should be cancelled. --max_remote_read_network_bandwidth arg The maximum speed of data exchange over the network in bytes per second for read. --max_remote_write_network_bandwidth arg The maximum speed of data exchange over the network in bytes per second for write. --max_local_read_bandwidth arg The maximum speed of local reads in bytes per second. --max_local_write_bandwidth arg The maximum speed of local writes in bytes per second. --stream_like_engine_allow_direct_select [=arg(=1)] Allow direct SELECT query for Kafka, RabbitMQ, FileLog, Redis Streams, and NATS engines. In case there are attached materialized views, SELECT query is not allowed even if this setting is enabled. --stream_like_engine_insert_queue arg When stream-like engine reads from multiple queues, the user will need to select one queue to insert into when writing. Used by Redis Streams and NATS. --dictionary_validate_primary_key_type [=arg(=1)] Validate primary key type for dictionaries. By default id type for simple layouts will be implicitly converted to UInt64. --distributed_insert_skip_read_only_replicas [=arg(=1)] Enables skipping read-only replicas for INSERT queries into Distributed. Possible values: - 0 — INSERT was as usual, if it will go to read-only replica it will fail - 1 — Initiator will skip read-only replicas before sending data to shards. --distributed_foreground_insert [=arg(=1)] Enables or disables synchronous data insertion into a [Distributed](/engines /table-engines/special/distributed) table. By default, when inserting data into a `Distributed` table, the ClickHouse server sends data to cluster nodes in background mode. When `distributed_foreground_insert=1`, the data is processed synchronously, and the `INSERT` operation succeeds only after all the data is saved on all shards (at least one replica for each shard if `internal_replication` is true). Possible values: - 0 — Data is inserted in background mode. - 1 — Data is inserted in synchronous mode. Cloud default value: `1`. **See Also** - [Distributed Table Engine](/engines/table-engines/special/ distributed) - [Managing Distributed Tables](/sql-reference/statements/syste m#managing-distributed-tables) --insert_distributed_sync [=arg(=1)] Enables or disables synchronous data insertion into a [Distributed](/engines /table-engines/special/distributed) table. By default, when inserting data into a `Distributed` table, the ClickHouse server sends data to cluster nodes in background mode. When `distributed_foreground_insert=1`, the data is processed synchronously, and the `INSERT` operation succeeds only after all the data is saved on all shards (at least one replica for each shard if `internal_replication` is true). Possible values: - 0 — Data is inserted in background mode. - 1 — Data is inserted in synchronous mode. Cloud default value: `1`. **See Also** - [Distributed Table Engine](/engines/table-engines/special/ distributed) - [Managing Distributed Tables](/sql-reference/statements/syste m#managing-distributed-tables) --distributed_background_insert_timeout arg Timeout for insert query into distributed. Setting is used only with insert_distributed_sync enabled. Zero value means no timeout. --insert_distributed_timeout arg Timeout for insert query into distributed. Setting is used only with insert_distributed_sync enabled. Zero value means no timeout. --distributed_background_insert_sleep_time_ms arg Base interval for the [Distributed](../../engines/table-engin es/special/distributed.md) table engine to send data. The actual interval grows exponentially in the event of errors. Possible values: - A positive integer number of milliseconds. --distributed_directory_monitor_sleep_time_ms arg Base interval for the [Distributed](../../engines/table-engin es/special/distributed.md) table engine to send data. The actual interval grows exponentially in the event of errors. Possible values: - A positive integer number of milliseconds. --distributed_background_insert_max_sleep_time_ms arg Maximum interval for the [Distributed](../../engines/table-engin es/special/distributed.md) table engine to send data. Limits exponential growth of the interval set in the [distributed_background_insert_sleep_ti me_ms](#distributed_background_insert_s leep_time_ms) setting. Possible values: - A positive integer number of milliseconds. --distributed_directory_monitor_max_sleep_time_ms arg Maximum interval for the [Distributed](../../engines/table-engin es/special/distributed.md) table engine to send data. Limits exponential growth of the interval set in the [distributed_background_insert_sleep_ti me_ms](#distributed_background_insert_s leep_time_ms) setting. Possible values: - A positive integer number of milliseconds. --distributed_background_insert_batch [=arg(=1)] Enables/disables inserted data sending in batches. When batch sending is enabled, the [Distributed](../../engines/table-engin es/special/distributed.md) table engine tries to send multiple files of inserted data in one operation instead of sending them separately. Batch sending improves cluster performance by better-utilizing server and network resources. Possible values: - 1 — Enabled. - 0 — Disabled. --distributed_directory_monitor_batch_inserts [=arg(=1)] Enables/disables inserted data sending in batches. When batch sending is enabled, the [Distributed](../../engines/table-engin es/special/distributed.md) table engine tries to send multiple files of inserted data in one operation instead of sending them separately. Batch sending improves cluster performance by better-utilizing server and network resources. Possible values: - 1 — Enabled. - 0 — Disabled. --distributed_background_insert_split_batch_on_failure [=arg(=1)] Enables/disables splitting batches on failures. Sometimes sending particular batch to the remote shard may fail, because of some complex pipeline after (i.e. `MATERIALIZED VIEW` with `GROUP BY`) due to `Memory limit exceeded` or similar errors. In this case, retrying will not help (and this will stuck distributed sends for the table) but sending files from that batch one by one may succeed INSERT. So installing this setting to `1` will disable batching for such batches (i.e. temporary disables `distributed_backgro und_insert_batch` for failed batches). Possible values: - 1 — Enabled. - 0 — Disabled. :::note This setting also affects broken batches (that may appears because of abnormal server (machine) termination and no `fsync_after_insert`/`fsync_dire ctories` for [Distributed](../../engine s/table-engines/special/distributed.md) table engine). ::: :::note You should not rely on automatic batch splitting, since this may hurt performance. ::: --distributed_directory_monitor_split_batch_on_failure [=arg(=1)] Enables/disables splitting batches on failures. Sometimes sending particular batch to the remote shard may fail, because of some complex pipeline after (i.e. `MATERIALIZED VIEW` with `GROUP BY`) due to `Memory limit exceeded` or similar errors. In this case, retrying will not help (and this will stuck distributed sends for the table) but sending files from that batch one by one may succeed INSERT. So installing this setting to `1` will disable batching for such batches (i.e. temporary disables `distributed_backgro und_insert_batch` for failed batches). Possible values: - 1 — Enabled. - 0 — Disabled. :::note This setting also affects broken batches (that may appears because of abnormal server (machine) termination and no `fsync_after_insert`/`fsync_dire ctories` for [Distributed](../../engine s/table-engines/special/distributed.md) table engine). ::: :::note You should not rely on automatic batch splitting, since this may hurt performance. ::: --optimize_move_to_prewhere [=arg(=1)] Enables or disables automatic [PREWHERE](../../sql-reference/statemen ts/select/prewhere.md) optimization in [SELECT](../../sql-reference/statements /select/index.md) queries. Works only for [*MergeTree](../../engin es/table-engines/mergetree-family/index .md) tables. Possible values: - 0 — Automatic `PREWHERE` optimization is disabled. - 1 — Automatic `PREWHERE` optimization is enabled. --optimize_move_to_prewhere_if_final [=arg(=1)] Enables or disables automatic [PREWHERE](../../sql-reference/statemen ts/select/prewhere.md) optimization in [SELECT](../../sql-reference/statements /select/index.md) queries with [FINAL](/sql-reference/statements/selec t/from#final-modifier) modifier. Works only for [*MergeTree](../../engin es/table-engines/mergetree-family/index .md) tables. Possible values: - 0 — Automatic `PREWHERE` optimization in `SELECT` queries with `FINAL` modifier is disabled. - 1 — Automatic `PREWHERE` optimization in `SELECT` queries with `FINAL` modifier is enabled. **See Also** - [optimize_move_to_prewhere](#optimize _move_to_prewhere) setting --move_all_conditions_to_prewhere [=arg(=1)] Move all viable conditions from WHERE to PREWHERE --enable_multiple_prewhere_read_steps [=arg(=1)] Move more conditions from WHERE to PREWHERE and do reads from disk and filtering in multiple steps if there are multiple conditions combined with AND --move_primary_key_columns_to_end_of_prewhere [=arg(=1)] Move PREWHERE conditions containing primary key columns to the end of AND chain. It is likely that these conditions are taken into account during primary key analysis and thus will not contribute a lot to PREWHERE filtering. --allow_reorder_prewhere_conditions [=arg(=1)] When moving conditions from WHERE to PREWHERE, allow reordering them to optimize filtering --alter_sync arg Allows to set up waiting for actions to be executed on replicas by [ALTER](../../sql-reference/statements/ alter/index.md), [OPTIMIZE](../../sql-r eference/statements/optimize.md) or [TRUNCATE](../../sql-reference/statemen ts/truncate.md) queries. Possible values: - 0 — Do not wait. - 1 — Wait for own execution. - 2 — Wait for everyone. Cloud default value: `0`. :::note `alter_sync` is applicable to `Replicated` tables only, it does nothing to alters of not `Replicated` tables. ::: --replication_alter_partitions_sync arg Allows to set up waiting for actions to be executed on replicas by [ALTER](../../sql-reference/statements/ alter/index.md), [OPTIMIZE](../../sql-r eference/statements/optimize.md) or [TRUNCATE](../../sql-reference/statemen ts/truncate.md) queries. Possible values: - 0 — Do not wait. - 1 — Wait for own execution. - 2 — Wait for everyone. Cloud default value: `0`. :::note `alter_sync` is applicable to `Replicated` tables only, it does nothing to alters of not `Replicated` tables. ::: --replication_wait_for_inactive_replica_timeout arg Specifies how long (in seconds) to wait for inactive replicas to execute [ALTER](../../sql-reference/statements/ alter/index.md), [OPTIMIZE](../../sql-r eference/statements/optimize.md) or [TRUNCATE](../../sql-reference/statemen ts/truncate.md) queries. Possible values: - 0 — Do not wait. - Negative integer — Wait for unlimited time. - Positive integer — The number of seconds to wait. --alter_move_to_space_execute_async [=arg(=1)] Execute ALTER TABLE MOVE ... TO [DISK|VOLUME] asynchronously --load_balancing arg Specifies the algorithm of replicas selection that is used for distributed query processing. ClickHouse supports the following algorithms of choosing replicas: - [Random](#load_balancing-random) (by default) - [Nearest hostname](#load_balancing-ne arest_hostname) - [Hostname levenshtein distance](#load_balancing-hostname_leve nshtein_distance) - [In order](#load_balancing-in_order) - [First or random](#load_balancing-fir st_or_random) - [Round robin](#load_balancing-round_r obin) See also: - [distributed_replica_max_ignored_erro rs](#distributed_replica_max_ignored_er rors) ### Random (by Default) {#load_balancing-random} ```sql load_balancing = random ``` The number of errors is counted for each replica. The query is sent to the replica with the fewest errors, and if there are several of these, to anyone of them. Disadvantages: Server proximity is not accounted for; if the replicas have different data, you will also get different data. ### Nearest Hostname {#load_balancing-nearest_hostname} ```sql load_balancing = nearest_hostname ``` The number of errors is counted for each replica. Every 5 minutes, the number of errors is integrally divided by 2. Thus, the number of errors is calculated for a recent time with exponential smoothing. If there is one replica with a minimal number of errors (i.e. errors occurred recently on the other replicas), the query is sent to it. If there are multiple replicas with the same minimal number of errors, the query is sent to the replica with a hostname that is most similar to the server's hostname in the config file (for the number of different characters in identical positions, up to the minimum length of both hostnames). For instance, example01-01-1 and example01-01-2 are different in one position, while example01-01-1 and example01-02-2 differ in two places. This method might seem primitive, but it does not require external data about network topology, and it does not compare IP addresses, which would be complicated for our IPv6 addresses. Thus, if there are equivalent replicas, the closest one by name is preferred. We can also assume that when sending a query to the same server, in the absence of failures, a distributed query will also go to the same servers. So even if different data is placed on the replicas, the query will return mostly the same results. ### Hostname levenshtein distance {#load_balancing-hostname_levenshtein_d istance} ```sql load_balancing = hostname_levenshtein_d istance ``` Just like `nearest_hostname`, but it compares hostname in a [levenshtein distance](https://en.wikipedia.org/wiki /Levenshtein_distance) manner. For example: ```text example-clickhouse-0-0 ample-clickhouse-0-0 1 example-clickhouse-0-0 example-clickhouse-1-10 2 example-clickhouse-0-0 example-clickhouse-12-0 3 ``` ### In Order {#load_balancing-in_order} ```sql load_balancing = in_order ``` Replicas with the same number of errors are accessed in the same order as they are specified in the configuration. This method is appropriate when you know exactly which replica is preferable. ### First or Random {#load_balancing-fi rst_or_random} ```sql load_balancing = first_or_random ``` This algorithm chooses the first replica in the set or a random replica if the first is unavailable. It's effective in cross-replication topology setups, but useless in other configurations. The `first_or_random` algorithm solves the problem of the `in_order` algorithm. With `in_order`, if one replica goes down, the next one gets a double load while the remaining replicas handle the usual amount of traffic. When using the `first_or_random` algorithm, the load is evenly distributed among replicas that are still available. It's possible to explicitly define what the first replica is by using the setting `load_balancing_first_offset`. This gives more control to rebalance query workloads among replicas. ### Round Robin {#load_balancing-round_ robin} ```sql load_balancing = round_robin ``` This algorithm uses a round-robin policy across replicas with the same number of errors (only the queries with `round_robin` policy is accounted). --load_balancing_first_offset arg Which replica to preferably send a query when FIRST_OR_RANDOM load balancing strategy is used. --totals_mode arg How to calculate TOTALS when HAVING is present, as well as when max_rows_to_group_by and group_by_overflow_mode = 'any' are present. See the section "WITH TOTALS modifier". --totals_auto_threshold arg The threshold for `totals_mode = 'auto'`. See the section "WITH TOTALS modifier". --allow_suspicious_low_cardinality_types [=arg(=1)] Allows or restricts using [LowCardinality](../../sql-reference/da ta-types/lowcardinality.md) with data types with fixed size of 8 bytes or less: numeric data types and `FixedString(8_bytes_or_less)`. For small fixed values using of `LowCardinality` is usually inefficient, because ClickHouse stores a numeric index for each row. As a result: - Disk space usage can rise. - RAM consumption can be higher, depending on a dictionary size. - Some functions can work slower due to extra coding/encoding operations. Merge times in [MergeTree](../../engine s/table-engines/mergetree-family/merget ree.md)-engine tables can grow due to all the reasons described above. Possible values: - 1 — Usage of `LowCardinality` is not restricted. - 0 — Usage of `LowCardinality` is restricted. --allow_suspicious_fixed_string_types [=arg(=1)] In CREATE TABLE statement allows creating columns of type FixedString(n) with n > 256. FixedString with length >= 256 is suspicious and most likely indicates a misuse --allow_suspicious_indices [=arg(=1)] Reject primary/secondary indexes and sorting keys with identical expressions --allow_suspicious_ttl_expressions [=arg(=1)] Reject TTL expressions that don't depend on any of table's columns. It indicates a user error most of the time. --allow_suspicious_variant_types [=arg(=1)] In CREATE TABLE statement allows specifying Variant type with similar variant types (for example, with different numeric or date types). Enabling this setting may introduce some ambiguity when working with values with similar types. --allow_suspicious_primary_key [=arg(=1)] Allow suspicious `PRIMARY KEY`/`ORDER BY` for MergeTree (i.e. SimpleAggregateFunction). --allow_suspicious_types_in_group_by [=arg(=1)] Allows or restricts using [Variant](../../sql-reference/data-type s/variant.md) and [Dynamic](../../sql-r eference/data-types/dynamic.md) types in GROUP BY keys. --allow_suspicious_types_in_order_by [=arg(=1)] Allows or restricts using [Variant](../../sql-reference/data-type s/variant.md) and [Dynamic](../../sql-r eference/data-types/dynamic.md) types in ORDER BY keys. --allow_not_comparable_types_in_order_by [=arg(=1)] Allows or restricts using not comparable types (like JSON/Object/AggregateFunction) in ORDER BY keys. --allow_not_comparable_types_in_comparison_functions [=arg(=1)] Allows or restricts using not comparable types (like JSON/Object/AggregateFunction) in comparison functions `equal/less/greater/etc`. --compile_expressions [=arg(=1)] Compile some scalar functions and operators to native code. Due to a bug in the LLVM compiler infrastructure, on AArch64 machines, it is known to lead to a nullptr dereference and, consequently, server crash. Do not enable this setting. --min_count_to_compile_expression arg Minimum count of executing same expression before it is get compiled. --compile_aggregate_expressions [=arg(=1)] Enables or disables JIT-compilation of aggregate functions to native code. Enabling this setting can improve the performance. Possible values: - 0 — Aggregation is done without JIT compilation. - 1 — Aggregation is done using JIT compilation. **See Also** - [min_count_to_compile_aggregate_expre ssion](#min_count_to_compile_aggregate_ expression) --min_count_to_compile_aggregate_expression arg The minimum number of identical aggregate expressions to start JIT-compilation. Works only if the [compile_aggregate_expressions](#compil e_aggregate_expressions) setting is enabled. Possible values: - Positive integer. - 0 — Identical aggregate expressions are always JIT-compiled. --compile_sort_description [=arg(=1)] Compile sort description to native code. --min_count_to_compile_sort_description arg The number of identical sort descriptions before they are JIT-compiled --group_by_two_level_threshold arg From what number of keys, a two-level aggregation starts. 0 - the threshold is not set. --group_by_two_level_threshold_bytes arg From what size of the aggregation state in bytes, a two-level aggregation begins to be used. 0 - the threshold is not set. Two-level aggregation is used when at least one of the thresholds is triggered. --distributed_aggregation_memory_efficient [=arg(=1)] Is the memory-saving mode of distributed aggregation enabled. --aggregation_memory_efficient_merge_threads arg Number of threads to use for merge intermediate aggregation results in memory efficient mode. When bigger, then more memory is consumed. 0 means - same as 'max_threads'. --enable_memory_bound_merging_of_aggregation_results [=arg(=1)] Enable memory bound merging strategy for aggregation. --enable_positional_arguments [=arg(=1)] Enables or disables supporting positional arguments for [GROUP BY](/sql-reference/statements/select/gr oup-by), [LIMIT BY](../../sql-reference /statements/select/limit-by.md), [ORDER BY](../../sql-reference/statements/sele ct/order-by.md) statements. Possible values: - 0 — Positional arguments aren't supported. - 1 — Positional arguments are supported: column numbers can use instead of column names. **Example** Query: ```sql CREATE TABLE positional_arguments(one Int, two Int, three Int) ENGINE=Memory(); INSERT INTO positional_arguments VALUES (10, 20, 30), (20, 20, 10), (30, 10, 20); SELECT * FROM positional_arguments ORDER BY 2,3; ``` Result: ```text ┌─one─┬─two─┬─three─� � │ 30 │ 10 │ 20 │ │ 20 │ 20 │ 10 │ │ 10 │ 20 │ 30 │ └─────┴─────┴ ───────┘ ``` --enable_extended_results_for_datetime_functions [=arg(=1)] Enables or disables returning results of type: - `Date32` with extended range (compared to type `Date`) for functions [toStartOfYear](../../sql-reference/fun ctions/date-time-functions.md/#tostarto fyear), [toStartOfISOYear](../../sql-re ference/functions/date-time-functions.m d/#tostartofisoyear), [toStartOfQuarter](../../sql-reference/ functions/date-time-functions.md/#tosta rtofquarter), [toStartOfMonth](../../sq l-reference/functions/date-time-functio ns.md/#tostartofmonth), [toLastDayOfMonth](../../sql-reference/ functions/date-time-functions.md/#tolas tdayofmonth), [toStartOfWeek](../../sql -reference/functions/date-time-function s.md/#tostartofweek), [toLastDayOfWeek](../../sql-reference/f unctions/date-time-functions.md/#tolast dayofweek) and [toMonday](../../sql-ref erence/functions/date-time-functions.md /#tomonday). - `DateTime64` with extended range (compared to type `DateTime`) for functions [toStartOfDay](../../sql-refe rence/functions/date-time-functions.md/ #tostartofday), [toStartOfHour](../../s ql-reference/functions/date-time-functi ons.md/#tostartofhour), [toStartOfMinute](../../sql-reference/f unctions/date-time-functions.md/#tostar tofminute), [toStartOfFiveMinutes](../. ./sql-reference/functions/date-time-fun ctions.md/#tostartoffiveminutes), [toStartOfTenMinutes](../../sql-referen ce/functions/date-time-functions.md/#to startoftenminutes), [toStartOfFifteenMi nutes](../../sql-reference/functions/da te-time-functions.md/#tostartoffifteenm inutes) and [timeSlot](../../sql-refere nce/functions/date-time-functions.md/#t imeslot). Possible values: - 0 — Functions return `Date` or `DateTime` for all types of arguments. - 1 — Functions return `Date32` or `DateTime64` for `Date32` or `DateTime64` arguments and `Date` or `DateTime` otherwise. --allow_nonconst_timezone_arguments [=arg(=1)] Allow non-const timezone arguments in certain time-related functions like toTimeZone(), fromUnixTimestamp*(), snowflakeToDateTime*() --function_locate_has_mysql_compatible_argument_order [=arg(=1)] Controls the order of arguments in function [locate](../../sql-reference/f unctions/string-search-functions.md/#lo cate). Possible values: - 0 — Function `locate` accepts arguments `(haystack, needle[, start_pos])`. - 1 — Function `locate` accepts arguments `(needle, haystack, [, start_pos])` (MySQL-compatible behavior) --group_by_use_nulls [=arg(=1)] Changes the way the [GROUP BY clause](/sql-reference/statements/selec t/group-by) treats the types of aggregation keys. When the `ROLLUP`, `CUBE`, or `GROUPING SETS` specifiers are used, some aggregation keys may not be used to produce some result rows. Columns for these keys are filled with either default value or `NULL` in corresponding rows depending on this setting. Possible values: - 0 — The default value for the aggregation key type is used to produce missing values. - 1 — ClickHouse executes `GROUP BY` the same way as the SQL standard says. The types of aggregation keys are converted to [Nullable](/sql-reference/ data-types/nullable). Columns for corresponding aggregation keys are filled with [NULL](/sql-reference/synta x#null) for rows that didn't use it. See also: - [GROUP BY clause](/sql-reference/stat ements/select/group-by) --skip_unavailable_shards [=arg(=1)] Enables or disables silently skipping of unavailable shards. Shard is considered unavailable if all its replicas are unavailable. A replica is unavailable in the following cases: - ClickHouse can't connect to replica for any reason. When connecting to a replica, ClickHouse performs several attempts. If all these attempts fail, the replica is considered unavailable. - Replica can't be resolved through DNS. If replica's hostname can't be resolved through DNS, it can indicate the following situations: - Replica's host has no DNS record. It can occur in systems with dynamic DNS, for example, [Kubernetes](https:// kubernetes.io), where nodes can be unresolvable during downtime, and this is not an error. - Configuration error. ClickHouse configuration file contains a wrong hostname. Possible values: - 1 — skipping enabled. If a shard is unavailable, ClickHouse returns a result based on partial data and does not report node availability issues. - 0 — skipping disabled. If a shard is unavailable, ClickHouse throws an exception. --parallel_distributed_insert_select arg Enables parallel distributed `INSERT ... SELECT` query. If we execute `INSERT INTO distributed_table_a SELECT ... FROM distributed_table_b` queries and both tables use the same cluster, and both tables are either [replicated](../../en gines/table-engines/mergetree-family/re plication.md) or non-replicated, then this query is processed locally on every shard. Possible values: - 0 — Disabled. - 1 — `SELECT` will be executed on each shard from the underlying table of the distributed engine. - 2 — `SELECT` and `INSERT` will be executed on each shard from/to the underlying table of the distributed engine. --distributed_group_by_no_merge arg Do not merge aggregation states from different servers for distributed query processing, you can use this in case it is for certain that there are different keys on different shards Possible values: - `0` — Disabled (final query processing is done on the initiator node). - `1` - Do not merge aggregation states from different servers for distributed query processing (query completely processed on the shard, initiator only proxy the data), can be used in case it is for certain that there are different keys on different shards. - `2` - Same as `1` but applies `ORDER BY` and `LIMIT` (it is not possible when the query processed completely on the remote node, like for `distributed_group_by_no_merge=1`) on the initiator (can be used for queries with `ORDER BY` and/or `LIMIT`). **Example** ```sql SELECT * FROM remote('127.0.0.{2,3}', system.one) GROUP BY dummy LIMIT 1 SETTINGS distributed_group_by_no_merge = 1 FORMAT PrettyCompactMonoBlock ┌─dummy─┐ │ 0 │ │ 0 │ └───────┘ ``` ```sql SELECT * FROM remote('127.0.0.{2,3}', system.one) GROUP BY dummy LIMIT 1 SETTINGS distributed_group_by_no_merge = 2 FORMAT PrettyCompactMonoBlock ┌─dummy─┐ │ 0 │ └───────┘ ``` --distributed_push_down_limit arg Enables or disables [LIMIT](#limit) applying on each shard separately. This will allow to avoid: - Sending extra rows over network; - Processing rows behind the limit on the initiator. Starting from 21.9 version you cannot get inaccurate results anymore, since `distributed_push_down_limit` changes query execution only if at least one of the conditions met: - [distributed_group_by_no_merge](#dist ributed_group_by_no_merge) > 0. - Query **does not have** `GROUP BY`/`DISTINCT`/`LIMIT BY`, but it has `ORDER BY`/`LIMIT`. - Query **has** `GROUP BY`/`DISTINCT`/`LIMIT BY` with `ORDER BY`/`LIMIT` and: - [optimize_skip_unused_shards](#op timize_skip_unused_shards) is enabled. - [optimize_distributed_group_by_sh arding_key](#optimize_distributed_group _by_sharding_key) is enabled. Possible values: - 0 — Disabled. - 1 — Enabled. See also: - [distributed_group_by_no_merge](#dist ributed_group_by_no_merge) - [optimize_skip_unused_shards](#optimi ze_skip_unused_shards) - [optimize_distributed_group_by_shardi ng_key](#optimize_distributed_group_by_ sharding_key) --optimize_distributed_group_by_sharding_key [=arg(=1)] Optimize `GROUP BY sharding_key` queries, by avoiding costly aggregation on the initiator server (which will reduce memory usage for the query on the initiator server). The following types of queries are supported (and all combinations of them): - `SELECT DISTINCT [..., ]sharding_key[, ...] FROM dist` - `SELECT ... FROM dist GROUP BY sharding_key[, ...]` - `SELECT ... FROM dist GROUP BY sharding_key[, ...] ORDER BY x` - `SELECT ... FROM dist GROUP BY sharding_key[, ...] LIMIT 1` - `SELECT ... FROM dist GROUP BY sharding_key[, ...] LIMIT 1 BY x` The following types of queries are not supported (support for some of them may be added later): - `SELECT ... GROUP BY sharding_key[, ...] WITH TOTALS` - `SELECT ... GROUP BY sharding_key[, ...] WITH ROLLUP` - `SELECT ... GROUP BY sharding_key[, ...] WITH CUBE` - `SELECT ... GROUP BY sharding_key[, ...] SETTINGS extremes=1` Possible values: - 0 — Disabled. - 1 — Enabled. See also: - [distributed_group_by_no_merge](#dist ributed_group_by_no_merge) - [distributed_push_down_limit](#distri buted_push_down_limit) - [optimize_skip_unused_shards](#optimi ze_skip_unused_shards) :::note Right now it requires `optimize_skip_unused_shards` (the reason behind this is that one day it may be enabled by default, and it will work correctly only if data was inserted via Distributed table, i.e. data is distributed according to sharding_key). ::: --optimize_skip_unused_shards_limit arg Limit for number of sharding key values, turns off `optimize_skip_unused _shards` if the limit is reached. Too many values may require significant amount for processing, while the benefit is doubtful, since if you have huge number of values in `IN (...)`, then most likely the query will be sent to all shards anyway. --optimize_skip_unused_shards [=arg(=1)] Enables or disables skipping of unused shards for [SELECT](../../sql-reference /statements/select/index.md) queries that have sharding key condition in `WHERE/PREWHERE` (assuming that the data is distributed by sharding key, otherwise a query yields incorrect result). Possible values: - 0 — Disabled. - 1 — Enabled. --optimize_skip_unused_shards_rewrite_in [=arg(=1)] Rewrite IN in query for remote shards to exclude values that does not belong to the shard (requires optimize_skip_unused_shards). Possible values: - 0 — Disabled. - 1 — Enabled. --allow_nondeterministic_optimize_skip_unused_shards [=arg(=1)] Allow nondeterministic (like `rand` or `dictGet`, since later has some caveats with updates) functions in sharding key. Possible values: - 0 — Disallowed. - 1 — Allowed. --force_optimize_skip_unused_shards arg Enables or disables query execution if [optimize_skip_unused_shards](#optimize _skip_unused_shards) is enabled and skipping of unused shards is not possible. If the skipping is not possible and the setting is enabled, an exception will be thrown. Possible values: - 0 — Disabled. ClickHouse does not throw an exception. - 1 — Enabled. Query execution is disabled only if the table has a sharding key. - 2 — Enabled. Query execution is disabled regardless of whether a sharding key is defined for the table. --optimize_skip_unused_shards_nesting arg Controls [`optimize_skip_unused_shards` ](#optimize_skip_unused_shards) (hence still requires [`optimize_skip_unused_s hards`](#optimize_skip_unused_shards)) depends on the nesting level of the distributed query (case when you have `Distributed` table that look into another `Distributed` table). Possible values: - 0 — Disabled, `optimize_skip_unused _shards` works always. - 1 — Enables `optimize_skip_unused_s hards` only for the first level. - 2 — Enables `optimize_skip_unused_s hards` up to the second level. --force_optimize_skip_unused_shards_nesting arg Controls [`force_optimize_skip_unused_s hards`](#force_optimize_skip_unused_sha rds) (hence still requires [`force_optimize_skip_unused_shards`](# force_optimize_skip_unused_shards)) depends on the nesting level of the distributed query (case when you have `Distributed` table that look into another `Distributed` table). Possible values: - 0 - Disabled, `force_optimize_skip_un used_shards` works always. - 1 — Enables `force_optimize_skip_un used_shards` only for the first level. - 2 — Enables `force_optimize_skip_un used_shards` up to the second level. --input_format_parallel_parsing [=arg(=1)] Enables or disables order-preserving parallel parsing of data formats. Supported only for [TSV](../../interfac es/formats.md/#tabseparated), [TSKV](../../interfaces/formats.md/#tsk v), [CSV](../../interfaces/formats.md/# csv) and [JSONEachRow](../../interfaces /formats.md/#jsoneachrow) formats. Possible values: - 1 — Enabled. - 0 — Disabled. --min_chunk_bytes_for_parallel_parsing arg - Type: unsigned int - Default value: 1 MiB The minimum chunk size in bytes, which each thread will parse in parallel. --output_format_parallel_formatting [=arg(=1)] Enables or disables parallel formatting of data formats. Supported only for [TSV](../../interfaces/formats.md/#tabs eparated), [TSKV](../../interfaces/form ats.md/#tskv), [CSV](../../interfaces/f ormats.md/#csv) and [JSONEachRow](../.. /interfaces/formats.md/#jsoneachrow) formats. Possible values: - 1 — Enabled. - 0 — Disabled. --output_format_compression_level arg Default compression level if query output is compressed. The setting is applied when `SELECT` query has `INTO OUTFILE` or when writing to table functions `file`, `url`, `hdfs`, `s3`, or `azureBlobStorage`. Possible values: from `1` to `22` --output_format_compression_zstd_window_log arg Can be used when the output compression method is `zstd`. If greater than `0`, this setting explicitly sets compression window size (power of `2`) and enables a long-range mode for zstd compression. This can help to achieve a better compression ratio. Possible values: non-negative numbers. Note that if the value is too small or too big, `zstdlib` will throw an exception. Typical values are from `20` (window size = `1MB`) to `30` (window size = `1GB`). --enable_parsing_to_custom_serialization [=arg(=1)] If true then data can be parsed directly to columns with custom serialization (e.g. Sparse) according to hints for serialization got from the table. --merge_tree_use_v1_object_and_dynamic_serialization [=arg(=1)] 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. --merge_tree_min_rows_for_concurrent_read arg If the number of rows to be read from a file of a [MergeTree](../../engines/tab le-engines/mergetree-family/mergetree.m d) table exceeds `merge_tree_min_rows_f or_concurrent_read` then ClickHouse tries to perform a concurrent reading from this file on several threads. Possible values: - Positive integer. --merge_tree_min_bytes_for_concurrent_read arg If the number of bytes to read from one file of a [MergeTree](../../engines/tab le-engines/mergetree-family/mergetree.m d)-engine table exceeds `merge_tree_min_bytes_for_concurrent_re ad`, then ClickHouse tries to concurrently read from this file in several threads. Possible value: - Positive integer. --merge_tree_min_rows_for_seek arg 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_min_bytes_for_seek arg 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_coarse_index_granularity arg 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_gra nularity` subranges and searches the required keys there recursively. Possible values: - Any positive even integer. --merge_tree_max_rows_to_use_cache arg 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](/op erations/server-configuration-parameter s/settings#uncompressed_cache_size) server setting defines the size of the cache of uncompressed blocks. Possible values: - Any positive integer. --merge_tree_max_bytes_to_use_cache arg 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](/op erations/server-configuration-parameter s/settings#uncompressed_cache_size) server setting defines the size of the cache of uncompressed blocks. Possible values: - Any positive integer. --merge_tree_use_deserialization_prefixes_cache [=arg(=1)] Enables caching of columns metadata from the file prefixes during reading from Wide parts in MergeTree. --merge_tree_use_prefixes_deserialization_thread_pool [=arg(=1)] 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_po ol_size`. --do_not_merge_across_partitions_select_final [=arg(=1)] Merge parts only in one partition in select final --split_parts_ranges_into_intersecting_and_non_intersecting_final [=arg(=1)] Split parts ranges into intersecting and non intersecting during FINAL optimization --split_intersecting_parts_ranges_into_layers_final [=arg(=1)] Split intersecting parts ranges into layers during FINAL optimization --mysql_max_rows_to_insert arg The maximum number of rows in MySQL batch insertion of the MySQL storage engine --mysql_map_string_to_text_in_show_columns [=arg(=1)] When enabled, [String](../../sql-refere nce/data-types/string.md) ClickHouse data type will be displayed as `TEXT` in [SHOW COLUMNS](../../sql-reference/s tatements/show.md/#show_columns). Has an effect only when the connection is made through the MySQL wire protocol. - 0 - Use `BLOB`. - 1 - Use `TEXT`. --mysql_map_fixed_string_to_text_in_show_columns [=arg(=1)] When enabled, [FixedString](../../sql-r eference/data-types/fixedstring.md) ClickHouse data type will be displayed as `TEXT` in [SHOW COLUMNS](../../sql-r eference/statements/show.md/#show_colum ns). Has an effect only when the connection is made through the MySQL wire protocol. - 0 - Use `BLOB`. - 1 - Use `TEXT`. --optimize_min_equality_disjunction_chain_length arg The minimum length of the expression `expr = x1 OR ... expr = xN` for optimization --optimize_min_inequality_conjunction_chain_length arg The minimum length of the expression `expr <> x1 AND ... expr <> xN` for optimization --min_bytes_to_use_direct_io arg The minimum data volume required for using direct I/O access to the storage disk. ClickHouse uses this setting when reading data from tables. If the total storage volume of all the data to be read exceeds `min_bytes_to_use_direct_i o` bytes, then ClickHouse reads the data from the storage disk with the `O_DIRECT` option. Possible values: - 0 — Direct I/O is disabled. - Positive integer. --min_bytes_to_use_mmap_io arg This is an experimental setting. Sets the minimum amount of memory for reading large files without copying data from the kernel to userspace. Recommended threshold is about 64 MB, because [mmap/munmap](https://en.wikipe dia.org/wiki/Mmap) is slow. It makes sense only for large files and helps only if data reside in the page cache. Possible values: - Positive integer. - 0 — Big files read with only copying data from kernel to userspace. --checksum_on_read [=arg(=1)] Validate checksums on reading. It is enabled by default and should be always enabled in production. Please do not expect any benefits in disabling this setting. It may only be used for experiments and benchmarks. The setting is only applicable for tables of MergeTree family. Checksums are always validated for other table engines and when receiving data over the network. --force_index_by_date [=arg(=1)] Disables query execution if the index can't be used by date. Works with tables in the MergeTree family. If `force_index_by_date=1`, ClickHouse checks whether the query has a date key condition that can be used for restricting data ranges. If there is no suitable condition, it throws an exception. However, it does not check whether the condition reduces the amount of data to read. For example, the condition `Date != ' 2000-01-01 '` is acceptable even when it matches all the data in the table (i.e., running the query requires a full scan). For more information about ranges of data in MergeTree tables, see [MergeTree](../../engines/table-engines /mergetree-family/mergetree.md). --force_primary_key [=arg(=1)] Disables query execution if indexing by the primary key is not possible. Works with tables in the MergeTree family. If `force_primary_key=1`, ClickHouse checks to see if the query has a primary key condition that can be used for restricting data ranges. If there is no suitable condition, it throws an exception. However, it does not check whether the condition reduces the amount of data to read. For more information about data ranges in MergeTree tables, see [MergeTree](../../engines/table-engines /mergetree-family/mergetree.md). --use_skip_indexes [=arg(=1)] Use data skipping indexes during query execution. Possible values: - 0 — Disabled. - 1 — Enabled. --use_skip_indexes_if_final [=arg(=1)] Controls whether skipping indexes are used when executing a query with the FINAL modifier. By default, this setting is disabled because skip indexes may exclude rows (granules) containing the latest data, which could lead to incorrect results. When enabled, skipping indexes are applied even with the FINAL modifier, potentially improving performance but with the risk of missing recent updates. Possible values: - 0 — Disabled. - 1 — Enabled. --materialize_skip_indexes_on_insert [=arg(=1)] If INSERTs build and store skip indexes. If disabled, skip indexes will be build and stored during merges or by explicit MATERIALIZE INDEX --materialize_statistics_on_insert [=arg(=1)] If INSERTs build and insert statistics. If disabled, statistics will be build and stored during merges or by explicit MATERIALIZE STATISTICS --ignore_data_skipping_indices arg Ignores the skipping indexes specified if used by the query. Consider the following example: ```sql CREATE TABLE data ( key Int, x Int, y Int, INDEX x_idx x TYPE minmax GRANULARITY 1, INDEX y_idx y TYPE minmax GRANULARITY 1, INDEX xy_idx (x,y) TYPE minmax GRANULARITY 1 ) Engine=MergeTree() ORDER BY key; INSERT INTO data VALUES (1, 2, 3); SELECT * FROM data; SELECT * FROM data SETTINGS ignore_data_skipping_indices=''; -- query will produce CANNOT_PARSE_TEXT error. SELECT * FROM data SETTINGS ignore_data_skipping_indices='x_idx'; -- Ok. SELECT * FROM data SETTINGS ignore_data_skipping_indices='na_idx'; -- Ok. SELECT * FROM data WHERE x = 1 AND y = 1 SETTINGS ignore_data_skipping_indices ='xy_idx',force_data_skipping_indices=' xy_idx' ; -- query will produce INDEX_NOT_USED error, since xy_idx is explicitly ignored. SELECT * FROM data WHERE x = 1 AND y = 2 SETTINGS ignore_data_skipping_indices ='xy_idx'; ``` The query without ignoring any indexes: ```sql EXPLAIN indexes = 1 SELECT * FROM data WHERE x = 1 AND y = 2; Expression ((Projection + Before ORDER BY)) Filter (WHERE) ReadFromMergeTree (default.data) Indexes: PrimaryKey Condition: true Parts: 1/1 Granules: 1/1 Skip Name: x_idx Description: minmax GRANULARITY 1 Parts: 0/1 Granules: 0/1 Skip Name: y_idx Description: minmax GRANULARITY 1 Parts: 0/0 Granules: 0/0 Skip Name: xy_idx Description: minmax GRANULARITY 1 Parts: 0/0 Granules: 0/0 ``` Ignoring the `xy_idx` index: ```sql EXPLAIN indexes = 1 SELECT * FROM data WHERE x = 1 AND y = 2 SETTINGS ignore_data_skipping_indices='xy_idx'; Expression ((Projection + Before ORDER BY)) Filter (WHERE) ReadFromMergeTree (default.data) Indexes: PrimaryKey Condition: true Parts: 1/1 Granules: 1/1 Skip Name: x_idx Description: minmax GRANULARITY 1 Parts: 0/1 Granules: 0/1 Skip Name: y_idx Description: minmax GRANULARITY 1 Parts: 0/0 Granules: 0/0 ``` Works with tables in the MergeTree family. --force_data_skipping_indices arg Disables query execution if passed data skipping indices wasn't used. Consider the following example: ```sql CREATE TABLE data ( key Int, d1 Int, d1_null Nullable(Int), INDEX d1_idx d1 TYPE minmax GRANULARITY 1, INDEX d1_null_idx assumeNotNull(d1_null) TYPE minmax GRANULARITY 1 ) Engine=MergeTree() ORDER BY key; SELECT * FROM data_01515; SELECT * FROM data_01515 SETTINGS force_data_skipping_indices=''; -- query will produce CANNOT_PARSE_TEXT error. SELECT * FROM data_01515 SETTINGS force_data_skipping_indices='d1_idx'; -- query will produce INDEX_NOT_USED error. SELECT * FROM data_01515 WHERE d1 = 0 SETTINGS force_data_skipping_indices='d 1_idx'; -- Ok. SELECT * FROM data_01515 WHERE d1 = 0 SETTINGS force_data_skipping_indices='` d1_idx`'; -- Ok (example of full featured parser). SELECT * FROM data_01515 WHERE d1 = 0 SETTINGS force_data_skipping_indices='` d1_idx`, d1_null_idx'; -- query will produce INDEX_NOT_USED error, since d1_null_idx is not used. SELECT * FROM data_01515 WHERE d1 = 0 AND assumeNotNull(d1_null) = 0 SETTINGS force_data_skipping_indices='`d1_idx`, d1_null_idx'; -- Ok. ``` --max_streams_to_max_threads_ratio arg Allows you to use more sources than the number of threads - to more evenly distribute work across threads. It is assumed that this is a temporary solution since it will be possible in the future to make the number of sources equal to the number of threads, but for each source to dynamically select available work for itself. --max_streams_multiplier_for_merge_tables arg Ask more streams when reading from Merge table. Streams will be spread across tables that Merge table will use. This allows more even distribution of work across threads and is especially helpful when merged tables differ in size. --network_compression_method arg Sets the method of data compression that is used for communication between servers and between server and [clickhouse-client](../../interfaces/cl i.md). Possible values: - `LZ4` — sets LZ4 compression method. - `ZSTD` — sets ZSTD compression method. **See Also** - [network_zstd_compression_level](#net work_zstd_compression_level) --network_zstd_compression_level arg Adjusts the level of ZSTD compression. Used only when [network_compression_met hod](#network_compression_method) is set to `ZSTD`. Possible values: - Positive integer from 1 to 15. --zstd_window_log_max arg Allows you to select the max window log of ZSTD (it will not be used for MergeTree family) --priority arg Priority of the query. 1 - the highest, higher value - lower priority; 0 - do not use priorities. --os_thread_priority arg Sets the priority ([nice](https://en.wi kipedia.org/wiki/Nice_(Unix))) for threads that execute queries. The OS scheduler considers this priority when choosing the next thread to run on each available CPU core. :::note To use this setting, you need to set the `CAP_SYS_NICE` capability. The `clickhouse-server` package sets it up during installation. Some virtual environments do not allow you to set the `CAP_SYS_NICE` capability. In this case, `clickhouse-server` shows a message about it at the start. ::: Possible values: - You can set values in the range `[-20, 19]`. Lower values mean higher priority. Threads with low `nice` priority values are executed more frequently than threads with high values. High values are preferable for long-running non-interactive queries because it allows them to quickly give up resources in favour of short interactive queries when they arrive. --log_queries [=arg(=1)] Setting up query logging. Queries sent to ClickHouse with this setup are logged according to the rules in the [query_log](../../operations/ser ver-configuration-parameters/settings.m d/#query-log) server configuration parameter. Example: ```text log_queries=1 ``` --log_formatted_queries [=arg(=1)] Allows to log formatted queries to the [system.query_log](../../operations/sys tem-tables/query_log.md) system table (populates `formatted_query` column in the [system.query_log](../../operations /system-tables/query_log.md)). Possible values: - 0 — Formatted queries are not logged in the system table. - 1 — Formatted queries are logged in the system table. --log_queries_min_type arg `query_log` minimal type to log. Possible values: - `QUERY_START` (`=1`) - `QUERY_FINISH` (`=2`) - `EXCEPTION_BEFORE_START` (`=3`) - `EXCEPTION_WHILE_PROCESSING` (`=4`) Can be used to limit which entities will go to `query_log`, say you are interested only in errors, then you can use `EXCEPTION_WHILE_PROCESSING`: ``` text log_queries_min_type='EXCEPTION_WHILE_P ROCESSING' ``` --log_queries_min_query_duration_ms arg If enabled (non-zero), queries faster than the value of this setting will not be logged (you can think about this as a `long_query_time` for [MySQL Slow Query Log](https://dev.mysql.com/doc/re fman/5.7/slow-query-log.html)), and this basically means that you will not find them in the following tables: - `system.query_log` - `system.query_thread_log` Only the queries with the following type will get to the log: - `QUERY_FINISH` - `EXCEPTION_WHILE_PROCESSING` - Type: milliseconds - Default value: 0 (any query) --log_queries_cut_to_length arg If query length is greater than a specified threshold (in bytes), then cut query when writing to query log. Also limit the length of printed query in ordinary text log. --log_queries_probability arg Allows a user to write to [query_log](../../operations/system-tab les/query_log.md), [query_thread_log](. ./../operations/system-tables/query_thr ead_log.md), and [query_views_log](../. ./operations/system-tables/query_views_ log.md) system tables only a sample of queries selected randomly with the specified probability. It helps to reduce the load with a large volume of queries in a second. Possible values: - 0 — Queries are not logged in the system tables. - Positive floating-point number in the range [0..1]. For example, if the setting value is `0.5`, about half of the queries are logged in the system tables. - 1 — All queries are logged in the system tables. --log_processors_profiles [=arg(=1)] Write time that processor spent during execution/waiting for data to `system.processors_profile_log` table. See also: - [`system.processors_profile_log`](../ ../operations/system-tables/processors_ profile_log.md) - [`EXPLAIN PIPELINE`](../../sql-refere nce/statements/explain.md/#explain-pipe line) --distributed_product_mode arg Changes the behaviour of [distributed subqueries](../../sql-reference/operato rs/in.md). ClickHouse applies this setting when the query contains the product of distributed tables, i.e. when the query for a distributed table contains a non-GLOBAL subquery for the distributed table. Restrictions: - Only applied for IN and JOIN subqueries. - Only if the FROM section uses a distributed table containing more than one shard. - If the subquery concerns a distributed table containing more than one shard. - Not used for a table-valued [remote](../../sql-reference/table-func tions/remote.md) function. Possible values: - `deny` — Default value. Prohibits using these types of subqueries (returns the "Double-distributed in/JOIN subqueries is denied" exception). - `local` — Replaces the database and table in the subquery with local ones for the destination server (shard), leaving the normal `IN`/`JOIN.` - `global` — Replaces the `IN`/`JOIN` query with `GLOBAL IN`/`GLOBAL JOIN.` - `allow` — Allows the use of these types of subqueries. --max_concurrent_queries_for_all_users arg Throw exception if the value of this setting is less or equal than the current number of simultaneously processed queries. Example: `max_concurrent_queries_for_al l_users` can be set to 99 for all users and database administrator can set it to 100 for itself to run queries for investigation even when the server is overloaded. Modifying the setting for one query or user does not affect other queries. Possible values: - Positive integer. - 0 — No limit. **Example** [truncated]