Global Server Settings
This section contains descriptions of server settings that cannot be changed at the session or query level. These settings are stored in the config.xml
file on the ClickHouse server. 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 to read the Configuration files section and note the use of substitutions (the incl
and optional
attributes).
allow_use_jemalloc_memory
Allows to use jemalloc memory.
Type: Bool
Default: 1
asynchronous_heavy_metrics_update_period_s
Period in seconds for updating asynchronous metrics.
Type: UInt32
Default: 120
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:
<clickhouse>
<asynchronous_metric_log>
<database>system</database>
<table>asynchronous_metric_log</table>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
<collect_interval_milliseconds>1000</collect_interval_milliseconds>
<max_size_rows>1048576</max_size_rows>
<reserved_size_rows>8192</reserved_size_rows>
<buffer_size_rows_flush_threshold>524288</buffer_size_rows_flush_threshold>
<flush_on_crash>false</flush_on_crash>
</asynchronous_metric_log>
</clickhouse>
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:
<clickhouse>
<asynchronous_metric_log remove="1" />
</clickhouse>
asynchronous_metrics_update_period_s
Period in seconds for updating asynchronous metrics.
Type: UInt32
Default: 1
auth_use_forwarded_address
Use originating address for authentication for clients connected through proxy.
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.
Type: Bool
Default: 0
background_buffer_flush_schedule_pool_size
The maximum number of threads that will be used for performing flush operations for Buffer-engine tables in the background.
Type: UInt64
Default: 16
background_common_pool_size
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.
Type: UInt64
Default: 8
background_distributed_schedule_pool_size
The maximum number of threads that will be used for executing distributed sends.
Type: UInt64
Default: 16
background_fetches_pool_size
The maximum number of threads that will be used for fetching data parts from another replica for *MergeTree-engine tables in the background.
Type: UInt64
Default: 16
background_merges_mutations_concurrency_ratio
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.
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.
Type: Float
Default: 2
background_merges_mutations_scheduling_policy
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 byINSERT
s.
Type: String
Default: round_robin
background_message_broker_schedule_pool_size
The maximum number of threads that will be used for executing background operations for message streaming.
Type: UInt64
Default: 16
background_move_pool_size
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.
Type: UInt64
Default: 8
background_schedule_pool_size
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.
Type: UInt64
Default: 512
backup_threads
The maximum number of threads to execute BACKUP
requests.
Type: UInt64
Default: 16
backups_io_thread_pool_queue_size
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.
A value of 0
(default) means unlimited.
Type: UInt64
Default: 0
cache_size_to_ram_max_ratio
Set cache size to RAM max ratio. Allows lowering the cache size on low-memory systems.
Type: Double
Default: 0.5
concurrent_threads_soft_limit_num
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.
A value of 0
(default) means unlimited.
Type: UInt64
Default: 0
concurrent_threads_soft_limit_ratio_to_cores
Same as concurrent_threads_soft_limit_num
, but with ratio to cores.
Type: UInt64
Default: 0
default_database
The default database name.
Type: String
Default: default
disable_internal_dns_cache
Disables the internal DNS cache. Recommended for operating ClickHouse in systems with frequently changing infrastructure such as Kubernetes.
Type: Bool
Default: 0
dns_cache_max_entries
Internal DNS cache max entries.
Type: UInt64
Default: 10000
dns_cache_update_period
Internal DNS cache update period in seconds.
Type: Int32
Default: 15
dns_max_consecutive_failures
Max consecutive resolving failures before dropping a host from ClickHouse DNS cache
Type: UInt32
Default: 10
index_mark_cache_policy
Index mark cache policy name.
Type: String
Default: SLRU
index_mark_cache_size
Maximum size of cache for index marks.
A value of 0
means disabled.
This setting can be modified at runtime and will take effect immediately.
Type: UInt64
Default: 0
index_mark_cache_size_ratio
The size of the protected queue (in case of SLRU policy) in the index mark cache relative to the cache's total size.
Type: Double
Default: 0.5
index_uncompressed_cache_policy
Index uncompressed cache policy name.
Type: String
Default: SLRU
index_uncompressed_cache_size
Maximum size of cache for uncompressed blocks of MergeTree
indices.
A value of 0
means disabled.
This setting can be modified at runtime and will take effect immediately.
Type: UInt64
Default: 0
index_uncompressed_cache_size_ratio
The size of the protected queue (in case of SLRU policy) in the index uncompressed cache relative to the cache's total size.
Type: Double
Default: 0.5
skipping_index_cache_policy
Skipping index cache policy name.
Type: String
Default: SLRU
skipping_index_cache_size
Size of cache for skipping indexes. Zero means disabled.
This setting can be modified at runtime and will take effect immediately.
Type: UInt64
Default: 5368709120 (= 5 GiB)
skipping_index_cache_size_ratio
The size of the protected queue (in case of SLRU policy) in the skipping index cache relative to the cache's total size.
Type: Double
Default: 0.5
skipping_index_cache_max_entries
The maximum number of entries in the skipping index cache.
Type: UInt64
Default: 10000000
io_thread_pool_queue_size
The maximum number of jobs that can be scheduled on the IO Thread pool.
A value of 0
means unlimited.
Type: UInt64
Default: 10000
mark_cache_policy
Mark cache policy name.
Type: String
Default: SLRU
mark_cache_size
Maximum size of cache for marks (index of MergeTree
family of tables).
This setting can be modified at runtime and will take effect immediately.
Type: UInt64
Default: 5368709120
mark_cache_size_ratio
The size of the protected queue (in case of SLRU policy) in the mark cache relative to the cache's total size.
Type: Double
Default: 0.5
max_backup_bandwidth_for_server
The maximum read speed in bytes per second for all backups on server. Zero means unlimited.
Type: UInt64
Default: 0
max_backups_io_thread_pool_free_size
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.
Type: UInt64
Default: 0
max_backups_io_thread_pool_size
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.
Type: UInt64
Default: 1000
max_concurrent_queries
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:
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.
Type: UInt64
Default: 0
max_concurrent_insert_queries
Limit on total number of concurrent insert queries.
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.
Type: UInt64
Default: 0
max_concurrent_select_queries
Limit on total number of concurrently select queries.
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.
Type: UInt64
Default: 0
max_waiting_queries
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
.
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.
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.
Type: UInt64
Default: 0
max_connections
Max server connections.
Type: Int32
Default: 1024
max_io_thread_pool_free_size
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.
Type: UInt64
Default: 0
max_io_thread_pool_size
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.
Type: UInt64
Default: 100
max_local_read_bandwidth_for_server
The maximum speed of local reads in bytes per second.
A value of 0
means unlimited.
Type: UInt64
Default: 0
max_local_write_bandwidth_for_server
The maximum speed of local writes in bytes per seconds.
A value of 0
means unlimited.
Type: UInt64
Default: 0
max_partition_size_to_drop
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.
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_partition_size_to_drop>0</max_partition_size_to_drop>
Type: UInt64
Default: 50
max_remote_read_network_bandwidth_for_server
The maximum speed of data exchange over the network in bytes per second for read.
A value of 0
(default) means unlimited.
Type: UInt64
Default: 0
max_remote_write_network_bandwidth_for_server
The maximum speed of data exchange over the network in bytes per second for write.
A value of 0
(default) means unlimited.
Type: UInt64
Default: 0
max_server_memory_usage
Limit on total memory usage.
The default max_server_memory_usage
value is calculated as memory_amount * max_server_memory_usage_to_ram_ratio
.
A value of 0
(default) means unlimited.
Type: UInt64
Default: 0
max_server_memory_usage_to_ram_ratio
Same as max_server_memory_usage
but in a ratio to physical RAM. Allows lowering the memory usage on low-memory systems.
On hosts with low RAM and swap, you possibly need setting max_server_memory_usage_to_ram_ratio
larger than 1.
A value of 0
means unlimited.
Type: Double
Default: 0.9
max_build_vector_similarity_index_thread_pool_size
The maximum number of threads to use for building vector indexes.
A value of 0
means all cores.
Type: UInt64
Default: 16
cgroups_memory_usage_observer_wait_time
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:
Type: UInt64
Default: 15
cgroup_memory_watcher_hard_limit_ratio
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:
Type: Double
Default: 0.95
cgroup_memory_watcher_soft_limit_ratio
Specifies the "soft" threshold of the memory consumption of the server process according to cgroups after which arenas in jemalloc are purged.
See settings:
Type: Double
Default: 0.9
max_database_num_to_warn
If the number of attached databases exceeds the specified value, clickhouse server will add warning messages to system.warnings
table.
Example
<max_database_num_to_warn>50</max_database_num_to_warn>
Type:
Default: 1000
max_table_num_to_warn
If the number of attached tables exceeds the specified value, clickhouse server will add warning messages to system.warnings
table.
Example
<max_table_num_to_warn>400</max_table_num_to_warn>
Default: 5000
max_view_num_to_warn
If the number of attached views exceeds the specified value, clickhouse server will add warning messages to system.warnings
table.
Example
<max_view_num_to_warn>400</max_view_num_to_warn>
Type: UInt64
Default: 10000
max_dictionary_num_to_warn
If the number of attached dictionaries exceeds the specified value, clickhouse server will add warning messages to system.warnings
table.
Example
<max_dictionary_num_to_warn>400</max_dictionary_num_to_warn>
Type: UInt64
Default: 1000
max_part_num_to_warn
If the number of active parts exceeds the specified value, clickhouse server will add warning messages to system.warnings
table.
Example
<max_part_num_to_warn>400</max_part_num_to_warn>
Type: UInt64
Default: 100000
max_table_num_to_throw
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
A value of 0
means no limitation.
Example
<max_table_num_to_throw>400</max_table_num_to_throw>
Type: UInt64
Default: 0
max_replicated_table_num_to_throw
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
A value of 0
means no limitation.
Example
<max_replicated_table_num_to_throw>400</max_replicated_table_num_to_throw>
Type: UInt64
Default: 0
max_dictionary_num_to_throw
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
A value of 0
means no limitation.
Example
<max_dictionary_num_to_throw>400</max_dictionary_num_to_throw>
Type: UInt64
Default: 0
max_view_num_to_throw
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
A value of 0
means no limitation.
Example
<max_view_num_to_throw>400</max_view_num_to_throw>
Type: UInt64
Default: 0
max_database_num_to_throw
If the number of databases is greater than this value, the server will throw an exception.
A value of 0
(default) means no limitation.
Example
<max_database_num_to_throw>400</max_database_num_to_throw>
Type: UInt64
Default: 0
max_temporary_data_on_disk_size
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.
A value of 0
means unlimited.
See also:
Type: UInt64
Default: 0
max_thread_pool_free_size
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_free_size>1200</max_thread_pool_free_size>
Type: UInt64
Default: 0
max_thread_pool_size
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_thread_pool_size>12000</max_thread_pool_size>
Type: UInt64
Default: 10000
mmap_cache_size
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 |
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.
Type: UInt64
Default: 1000
restore_threads
The maximum number of threads to execute RESTORE requests.
Type: UInt64
Default: 16
show_addresses_in_stack_traces
If it is set true will show addresses in stack traces
Type: Bool
Default: 1
shutdown_wait_unfinished_queries
If set true ClickHouse will wait for running queries finish before shutdown.
Type: Bool
Default: 0
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
.
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.
Type: Bool
Default: false