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. 

Configurable Parameters

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

    Overriding this parameter will trigger an automated application restart, causing a brief node downtime.

    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: 0 (unlimited)
    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)

Configuring Parameters via Support Ticket

If you wish to override a parameter that is not listed below above, you may still request the change specially for your use case via a NetApp Instaclustr Support Ticket.

Important: Changes beyond those documented here are optionally made at your discretion and are not the responsibility of NetApp Instaclustr. Before proceeding, carefully evaluate how these changes may affect your ClickHouse environment and overall business operations. Only request modifications when you are confident that they are appropriate for your specific use case.