[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for ClickHouse®](../index.md) > [Step-by-step guides](index.md) > Clusters > Editing ClickHouse® settings > Server level

# Changing ClickHouse® settings at the server level

You can specify [ClickHouse® settings at the server level](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings) to configure the way databases or individual tables work in a Managed Service for ClickHouse® cluster. You can specify settings using several methods:

  * Using the [Yandex Cloud interfaces](#yandex-cloud-interfaces). This way you can only specify the ClickHouse® settings available in Yandex Cloud.
  * Using [SQL queries](#sql-queries). This way you can specify settings for MergeTree tables. You can:

    * Specify the settings when [creating a table](#set-settings-for-new-table).
    * [Specify the settings](#change-settings-of-existing-table) of an existing table.
    * [Restore the default settings](#reset-table-settings) of an existing table.

## Specifying ClickHouse® settings using the Yandex Cloud interfaces {#yandex-cloud-interfaces}

Changing some [server settings](../concepts/settings-list.md#server-level-settings) will restart ClickHouse® servers on the cluster hosts.

{% note info %}

You cannot directly change the [Max server memory usage](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#max_server_memory_usage) value. Managed Service for ClickHouse® sets this value automatically depending on the amount of RAM available to ClickHouse® hosts. To change this setting, you need to [change the ClickHouse® host class](#change-resource-preset) first. For more information, see [Memory management](../concepts/memory-management.md).

{% endnote %}

{% list tabs group=instructions %}

- Management console {#console}

   To configure ClickHouse®:

   1. In the [management console](https://console.yandex.cloud), select the folder containing the cluster.
   1. Navigate to **Managed Service for ClickHouse**.
   1. Select your cluster and click **Edit** in the top panel.
   1. Under **DBMS settings**, click **Settings**.
   1. Specify the [ClickHouse® settings](../concepts/settings-list.md#server-level-settings).
   1. Click **Save changes**.

- CLI {#cli}

   If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

   The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

   To configure ClickHouse®:

   1. View the full list of settings for the cluster:

      ```bash
      yc managed-clickhouse cluster get <cluster_name_or_ID> --full
      ```

   1. View the description of the CLI command for updating the cluster configuration:

      ```bash
      yc managed-clickhouse cluster update-config --help
      ```

   1. Set the parameter values as needed:

      ```bash
      yc managed-clickhouse cluster update-config <cluster_name_or_ID> \
         --set <parameter_1_name>=<value_1>,...
      ```


- Terraform {#tf}

   To configure ClickHouse®:

   1. Open the current Terraform configuration file with the infrastructure plan.

      For information on how to create this file, see [Creating a cluster](cluster-create.md).

   1. In the Managed Service for ClickHouse® cluster description, under `clickhouse.config`, edit the parameters as follows:

      ```hcl
      resource "yandex_mdb_clickhouse_cluster_v2" "<cluster_name>" {
        ...
        clickhouse = {
          ...

          config = {
            # General DBMS settings
            ...

            merge_tree = {
              # MergeTree engine settings
              ...
            }

            kafka = {
              # General settings for getting data from Apache Kafka
              ...
            }

            rabbit_mq = {
              # Settings for getting data from RabbitMQ
              username = "<username>"
              password = "<password>"
            }

            compression = [
              {
                # Data compression settings
                method              = "<compression_method>"
                min_part_size       = <data_part_size>
                min_part_size_ratio = <size_ratio>
              }
            ]

            graphite_rollup = [
              {
                # GraphiteMergeTree engine settings for data thinning, aggregation, and rollup in Graphite.
                ...
              }
            ]
          }
          ...
        }
        ...
      }
      ```

      Where:

      * `method`: Compression method, `LZ4` or `ZSTD`.
      * `min_part_size`: Minimum size of a table data part, in bytes.
      * `min_part_size_ratio`: Ratio of the smallest data part size to the full table size.

   1. Make sure the settings are correct.

      1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.
      1. Run this command:
      
         ```bash
         terraform validate
         ```
      
         Terraform will show any errors found in your configuration files.

   1. Confirm resource changes.

      1. Run this command to view the planned changes:
      
         ```bash
         terraform plan
         ```
      
         If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.
      
      1. If everything looks correct, apply the changes:
         1. Run this command:
      
            ```bash
            terraform apply
            ```
      
         1. Confirm updating the resources.
         1. Wait for the operation to complete.

   For more information, see [this Terraform provider guide](../../terraform/resources/mdb_clickhouse_cluster.md).

   {% note warning "Timeouts" %}
   
   The Terraform provider sets the following timeouts for Managed Service for ClickHouse® cluster operations:
   
   * Creating a cluster, including by restoring from a backup: 60 minutes.
   * Updating a cluster: 90 minutes.
   * Deleting a cluster: 30 minutes.
   
   Operations exceeding the timeout are aborted.
   
   {% cut "How to change these limits" %}
   
   Add the `timeouts` section to your cluster description, such as the following:
   
   ```hcl
   resource "yandex_mdb_clickhouse_cluster_v2" "<cluster_name>" {
     ...
     timeouts = {
       create = "1h30m" # 1 hour 30 minutes
       update = "2h"    # 2 hours
       delete = "30m"   # 30 minutes
     }
   }
   ```
   
   {% endcut %}
   
   {% endnote %}


- REST API {#api}

   1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

      ```bash
      export IAM_TOKEN="<IAM_token>"
      ```

   1. Call the [Cluster.Update](../api-ref/Cluster/update.md) method, e.g., via the following [cURL](https://curl.se/) request:

      {% note warning %}
      
      The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the `updateMask` parameter as a single comma-separated string.
      
      {% endnote %}

      ```bash
      curl \
         --request PATCH \
         --header "Authorization: Bearer $IAM_TOKEN" \
         --header "Content-Type: application/json" \
         --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/<cluster_ID>' \
         --data '{
                    "updateMask": "configSpec.clickhouse.config.<setting_1>,...,configSpec.clickhouse.config.<setting_N>",
                    "configSpec": {
                       "clickhouse": {
                          "config": {
                             "<setting_1>": "<value_1>",
                             "<setting_2>": "<value_2>",
                             ...
                             "<setting_N>": "<value_N>"
                          }
                       }
                    }
                 }'
      ```

      Where:

      * `updateMask`: Comma-separated string of settings to update.
      * `configSpec.clickhouse.config`: ClickHouse® server-level settings. For possible parameters and their values, see [this method description](../api-ref/Cluster/update.md#yandex.cloud.mdb.clickhouse.v1.UpdateClusterRequest).

      You can get the cluster ID with the [list of clusters in the folder](cluster-list.md#list-clusters).

   1. Check the [server response](../api-ref/Cluster/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

- gRPC API {#grpc-api}

   1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

      ```bash
      export IAM_TOKEN="<IAM_token>"
      ```

   1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
      
      ```bash
      cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
      ```
      
      Below, we assume that the repository contents reside in the `~/cloudapi/` directory.
   1. Call the [ClusterService.Update](../api-ref/grpc/Cluster/update.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

      {% note warning %}
      
      The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the `update_mask` parameter as an array of `paths[]` strings.
      
      {% cut "Format for listing settings" %}
      
      ```yaml
      "update_mask": {
          "paths": [
              "<setting_1>",
              "<setting_2>",
              ...
              "<setting_N>"
          ]
      }
      ```
      
      {% endcut %}
      
      {% endnote %}

      ```bash
      grpcurl \
         -format json \
         -import-path ~/cloudapi/ \
         -import-path ~/cloudapi/third_party/googleapis/ \
         -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/cluster_service.proto \
         -rpc-header "Authorization: Bearer $IAM_TOKEN" \
         -d '{
                "cluster_id": "<cluster_ID>",
                "update_mask": {
                   "paths": [
                      "configSpec.clickhouse.config.<setting_1>",
                      "configSpec.clickhouse.config.<setting_2>",
                      ...
                      "configSpec.clickhouse.config.<setting_N>"
                   ]
                },
                "config_spec": {
                   "clickhouse": {
                      "config": {
                         "<setting_1>": "<value_1>",
                         "<setting_2>": "<value_2>",
                         ...
                         "<setting_N>": "<value_N>"
                      }
                   }
                }
             }' \
         mdb.api.cloud.yandex.net:443 \
         yandex.cloud.mdb.clickhouse.v1.ClusterService.Update
      ```

      Where:

      * `update_mask`: List of settings to update as an array of strings (`paths[]`).
      * `config_spec.clickhouse.config`: ClickHouse® server-level settings. For possible parameters and their values, see [this method description](../api-ref/grpc/Cluster/update.md#yandex.cloud.mdb.clickhouse.v1.UpdateClusterRequest).

      You can get the cluster ID with the [list of clusters in the folder](cluster-list.md#list-clusters).

   1. View the [server response](../api-ref/grpc/Cluster/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

{% endlist %}

## Checking MergeTree table settings {#check-current-settings}

   {% list tabs group=instructions %}

   - SQL {#sql}

      1. [Connect](connect/clients.md) to the database in the cluster.
      1. To view all table-level settings, run the following query:

         ```sql
         SHOW CREATE TABLE <table_name>;
         ```

         {% note warning %}

         The `SHOW CREATE TABLE` output only includes settings overridden by the user. If a user-defined value matches the default value, this setting is also output.

         {% endnote %}

   {% endlist %}

## Specifying settings for MergeTree tables using SQL queries {#sql-queries}

### Changing settings when creating a MergeTree table {#set-settings-for-new-table}

   {% list tabs group=instructions %}

   - SQL {#sql}

      1. [Connect](connect/clients.md) to the database in the cluster.
      1. Create a table. To configure it, list its settings under `SETTINGS`, separated by commas:

         ```sql
         CREATE TABLE <table_name>
            (
            <table_column_description>
            )
            ENGINE = MergeTree
            PRIMARY KEY (<column_or_group_of_columns>)
            SETTINGS
            <setting_name> = <setting_value>,
            <setting_name> = <setting_value>;
         ```

         Here is an example of the query for the [merge_with_ttl_timeout](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#merge_with_ttl_timeout) and [merge_with_recompression_ttl_timeout](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#merge_with_recompression_ttl_timeout) settings:

         ```sql
         CREATE TABLE <table_name>
            (
            user_id UInt32,
            message String,
            )
            ENGINE = MergeTree
            PRIMARY KEY (user_id)
            SETTINGS merge_with_ttl_timeout = 15000,
            merge_with_recompression_ttl_timeout = 15000;
         ```

      For more information on creating MergeTree tables, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-creating-a-table).

   {% endlist %}

### Changing the settings of an existing MergeTree table {#change-settings-of-existing-table}

   {% list tabs group=instructions %}

   - SQL {#sql}

      1. [Connect](connect/clients.md) to the database in the cluster.
      1. To change the settings of an existing table, run this query:

         ```sql
         ALTER TABLE <table_name> MODIFY SETTING <setting_name> = <new_setting_value>;
         ```
         You can change multiple settings with a single query. To do this, list `<setting_name> = <new_setting_value>` pairs separated by commas.

   {% endlist %}

### Resetting a MergeTree table setting to default {#reset-table-settings}

   {% list tabs group=instructions %}

   - SQL {#sql}

      1. [Connect](connect/clients.md) to the database in the cluster.
      1. To reset a setting of an existing table to its default, run this query:

         ```sql
         ALTER TABLE <table_name> RESET SETTING <setting_name>;
         ```
         You can reset multiple settings to default with a single query. To do this, list the names of the settings separated by commas.

   {% endlist %}

_ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._