Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://clickhouse.com/docs/operations/server-configuration-parameters/settings below:

Server Settings | ClickHouse Docs

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 incl and optional attributes).

Crash the server on LOGICAL_ERROR exceptions. Only for experts.

Settings for optional improvements in the access control system.

Example:

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

See also

• Access Control and Account Management

Action to execute when max array element size is exceeded in groupArray:

exception, or

extra values

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

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.

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

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

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

Allows to use jemalloc memory.

If true queue of asynchronous inserts is flushed on graceful shutdown

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

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

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

Period in seconds for updating heavy asynchronous metrics.

Settings for the asynchronous_insert_log system table for logging async inserts.

The following settings can be configured by sub-tags:

Example

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:

Enable the calculation of heavy asynchronous metrics.

Period in seconds for updating asynchronous metrics.

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.

The maximum number of threads that will be used for performing flush operations for

in the background.

The maximum number of threads that will be used for performing a variety of operations (mostly garbage collection) for

tables in the background.

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

The maximum number of threads that will be used for fetching data parts from another replica for

tables in the background.

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.

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.

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

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.

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

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.

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

The following settings can be configured by sub-tags:

Example

The maximum number of threads to execute

requests.

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

The following settings can be configured by sub-tags:

This setting is configured by default as:

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.

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

Settings for the blob_storage_log system table.

The following settings can be configured by sub-tags:

Example:

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

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

For testing purposes.

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

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

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.

Sets the cache size (in elements) for

.

Sets the cache size (in bytes) for

.

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

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.

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.

Same as

, but with ratio to cores.

How often clickhouse will reload config and check for new changes

Configures soft limit for core dump file size.

Note

Hard limit is configured via system tools

Example

Defines how workload scheduling for CPU resources (MASTER THREAD and WORKER THREAD) is done.

• If true (recommended), accounting is done based on actual CPU time consumed. A fair number of CPU time would be allocated to competing workloads. Slots are allocated for a limited amount of time and re-requested after expiry. Slot requesting may block thread execution in case of CPU resource overload, i.e., preemption may happen. This ensures CPU-time fairness.
• If false (default), accounting is based on the number of CPU slots allocated. A fair number of CPU slots would be allocated to competing workloads. A slot is allocated when a thread starts, held continuously, and released when the thread ends execution. The number of threads allocated for query execution may only increase from 1 to max_threads and never decrease. This is more favorable to long-running queries and may lead to CPU starvation of short queries.

Example

See Also

• Workload Scheduling

It defines how many milliseconds could a worker thread wait during preemption, i.e. while waiting for another CPU slot to be granted. After this timeout, if thread was unable to acquire a new CPU slot it will exit and the query is scaled down to a lower number of concurrently executing threads dynamically. Note that master thread never downscaled, but could be preempted indefinitely. Makes sense only when cpu_slot_preemption is enabled and CPU resource is defined for WORKER THREAD.

Example

See Also

• Workload Scheduling

It defines how many CPU nanoseconds a thread is allowed to consume after acquired a CPU slot and before it should request another CPU slot. Makes sense only if cpu_slot_preemption is enabled and CPU resource is defined for MASTER THREAD or WORKER THREAD.

Example

See Also

• Workload Scheduling

Settings for the crash_log system table operation.

The following settings can be configured by sub-tags:

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

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:

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

Example

See Also

• Custom settings

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).

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

The size of the threadpool used for dropping tables.

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.

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".

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.

Allow detaching tables permanently in Replicated databases

Setting for the 'dead_letter_queue' system table.

The following settings can be configured by sub-tags:

The default settings are:

The default database name.

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 settings profile. Settings profiles are located in the file specified in the setting user_config.

Example

The replica name in ZooKeeper.

Example

The path to the table in ZooKeeper.

Example

Default session timeout, in seconds.

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

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

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

enabled.

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.

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

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.

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

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

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.

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.

Whether cache server should apply throttling settings received from client.

Soft limit for number of active connection distributed cache will try to keep free. After the number of free connections goes below distributed_cache_keep_up_free_connections_ratio * max_connections, connections with oldest activity will be closed until the number goes above the limit.

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

The configurable settings within <distributed_ddl> include:

Example

Allows resolve names to ipv4 addresses.

Allows resolve names to ipv6 addresses.

Internal DNS cache max entries.

Internal DNS cache update period in seconds.

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

The size of the threadpool used for dropping distributed cache.

The queue size of the threadpool used for dropping distributed cache.

Enables logging from Azure sdk

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).

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:

The maximum number of jobs that can be scheduled on thread pool for parsing input.

Note

A value of 0 means unlimited.

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

Example

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.

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.

Defines a directory containing proto files for Protobuf types.

Example:

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

Settings for thinning data for Graphite.

For more details, see GraphiteMergeTree.

Example

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

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.

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.

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.

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:

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

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

Example:

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:

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.

Size of background pool for iceberg catalog

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

Maximum size of iceberg metadata files cache in entries. Zero means disabled.

Iceberg metadata cache policy name.

Maximum size of iceberg metadata cache in bytes. Zero means disabled.

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

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

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

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

Example

Secondary index mark cache policy name.

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.

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

Secondary index uncompressed cache policy name.

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.

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

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.

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

Port for exchanging data between ClickHouse servers.

Example

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

Example

Port for exchanging data between ClickHouse servers over HTTPS.

Example

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:

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

Note

A value of 0 means unlimited.

Flushing jemalloc profile will be done after global peak memory usage increased by jemalloc_flush_profile_interval_bytes

Flushing jemalloc profile will be done on total memory exceeded errors

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

Example

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.

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 user_dn_detection can be configured with sub-tags:

Example:

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

License key for ClickHouse Enterprise Edition

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

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

Examples:

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:

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

Example

Size of background pool for marks loading

Number of tasks which is possible to push into prefetches pool

The location and format of log messages.

Keys:

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.

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>:

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.

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 name.

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

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.

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

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

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.

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

If the number of

threads in the Backups IO Thread pool exceeds

, ClickHouse will release resources occupied by idling threads and decrease the pool size. Threads can be created again if necessary.

ClickHouse uses threads from the Backups IO Thread pool to do S3 backup IO operations.

limits the maximum number of threads in the pool.

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

Note

A value of 0 means all cores.

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.

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.

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 server connections.

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

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

Example

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

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

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

Example

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

The number of threads for ALTER TABLE FETCH PARTITION.

Maximum number of idle standby threads to keep in the thread pool for parsing input.

Maximum total number of threads to use for parsing input.

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.

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.

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

Example

The maximum speed of local reads in bytes per second.

Note

A value of 0 means unlimited.

The maximum speed of local writes in bytes per seconds.

Note

A value of 0 means unlimited.

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.

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

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

The maximum number of open files.

Note

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

Example

Max ratio between OS CPU wait (OSCPUWaitMicroseconds metric) and busy (OSCPUVirtualTimeMicroseconds metric) times to consider dropping connections. Linear interpolation between min and max ratio is used to calculate the probability, the probability is 1 at this point. See Controlling behavior on server CPU overload for more details.

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

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

Example

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

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

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

Example

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

Example

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.

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.

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

Note

A value of 0 (default) means unlimited.

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

Note

A value of 0 (default) means unlimited.

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

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

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

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).

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.

Maximum session timeout, in seconds.

Example:

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

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

Example

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

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

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

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

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

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

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

Example

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

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.

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

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

Use current cgroup memory usage information to correct memory tracking.

Fine-tuning for tables in the MergeTree.

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

Example

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

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

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

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:

Min ratio between OS CPU wait (OSCPUWaitMicroseconds metric) and busy (OSCPUVirtualTimeMicroseconds metric) times to consider dropping connections. Linear interpolation between min and max ratio is used to calculate the probability, the probability is 0 at this point. See Controlling behavior on server CPU overload for more details.

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

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:

• MMappedFiles/MMappedFileBytes/MMapCacheCells in system.metrics, system.metric_log
• CreatedReadBufferMMap/CreatedReadBufferMMapFailed/MMappedFileCacheHits/MMappedFileCacheMisses in system.events, system.processes, system.query_log, system.query_thread_log, system.query_views_log

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.

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

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

If set to true, secure communication is required with clients over mysql_port. Connection with option --ssl-mode=none will be refused. Use it with OpenSSL settings.

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:

Example of settings:

Settings for the opentelemetry_span_log system table.

The following settings can be configured by sub-tags:

Example:

Threshold of OS CPU busy time in microseconds (OSCPUVirtualTimeMicroseconds metric) to consider CPU doing some useful work, no CPU overload would be considered if busy time was below this value.

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

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

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]).

Minimum size of the userspace page cache.

Userspace page cache policy name.

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

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

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:

Example

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

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

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

The path to the directory containing data.

Note

The trailing slash is mandatory.

Example

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 PostgreSQL protocol.

Example

If set to true, secure communication is required with clients over postgresql_port. Connection with option sslmode=disable will be refused. Use it with OpenSSL settings.

Size of background pool for prefetches for remote object storages

Number of tasks which is possible to push into prefetches pool

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

Note

A value of 0 means unlimited.

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 name.

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

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

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

This setting allows reading QueryPlan packet. This packet is sent for distributed queries when serialize_query_plan is enabled. Disabled by default to avoid possible security issues which can be caused by bugs in query plan binary deserialization.

Example

Settings for the processors_profile_log system table.

The following settings can be configured by sub-tags:

The default settings are:

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):

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:

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:

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.

Precedence

Proxy settings are determined in the following order:

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 configuration.

The following settings are available:

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 name.

Maximum size of the query condition cache.

Note

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

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

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:

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

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:

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.

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 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:

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

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:

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

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

Note

This feature is highly experimental.

Example:

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

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 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

HTTP connection timeout for part fetch requests. Inherited from default profile

if not set explicitly.

HTTP receive timeout for fetch part requests. Inherited from default profile

if not set explicitly.

HTTP send timeout for part fetch requests. Inherited from default profile

if not set explicitly.

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

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

Example

The maximum number of threads to execute RESTORE requests.

Disable streaming in S3Queue even if the table is created and there are attached materiaized views

Settings for the s3queue_log system table.

The following settings can be configured by sub-tags:

The default settings are:

Settings for sending of crash reports to the ClickHouse core developers team.

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

Keys:

Recommended usage

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

If it is set true will show addresses in stack traces

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

Delay in seconds to wait for unfinished queries

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

Skips ClickHouse binary checksum integrity checks

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:

Debug parameter to simulate materizlied view creation delay

Allows for multi-disk configuration of storage.

Storage configuration follows the structure:

Configuration of disks follows the structure given below:

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

Note

The order of the disks does not matter.

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

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.

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

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

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.

Write disk metadata files with VERSION_FULL_OBJECT_KEY format

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

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.

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.

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.

Maximum number of queries allowed per TCP connection before the connection is closed. Set to 0 for unlimited queries.

Maximum lifetime of a TCP connection in seconds before it is closed. Set to 0 for unlimited connection lifetime.

Port for communicating with clients over the TCP protocol.

Example

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

Default value

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:

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.

Settings for the text_log system table for logging text messages.

The following settings can be configured by sub-tags:

Additionally:

Example

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

The number of threads in the thread pool for reading from local filesystem when

.

The maximum number of jobs that can be scheduled on the thread pool for reading from local filesystem.

Number of threads in the Thread pool used for reading from remote filesystem when

.

The maximum number of jobs that can be scheduled on the thread pool for reading from remote filesystem.

Size of background pool for write requests to object storages

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

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

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

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

Policy for storage with temporary data. All files with tmp prefix will be removed at start.

Note

Recommendations for using object storage as tmp_policy:

• Use separate bucket:path on each server
• Use metadata_type=plain
• You may also want to set TTL for this bucket

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

For more information see the MergeTree Table Engine documentation.

Example

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

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.

Collect random allocations of size less or equal than specified value with probability equal to

. 0 means disabled. You may want to set 'max_untracked_memory' to 0 to make this threshold to work as expected.

Collect random allocations of size greater or equal than specified value with probability equal to

. 0 means disabled. You may want to set 'max_untracked_memory' to 0 to make this threshold to work as expected.

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.

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 double.
• 0 — Writing of random allocations and de-allocations in the system.trace_log system table is disabled.

Settings for the trace_log system table operation.

The following settings can be configured by sub-tags:

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

Uncompressed cache policy name.

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.

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

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

Example:

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.

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

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

Example

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:

Example

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

Example

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

Example

Type:

Default:

Path to the file that contains:

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

Example

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

By default, it is false:

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

Vector similarity index cache policy name.

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

Note

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

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

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

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

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

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:

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

Example configuration

See Also

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

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4