ClickHouse® cluster settings

• How do I create a user to access a cluster from DataLens with read-only permissions?

• How do I grant a user permissions to create and delete tables or databases?

• How do I find out the internal_replication setting value?

• How do I increase the maximum amount of RAM to run a query?

• Why must a Managed Service for ClickHouse® cluster have three or five ZooKeeper hosts?

• How do I add a host to a cluster with disabled coordination service?

• How do I add a multi-host shard to a cluster with disabled coordination service?

How do I create a user to access a cluster from DataLens with read-only permissions?How do I create a user to access a cluster from DataLens with read-only permissions?

Follow this guide to create a user with read-only permissions. With DataLens access enabled in the cluster settings, the service will be able to connect to the cluster using this user.

How do I grant a user permissions to create and delete tables or databases?How do I grant a user permissions to create and delete tables or databases?

Enable managing users via SQL and grant the user the required permissions using the GRANT command.

For more information about the GRANT command, see this ClickHouse® guide.

How do I find out the internal_replication setting value?How do I find out the internal_replication setting value?

The internal_replication setting information is not available in Yandex Cloud interfaces or ClickHouse® system tables. The default setting value is true.

Why do I get an error?Why do I get an MEMORY_LIMIT_EXCEEDED error?

The Max memory usage setting limits the amount of memory that a single query can use on one server. By default, its value is 0, meaning that no limit is set.

Increase Max memory usage only if it is set to a non-zero value and the query exceeds this limit. In this case, you will get the following error:

DB::Exception: Memory limit (total) exceeded:
would use 14.10 GiB (attempt to allocate chunk of 4219924 bytes), maximum: 14.10 GiB.
(MEMORY_LIMIT_EXCEEDED), Stack trace (when copying this message, always include the lines below)

The Max memory usage value cannot exceed the limit set by Max server memory usage. If Max memory usage is set to 0, the MEMORY_LIMIT_EXCEEDED error may be caused by the server reaching its overall memory limit. Increasing Max memory usage will not resolve the issue in this case. Optimize the query to reduce memory usage or change the host class. For more information, see Memory management in Managed Service for ClickHouse®.

You can increase the Max memory usage value in the user settings or using SQL queries:

• For the current session:

  SET max_memory_usage = <value_in_bytes>;
  

• For an individual query:

  SELECT <expression>
  FROM <table_name>
  SETTINGS max_memory_usage = <value_in_bytes>;
  

If user management via SQL is enabled in the cluster, you can set the Max memory usage value for selected users via the settings profile. For example, to set a value for a single user:

CREATE SETTINGS PROFILE max_memory_usage_profile
SETTINGS max_memory_usage = <value_in_bytes>
TO <username>;

Why must a highly available Managed Service for ClickHouse® cluster have three or five ZooKeeper hosts?Why must a highly available Managed Service for ClickHouse® cluster have three or five ZooKeeper hosts?

ZooKeeper uses the consensus algorithm: it keeps on running as long as most ZooKeeper hosts are healthy.

For example, if a cluster has two ZooKeeper hosts, then, should one of them stop, the remaining host will not form the majority, so the service will become unavailable. Which means a cluster with two ZooKeeper hosts is not highly available.

A cluster with three ZooKeeper hosts, however, is highly available. When one of its hosts is under maintenance or down, the cluster remains operational. Therefore, three is the minimum recommended number of ZooKeeper hosts per Managed Service for ClickHouse® cluster.

A cluster with four ZooKeeper hosts has no advantages over a three-host cluster: it will also remain operational if only one of its hosts fails. With two hosts down, the consensus is not met, so the service becomes unavailable.

A cluster with five ZooKeeper hosts is resilient enough to keep running without two of its hosts, three hosts out of five still forming the majority. This is why this cluster is easier to maintain than a three-host cluster. Even if one host out of five is under maintenance or restarting, the cluster remains highly available, i.e., it can lose one more host and still be operational.

Adding more than five ZooKeeper hosts to a cluster is not supported.

Thus, we recommend creating three or five ZooKeeper hosts per Managed Service for ClickHouse® cluster.

How do I add a host to a cluster with disabled coordination service?How do I add a host to a cluster with disabled coordination service?

If you try to add a host to a cluster with disabled coordination service, you will get this error:

ERROR: rpc error: code = FailedPrecondition desc = shard cannot have more than 1 host in non-HA cluster configuration

To add a host to the cluster, first enable the ClickHouse® Keeper or ZooKeeper coordination service on individual hosts.

How do I add a multi-host shard to a cluster with disabled coordination service?How do I add a multi-host shard to a cluster with disabled coordination service?

If you try to add a multi-host shard to a sharded cluster with disabled coordination service, you will get this error:

ERROR: rpc error: code = FailedPrecondition desc = To create a shard with two or more hosts, you must enable the coordination service first.

To add a multi-host shard to the cluster, first enable the ClickHouse® Keeper or ZooKeeper coordination service on individual hosts.

ClickHouse® is a registered trademark of ClickHouse, Inc.