Changing cluster settings

If you don't have the Yandex.Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To change the host class for the cluster:

1. View a description of the CLI's update cluster command:

   $ yc managed-clickhouse cluster update --help
   

2. Request a list of available host classes (the ZONES column specifies the availability zones where you can select the appropriate class):

   $ yc managed-clickhouse resource-preset list
   
   +-----------+--------------------------------+-------+----------+
   |    ID     |            ZONE IDS            | CORES |  MEMORY  |
   +-----------+--------------------------------+-------+----------+
   | s1.micro  | ru-central1-a, ru-central1-b,  |     2 | 8.0 GB   |
   |           | ru-central1-c                  |       |          |
   | ...                                                           |
   +-----------+--------------------------------+-------+----------+
   

3. Specify the class in the update cluster command:

   $ yc managed-clickhouse cluster update <cluster name>
        --clickhouse-resource-preset <class ID>
   

   Managed Service for ClickHouse will run the update host class command for the cluster.

   You can change the ZooKeeper host class using a similar parameter: --zookeeper-resource-preset.

If you don't have the Yandex.Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To increase the storage size for a cluster:

1. Make sure the required cluster is using network storage (it is not yet possible to increase the size of local storage). To do this, request information about the cluster and find the disk_type_id field: it should be set to network-hdd or network-ssd:

   $ yc managed-clickhouse cluster get <cluster name>
   
   id: c7qkvr3u78qiopj3u4k2
   folder_id: b1g0ftj57rrjk9thribv
   ...
   config:
     clickhouse:
       resources:
         resource_preset_id: s1.micro
         disk_size: "21474836480"
         disk_type_id: network-ssd
   ...
   

2. View a description of the CLI's update cluster command:

   $ yc managed-clickhouse cluster update --help
   

3. Make sure the cloud's quota is sufficient to increase the storage size: open the Quotas page for your cloud and check that the Yandex Managed Service for ClickHouse section still has space available in the space line.

4. Specify the required amount of storage in the update cluster command (it must be at least as large as disk_size in the cluster properties):

   $ yc managed-clickhouse cluster update <cluster name>
        --clickhouse-disk-size <storage size in GB>
   

   If all these conditions are met, Managed Service for ClickHouse launches the operation to increase storage space.

   You can change the storage size for ZooKeeper by using the same parameter, --zookeeper-disk-size.

1. Go to the folder page and select Managed Service for ClickHouse.

2. Select the cluster and click Edit cluster in the top panel.

3. Change the ClickHouse settings by clicking Configure under DBMS settings:

   • Geobase uri: Address of the archive with the user geobase in Object Storage.

   • Keep alive timeout: Amount of time in seconds after the last request to ClickHouse, during which the server waits for a new request. If no requests are received during this time, ClickHouse breaks the connection. To learn more, see the ClickHouse documentation.

   • Log level: Event logging level. At each next level, the log will contain complete information from the previous one:

     1. ERROR: Information about errors in the cluster.
     2. WARNING: Information about events that may cause errors in the cluster.
     3. INFORMATION: Confirmations, information about events that don't lead to errors in the cluster.
     4. DEBUG: System information to be used later in debugging.
     5. TRACE: All available information about cluster performance.

     For more information about log levels, see the ClickHouse documentation.

   • Mark cache size: Approximate size (in bytes) of the mark cache used by MergeTree table engines. The cache is shared by the server and memory is allocated as needed. To learn more about logging in ClickHouse, see the documentation.

   • Max concurrent queries: Maximum number of queries processed simultaneously. To learn more, see the ClickHouse documentation.

   • Max connections: Maximum number of inbound connections. To learn more, see the ClickHouse documentation.

   • Max partition size to drop: Maximum size (in bytes) of a partition in a MergeTree table, which you can delete using a DROP query.

   • Max table size to drop: Maximum size (in bytes) of a MergeTree table, which you can delete using a DROP query. If 0, you can delete all tables without restrictions. To learn more, see the ClickHouse documentation.

   • Metric log enabled: Enables or disables logging of the history of metric values from the system.metrics and system.events tables to the system.metric_log table. Logging is enabled by default (true).

   • Metric log retention size: The maximum size in bytes that the system.metric_log table can reach before old records start being deleted from it. A value of 0 means that the old records aren't deleted as the table size grows. Default value: 536870912 (0.5 GB).

   • Metric log retention time: The period of time in milliseconds after which a record in the system.metric_log table is deleted. Time is counted as soon as the record is created in the table. A value of 0 means that records aren't deleted when the time elapses. The value must be a multiple of 1000. Default value: 2592000000 (30 days).

   • Part log retention size: The maximum size in bytes that the system.part_log table can reach before old records start being deleted from it. A value of 0 means that the old records aren't deleted as the table size grows. Default value: 536870912 (0.5 GB).

   • Part log retention time: The period of time in milliseconds after which a record in the system.part_log table is deleted. Time is counted as soon as the record is created in the table. A value of 0 means that records aren't deleted when the time elapses. The value must be a multiple of 1000. Default value: 2592000000 (30 days).

   • Query log retention size: The maximum size in bytes that the system.query_log table can reach before old records start being deleted from it. A value of 0 means that the old records aren't deleted as the table size grows. Default value: 1073741824 (1 GB).

   • Query log retention time: The period of time in milliseconds after which a record in the system.query_log table is deleted. Time is counted as soon as the record is created in the table. A value of 0 means that records aren't deleted when the time elapses. The value must be a multiple of 1000. Default value: 2592000000 (30 days).

   • Query thread log enabled: Enables or disables logging of information about the threads that execute requests, such as the name of the thread, time it was started, and how long a request was processed. Logs are written to the system.query_thread_log table. Logging is enabled by default (true).

   • Query thread log retention size: The maximum size in bytes that the system.query_thread_log table can reach before old records start being deleted from it. A value of 0 means that the old records aren't deleted as the table size grows. Default value: 536870912 (0.5 GB).

   • Query thread log retention time: The period of time in milliseconds after which a record in the system.query_thread_log table is deleted. Time is counted as soon as the record is created in the table. A value of 0 means that records aren't deleted when the time elapses. The value must be a multiple of 1000. Default value: 2592000000 (30 days).

   • Text log enabled: Enables or disables writing of system logs to the system.text_log table. Logging is disabled by default (false).

   • Text log level: The level of event logging in the system.text_log table. At each next level, the log will contain complete information from the previous one:

     1. ERROR: Information about errors in the DBMS.
     2. WARNING: Information about events that may cause errors in the DBMS.
     3. INFORMATION: Confirmation and information about events that don't lead to errors in the DBMS.
     4. DEBUG: System information to be used later in debugging.
     5. TRACE: All available information about DBMS performance.

   • Text log retention size: The maximum size in bytes that the system.text_log table can reach before old records start being deleted from it. A value of 0 means that the old records aren't deleted as the table size grows. Default value: 536870912 (0.5 GB).

   • Text log retention time: The period of time in milliseconds after which a record in the system.text_log table is deleted. Time is counted as soon as the record is created in the table. A value of 0 means that records aren't deleted when the time elapses. The value must be a multiple of 1000. Default value: 2592000000 (30 days).

   • Timezone: Server time zone. Specified by the IANA identifier as the UTC time zone or geographical location (for example, Africa/Abidjan). For more information, see the ClickHouse documentation.

   • Trace log enabled: Enables or disables logging of stack traces collected by the request profiler to the system.trace_log table. Logging is enabled by default (true).

   • Trace log retention size: The maximum size in bytes that the system.trace_log table can reach before old records start being deleted from it. A value of 0 means that the old records aren't deleted as the table size grows. Default value: 536870912 (0.5 GB).

   • Trace log retention time: The period of time in milliseconds after which a record in the system.trace_log table is deleted. Time is counted as soon as the record is created in the table. A value of 0 means that records aren't deleted when the time elapses. The value must be a multiple of 1000. Default value: 2592000000 (30 days).

   • Uncompressed cache size: Cache size in bytes for uncompressed data used by the MergeTree table engines. To learn more, see the ClickHouse documentation.

   • Compression: Rules for compressing data in MergeTree tables.

     • Method: Compression method. Two methods are available: LZ4 and zstd.
     • Min part size: Minimum size (in bytes) of a data part in a table. ClickHouse only applies the rule to tables with data parts greater than or equal to the Min part size value.
     • Min part size ratio: Minimum ratio of table part size to total table size. ClickHouse only applies the rule to tables in which this ratio is greater than or equal to the Min part size ratio value.

     You can add multiple compression rules. ClickHouse checks the Min part size and Min part size ratio conditions and applies the rules to those tables that meet both of them. If multiple rules can be applied to the same table, ClickHouse applies the first one. If none of the rules are applicable, ClickHouse uses the LZ4 compression method. To learn more, see the ClickHouse documentation

   • Graphite rollup: GraphiteMergeTree engine configurations for thinning and aggregating/averaging (rollup) Graphite data. You can set up multiple configurations and use them for different tables.

     To learn more about Graphite support in ClickHouse, see the documentation.

     • Name: Configuration name.
     • Patterns: Set of thinning rules. A rule applies if the metric name matches the Regexp parameter value and the age of the data matches the Retention parameter group value.
       • Function: Aggregation function name.
       • Regexp: Regular expression that the metric name must match.
       • Retention: Retention parameters. The function applies to data whose age is in the range of [Age, Age + Precision]. You can set several groups of these parameters.
         • Age: Minimum data age, in seconds.
         • Precision: Accuracy of determining the age of the data, in seconds. Must be a divisor of 86,400 (the number of seconds in 24 hours).

   • Merge tree: MergeTree engine configuration. For more information, see the ClickHouse documentation

     • Max bytes to merge at min space in pool: Maximum total size of a data part to merge when the number of free threads in the background pool is minimum.
     • Max replicated merges in queue: Maximum number of merge tasks that can be in the ReplicatedMergeTree queue at the same time.
     • Number of free entries in pool to lower max size of merge: Threshold value of free entries in the pool. If the number of entries in the pool falls below this value, ClickHouse reduces the maximum size of a data part to merge. This helps handle small merges faster, rather than filling the pool with lengthy merges.
     • Parts to delay insert: Number of active data parts in a table. When exceed, ClickHouse starts artificially reducing the rate of inserting data into the table.
     • Parts to throw insert: Threshold value of active data parts in a table. When exceeded,ClickHouse throws the 'Too many parts ...' exception.
     • Replicated deduplication window: Number of recent hash blocks that ZooKeeper stores (old ones are deleted).
     • Replicated deduplication window seconds: Time during which ZooKeeper stores hash blocks (old ones are deleted).

4. Click Save changes.

1. Go to the folder page and select Managed Service for ClickHouse.

2. Select the cluster and click Edit cluster in the top panel.

3. Change additional cluster settings:

   • Backup start (UTC): The time in UTC when you want to start creating a backup of a cluster (in 24-hour format). If the time is not set, the backup will start at 22:00 UTC.

   • Maintenance window: Settings of the technical support window. You can use the settings to specify the preferred start time for cluster host maintenance (for example, you can select the time when the cluster is least loaded with requests):

     • To specify the preferred start time for the maintenance window, select by schedule and set the desired day of the week and hour of day in UTC (Coordinated Universal Time) using the drop-down lists.
     • To allow maintenance at any time, select arbitrary.

     Maintenance may include updating the DBMS version, applying patches, and so on.

   • Access from DataLens: Enable this option to be able to analyze data from the cluster in Yandex DataLens. For more information about setting up a connection, see Connecting to DataLens.

   • Access from the management console: Select this option to be able to execute SQL queries to cluster databases from the Yandex.Cloud management console.

   • Access from Yandex.Metrica and AppMetrica: Enable this option to be able to import data from AppMetrica to the cluster.

4. Click Save changes.