Server Settings

This section contains descriptions of server settings. These are settings which cannot be changed at the session or query level.

For more information on configuration files in ClickHouse see Configuration Files.

Other settings are described in the Settings section. Before studying the settings, we recommend reading the Configuration files section and note the use of substitutions (the and attributes).

access_control_improvements

Settings for optional improvements in the access control system.

Setting	Description	Default
users_without_row_policies_can_read_rows	Sets whether users without permissive row policies can still read rows using a SELECT query. For example, if there are two users A and B and a row policy is defined only for A, then if this setting is true, user B will see all rows. If this setting is false, user B will see no rows.	true
on_cluster_queries_require_cluster_grant	Sets whether ON CLUSTER queries require the CLUSTER grant.	true
select_from_system_db_requires_grant	Sets whether SELECT * FROM system.<table> requires any grants and can be executed by any user. If set to true then this query requires GRANT SELECT ON system.<table> just as for non-system tables. Exceptions: a few system tables (tables, columns, databases, and some constant tables like one, contributors) are still accessible for everyone; and if there is a SHOW privilege (e.g. SHOW USERS) granted then the corresponding system table (i.e. system.users) will be accessible.	true
select_from_information_schema_requires_grant	Sets whether SELECT * FROM information_schema.<table> requires any grants and can be executed by any user. If set to true, then this query requires GRANT SELECT ON information_schema.<table>, just as for ordinary tables.	true
settings_constraints_replace_previous	Sets whether a constraint in a settings profile for some setting will cancel actions of the previous constraint (defined in other profiles) for that setting, including fields which are not set by the new constraint. It also enables the changeable_in_readonly constraint type.	true
table_engines_require_grant	Sets whether creating a table with a specific table engine requires a grant.	false
role_cache_expiration_time_seconds	Sets the number of seconds since last access, that a role is stored in the Role Cache.	600

Example:

access_control_path

Path to a folder where a ClickHouse server stores user and role configurations created by SQL commands.

See also

• Access Control and Account Management

aggregate_function_group_array_action_when_limit_is_reached

Type: GroupArrayActionWhenLimitReached

Default: throw

Action to execute when max array element size is exceeded in groupArray: throw exception, or discard extra values

aggregate_function_group_array_max_element_size

Type: UInt64

Default: 16777215

Max array element size in bytes for groupArray function. This limit is checked at serialization and help to avoid large state size.

allow_feature_tier

Type: UInt32

Default: 0

Controls if the user can change settings related to the different feature tiers.

• 0 - Changes to any setting are allowed (experimental, beta, production).
• 1 - Only changes to beta and production feature settings are allowed. Changes to experimental settings are rejected.
• 2 - Only changes to production settings are allowed. Changes to experimental or beta settings are rejected.

This is equivalent to setting a readonly constraint on all EXPERIMENTAL / BETA features.

note

A value of 0 means that all settings can be changed.

allow_implicit_no_password

Forbids creating a user with no password unless 'IDENTIFIED WITH no_password' is explicitly specified.

allow_no_password

Sets whether an insecure password type of no_password is allowed or not.

allow_plaintext_password

Sets whether plaintext-password types (insecure) are allowed or not.

allow_use_jemalloc_memory

Type: Bool

Default: 1

Allows to use jemalloc memory.

async_insert_queue_flush_on_shutdown

Type: Bool

Default: 1

If true queue of asynchronous inserts is flushed on graceful shutdown

async_insert_threads

Type: UInt64

Default: 16

Maximum number of threads to actually parse and insert data in background. Zero means asynchronous mode is disabled

async_load_databases

Type: Bool

Default: 1

Asynchronous loading of databases and tables.

• If true all non-system databases with Ordinary, Atomic and Replicated engine will be loaded asynchronously after the ClickHouse server start up. See system.asynchronous_loader table, tables_loader_background_pool_size and tables_loader_foreground_pool_size server settings. Any query that tries to access a table, that is not yet loaded, will wait for exactly this table to be started up. If load job fails, query will rethrow an error (instead of shutting down the whole server in case of async_load_databases = false). The table that is waited for by at least one query will be loaded with higher priority. DDL queries on a database will wait for exactly that database to be started up. Also consider setting a limit max_waiting_queries for the total number of waiting queries.
• If false, all databases are loaded when the server starts.

Example

async_load_system_database

Type: Bool

Default: 0

Asynchronous loading of system tables. Helpful if there is a high amount of log tables and parts in the system database. Independent of the async_load_databases setting.

• If set to true, all system databases with Ordinary, Atomic, and Replicated engines will be loaded asynchronously after the ClickHouse server starts. See system.asynchronous_loader table, tables_loader_background_pool_size and tables_loader_foreground_pool_size server settings. Any query that tries to access a system table, that is not yet loaded, will wait for exactly this table to be started up. The table that is waited for by at least one query will be loaded with higher priority. Also consider setting the max_waiting_queries setting to limit the total number of waiting queries.
• If set to false, system database loads before server start.

Example

asynchronous_heavy_metrics_update_period_s

Type: UInt32

Default: 120

Period in seconds for updating heavy asynchronous metrics.

asynchronous_insert_log

Settings for the asynchronous_insert_log system table for logging async inserts.

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

Example

asynchronous_metric_log

Enabled by default on ClickHouse Cloud deployments.

If the setting is not enabled by default on your environment, depending on how ClickHouse was installed, you can follow the instruction below to enable or disable it.

Enabling

To manually turn on asynchronous metric logs history collection system.asynchronous_metric_log, create /etc/clickhouse-server/config.d/asynchronous_metric_log.xml with the following content:

Disabling

To disable asynchronous_metric_log setting, you should create the following file /etc/clickhouse-server/config.d/disable_asynchronous_metric_log.xml with the following content:

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

asynchronous_metrics_enable_heavy_metrics

Type: Bool

Default: 0

Enable the calculation of heavy asynchronous metrics.

asynchronous_metrics_update_period_s

Type: UInt32

Default: 1

Period in seconds for updating asynchronous metrics.

auth_use_forwarded_address

Use originating address for authentication for clients connected through proxy.

note

This setting should be used with extra caution since forwarded addresses can be easily spoofed - servers accepting such authentication should not be accessed directly but rather exclusively through a trusted proxy.

background_buffer_flush_schedule_pool_size

Type: UInt64

Default: 16

The maximum number of threads that will be used for performing flush operations for Buffer-engine tables in the background.

background_common_pool_size

Type: UInt64

Default: 8

The maximum number of threads that will be used for performing a variety of operations (mostly garbage collection) for *MergeTree-engine tables in the background.

background_distributed_schedule_pool_size

Type: UInt64

Default: 16

The maximum number of threads that will be used for executing distributed sends.

background_fetches_pool_size

Type: UInt64

Default: 16

The maximum number of threads that will be used for fetching data parts from another replica for *MergeTree-engine tables in the background.

background_merges_mutations_concurrency_ratio

Type: Float

Default: 2

Sets a ratio between the number of threads and the number of background merges and mutations that can be executed concurrently.

For example, if the ratio equals to 2 and background_pool_size is set to 16 then ClickHouse can execute 32 background merges concurrently. This is possible, because background operations could be suspended and postponed. This is needed to give small merges more execution priority.

note

You can only increase this ratio at runtime. To lower it you have to restart the server.

As with the background_pool_size setting background_merges_mutations_concurrency_ratio could be applied from the default profile for backward compatibility.

background_merges_mutations_scheduling_policy

Type: String

Default: round_robin

The policy on how to perform a scheduling for background merges and mutations. Possible values are: round_robin and shortest_task_first.

Algorithm used to select next merge or mutation to be executed by background thread pool. Policy may be changed at runtime without server restart. Could be applied from the default profile for backward compatibility.

Possible values:

• round_robin — Every concurrent merge and mutation is executed in round-robin order to ensure starvation-free operation. Smaller merges are completed faster than bigger ones just because they have fewer blocks to merge.
• shortest_task_first — Always execute smaller merge or mutation. Merges and mutations are assigned priorities based on their resulting size. Merges with smaller sizes are strictly preferred over bigger ones. This policy ensures the fastest possible merge of small parts but can lead to indefinite starvation of big merges in partitions heavily overloaded by INSERTs.

background_message_broker_schedule_pool_size

Type: UInt64

Default: 16

The maximum number of threads that will be used for executing background operations for message streaming.

background_move_pool_size

Type: UInt64

Default: 8

The maximum number of threads that will be used for moving data parts to another disk or volume for *MergeTree-engine tables in a background.

background_pool_size

Type: UInt64

Default: 16

Sets the number of threads performing background merges and mutations for tables with MergeTree engines.

note

• This setting could also be applied at server startup from the default profile configuration for backward compatibility at the ClickHouse server start.
• You can only increase the number of threads at runtime.
• To lower the number of threads you have to restart the server.
• By adjusting this setting, you manage CPU and disk load.

danger

Smaller pool size utilizes less CPU and disk resources, but background processes advance slower which might eventually impact query performance.

Before changing it, please also take a look at related MergeTree settings, such as:

• number_of_free_entries_in_pool_to_lower_max_size_of_merge .
• number_of_free_entries_in_pool_to_execute_mutation.

Example

background_schedule_pool_size

Type: UInt64

Default: 512

The maximum number of threads that will be used for constantly executing some lightweight periodic operations for replicated tables, Kafka streaming, and DNS cache updates.

backup_log

Settings for the backup_log system table for logging BACKUP and RESTORE operations.

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

Example

backup_threads

Type: UInt64

Default: 16

The maximum number of threads to execute BACKUP requests.

backups

Settings for backups, used when writing BACKUP TO File().

The following settings can be configured by sub-tags:

Setting	Description	Default
allowed_path	Path to backup to when using File(). This setting must be set in order to use File. The path can be relative to the instance directory or it can be absolute.	true
remove_backup_files_after_failure	If the BACKUP command fails, ClickHouse will try to remove the files already copied to the backup before the failure, otherwise it will leave the copied files as they are.	true

This setting is configured by default as:

backups_io_thread_pool_queue_size

Type: UInt64

Default: 0

The maximum number of jobs that can be scheduled on the Backups IO Thread pool. It is recommended to keep this queue unlimited due to the current S3 backup logic.

note

A value of 0 (default) means unlimited.

bcrypt_workfactor

Work factor for the bcrypt_password authentication type which uses the Bcrypt algorithm.

blog_storage_log

Settings for the blob_storage_log system table.

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

Example:

builtin_dictionaries_reload_interval

The interval in seconds before reloading built-in dictionaries.

ClickHouse reloads built-in dictionaries every x seconds. This makes it possible to edit dictionaries "on the fly" without restarting the server.

Example

cache_size_to_ram_max_ratio

Type: Double

Default: 0.5

Set cache size to RAM max ratio. Allows lowering the cache size on low-memory systems.

cannot_allocate_thread_fault_injection_probability

Type: Double

Default: 0

For testing purposes.

cgroup_memory_watcher_hard_limit_ratio

Type: Double

Default: 0.95

Specifies the "hard" threshold of the memory consumption of the server process according to cgroups after which the server's maximum memory consumption is adjusted to the threshold value.

See settings:

• cgroups_memory_usage_observer_wait_time
• cgroup_memory_watcher_soft_limit_ratio

cgroup_memory_watcher_soft_limit_ratio

Type: Double

Default: 0.9

Specifies the "soft" threshold of the memory consumption of the server process according to cgroups after which arenas in jemalloc are purged.

See settings:

• cgroups_memory_usage_observer_wait_time
• cgroup_memory_watcher_hard_limit_ratio

cgroups_memory_usage_observer_wait_time

Type: UInt64

Default: 15

Interval in seconds during which the server's maximum allowed memory consumption is adjusted by the corresponding threshold in cgroups.

To disable the cgroup observer, set this value to 0.

see settings:

• cgroup_memory_watcher_hard_limit_ratio
• cgroup_memory_watcher_soft_limit_ratio.

compiled_expression_cache_elements_size

Type: UInt64

Default: 10000

Sets the cache size (in elements) for compiled expressions.

compiled_expression_cache_size

Type: UInt64

Default: 134217728

Sets the cache size (in bytes) for compiled expressions.

compression

Data compression settings for MergeTree-engine tables.

note

We recommend not changing this if you have just started using ClickHouse.

Configuration template:

<case> fields:

• min_part_size – The minimum size of a data part.
• min_part_size_ratio – The ratio of the data part size to the table size.
• method – Compression method. Acceptable values: lz4, lz4hc, zstd,deflate_qpl.
• level – Compression level. See Codecs.

note

You can configure multiple <case> sections.

Actions when conditions are met:

• If a data part matches a condition set, ClickHouse uses the specified compression method.
• If a data part matches multiple condition sets, ClickHouse uses the first matched condition set.

note

If no conditions are met for a data part, ClickHouse uses the lz4 compression.

Example

concurrent_threads_scheduler

Type: String

Default: round_robin

The policy on how to perform a scheduling of CPU slots specified by concurrent_threads_soft_limit_num and concurrent_threads_soft_limit_ratio_to_cores. Algorithm used to govern how limited number of CPU slots are distributed among concurrent queries. Scheduler may be changed at runtime without server restart.

Possible values:

• round_robin — Every query with setting use_concurrency_control = 1 allocates up to max_threads CPU slots. One slot per thread. On contention CPU slot are granted to queries using round-robin. Note that the first slot is granted unconditionally, which could lead to unfairness and increased latency of queries having high max_threads in presence of high number of queries with max_threads = 1.
• fair_round_robin — Every query with setting use_concurrency_control = 1 allocates up to max_threads - 1 CPU slots. Variation of round_robin that does not require a CPU slot for the first thread of every query. This way queries having max_threads = 1 do not require any slot and could not unfairly allocate all slots. There are no slots granted unconditionally.

concurrent_threads_soft_limit_num

Type: UInt64

Default: 0

The maximum number of query processing threads, excluding threads for retrieving data from remote servers, allowed to run all queries. This is not a hard limit. In case if the limit is reached the query will still get at least one thread to run. Query can upscale to desired number of threads during execution if more threads become available.

note

A value of 0 (default) means unlimited.

concurrent_threads_soft_limit_ratio_to_cores

Type: UInt64

Default: 0

Same as concurrent_threads_soft_limit_num, but with ratio to cores.

config_reload_interval_ms

Type: UInt64

Default: 2000

How often clickhouse will reload config and check for new changes

core_dump

Configures soft limit for core dump file size.

note

Hard limit is configured via system tools

Example

crash_log

Settings for the crash_log system table operation.

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

The default server configuration file config.xml contains the following settings section:

custom_cached_disks_base_directory

This setting specifies the cache path for custom (created from SQL) cached disks. custom_cached_disks_base_directory has higher priority for custom disks over filesystem_caches_path (found in filesystem_caches_path.xml), which is used if the former one is absent. The filesystem cache setting path must lie inside that directory, otherwise an exception will be thrown preventing the disk from being created.

note

This will not affect disks created on an older version for which the server was upgraded. In this case, an exception will not be thrown, to allow the server to successfully start.

Example:

custom_settings_prefixes

List of prefixes for custom settings. The prefixes must be separated with commas.

Example

See Also

• Custom settings

database_atomic_delay_before_drop_table_sec

Type: UInt64

Default: 480

The delay during which a dropped table can be restored using the UNDROP statement. If DROP TABLE ran with a SYNC modifier, the setting is ignored. The default for this setting is 480 (8 minutes).

database_catalog_drop_error_cooldown_sec

Type: UInt64

Default: 5

In case of a failed table drop, ClickHouse will wait for this time-out before retrying the operation.

database_catalog_drop_table_concurrency

Type: UInt64

Default: 16

The size of the threadpool used for dropping tables.

database_catalog_unused_dir_cleanup_period_sec

Type: UInt64

Default: 86400

Parameter of a task that cleans up garbage from store/ directory. Sets scheduling period of the task.

note

A value of 0 means "never". The default value corresponds to 1 day.

database_catalog_unused_dir_hide_timeout_sec

Type: UInt64

Default: 3600

Parameter of a task that cleans up garbage from store/ directory. If some subdirectory is not used by clickhouse-server and this directory was not modified for last database_catalog_unused_dir_hide_timeout_sec seconds, the task will "hide" this directory by removing all access rights. It also works for directories that clickhouse-server does not expect to see inside store/.

note

A value of 0 means "immediately".

database_catalog_unused_dir_rm_timeout_sec

Type: UInt64

Default: 2592000

Parameter of a task that cleans up garbage from store/ directory. If some subdirectory is not used by clickhouse-server and it was previously "hidden" (see database_catalog_unused_dir_hide_timeout_sec) and this directory was not modified for last [database_catalog_unused_dir_rm_timeout_sec]/operations/server-configuration-parameters/settings#database_catalog_unused_dir_rm_timeout_sec) seconds, the task will remove this directory. It also works for directories that clickhouse-server does not expect to see inside store/.

note

A value of 0 means "never". The default value corresponds to 30 days.

database_replicated_allow_detach_permanently

Type: Bool

Default: 1

Allow detaching tables permanently in Replicated databases

default_database

Type: String

Default: default

The default database name.

default_password_type

Sets the password type to be automatically set for in queries like CREATE USER u IDENTIFIED BY 'p'.

Accepted values are:

• plaintext_password
• sha256_password
• double_sha1_password
• bcrypt_password

default_profile

Default settings profile. Settings profiles are located in the file specified in the setting user_config.

Example

default_replica_name

Type: String

Default: {replica}

The replica name in ZooKeeper.

Example

default_replica_path

Type: String

Default: /clickhouse/tables/{uuid}/{shard}

The path to the table in ZooKeeper.

Example

default_session_timeout

Default session timeout, in seconds.

dictionaries_config

The path to the config file for dictionaries.

Path:

• Specify the absolute path or the path relative to the server config file.
• The path can contain wildcards * and ?.

See also:

• "Dictionaries".

Example

dictionaries_lazy_load

Type: Bool

Default: 1

Lazy loading of dictionaries.

• If true, then each dictionary is loaded on the first use. If the loading is failed, the function that was using the dictionary throws an exception.
• If false, then the server loads all dictionaries at startup.

note

The server will wait at startup until all the dictionaries finish their loading before receiving any connections (exception: if wait_dictionaries_load_at_startup is set to false).

Example

dictionary_background_reconnect_interval

Type: UInt64

Default: 1000

Interval in milliseconds for reconnection attempts of failed MySQL and Postgres dictionaries having background_reconnect enabled.

disable_insertion_and_mutation

Type: Bool

Default: 0

Disable all insert/alter/delete queries. This setting will be enabled if someone needs read-only nodes to prevent insertion and mutation affect reading performance.

disable_internal_dns_cache

Type: Bool

Default: 0

Disables the internal DNS cache. Recommended for operating ClickHouse in systems with frequently changing infrastructure such as Kubernetes.

disable_tunneling_for_https_requests_over_http_proxy

By default, tunneling (i.e, HTTP CONNECT) is used to make HTTPS requests over HTTP proxy. This setting can be used to disable it.

no_proxy

By default, all requests will go through the proxy. In order to disable it for specific hosts, the no_proxy variable must be set. It can be set inside the <proxy> clause for list and remote resolvers and as an environment variable for environment resolver. It supports IP addresses, domains, subdomains and '*' wildcard for full bypass. Leading dots are stripped just like curl does.

Example

The below configuration bypasses proxy requests to clickhouse.cloud and all of its subdomains (e.g, auth.clickhouse.cloud). The same applies to GitLab, even though it has a leading dot. Both gitlab.com and about.gitlab.com would bypass the proxy.

disk_connections_soft_limit

Type: UInt64

Default: 5000

Connections above this limit have significantly shorter time to live. The limit applies to the disks connections.

disk_connections_store_limit

Type: UInt64

Default: 30000

Connections above this limit reset after use. Set to 0 to turn connection cache off. The limit applies to the disks connections.

disk_connections_warn_limit

Type: UInt64

Default: 10000

Warning massages are written to the logs if number of in-use connections are higher than this limit. The limit applies to the disks connections.

display_secrets_in_show_and_select

Type: Bool

Default: 0

Enables or disables showing secrets in SHOW and SELECT queries for tables, databases, table functions, and dictionaries.

User wishing to see secrets must also have format_display_secrets_in_show_and_select format setting turned on and a displaySecretsInShowAndSelect privilege.

Possible values:

• 0 — Disabled.
• 1 — Enabled.

distributed_ddl

Manage executing distributed ddl queries (CREATE, DROP, ALTER, RENAME) on cluster. Works only if ZooKeeper is enabled.

The configurable settings within <distributed_ddl> include:

Setting	Description	Default Value
path	the path in Keeper for the task_queue for DDL queries
profile	the profile used to execute the DDL queries
pool_size	how many ON CLUSTER queries can be run simultaneously
max_tasks_in_queue	the maximum number of tasks that can be in the queue.	1,000
task_max_lifetime	delete node if its age is greater than this value.	7 * 24 * 60 * 60 (a week in seconds)
cleanup_delay_period	cleaning starts after new node event is received if the last cleaning wasn't made sooner than cleanup_delay_period seconds ago.	60 seconds

Example

dns_allow_resolve_names_to_ipv4

Type: Bool

Default: 1

Allows resolve names to ipv4 addresses.

dns_allow_resolve_names_to_ipv6

Type: Bool

Default: 1

Allows resolve names to ipv6 addresses.

dns_cache_max_entries

Type: UInt64

Default: 10000

Internal DNS cache max entries.

dns_cache_update_period

Type: Int32

Default: 15

Internal DNS cache update period in seconds.

dns_max_consecutive_failures

Type: UInt32

Default: 10

Max DNS resolve failures of a hostname before dropping the hostname from ClickHouse DNS cache.

enable_azure_sdk_logging

Type: Bool

Default: 0

Enables logging from Azure sdk

encryption

Configures a command to obtain a key to be used by encryption codecs. Key (or keys) should be written in environment variables or set in the configuration file.

Keys can be hex or string with a length equal to 16 bytes.

Example

Loading from config:

note

Storing keys in the configuration file is not recommended. It isn't secure. You can move the keys into a separate config file on a secure disk and put a symlink to that config file to config.d/ folder.

Loading from config, when the key is in hex:

Loading key from the environment variable:

Here current_key_id sets the current key for encryption, and all specified keys can be used for decryption.

Each of these methods can be applied for multiple keys:

Here current_key_id shows current key for encryption.

Also, users can add nonce that must be 12 bytes long (by default encryption and decryption processes use nonce that consists of zero bytes):

Or it can be set in hex:

note

Everything mentioned above can be applied for aes_256_gcm_siv (but the key must be 32 bytes long).

error_log

It is disabled by default.

Enabling

To manually turn on error history collection system.error_log, create /etc/clickhouse-server/config.d/error_log.xml with the following content:

Disabling

To disable error_log setting, you should create the following file /etc/clickhouse-server/config.d/disable_error_log.xml with the following content:

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

format_alter_operations_with_parentheses

Type: Bool

Default: 1

If set to true, then alter operations will be surrounded by parentheses in formatted queries. This makes the parsing of formatted alter queries less ambiguous.

format_schema_path

The path to the directory with the schemes for the input data, such as schemas for the CapnProto format.

Example

global_profiler_cpu_time_period_ns

Type: UInt64

Default: 0

Period for CPU clock timer of global profiler (in nanoseconds). Set 0 value to turn off the CPU clock global profiler. Recommended value is at least 10000000 (100 times a second) for single queries or 1000000000 (once a second) for cluster-wide profiling.

global_profiler_real_time_period_ns

Type: UInt64

Default: 0

Period for real clock timer of global profiler (in nanoseconds). Set 0 value to turn off the real clock global profiler. Recommended value is at least 10000000 (100 times a second) for single queries or 1000000000 (once a second) for cluster-wide profiling.

google_protos_path

Defines a directory containing proto files for Protobuf types.

Example:

graphite

Sending data to Graphite.

Settings:

• host – The Graphite server.
• port – The port on the Graphite server.
• interval – The interval for sending, in seconds.
• timeout – The timeout for sending data, in seconds.
• root_path – Prefix for keys.
• metrics – Sending data from the system.metrics table.
• events – Sending deltas data accumulated for the time period from the system.events table.
• events_cumulative – Sending cumulative data from the system.events table.
• asynchronous_metrics – Sending data from the system.asynchronous_metrics table.

You can configure multiple <graphite> clauses. For instance, you can use this for sending different data at different intervals.

Example

graphite_rollup

Settings for thinning data for Graphite.

For more details, see GraphiteMergeTree.

Example

hsts_max_age

Expired time for HSTS in seconds.

note

A value of 0 means ClickHouse disables HSTS. If you set a positive number, the HSTS will be enabled and the max-age is the number you set.

Example

http_connections_soft_limit

Type: UInt64

Default: 100

Connections above this limit have significantly shorter time to live. The limit applies to the http connections which do not belong to any disk or storage.

http_connections_store_limit

Type: UInt64

Default: 5000

Connections above this limit reset after use. Set to 0 to turn connection cache off. The limit applies to the http connections which do not belong to any disk or storage.

http_connections_warn_limit

Type: UInt64

Default: 1000

Warning massages are written to the logs if number of in-use connections are higher than this limit. The limit applies to the http connections which do not belong to any disk or storage.

http_handlers

Allows using custom HTTP handlers. To add a new http handler simply add a new <rule>. Rules are checked from top to bottom as defined, and the first match will run the handler.

The following settings can be configured by sub-tags:

Sub-tags	Definition
url	To match the request URL, you can use the 'regex:' prefix to use regex match (optional)
methods	To match request methods, you can use commas to separate multiple method matches (optional)
headers	To match request headers, match each child element (child element name is header name), you can use 'regex:' prefix to use regex match (optional)
handler	The request handler
empty_query_string	Check that there is no query string in the URL

handler contains the following settings, which can be configured by sub-tags:

Sub-tags	Definition
url	A location for redirect
type	Supported types: static, dynamic_query_handler, predefined_query_handler, redirect
status	Use with static type, response status code
query_param_name	Use with dynamic_query_handler type, extracts and executes the value corresponding to the <query_param_name> value in HTTP request params
query	Use with predefined_query_handler type, executes query when the handler is called
content_type	Use with static type, response content-type
response_content	Use with static type, Response content sent to client, when using the prefix 'file://' or 'config://', find the content from the file or configuration send to client

Along with a list of rules, you can specify <defaults/> which specifies to enable all the default handlers.

Example:

http_options_response

Used to add headers to the response in an OPTIONS HTTP request. The OPTIONS method is used when making CORS preflight requests.

For more information, see OPTIONS.

Example:

http_server_default_response

The page that is shown by default when you access the ClickHouse HTTP(s) server. The default value is "Ok." (with a line feed at the end)

Example

Opens https://tabix.io/ when accessing http://localhost: http_port.

iceberg_catalog_threadpool_pool_size

Type: UInt64

Default: 50

Size of background pool for iceberg catalog

iceberg_catalog_threadpool_queue_size

Type: UInt64

Default: 1000000

Number of tasks which is possible to push into iceberg catalog pool

ignore_empty_sql_security_in_create_view_query

Type: Bool

Default: 1

If true, ClickHouse doesn't write defaults for empty SQL security statement in CREATE VIEW queries.

note

This setting is only necessary for the migration period and will become obsolete in 24.4

include_from

The path to the file with substitutions. Both XML and YAML formats are supported.

For more information, see the section "Configuration files".

Example

index_mark_cache_policy

Type: String

Default: SLRU

Secondary index mark cache policy name.

index_mark_cache_size

Type: UInt64

Default: 5368709120

Maximum size of cache for index marks.

note

A value of 0 means disabled.

This setting can be modified at runtime and will take effect immediately.

index_mark_cache_size_ratio

Type: Double

Default: 0.3

The size of the protected queue (in case of SLRU policy) in the secondary index mark cache relative to the cache's total size.

index_uncompressed_cache_policy

Type: String

Default: SLRU

Secondary index uncompressed cache policy name.

index_uncompressed_cache_size

Type: UInt64

Default: 0

Maximum size of cache for uncompressed blocks of MergeTree indices.

note

A value of 0 means disabled.

This setting can be modified at runtime and will take effect immediately.

index_uncompressed_cache_size_ratio

Type: Double

Default: 0.5

The size of the protected queue (in case of SLRU policy) in the secondary index uncompressed cache relative to the cache's total size.

interserver_http_credentials

A username and a password used to connect to other servers during replication. Additionally, the server authenticates other replicas using these credentials. interserver_http_credentials must therefore be the same for all replicas in a cluster.

note

• By default, if interserver_http_credentials section is omitted, authentication is not used during replication.
• interserver_http_credentials settings do not relate to a ClickHouse client credentials configuration.
• These credentials are common for replication via HTTP and HTTPS.

The following settings can be configured by sub-tags:

• user — Username.
• password — Password.
• allow_empty — If true, then other replicas are allowed to connect without authentication even if credentials are set. If false, then connections without authentication are refused. Default: false.
• old — Contains old user and password used during credential rotation. Several old sections can be specified.

Credentials Rotation

ClickHouse supports dynamic interserver credentials rotation without stopping all replicas at the same time to update their configuration. Credentials can be changed in several steps.

To enable authentication, set interserver_http_credentials.allow_empty to true and add credentials. This allows connections with authentication and without it.

After configuring all replicas set allow_empty to false or remove this setting. It makes authentication with new credentials mandatory.

To change existing credentials, move the username and the password to interserver_http_credentials.old section and update user and password with new values. At this point the server uses new credentials to connect to other replicas and accepts connections with either new or old credentials.

When new credentials are applied to all replicas, old credentials may be removed.

interserver_http_host

The hostname that can be used by other servers to access this server.

If omitted, it is defined in the same way as the hostname -f command.

Useful for breaking away from a specific network interface.

Example

interserver_http_port

Port for exchanging data between ClickHouse servers.

Example

interserver_https_host

Similar to interserver_http_host, except that this hostname can be used by other servers to access this server over HTTPS.

Example

interserver_https_port

Port for exchanging data between ClickHouse servers over HTTPS.

Example

interserver_listen_host

Restriction on hosts that can exchange data between ClickHouse servers. If Keeper is used, the same restriction will be applied to the communication between different Keeper instances.

note

By default, the value is equal to the listen_host setting.

Example

Type:

Default:

io_thread_pool_queue_size

Type: UInt64

Default: 10000

The maximum number of jobs that can be scheduled on the IO Thread pool.

note

A value of 0 means unlimited.

keep_alive_timeout

Type: Seconds

Default: 30

The number of seconds that ClickHouse waits for incoming requests for HTTP protocol before closing the connection.

Example

keeper_multiread_batch_size

Type: UInt64

Default: 10000

Maximum size of batch for MultiRead request to [Zoo]Keeper that support batching. If set to 0, batching is disabled. Available only in ClickHouse Cloud.

latency_log

It is disabled by default.

Enabling

To manually turn on latency history collection system.latency_log, create /etc/clickhouse-server/config.d/latency_log.xml with the following content:

Disabling

To disable latency_log setting, you should create the following file /etc/clickhouse-server/config.d/disable_latency_log.xml with the following content:

ldap_servers

List LDAP servers with their connection parameters here to:

• use them as authenticators for dedicated local users, who have an 'ldap' authentication mechanism specified instead of 'password'
• use them as remote user directories.

The following settings can be configured by sub-tags:

Setting	Description
host	LDAP server hostname or IP, this parameter is mandatory and cannot be empty.
port	LDAP server port, default is 636 if enable_tls is set to true, 389 otherwise.
bind_dn	Template used to construct the DN to bind to. The resulting DN will be constructed by replacing all \{user_name\} substrings of the template with the actual user name during each authentication attempt.
user_dn_detection	Section with LDAP search parameters for detecting the actual user DN of the bound user. This is mainly used in search filters for further role mapping when the server is Active Directory. The resulting user DN will be used when replacing \{user_dn\} substrings wherever they are allowed. By default, user DN is set equal to bind DN, but once search is performed, it will be updated with to the actual detected user DN value.
verification_cooldown	A period of time, in seconds, after a successful bind attempt, during which a user will be assumed to be successfully authenticated for all consecutive requests without contacting the LDAP server. Specify 0 (the default) to disable caching and force contacting the LDAP server for each authentication request.
enable_tls	Flag to trigger use of secure connection to the LDAP server. Specify no for plain text (ldap://) protocol (not recommended). Specify yes for LDAP over SSL/TLS (ldaps://) protocol (recommended, the default). Specify starttls for legacy StartTLS protocol (plain text (ldap://) protocol, upgraded to TLS).
tls_minimum_protocol_version	The minimum protocol version of SSL/TLS. Accepted values are: ssl2, ssl3, tls1.0, tls1.1, tls1.2 (the default).
tls_require_cert	SSL/TLS peer certificate verification behavior. Accepted values are: never, allow, try, demand (the default).
tls_cert_file	path to certificate file.
tls_key_file	path to certificate key file.
tls_ca_cert_file	path to CA certificate file.
tls_ca_cert_dir	path to the directory containing CA certificates.
tls_cipher_suite	allowed cipher suite (in OpenSSL notation).

Setting user_dn_detection can be configured with sub-tags:

Setting	Description
base_dn	template used to construct the base DN for the LDAP search. The resulting DN will be constructed by replacing all \{user_name\} and '{bind_dn}' substrings of the template with the actual user name and bind DN during the LDAP search.
scope	scope of the LDAP search. Accepted values are: base, one_level, children, subtree (the default).
search_filter	template used to construct the search filter for the LDAP search. The resulting filter will be constructed by replacing all \{user_name\}, \{bind_dn\}, and \{base_dn\} substrings of the template with the actual user name, bind DN, and base DN during the LDAP search. Note, that the special characters must be escaped properly in XML.

Example:

Example (typical Active Directory with configured user DN detection for further role mapping):

license_key

Type: String

License key for ClickHouse Enterprise Edition

listen_backlog

Backlog (queue size of pending connections) of the listen socket. The default value of 4096 is the same as that of linux 5.4+).

Usually this value does not need to be changed, since:

• The default value is large enough,
• For accepting client's connections server has separate thread.

So even if you have TcpExtListenOverflows (from nstat) non-zero and this counter grows for ClickHouse server it does not mean that this value needs to be increased, since:

• Usually if 4096 is not enough it shows some internal ClickHouse scaling issue, so it is better to report an issue.
• It does not mean that the server can handle more connections later (and even if it could, by that moment clients may be gone or disconnected).

Example

listen_host

Restriction on hosts that requests can come from. If you want the server to answer all of them, specify ::.

Examples:

listen_reuse_port

Allow multiple servers to listen on the same address:port. Requests will be routed to a random server by the operating system. Enabling this setting is not recommended.

Example

Type:

Default:

listen_try

The server will not exit if IPv6 or IPv4 networks are unavailable while trying to listen.

Example

load_marks_threadpool_pool_size

Type: UInt64

Default: 50

Size of background pool for marks loading

load_marks_threadpool_queue_size

Type: UInt64

Default: 1000000

Number of tasks which is possible to push into prefetches pool

logger

The location and format of log messages.

Keys:

Key	Description
level	Log level. Acceptable values: none (turn logging off), fatal, critical, error, warning, notice, information,debug, trace, test
log	The path to the log file.
errorlog	The path to the error log file.
size	Rotation policy: Maximum size of the log files in bytes. Once the log file size exceeds this threshold, it is renamed and archived, and a new log file is created.
count	Rotation policy: How many historical log files Clickhouse are kept at most.
stream_compress	Compress log messages using LZ4. Set to 1 or true to enable.
console	Do not write log messages to log files, instead print them in the console. Set to 1 or true to enable. Default is 1 if Clickhouse does not run in daemon mode, 0 otherwise.
console_log_level	Log level for console output. Defaults to level.
formatting	Log format for console output. Currently, only json is supported
use_syslog	Also forward log output to syslog.
syslog_level	Log level for logging to syslog.

Log format specifiers

File names in log and errorLog paths support below format specifiers for the resulting file name (the directory part does not support them).

Column "Example" shows the output at 2023-07-06 18:32:07.

Specifier	Description	Example
%%	Literal %	%
%n	New-line character
%t	Horizontal tab character
%Y	Year as a decimal number, e.g. 2017	2023
%y	Last 2 digits of year as a decimal number (range [00,99])	23
%C	First 2 digits of year as a decimal number (range [00,99])	20
%G	Four-digit ISO 8601 week-based year, i.e. the year that contains the specified week. Normally useful only with %V	2023
%g	Last 2 digits of ISO 8601 week-based year, i.e. the year that contains the specified week.	23
%b	Abbreviated month name, e.g. Oct (locale dependent)	Jul
%h	Synonym of %b	Jul
%B	Full month name, e.g. October (locale dependent)	July
%m	Month as a decimal number (range [01,12])	07
%U	Week of the year as a decimal number (Sunday is the first day of the week) (range [00,53])	27
%W	Week of the year as a decimal number (Monday is the first day of the week) (range [00,53])	27
%V	ISO 8601 week number (range [01,53])	27
%j	Day of the year as a decimal number (range [001,366])	187
%d	Day of the month as a zero-padded decimal number (range [01,31]). Single digit is preceded by zero.	06
%e	Day of the month as a space-padded decimal number (range [1,31]). Single digit is preceded by a space.	  6
%a	Abbreviated weekday name, e.g. Fri (locale dependent)	Thu
%A	Full weekday name, e.g. Friday (locale dependent)	Thursday
%w	Weekday as a integer number with Sunday as 0 (range [0-6])	4
%u	Weekday as a decimal number, where Monday is 1 (ISO 8601 format) (range [1-7])	4
%H	Hour as a decimal number, 24 hour clock (range [00-23])	18
%I	Hour as a decimal number, 12 hour clock (range [01,12])	06
%M	Minute as a decimal number (range [00,59])	32
%S	Second as a decimal number (range [00,60])	07
%c	Standard date and time string, e.g. Sun Oct 17 04:41:13 2010 (locale dependent)	Thu Jul 6 18:32:07 2023
%x	Localized date representation (locale dependent)	07/06/23
%X	Localized time representation, e.g. 18:40:20 or 6:40:20 PM (locale dependent)	18:32:07
%D	Short MM/DD/YY date, equivalent to %m/%d/%y	07/06/23
%F	Short YYYY-MM-DD date, equivalent to %Y-%m-%d	2023-07-06
%r	Localized 12-hour clock time (locale dependent)	06:32:07 PM
%R	Equivalent to "%H:%M"	18:32
%T	Equivalent to "%H:%M:%S" (the ISO 8601 time format)	18:32:07
%p	Localized a.m. or p.m. designation (locale dependent)	PM
%z	Offset from UTC in the ISO 8601 format (e.g. -0430), or no characters if the time zone information is not available	+0800
%Z	Locale-dependent time zone name or abbreviation, or no characters if the time zone information is not available	Z AWST

Example

To print log messages only in the console:

Per-level Overrides

The log level of individual log names can be overridden. For example, to mute all messages of loggers "Backup" and "RBAC".

syslog

To write log messages additionally to syslog:

Keys for <syslog>:

Key	Description
address	The address of syslog in format host\[:port\]. If omitted, the local daemon is used.
hostname	The name of the host from which logs are send (optional).
facility	The syslog facility keyword. Must be specified uppercase with a "LOG_" prefix, e.g. LOG_USER, LOG_DAEMON, LOG_LOCAL3, etc. Default: LOG_USER if address is specified, LOG_DAEMON otherwise.
format	Log message format. Possible values: bsd and syslog.

Log formats

You can specify the log format that will be outputted in the console log. Currently, only JSON is supported.

Example

Here is an example of an output JSON log:

To enable JSON logging support, use the following snippet:

Renaming keys for JSON logs

Key names can be modified by changing tag values inside the <names> tag. For example, to change DATE_TIME to MY_DATE_TIME, you can use <date_time>MY_DATE_TIME</date_time>.

Omitting keys for JSON logs

Log properties can be omitted by commenting out the property. For example, if you do not want your log to print query_id, you can comment out the <query_id> tag.

macros

Parameter substitutions for replicated tables.

Can be omitted if replicated tables are not used.

For more information, see the section Creating replicated tables.

Example

mark_cache_policy

Type: String

Default: SLRU

Mark cache policy name.

mark_cache_prewarm_ratio

Type: Double

Default: 0.95

The ratio of total size of mark cache to fill during prewarm.

mark_cache_size

Type: UInt64

Default: 5368709120

Maximum size of cache for marks (index of MergeTree family of tables).

note

This setting can be modified at runtime and will take effect immediately.

mark_cache_size_ratio

Type: Double

Default: 0.5

The size of the protected queue (in case of SLRU policy) in the mark cache relative to the cache's total size.

max_active_parts_loading_thread_pool_size

Type: UInt64

Default: 64

The number of threads to load active set of data parts (Active ones) at startup.

max_authentication_methods_per_user

Type: UInt64

Default: 100

The maximum number of authentication methods a user can be created with or altered to. Changing this setting does not affect existing users. Create/alter authentication-related queries will fail if they exceed the limit specified in this setting. Non authentication create/alter queries will succeed.

note

A value of 0 means unlimited.

max_backup_bandwidth_for_server

Type: UInt64

Default: 0

The maximum read speed in bytes per second for all backups on server. Zero means unlimited.

max_backups_io_thread_pool_free_size

Type: UInt64

Default: 0

If the number of idle threads in the Backups IO Thread pool exceeds max_backup_io_thread_pool_free_size, ClickHouse will release resources occupied by idling threads and decrease the pool size. Threads can be created again if necessary.

max_backups_io_thread_pool_size

Type: UInt64

Default: 1000

ClickHouse uses threads from the Backups IO Thread pool to do S3 backup IO operations. max_backups_io_thread_pool_size limits the maximum number of threads in the pool.

max_build_vector_similarity_index_thread_pool_size

Type: UInt64

Default: 16

The maximum number of threads to use for building vector indexes.

note

A value of 0 means all cores.

max_concurrent_insert_queries

Type: UInt64

Default: 0

Limit on total number of concurrent insert queries.

note

A value of 0 (default) means unlimited.

This setting can be modified at runtime and will take effect immediately. Queries that are already running will remain unchanged.

max_concurrent_queries

Type: UInt64

Default: 0

Limit on total number of concurrently executed queries. Note that limits on INSERT and SELECT queries, and on the maximum number of queries for users must also be considered.

See also:

• max_concurrent_insert_queries
• max_concurrent_select_queries
• max_concurrent_queries_for_all_users

note

A value of 0 (default) means unlimited.

This setting can be modified at runtime and will take effect immediately. Queries that are already running will remain unchanged.

max_concurrent_select_queries

Type: UInt64

Default: 0

Limit on total number of concurrently select queries.

note

A value of 0 (default) means unlimited.

This setting can be modified at runtime and will take effect immediately. Queries that are already running will remain unchanged.

max_connections

Type: Int32

Default: 4096

Max server connections.

max_database_num_to_throw

Type: UInt64

Default: 0

If number of databases is greater than this value, server will throw an exception. 0 means no limitation.

max_database_num_to_warn

Type: UInt64

Default: 1000

If the number of attached databases exceeds the specified value, clickhouse server will add warning messages to system.warnings table.

Example

max_database_replicated_create_table_thread_pool_size

Type: UInt32

Default: 1

The number of threads to create tables during replica recovery in DatabaseReplicated. Zero means number of threads equal number of cores.

max_dictionary_num_to_throw

Type: UInt64

Default: 0

If the number of dictionaries is greater than this value, the server will throw an exception.

Only counts tables for database engines:

• Atomic
• Ordinary
• Replicated
• Lazy

note

A value of 0 means no limitation.

Example

max_dictionary_num_to_warn

Type: UInt64

Default: 1000

If the number of attached dictionaries exceeds the specified value, clickhouse server will add warning messages to system.warnings table.

Example

max_entries_for_hash_table_stats

Type: UInt64

Default: 10000

How many entries hash table statistics collected during aggregation is allowed to have

max_fetch_partition_thread_pool_size

Type: UInt64

Default: 64

The number of threads for ALTER TABLE FETCH PARTITION.

max_io_thread_pool_free_size

Type: UInt64

Default: 0

If the number of idle threads in the IO Thread pool exceeds max_io_thread_pool_free_size, ClickHouse will release resources occupied by idling threads and decrease the pool size. Threads can be created again if necessary.

max_io_thread_pool_size

Type: UInt64

Default: 100

ClickHouse uses threads from the IO Thread pool to do some IO operations (e.g. to interact with S3). max_io_thread_pool_size limits the maximum number of threads in the pool.

max_keep_alive_requests

Type: UInt64

Default: 10000

Maximal number of requests through a single keep-alive connection until it will be closed by ClickHouse server.

Example

max_local_read_bandwidth_for_server

Type: UInt64

Default: 0

The maximum speed of local reads in bytes per second.

note

A value of 0 means unlimited.

max_local_write_bandwidth_for_server

Type: UInt64

Default: 0

The maximum speed of local writes in bytes per seconds.

note

A value of 0 means unlimited.

max_materialized_views_count_for_table

Type: UInt64

Default: 0

A limit on the number of materialized views attached to a table.

note

Only directly dependent views are considered here, and the creation of one view on top of another view is not considered.

max_merges_bandwidth_for_server

Type: UInt64

Default: 0

The maximum read speed of all merges on server in bytes per second. Zero means unlimited.

max_mutations_bandwidth_for_server

Type: UInt64

Default: 0

The maximum read speed of all mutations on server in bytes per second. Zero means unlimited.

max_open_files

The maximum number of open files.

note

We recommend using this option in macOS since the getrlimit() function returns an incorrect value.

Example

max_outdated_parts_loading_thread_pool_size

Type: UInt64

Default: 32

The number of threads to load inactive set of data parts (Outdated ones) at startup.

max_part_num_to_warn

Type: UInt64

Default: 100000

If the number of active parts exceeds the specified value, clickhouse server will add warning messages to system.warnings table.

Example

max_partition_size_to_drop

Type: UInt64

Default: 50000000000

Restriction on dropping partitions.

If the size of a MergeTree table exceeds max_partition_size_to_drop (in bytes), you can't drop a partition using a DROP PARTITION query. This setting does not require a restart of the ClickHouse server to apply. Another way to disable the restriction is to create the <clickhouse-path>/flags/force_drop_table file.

note

The value 0 means that you can drop partitions without any restrictions.

This limitation does not restrict drop table and truncate table, see max_table_size_to_drop

Example

max_parts_cleaning_thread_pool_size

Type: UInt64

Default: 128

The number of threads for concurrent removal of inactive data parts.

max_pending_mutations_to_warn

Type: UInt64

Default: 500

If the number of pending mutations exceeds the specified value, clickhouse server will add warning messages to system.warnings table.

Example

max_prefixes_deserialization_thread_pool_free_size

Type: UInt64

Default: 0

If the number of idle threads in the prefixes deserialization Thread pool exceeds max_prefixes_deserialization_thread_pool_free_size, ClickHouse will release resources occupied by idling threads and decrease the pool size. Threads can be created again if necessary.

max_prefixes_deserialization_thread_pool_size

Type: UInt64

Default: 100

ClickHouse uses threads from the prefixes deserialization Thread pool for parallel reading of metadata of columns and subcolumns from file prefixes in Wide parts in MergeTree. max_prefixes_deserialization_thread_pool_size limits the maximum number of threads in the pool.

max_remote_read_network_bandwidth_for_server

Type: UInt64

Default: 0

The maximum speed of data exchange over the network in bytes per second for read.

note

A value of 0 (default) means unlimited.

max_remote_write_network_bandwidth_for_server

Type: UInt64

Default: 0

The maximum speed of data exchange over the network in bytes per second for write.

note

A value of 0 (default) means unlimited.

max_replicated_fetches_network_bandwidth_for_server

Type: UInt64

Default: 0

The maximum speed of data exchange over the network in bytes per second for replicated fetches. Zero means unlimited.

max_replicated_sends_network_bandwidth_for_server

Type: UInt64

Default: 0

The maximum speed of data exchange over the network in bytes per second for replicated sends. Zero means unlimited.

max_replicated_table_num_to_throw

Type: UInt64

Default: 0

If the number of replicated tables is greater than this value, the server will throw an exception.

Only counts tables for database engines:

• Atomic
• Ordinary
• Replicated
• Lazy

note

A value of 0 means no limitation.

Example

max_server_memory_usage

Type: UInt64

Default: 0

The maximum amount of memory the server is allowed to use, expressed in bytes.

note

The maximum memory consumption of the server is further restricted by setting max_server_memory_usage_to_ram_ratio.

As a special case, a value of 0 (default) means the server may consume all available memory (excluding further restrictions imposed by max_server_memory_usage_to_ram_ratio).

max_server_memory_usage_to_ram_ratio

Type: Double

Default: 0.9

The maximum amount of memory the server is allowed to use, expressed as a ratio to all available memory.

For example, a value of 0.9 (default) means that the server may consume 90% of the available memory.

Allows lowering the memory usage on low-memory systems. On hosts with low RAM and swap, you may possibly need setting max_server_memory_usage_to_ram_ratio set larger than 1.

note

The maximum memory consumption of the server is further restricted by setting max_server_memory_usage.

max_session_timeout

Maximum session timeout, in seconds.

Example:

max_table_num_to_throw

Type: UInt64

Default: 0

If number of tables is greater than this value, server will throw an exception.

The following tables are not counted:

• view
• remote
• dictionary
• system

Only counts tables for database engines:

• Atomic
• Ordinary
• Replicated
• Lazy

note

A value of 0 means no limitation.

Example

max_table_num_to_warn

Type: UInt64

Default: 5000

If the number of attached tables exceeds the specified value, clickhouse server will add warning messages to system.warnings table.

Example

max_table_size_to_drop

Type: UInt64

Default: 50000000000

Restriction on deleting tables.

If the size of a MergeTree table exceeds max_table_size_to_drop (in bytes), you can't delete it using a DROP query or TRUNCATE query.

note

A value of 0 means that you can delete all tables without any restrictions.

This setting does not require a restart of the ClickHouse server to apply. Another way to disable the restriction is to create the <clickhouse-path>/flags/force_drop_table file.

Example

max_temporary_data_on_disk_size

Type: UInt64

Default: 0

The maximum amount of storage that could be used for external aggregation, joins or sorting. Queries that exceed this limit will fail with an exception.

note

A value of 0 means unlimited.

See also:

• max_temporary_data_on_disk_size_for_user
• max_temporary_data_on_disk_size_for_query

max_thread_pool_free_size

Type: UInt64

Default: 1000

If the number of idle threads in the Global Thread pool is greater than max_thread_pool_free_size, then ClickHouse releases resources occupied by some threads and the pool size is decreased. Threads can be created again if necessary.

Example

max_thread_pool_size

Type: UInt64

Default: 10000

ClickHouse uses threads from the Global Thread pool to process queries. If there is no idle thread to process a query, then a new thread is created in the pool. max_thread_pool_size limits the maximum number of threads in the pool.

Example

max_unexpected_parts_loading_thread_pool_size

Type: UInt64

Default: 8

The number of threads to load inactive set of data parts (Unexpected ones) at startup.

max_view_num_to_throw

Type: UInt64

Default: 0

If the number of views is greater than this value, the server will throw an exception.

Only counts tables for database engines:

• Atomic
• Ordinary
• Replicated
• Lazy

note

A value of 0 means no limitation.

Example

max_view_num_to_warn

Type: UInt64

Default: 10000

If the number of attached views exceeds the specified value, clickhouse server will add warning messages to system.warnings table.

Example

max_waiting_queries

Type: UInt64

Default: 0

Limit on total number of concurrently waiting queries. Execution of a waiting query is blocked while required tables are loading asynchronously (see async_load_databases.

note

Waiting queries are not counted when limits controlled by the following settings are checked:

• max_concurrent_queries
• max_concurrent_insert_queries
• max_concurrent_select_queries
• max_concurrent_queries_for_user
• max_concurrent_queries_for_all_users

This correction is done to avoid hitting these limits just after server startup.

note

A value of 0 (default) means unlimited.

This setting can be modified at runtime and will take effect immediately. Queries that are already running will remain unchanged.

memory_worker_correct_memory_tracker

Type: Bool

Default: 0

Whether background memory worker should correct internal memory tracker based on the information from external sources like jemalloc and cgroups

memory_worker_period_ms

Type: UInt64

Default: 0

Tick period of background memory worker which corrects memory tracker memory usages and cleans up unused pages during higher memory usage. If set to 0, default value will be used depending on the memory usage source

memory_worker_use_cgroup

Type: Bool

Default: 1

Use current cgroup memory usage information to correct memory tracking.

merge_tree

Fine-tuning for tables in the MergeTree.

For more information, see the MergeTreeSettings.h header file.

Example

merge_workload

Type: String

Default: default

Used to regulate how resources are utilized and shared between merges and other workloads. Specified value is used as workload setting value for all background merges. Can be overridden by a merge tree setting.

See Also

• Workload Scheduling

merges_mutations_memory_usage_soft_limit

Type: UInt64

Default: 0

Sets the limit on how much RAM is allowed to use for performing merge and mutation operations. If ClickHouse reaches the limit set, it won't schedule any new background merge or mutation operations but will continue to execute already scheduled tasks.

note

A value of 0 means unlimited.

Example

merges_mutations_memory_usage_to_ram_ratio

Type: Double

Default: 0.5

The default merges_mutations_memory_usage_soft_limit value is calculated as memory_amount * merges_mutations_memory_usage_to_ram_ratio.

See also:

• max_memory_usage
• merges_mutations_memory_usage_soft_limit

metric_log

It is disabled by default.

Enabling

To manually turn on metrics history collection system.metric_log, create /etc/clickhouse-server/config.d/metric_log.xml with the following content:

Disabling

To disable metric_log setting, you should create the following file /etc/clickhouse-server/config.d/disable_metric_log.xml with the following content:

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

mlock_executable

Perform mlockall after startup to lower first queries latency and to prevent clickhouse executable from being paged out under high IO load.

note

Enabling this option is recommended but will lead to increased startup time for up to a few seconds. Keep in mind that this setting would not work without "CAP_IPC_LOCK" capability.

Example

mmap_cache_size

Type: UInt64

Default: 1024

Sets the cache size (in bytes) for mapped files. This setting allows avoiding frequent open/close calls (which are very expensive due to consequent page faults), and to reuse mappings from several threads and queries. The setting value is the number of mapped regions (usually equal to the number of mapped files).

The amount of data in mapped files can be monitored in the following system tables with the following metrics:

System Table	Metric
system.metrics and system.metric_log	MMappedFiles and MMappedFileBytes
system.asynchronous_metrics_log	MMapCacheCells
system.events, system.processes, system.query_log, system.query_thread_log, system.query_views_log	CreatedReadBufferMMap, CreatedReadBufferMMapFailed, MMappedFileCacheHits, MMappedFileCacheMisses

note

The amount of data in mapped files does not consume memory directly and is not accounted for in query or server memory usage — because this memory can be discarded similar to the OS page cache. The cache is dropped (the files are closed) automatically on the removal of old parts in tables of the MergeTree family, also it can be dropped manually by the SYSTEM DROP MMAP CACHE query.

This setting can be modified at runtime and will take effect immediately.

mutation_workload

Type: String

Default: default

Used to regulate how resources are utilized and shared between mutations and other workloads. Specified value is used as workload setting value for all background mutations. Can be overridden by a merge tree setting.

See Also

• Workload Scheduling

mysql_port

Port for communicating with clients over MySQL protocol.

note

• Positive integers specify the port number to listen to
• Empty values are used to disable communication with clients over MySQL protocol.

Example

openSSL

SSL client/server configuration.

Support for SSL is provided by the libpoco library. The available configuration options are explained in SSLManager.h. Default values can be found in SSLManager.cpp.

Keys for server/client settings:

Option	Description	Default Value
privateKeyFile	Path to the file with the secret key of the PEM certificate. The file may contain a key and certificate at the same time.
certificateFile	Path to the client/server certificate file in PEM format. You can omit it if privateKeyFile contains the certificate.
caConfig	Path to the file or directory that contains trusted CA certificates. If this points to a file, it must be in PEM format and can contain several CA certificates. If this points to a directory, it must contain one .pem file per CA certificate. The filenames are looked up by the CA subject name hash value. Details can be found in the man page of SSL_CTX_load_verify_locations.
verificationMode	The method for checking the node's certificates. Details are in the description of the Context class. Possible values: none, relaxed, strict, once.	relaxed
verificationDepth	The maximum length of the verification chain. Verification will fail if the certificate chain length exceeds the set value.	9
loadDefaultCAFile	Wether built-in CA certificates for OpenSSL will be used. ClickHouse assumes that builtin CA certificates are in the file /etc/ssl/cert.pem (resp. the directory /etc/ssl/certs) or in file (resp. directory) specified by the environment variable SSL_CERT_FILE (resp. SSL_CERT_DIR).	true
cipherList	Supported OpenSSL encryptions.	ALL:!ADH:!LOW:!EXP:!MD5:!3DES:@STRENGTH
cacheSessions	Enables or disables caching sessions. Must be used in combination with sessionIdContext. Acceptable values: true, false.	false
sessionIdContext	A unique set of random characters that the server appends to each generated identifier. The length of the string must not exceed SSL_MAX_SSL_SESSION_ID_LENGTH. This parameter is always recommended since it helps avoid problems both if the server caches the session and if the client requested caching.	$\{application.name\}
sessionCacheSize	The maximum number of sessions that the server caches. A value of 0 means unlimited sessions.	1024*20
sessionTimeout	Time for caching the session on the server in hours.	2
extendedVerification	If enabled, verify that the certificate CN or SAN matches the peer hostname.	false
requireTLSv1	Require a TLSv1 connection. Acceptable values: true, false.	false
requireTLSv1_1	Require a TLSv1.1 connection. Acceptable values: true, false.	false
requireTLSv1_2	Require a TLSv1.2 connection. Acceptable values: true, false.	false
fips	Activates OpenSSL FIPS mode. Supported if the library's OpenSSL version supports FIPS.	false
privateKeyPassphraseHandler	Class (PrivateKeyPassphraseHandler subclass) that requests the passphrase for accessing the private key. For example: <privateKeyPassphraseHandler>, <name>KeyFileHandler</name>, <options><password>test</password></options>, </privateKeyPassphraseHandler>.	KeyConsoleHandler
invalidCertificateHandler	Class (a subclass of CertificateHandler) for verifying invalid certificates. For example: <invalidCertificateHandler> <name>RejectCertificateHandler</name> </invalidCertificateHandler> .	RejectCertificateHandler
disableProtocols	Protocols that are not allowed to be used.
preferServerCiphers	Client-preferred server ciphers.	false

Example of settings:

opentelemetry_span_log

Settings for the opentelemetry_span_log system table.

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

Example:

page_cache_block_size

Type: UInt64

Default: 1048576

Size of file chunks to store in the userspace page cache, in bytes. All reads that go through the cache will be rounded up to a multiple of this size.

page_cache_free_memory_ratio

Type: Double

Default: 0.15

Fraction of the memory limit to keep free from the userspace page cache. Analogous to Linux min_free_kbytes setting.

page_cache_history_window_ms

Type: UInt64

Default: 1000

Delay before freed memory can be used by userspace page cache.

page_cache_lookahead_blocks

Type: UInt64

Default: 16

On userspace page cache miss, read up to this many consecutive blocks at once from the underlying storage, if they're also not in the cache. Each block is page_cache_block_size bytes.

page_cache_max_size

Type: UInt64

Default: 0

Maximum size of the userspace page cache. Set to 0 to disable the cache. If greater than page_cache_min_size, the cache size will be continuously adjusted within this range, to use most of the available memory while keeping the total memory usage below the limit (max_server_memory_usage[_to_ram_ratio]).

page_cache_min_size

Type: UInt64

Default: 104857600

Minimum size of the userspace page cache.

page_cache_policy

Type: String

Default: SLRU

Userspace page cache policy name.

page_cache_shards

Type: UInt64

Default: 4

Stripe userspace page cache over this many shards to reduce mutex contention. Experimental, not likely to improve performance.

page_cache_size_ratio

Type: Double

Default: 0.5

The size of the protected queue in the userspace page cache relative to the cache's total size.

part_log

Logging events that are associated with MergeTree. For instance, adding or merging data. You can use the log to simulate merge algorithms and compare their characteristics. You can visualize the merge process.

Queries are logged in the system.part_log table, not in a separate file. You can configure the name of this table in the table parameter (see below).

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

Example

parts_kill_delay_period

Type: UInt64

Default: 30

Period to completely remove parts for SharedMergeTree. Only available in ClickHouse Cloud

parts_kill_delay_period_random_add

Type: UInt64

Default: 10

Add uniformly distributed value from 0 to x seconds to kill_delay_period to avoid thundering herd effect and subsequent DoS of ZooKeeper in case of very large number of tables. Only available in ClickHouse Cloud

parts_killer_pool_size

Type: UInt64

Default: 128

Threads for cleanup of shared merge tree outdated threads. Only available in ClickHouse Cloud

path

The path to the directory containing data.

note

The trailing slash is mandatory.

Example

postgresql_port

Port for communicating with clients over PostgreSQL protocol.

note

• Positive integers specify the port number to listen to
• Empty values are used to disable communication with clients over MySQL protocol.

Example

prefetch_threadpool_pool_size

Type: UInt64

Default: 100

Size of background pool for prefetches for remote object storages

prefetch_threadpool_queue_size

Type: UInt64

Default: 1000000

Number of tasks which is possible to push into prefetches pool

prefixes_deserialization_thread_pool_thread_pool_queue_size

Type: UInt64

Default: 10000

The maximum number of jobs that can be scheduled on the prefixes deserialization Thread pool.

note

A value of 0 means unlimited.

prepare_system_log_tables_on_startup

Type: Bool

Default: 0

If true, ClickHouse creates all configured system.*_log tables before the startup. It can be helpful if some startup scripts depend on these tables.

primary_index_cache_policy

Type: String

Default: SLRU

Primary index cache policy name.

primary_index_cache_prewarm_ratio

Type: Double

Default: 0.95

The ratio of total size of mark cache to fill during prewarm.

primary_index_cache_size

Type: UInt64

Default: 5368709120

Maximum size of cache for primary index (index of MergeTree family of tables).

primary_index_cache_size_ratio

Type: Double

Default: 0.5

The size of the protected queue (in case of SLRU policy) in the primary index cache relative to the cache's total size.

processors_profile_log

Settings for the processors_profile_log system table.

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

The default settings are:

prometheus

Exposing metrics data for scraping from Prometheus.

Settings:

• endpoint – HTTP endpoint for scraping metrics by prometheus server. Start from '/'.
• port – Port for endpoint.
• metrics – Expose metrics from the system.metrics table.
• events – Expose metrics from the system.events table.
• asynchronous_metrics – Expose current metrics values from the system.asynchronous_metrics table.
• errors - Expose the number of errors by error codes occurred since the last server restart. This information could be obtained from the system.errors as well.

Example

Check (replace 127.0.0.1 with the IP addr or hostname of your ClickHouse server):

proxy

Define proxy servers for HTTP and HTTPS requests, currently supported by S3 storage, S3 table functions, and URL functions.

There are three ways to define proxy servers:

• environment variables
• proxy lists
• remote proxy resolvers.

Bypassing proxy servers for specific hosts is also supported with the use of no_proxy.

Environment variables

The http_proxy and https_proxy environment variables allow you to specify a proxy server for a given protocol. If you have it set on your system, it should work seamlessly.

This is the simplest approach if a given protocol has only one proxy server and that proxy server doesn't change.

Proxy lists

This approach allows you to specify one or more proxy servers for a protocol. If more than one proxy server is defined, ClickHouse uses the different proxies on a round-robin basis, balancing the load across the servers. This is the simplest approach if there is more than one proxy server for a protocol and the list of proxy servers doesn't change.

Configuration template

Select a parent field in the tabs below to view their children:

<proxy>

Field	Description
<http>	A list of one or more HTTP proxies
<https>	A list of one or more HTTPS proxies

<http> and <https>

Field	Description
<uri>	The URI of the proxy

Remote proxy resolvers

It's possible that the proxy servers change dynamically. In that case, you can define the endpoint of a resolver. ClickHouse sends an empty GET request to that endpoint, the remote resolver should return the proxy host. ClickHouse will use it to form the proxy URI using the following template: \{proxy_scheme\}://\{proxy_host\}:{proxy_port}

Configuration template

Select a parent field in the tabs below to view their children:

<proxy>

Field	Description
<http>	A list of one or more resolvers*
<https>	A list of one or more resolvers*

<http> and <https>

Field	Description
<resolver>	The endpoint and other details for a resolver

note

You can have multiple <resolver> elements, but only the first <resolver> for a given protocol is used. Any other <resolver> elements for that protocol are ignored. That means load balancing (if needed) should be implemented by the remote resolver.

<resolver>

Field	Description
<endpoint>	The URI of the proxy resolver
<proxy_scheme>	The protocol of the final proxy URI. This can be either http or https.
<proxy_port>	The port number of the proxy resolver
<proxy_cache_time>	The time in seconds that values from the resolver should be cached by ClickHouse. Setting this value to 0 causes ClickHouse to contact the resolver for every HTTP or HTTPS request.

Precedence

Proxy settings are determined in the following order:

Order	Setting
1.	Remote proxy resolvers
2.	Proxy lists
3.	Environment variables

ClickHouse will check the highest priority resolver type for the request protocol. If it is not defined, it will check the next highest priority resolver type, until it reaches the environment resolver. This also allows a mix of resolver types can be used.

query_cache

Query cache configuration.

The following settings are available:

Setting	Description	Default Value
max_size_in_bytes	The maximum cache size in bytes. 0 means the query cache is disabled.	1073741824
max_entries	The maximum number of SELECT query results stored in the cache.	1024
max_entry_size_in_bytes	The maximum size in bytes SELECT query results may have to be saved in the cache.	1048576
max_entry_size_in_rows	The maximum number of rows SELECT query results may have to be saved in the cache.	30000000

note

• Changed settings take effect immediately.
• Data for the query cache is allocated in DRAM. If memory is scarce, make sure to set a small value for max_size_in_bytes or disable the query cache altogether.

Example

query_condition_cache_policy

Type: String

Default: SLRU

Query condition cache policy name.

query_condition_cache_size

Type: UInt64

Default: 104857600

Maximum size of the query condition cache.

note

This setting can be modified at runtime and will take effect immediately.

query_condition_cache_size_ratio

Type: Double

Default: 0.5

The size of the protected queue (in case of SLRU policy) in the query condition cache relative to the cache's total size.

query_log

Setting for logging queries received with the log_queries=1 setting.

Queries are logged in the system.query_log table, not in a separate file. You can change the name of the table in the table parameter (see below).

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

If the table does not exist, ClickHouse will create it. If the structure of the query log changed when the ClickHouse server was updated, the table with the old structure is renamed, and a new table is created automatically.

Example

query_masking_rules

Regexp-based rules, which will be applied to queries as well as all log messages before storing them in server logs, system.query_log, system.text_log, system.processes tables, and in logs sent to the client. That allows preventing sensitive data leakage from SQL queries such as names, emails, personal identifiers or credit card numbers to logs.

Example

Config fields:

Setting	Description
name	name for the rule (optional)
regexp	RE2 compatible regular expression (mandatory)
replace	substitution string for sensitive data (optional, by default - six asterisks)

The masking rules are applied to the whole query (to prevent leaks of sensitive data from malformed / non-parseable queries).

The system.events table has counter QueryMaskingRulesMatch which has an overall number of query masking rules matches.

For distributed queries each server has to be configured separately, otherwise, subqueries passed to other nodes will be stored without masking.

query_metric_log

It is disabled by default.

Enabling

To manually turn on metrics history collection system.query_metric_log, create /etc/clickhouse-server/config.d/query_metric_log.xml with the following content:

Disabling

To disable query_metric_log setting, you should create the following file /etc/clickhouse-server/config.d/disable_query_metric_log.xml with the following content:

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

query_thread_log

Setting for logging threads of queries received with the log_query_threads=1 setting.

Queries are logged in the system.query_thread_log table, not in a separate file. You can change the name of the table in the table parameter (see below).

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

If the table does not exist, ClickHouse will create it. If the structure of the query thread log changed when the ClickHouse server was updated, the table with the old structure is renamed, and a new table is created automatically.

Example

query_views_log

Setting for logging views (live, materialized etc) dependant of queries received with the log_query_views=1 setting.

Queries are logged in the system.query_views_log table, not in a separate file. You can change the name of the table in the table parameter (see below).

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

If the table does not exist, ClickHouse will create it. If the structure of the query views log changed when the ClickHouse server was updated, the table with the old structure is renamed, and a new table is created automatically.

Example

remap_executable

Setting to reallocate memory for machine code ("text") using huge pages.

note

This feature is highly experimental.

Example:

remote_servers

Configuration of clusters used by the Distributed table engine and by the cluster table function.

Example

For the value of the incl attribute, see the section "Configuration files".

See Also

• skip_unavailable_shards
• Cluster Discovery
• Replicated database engine

remote_url_allow_hosts

List of hosts which are allowed to be used in URL-related storage engines and table functions.

When adding a host with the \<host\> xml tag:

• it should be specified exactly as in the URL, as the name is checked before DNS resolution. For example: <host>clickhouse.com</host>
• if the port is explicitly specified in the URL, then host:port is checked as a whole. For example: <host>clickhouse.com:80</host>
• if the host is specified without a port, then any port of the host is allowed. For example: if <host>clickhouse.com</host> is specified then clickhouse.com:20 (FTP), clickhouse.com:80 (HTTP), clickhouse.com:443 (HTTPS) etc are allowed.
• if the host is specified as an IP address, then it is checked as specified in the URL. For example: [2a02:6b8:a::a].
• if there are redirects and support for redirects is enabled, then every redirect (the location field) is checked.

For example:

replica_group_name

Replica group name for database Replicated.

The cluster created by Replicated database will consist of replicas in the same group. DDL queries will only wait for the replicas in the same group.

Empty by default.

Example

replicated_fetches_http_connection_timeout

Type: Seconds

Default: 0

HTTP connection timeout for part fetch requests. Inherited from default profile http_connection_timeout if not set explicitly.

replicated_fetches_http_receive_timeout

Type: Seconds

Default: 0

HTTP receive timeout for fetch part requests. Inherited from default profile http_receive_timeout if not set explicitly.

replicated_fetches_http_send_timeout

Type: Seconds

Default: 0

HTTP send timeout for part fetch requests. Inherited from default profile http_send_timeout if not set explicitly.

replicated_merge_tree

Fine-tuning for tables in the ReplicatedMergeTree. This setting has a higher priority.

For more information, see the MergeTreeSettings.h header file.

Example

restore_threads

Type: UInt64

Default: 16

The maximum number of threads to execute RESTORE requests.

s3queue_log

Settings for the s3queue_log system table.

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

The default settings are:

send_crash_reports

Settings for opt-in sending of crash reports to the ClickHouse core developers team via Sentry.

Enabling it, especially in pre-production environments, is highly appreciated.

The server will need access to the public internet via IPv4 (at the time of writing IPv6 is not supported by Sentry) for this feature to function properly.

Keys:

Key	Description
enabled	Boolean flag to enable the feature, false by default. Set to true to allow sending crash reports.
send_logical_errors	LOGICAL_ERROR is like an assert, it is a bug in ClickHouse. This boolean flag enables sending this exceptions to sentry (Default: false).
endpoint	You can override the Sentry endpoint URL for sending crash reports. It can be either a separate Sentry account or your self-hosted Sentry instance. Use the Sentry DSN syntax.
anonymize	Avoid attaching the server hostname to the crash report.
http_proxy	Configure HTTP proxy for sending crash reports.
debug	Sets the Sentry client into debug mode.
tmp_path	Filesystem path for temporary crash report state.
environment	An arbitrary name of an environment in which the ClickHouse server is running. It will be mentioned in each crash report. The default value is test or prod depending on the version of ClickHouse.

Recommended usage

series_keeper_path

Type: String

Default: /clickhouse/series

Path in Keeper with auto-incremental numbers, generated by the generateSerialID function. Each series will be a node under this path.

show_addresses_in_stack_traces

Type: Bool

Default: 1

If it is set true will show addresses in stack traces

shutdown_wait_backups_and_restores

Type: Bool

Default: 1

If set to true ClickHouse will wait for running backups and restores to finish before shutdown.

shutdown_wait_unfinished

Type: UInt64

Default: 5

Delay in seconds to wait for unfinished queries

shutdown_wait_unfinished_queries

Type: Bool

Default: 0

If set true ClickHouse will wait for running queries finish before shutdown.

ssh_server

The public part of the host key will be written to the known_hosts file on the SSH client side on the first connect.

Host Key Configurations are inactive by default. Uncomment the host key configurations, and provide the path to the respective ssh key to active them:

Example:

storage_configuration

Allows for multi-disk configuration of storage.

Storage configuration follows the structure:

Configuration of disks

Configuration of disks follows the structure given below:

The sub-tags above define the following settings for disks:

Setting	Description
<disk_name_N>	The name of the disk, which should be unique.
path	The path to which server data will be stored (data and shadow catalogues). It should end with /
keep_free_space_bytes	Size of the reserved free space on disk.

note

The order of the disks does not matter.

Configuration of policies

The sub-tags above define the following settings for policies:

Setting	Description
policy_name_N	Name of the policy. Policy names must be unique.
volume_name_N	The volume name. Volume names must be unique.
disk	The disk located inside the volume.
max_data_part_size_bytes	The maximum size of a chunk of data that can reside on any of the disks in this volume. If the merge results in a chunk size expected to be larger than max_data_part_size_bytes, the chunk will be written to the next volume. Basically this feature allows you to store new / small chunks on a hot (SSD) volume and move them to a cold (HDD) volume when they reach a large size. Do not use this option if the policy has only one volume.
move_factor	The share of available free space on the volume. If the space becomes less, the data will start transferring to the next volume, if there is one. For transfer, chunks are sorted by size from larger to smaller (descending) and chunks whose total size is sufficient to meet the move_factor condition are selected, if the total size of all chunks is insufficient, all chunks will be moved.
perform_ttl_move_on_insert	Disables moving data with expired TTL on insertion. By default (if enabled), if we insert a piece of data that has already expired according to the move on life rule, it is immediately moved to the volume / disk specified in the move rule. This can significantly slow down insertion in case the target volume / disk is slow (e.g. S3). If disabled, the expired portion of the data is written to the default volume and then immediately moved to the volume specified in the rule for the expired TTL.
load_balancing	Disk balancing policy, round_robin or least_used.
least_used_ttl_ms	Sets the timeout (in milliseconds) to update the available space on all disks (0 - always update, -1 - never update, default value is 60000). Note, if the disk is only used by ClickHouse and will not be subject to file system resizing on the fly, you can use the -1 value. In all other cases this is not recommended, as it will eventually lead to incorrect space allocation.
prefer_not_to_merge	Disables merging parts of the data on this volume. Note: this is potentially harmful and can cause slowdown. When this setting is enabled (don't do this), merging data on this volume is prohibited (which is bad). This allows control of how ClickHouse interacts with slow disks. We recommend not to use this at all.
volume_priority	Defines the priority (order) in which volumes are filled. The smaller the value, the higher the priority. The parameter values must be natural numbers and cover the range from 1 to N (N is the largest parameter value specified) with no gaps.

For the volume_priority:

• If all volumes have this parameter, they are prioritized in the specified order.
• If only some volumes have it, volumes that do not have it have the lowest priority. Those that do have it are prioritized according to the tag value, the priority of the rest is determined by the order of description in the configuration file relative to each other.
• If no volumes are given this parameter, their order is determined by the order of the description in the configuration file.
• The priority of volumes may not be identical.

storage_connections_soft_limit

Type: UInt64

Default: 100

Connections above this limit have significantly shorter time to live. The limit applies to the storages connections.

storage_connections_store_limit

Type: UInt64

Default: 5000

Connections above this limit reset after use. Set to 0 to turn connection cache off. The limit applies to the storages connections.

storage_connections_warn_limit

Type: UInt64

Default: 1000

Warning massages are written to the logs if number of in-use connections are higher than this limit. The limit applies to the storages connections.

storage_metadata_write_full_object_key

Type: Bool

Default: 0

Write disk metadata files with VERSION_FULL_OBJECT_KEY format

storage_shared_set_join_use_inner_uuid

Type: Bool

Default: 1

If enabled, an inner UUID is generated during the creation of SharedSet and SharedJoin. ClickHouse Cloud only

table_engines_require_grant

If set to true, users require a grant to create a table with a specific engine e.g. GRANT TABLE ENGINE ON TinyLog to user.

note

By default, for backward compatibility creating table with a specific table engine ignores grant, however you can change this behaviour by setting this to true.

tables_loader_background_pool_size

Type: UInt64

Default: 0

Sets the number of threads performing asynchronous load jobs in background pool. The background pool is used for loading tables asynchronously after server start in case there are no queries waiting for the table. It could be beneficial to keep low number of threads in background pool if there are a lot of tables. It will reserve CPU resources for concurrent query execution.

note

A value of 0 means all available CPUs will be used.

tables_loader_foreground_pool_size

Type: UInt64

Default: 0

Sets the number of threads performing load jobs in foreground pool. The foreground pool is used for loading table synchronously before server start listening on a port and for loading tables that are waited for. Foreground pool has higher priority than background pool. It means that no job starts in background pool while there are jobs running in foreground pool.

note

A value of 0 means all available CPUs will be used.

tcp_port

Port for communicating with clients over the TCP protocol.

Example

tcp_port_secure

TCP port for secure communication with clients. Use it with OpenSSL settings.

Default value

tcp_ssh_port

Port for the SSH server which allows the user to connect and execute queries in an interactive fashion using the embedded client over the PTY.

Example:

temporary_data_in_cache

Type: String

With this option, temporary data will be stored in the cache for the particular disk. In this section, you should specify the disk name with the type cache. In that case, the cache and temporary data will share the same space, and the disk cache can be evicted to create temporary data.

note

Only one option can be used to configure temporary data storage: tmp_path ,tmp_policy, temporary_data_in_cache.

Example

Both the cache for local_disk, and temporary data will be stored in /tiny_local_cache on the filesystem, managed by tiny_local_cache.

text_log

Settings for the text_log system table for logging text messages.

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

Additionally:

Setting	Description	Default Value
level	Maximum Message Level (by default Trace) which will be stored in a table.	Trace

Example

thread_pool_queue_size

Type: UInt64

Default: 10000

The maximum number of jobs that can be scheduled on the Global Thread pool. Increasing queue size leads to larger memory usage. It is recommended to keep this value equal to max_thread_pool_size.

note

A value of 0 means unlimited.

Example

threadpool_writer_pool_size

Type: UInt64

Default: 100

Size of background pool for write requests to object storages

threadpool_writer_queue_size

Type: UInt64

Default: 1000000

Number of tasks which is possible to push into background pool for write requests to object storages

throw_on_unknown_workload

Type: Bool

Default: 0

Defines behaviour on access to unknown WORKLOAD with query setting 'workload'.

• If true, RESOURCE_ACCESS_DENIED exception is thrown from a query that is trying to access unknown workload. Useful to enforce resource scheduling for all queries after WORKLOAD hierarchy is established and contains WORKLOAD default.
• If false (default), unlimited access w/o resource scheduling is provided to a query with 'workload' setting pointing to unknown WORKLOAD. This is important during setting up hierarchy of WORKLOAD, before WORKLOAD default is added.

Example

See Also

• Workload Scheduling

timezone

The server's time zone.

Specified as an IANA identifier for the UTC timezone or geographic location (for example, Africa/Abidjan).

The time zone is necessary for conversions between String and DateTime formats when DateTime fields are output to text format (printed on the screen or in a file), and when getting DateTime from a string. Besides, the time zone is used in functions that work with the time and date if they didn't receive the time zone in the input parameters.

Example

See also

• session_timezone

tmp_path

Path on the local filesystem to store temporary data for processing large queries.

note

• Only one option can be used to configure temporary data storage: tmp_path ,tmp_policy, temporary_data_in_cache.
• The trailing slash is mandatory.

Example

tmp_policy

Type: String

Policy for storage with temporary data. For more information see the MergeTree Table Engine documentation.

note

• Only one option can be used to configure temporary data storage: tmp_path ,tmp_policy, temporary_data_in_cache.
• move_factor, keep_free_space_bytes,max_data_part_size_bytes and are ignored.
• Policy should have exactly one volume with local disks.

Example

When /disk1 is full, temporary data will be stored on /disk2.

top_level_domains_list

Defines a list of custom top level domains to add where each entry is, of the format <name>/path/to/file</name>.

For example:

See also:

• function cutToFirstSignificantSubdomainCustom and variations thereof, which accepts a custom TLD list name, returning the part of the domain that includes top-level subdomains up to the first significant subdomain.

total_memory_profiler_sample_max_allocation_size

Type: UInt64

Default: 0

Collect random allocations of size less or equal than specified value with probability equal to total_memory_profiler_sample_probability. 0 means disabled. You may want to set 'max_untracked_memory' to 0 to make this threshold to work as expected.

total_memory_profiler_sample_min_allocation_size

Type: UInt64

Default: 0

Collect random allocations of size greater or equal than specified value with probability equal to total_memory_profiler_sample_probability. 0 means disabled. You may want to set 'max_untracked_memory' to 0 to make this threshold to work as expected.

total_memory_profiler_step

Type: UInt64

Default: 0

Whenever server memory usage becomes larger than every next step in number of bytes the memory profiler will collect the allocating stack trace. Zero means disabled memory profiler. Values lower than a few megabytes will slow down server.

total_memory_tracker_sample_probability

Type: Double

Default: 0

Allows to collect random allocations and de-allocations and writes them in the system.trace_log system table with trace_type equal to a MemorySample with the specified probability. The probability is for every allocation or deallocations, regardless of the size of the allocation. Note that sampling happens only when the amount of untracked memory exceeds the untracked memory limit (default value is 4 MiB). It can be lowered if total_memory_profiler_step is lowered. You can set total_memory_profiler_step equal to 1 for extra fine-grained sampling.

Possible values:

• Positive integer.
• 0 — Writing of random allocations and de-allocations in the system.trace_log system table is disabled.

trace_log

Settings for the trace_log system table operation.

The following settings can be configured by sub-tags:

Setting	Description	Default	Note
database	Name of the database.
table	Name of the system table.
engine	MergeTree Engine Definition for a system table.		Cannot be used if partition_by or order_by defined. If not specified MergeTree is selected by default
partition_by	Custom partitioning key for a system table.		If engine is specified for system table, partition_by parameter should be specified directly inside 'engine'
ttl	Specifies the table TTL.		If engine is specified for system table, ttl parameter should be specified directly inside 'engine'
order_by	Custom sorting key for a system table. Can't be used if engine defined.		If engine is specified for system table, order_by parameter should be specified directly inside 'engine'
storage_policy	Name of the storage policy to use for the table (optional).		If engine is specified for system table, storage_policy parameter should be specified directly inside 'engine'
settings	Additional parameters that control the behavior of the MergeTree (optional).		If engine is specified for system table, settings parameter should be specified directly inside 'engine'
flush_interval_milliseconds	Interval for flushing data from the buffer in memory to the table.	7500
max_size_rows	Maximal size in lines for the logs. When the amount of non-flushed logs reaches the max_size, logs are dumped to the disk.	1048576
reserved_size_rows	Pre-allocated memory size in lines for the logs.	8192
buffer_size_rows_flush_threshold	Threshold for amount of lines. If the threshold is reached, flushing logs to the disk is launched in background.	max_size_rows / 2
flush_on_crash	Sets whether logs should be dumped to the disk in case of a crash.	false

The default server configuration file config.xml contains the following settings section:

uncompressed_cache_policy

Type: String

Default: SLRU

Uncompressed cache policy name.

uncompressed_cache_size

Type: UInt64

Default: 0

Maximum size (in bytes) for uncompressed data used by table engines from the MergeTree family.

There is one shared cache for the server. Memory is allocated on demand. The cache is used if the option use_uncompressed_cache is enabled.

The uncompressed cache is advantageous for very short queries in individual cases.

note

A value of 0 means disabled.

This setting can be modified at runtime and will take effect immediately.

uncompressed_cache_size_ratio

Type: Double

Default: 0.5

The size of the protected queue (in case of SLRU policy) in the uncompressed cache relative to the cache's total size.

url_scheme_mappers

Configuration for translating shortened or symbolic URL prefixes into full URLs.

Example:

use_minimalistic_part_header_in_zookeeper

Storage method for data part headers in ZooKeeper. This setting only applies to the MergeTree family. It can be specified:

Globally in the merge_tree section of the config.xml file

ClickHouse uses the setting for all the tables on the server. You can change the setting at any time. Existing tables change their behaviour when the setting changes.

For each table

When creating a table, specify the corresponding engine setting. The behaviour of an existing table with this setting does not change, even if the global setting changes.

Possible values

• 0 — Functionality is turned off.
• 1 — Functionality is turned on.

If use_minimalistic_part_header_in_zookeeper = 1, then replicated tables store the headers of the data parts compactly using a single znode. If the table contains many columns, this storage method significantly reduces the volume of the data stored in Zookeeper.

note

After applying use_minimalistic_part_header_in_zookeeper = 1, you can't downgrade the ClickHouse server to a version that does not support this setting. Be careful when upgrading ClickHouse on servers in a cluster. Don't upgrade all the servers at once. It is safer to test new versions of ClickHouse in a test environment, or on just a few servers of a cluster.

Data part headers already stored with this setting can't be restored to their previous (non-compact) representation.

user_defined_executable_functions_config

The path to the config file for executable user defined functions.

Path:

• Specify the absolute path or the path relative to the server config file.
• The path can contain wildcards * and ?.

See also:

• "Executable User Defined Functions.".

Example

user_defined_path

The directory with user defined files. Used for SQL user defined functions SQL User Defined Functions.

Example

user_directories

Section of the configuration file that contains settings:

• Path to configuration file with predefined users.
• Path to folder where users created by SQL commands are stored.
• ZooKeeper node path where users created by SQL commands are stored and replicated (experimental).

If this section is specified, the path from users_config and access_control_path won't be used.

The user_directories section can contain any number of items, the order of the items means their precedence (the higher the item the higher the precedence).

Examples

Users, roles, row policies, quotas, and profiles can be also stored in ZooKeeper:

You can also define sections memory — means storing information only in memory, without writing to disk, and ldap — means storing information on an LDAP server.

To add an LDAP server as a remote user directory of users that are not defined locally, define a single ldap section with the following settings:

Setting	Description
server	one of LDAP server names defined in ldap_servers config section. This parameter is mandatory and cannot be empty.
roles	section with a list of locally defined roles that will be assigned to each user retrieved from the LDAP server. If no roles are specified, user will not be able to perform any actions after authentication. If any of the listed roles is not defined locally at the time of authentication, the authentication attempt will fail as if the provided password was incorrect.

Example

user_files_path

The directory with user files. Used in the table function file(), fileCluster().

Example

user_scripts_path

The directory with user scripts files. Used for Executable user defined functions Executable User Defined Functions.

Example

Type:

Default:

users_config

Path to the file that contains:

• User configurations.
• Access rights.
• Settings profiles.
• Quota settings.

Example

validate_tcp_client_information

Type: Bool

Default: 0

Determines whether validation of client information is enabled when a query packet is received.

By default, it is false:

vector_similarity_index_cache_max_entries

Type: UInt64

Default: 10000000

Size of cache for vector similarity index in entries. Zero means disabled.

vector_similarity_index_cache_policy

Type: String

Default: SLRU

Vector similarity index cache policy name.

vector_similarity_index_cache_size

Type: UInt64

Default: 5368709120

Size of cache for vector similarity indexes. Zero means disabled.

note

This setting can be modified at runtime and will take effect immediately.

vector_similarity_index_cache_size_ratio

Type: Double

Default: 0.5

The size of the protected queue (in case of SLRU policy) in the vector similarity index cache relative to the cache's total size.

wait_dictionaries_load_at_startup

Type: Bool

Default: 1

This setting allows to specify behavior if dictionaries_lazy_load is false. (If dictionaries_lazy_load is true this setting doesn't affect anything.)

If wait_dictionaries_load_at_startup is false, then the server will start loading all the dictionaries at startup and it will receive connections in parallel with that loading. When a dictionary is used in a query for the first time then the query will wait until the dictionary is loaded if it's not loaded yet. Setting wait_dictionaries_load_at_startup to false can make ClickHouse start faster, however some queries can be executed slower (because they will have to wait for some dictionaries to be loaded).

If wait_dictionaries_load_at_startup is true, then the server will wait at startup until all the dictionaries finish their loading (successfully or not) before receiving any connections.

Example

workload_path

The directory used as a storage for all CREATE WORKLOAD and CREATE RESOURCE queries. By default /workload/ folder under server working directory is used.

Example

See Also

• Workload Hierarchy
• workload_zookeeper_path

workload_zookeeper_path

The path to a ZooKeeper node, which is used as a storage for all CREATE WORKLOAD and CREATE RESOURCE queries. For consistency all SQL definitions are stored as a value of this single znode. By default ZooKeeper is not used and definitions are stored on disk.

Example

See Also

• Workload Hierarchy
• workload_path

zookeeper

Contains settings that allow ClickHouse to interact with a ZooKeeper cluster. ClickHouse uses ZooKeeper for storing metadata of replicas when using replicated tables. If replicated tables are not used, this section of parameters can be omitted.

The following settings can be configured by sub-tags:

Setting	Description
node	ZooKeeper endpoint. You can set multiple endpoints. Eg. <node index="1"><host>example_host</host><port>2181</port></node>. The index attribute specifies the node order when trying to connect to the ZooKeeper cluster.
session_timeout_ms	Maximum timeout for the client session in milliseconds.
operation_timeout_ms	Maximum timeout for one operation in milliseconds.
root (optional)	The znode that is used as the root for znodes used by the ClickHouse server.
fallback_session_lifetime.min (optional)	Minimum limit for the lifetime of a zookeeper session to the fallback node when primary is unavailable (load-balancing). Set in seconds. Default: 3 hours.
fallback_session_lifetime.max (optional)	Maximum limit for the lifetime of a zookeeper session to the fallback node when primary is unavailable (load-balancing). Set in seconds. Default: 6 hours.
identity (optional)	User and password required by ZooKeeper to access requested znodes.
use_compression (optional)	Enables compression in Keeper protocol if set to true.

There is also the zookeeper_load_balancing setting (optional) which lets you select the algorithm for ZooKeeper node selection:

Algorithm Name	Description
random	randomly selects one of ZooKeeper nodes.
in_order	selects the first ZooKeeper node, if it's not available then the second, and so on.
nearest_hostname	selects a ZooKeeper node with a hostname that is most similar to the server's hostname, hostname is compared with name prefix.
hostname_levenshtein_distance	just like nearest_hostname, but it compares hostname in a levenshtein distance manner.
first_or_random	selects the first ZooKeeper node, if it's not available then randomly selects one of remaining ZooKeeper nodes.
round_robin	selects the first ZooKeeper node, if reconnection happens selects the next.

Example configuration

See Also

• Replication
• ZooKeeper Programmer's Guide
• Optional secured communication between ClickHouse and Zookeeper