Configuring ClickHouse Parameters

This article describes how to modify the configuration of a ClickHouse Cluster using the Instaclustr Console. It assumes that you have already provisioned a ClickHouse cluster in accordance with the steps in the article on Creating a ClickHouse Cluster.

You can alternatively choose to update the cluster configuration via the Instaclustr Provisioning API or Terraform. Please note that using these methods you can only modify the values of a limited set of parameters. If you’re interested in modifying other parameters, please reach out to our support team.  

The available parameters are a mix of the global server settings and query-level settings.  

  • The query-level parameters are applied to the “Default” user profile, and will not apply to users under different profiles, if you’ve created any.  
  • Global server parameters instead will apply to all users. 

You can check the relevant documentation to determine the scope of a parameter. 

Overriding Parameters

You can “override” the available parameters with values of your choice to alter cluster behaviour to suit your requirements. Some parameters may be constrained by available compute resources, shard-replica configuration etc. and you may wish to review such details before overriding them.

  1. To get started, after signing into the console, click on the dropdown button with the name of the ClickHouse cluster you want to modify.
  2. To modify the configuration on the cluster, simply click on the Configuration button that appears in the drop down list. This will take you to a page which displays all configuration parameters available.
  3. Once on the Configuration page, change the default value of an available configuration parameter to your desired value. The Status will change to “Override Pending”, indicating the setting has not been applied yet.
  4. Once you’ve updated each parameter you’d like to update with the desired values then scroll to the bottom of the page and click “Apply Pending Changes”.
  5. Applying the changes updates the ClickHouse configuration to the cluster and can take up to a couple of minutes. During this time, you are unable to apply further pending changes to the cluster.
  6. The information box will confirm when the cluster has been updated. 
  7. All overridden parameters now show the “Overridden” status and their respective new values. 

Resetting Parameters to Default Values

  1. The “Reset To Default” option is enabled allowing the parameter to be reset to the default value. 
  2. Pressing “Reset To Default” sets the parameter status to “Reset Pending”, then the reset can be applied by selecting “Apply Pending Changes”.
  3. The “Clear Pending Change” button restores the current value of a parameter, either “Default” or “Overridden”. Similarly the “Clear Pending Changes” button at the bottom of the page restores the current value of all parameters. 
  4. The “Restore All” button will reset all parameters to the default value. This button requires confirmation via a pop up window and once “Proceed” is clicked the configuration will be loaded onto the cluster. 

Parameters Configurable Manually

The following section provides details on the parameters whose values you can modify. Each parameter has specific constraints on allowable values, so ensure that the values set for each parameter fall within the specified constraints.

Note that certain parameters have constraints that depend on cluster configuration (e.g., insert_quorum).

  • distributed_ddl_task_timeout
    Sets the timeout (in seconds) for DDL query responses from all hosts in a cluster
    Default Value: 180 (3 minutes)
    Allowed Range: -1 to 1800. A value of -1 indicates an infinite timeout, while a value of 0 indicates async mode.
  • http_connection_timeout
    Maximum time (in seconds) to wait to establish an HTTP connection to an external resource
    Default value: 30
    Allowed Range: 10 to 40
  • http_receive_timeout
    Maximum time (in seconds) to wait to receive data from the client over an HTTP connection
    Default value: 180 (3 minutes)
    Allowed Range: 30 to 400
  • http_send_timeout
    Maximum time (in seconds) to wait to send data to the client over an HTTP connection
    Default value: 180 (3 minutes)
    Allowed Range: 30 to 400
  • insert_quorum
    Determines the number of replicas that must successfully write data for an INSERT query to be considered successful
    Default Value: 0
    Allowed Range: For a single replica cluster, this parameter cannot be changed from 0. For a cluster with two replicas, it can be set to 0, 2, or auto. For a cluster with three replicas, it can be set to 0, 2, 3, or auto. The set value is interpreted as: 0 (disabled), 2 or higher (enabled), auto (majority of replicas).
  • insert_quorum_parallel
    Enables or disables parallelism for quorum INSERT queries
    Default Value: 1 (enabled)
    Allowed Range: 0 (disabled) or 1 (enabled)
  • insert_quorum_timeout
    Maximum wait time (in milliseconds) for a quorum write to complete before throwing an exception
    Default Value: 600000 (10 minutes)
    Allowed Range: 0 to 1,000,000,000,000
  • keep_alive_timeout
    Idle time (in seconds) before closing an HTTP keep-alive connection
    Default value: 30
    Allowed Range: 30 to 400
  • log_queries
    Enables or disables query logging
    Default Value: 1 (enabled)
    Allowed Range: 0 (disabled) or 1 (enabled)
  • max_bytes_before_external_group_by
    Maximum RAM for GROUP BY before using external memory
    Default Value: 0 (disabled)
    Allowed Range: 0 up to the memory size
  • max_bytes_before_external_sort
    Maximum RAM for ORDER BY before using external memory
    Default Value: 0 (disabled)
    Allowed Range: 0 up to the memory size
  • max_execution_time
    Maximum query execution time (in seconds)
    Default Value: 60
    Allowed Range: 0 (unlimited) to 1,000,000,000,000
  • max_insert_threads
    Maximum number of threads to execute the INSERT SELECT query. Setting to a value greater than 1 uses parallel execution. 
    Default Value: 0
    Allowed Range: 0 to 1,000,000,000,000
  • max_partition_size_to_drop
    Maximum size (in bytes) of partition that can be dropped
    Default Value: 50000000000
    Allowed Range: 0 (unrestricted) to 1,000,000,000,000
  • max_threads
    Maximum number of query processing threads, excluding threads for retrieving data from remote servers. This parameter applies to threads that perform the same stages of the query processing pipeline in parallel.
    Default Value: Dynamic to node size. For nodes with less than 32GB RAM, the parameter is set to 1; for nodes with 32GB or more RAM, it’s set to be equal to the number of vCPUs on the node.
    Allowed Range: 0 and 1,000,000,000,000
  • optimize_on_insert
    Controls merging of data parts during insert
    Default Value: 1 (enabled)
    Allowed Range: 0 (disabled) or 1 (enabled)
  • tcp_keep_alive_timeout
    Idle time (in seconds) before closing a native TCP keep-alive connection
    Default value: 290
    Allowed Range: 10 to 600
  • use_skip_indexes
    Enables or disables use of skip indexes
    Default Value: 1 (enabled)
    Allowed Range: 0 (disabled) or 1 (enabled)

Parameters Configurable via Support Ticket

There are a few other parameters identified to be customisable, but in rarer cases. They can be modified via support tickets to Instaclustr.

  • 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.
    Default value: 10000
    Allowed range: Greater than 0
  • old_parts_lifetime
    The time (in seconds) of storing inactive parts to protect against data loss during spontaneous server reboots.
    Default value: 480 (8 minutes)
    Allowed range: Greater than 0
  • number_of_free_entries_in_pool_to_lower_max_size_of_merge
    When there is less than the specified number of free entries in pool (or replicated queue), start to lower maximum size of merge to process (or to put in queue). This is to allow small merges to process – not filling the pool with long running merges.
    Default value: 8
    Allowed range: Greater than 0
  • merge_selecting_sleep_ms
    Minimum time (in milliseconds) to wait before trying to select parts to merge again after no parts were selected. A lower setting will trigger selecting tasks in background_schedule_pool frequently which result in large amount of requests to zookeeper in large-scale clusters
    Default value: 5000
    Allowed range: Greater than 0
  • mark_cache_size
    Maximum size (in bytes) of cache for marks.
    Default value: Dynamic to node size. For nodes with less than 32GB RAM, the parameter is set to 5/32 of total RAM; for nodes with 32GB or more RAM, it’s set to 5GB.
    Allowed range: Greater than 0
  • background_pool_size
    Sets the number of threads performing background merges and mutations for tables with MergeTree engines.
    Default value: 16
    Allowed range: Powers of 2 (1, 2, 4, 8, 16, …), with the maximum allowed equal to the number of vCPUs on the node
  • 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.
    Default value: 0 (unlimited)
    Allowed range: 0 (unlimited) or greater