Clickhouse Common Issues

Memory Limit Exceeded Issues

The ClickHouse server provides a memory tracker for all query threads. All threads under the same query report to a memory tracker for the query, and at the top level, it is still the memory tracker for total.

• Memory limit (for query): Excessive memory usage during a query (70% of the total instance memory) indicates that the current memory configuration of your cluster does not satisfy the current query’s needs. You could upgrade the configuration to increase the memory.

• Memory limit (for total): Total memory usage exceeded (90% of the total instance memory). You might try reducing query concurrency, and if that doesn’t help, it could be that background asynchronous tasks are using a significant amount of memory (often this is the primary key merge task after a write). You could upgrade the configuration to increase the memory.

Clickhouse Timeout Issues

distributed_ddl_task_timeout timeout issue

The execution wait time for distributed DDL queries (with the on cluster clause) is 180s by default. You can execute the following command on DMS to set the global parameter. After setting, the cluster needs to restart.

set global on cluster default distributed_ddl_task_timeout = 1800;

Since distributed DDL builds a task queue asynchronously based on zookeeper, execution timeout does not indicate a query failure. It only shows that the previously sent task is still waiting in line for execution, so you do not need to resend the task.

max_execution_time timeout issue

The execution timeout for general queries triggers the automatic cancellation of a query upon exceeding the limit. Users can make query-level changes, such as select * from system.numbers settings max_execution_time = 3600, or execute the following command on DMS to set the global parameter.

set global on cluster default max_execution_time = 3600;

socket_timeout timeout issue

The wait time of the HTTP protocol when the socket returns a result. This parameter is not within the ClickHouse system but belongs to the JDBC parameter over the HTTP protocol. However, it affects the setting of the max_execution_time parameter in determining the client’s time limit for waiting for results to return. Therefore, users typically need to adjust the socket_timeout parameter slightly above the max_execution_time when modifying the max_execution_time parameter. Users need to add the socket_timeout property to the JDBC connection string parameter in milliseconds, such as ‘jdbc:clickhouse://127.0.0.1:8123/default?socket_timeout=3600000’.

How to Handle “too many parts” Error When Importing Data?

Every write in ClickHouse generates a data part. Writing one or a small number of records each time can cause a large number of data parts inside ClickHouse (which can be a heavy burden for merge and query). To prevent many data parts, ClickHouse has many built-in limitations, which is the underlying reason for the too many parts error. In case of this error, the first thing to do is increase the batch size of writes. If the batch size cannot be adjusted, you might need to adjust the parameter: max_partitions_per_insert_block.

How to Handle Insert Into Select Memory Limit Exceeded Issue?

• Common Reason 1: High memory usage.

  Solution: Adjust the parameter max_insert_threads to reduce potential memory usage.

• Common Reason 2: Data is being imported from one ClickHouse cluster to another using insert into select.

  Solution: Migrate data by importing files.

How to Query CPU and Memory Usage of Clickhouse?

In the system.query_log system table, you can view peak query CPU and Memory usage logs, which include statistics on CPU and Memory consumption for each query. For more details, please refer to the official documentation .

Why Does It Say Database or Table Does Not Exist After Creating Them?

• With a single replica, there’s only one node in the cluster, and this issue does not occur.

• With multiple replicas, there are multiple nodes, and you need to execute the DDL statement on each node or execute it on any node of the cluster but make sure to include the ON CLUSTER ck_cluster keyword. Please refer to the Development Guide for details.

How to Perform DDL to Add, Remove, or Modify Columns?

Directly execute to modify local tables. If you need to modify a distributed table, proceed according to the situations below.

• If no data is being written, first modify the local table, then modify the distributed table.
• If data is actively being written, differentiate between the operation types.

Why Is DDL Execution Slow or Frequently Stuck?

Common reason: The global execution of DDL is serial, and complex queries may cause deadlocks.

You can take the following solutions.

• Wait for completion.
• Attempt to terminate the query through the console.

How to Handle Distributed DDL Errors: longer than distributed_ddl_task_timeout (=xxx) seconds?

You can modify the default timeout using the set global on cluster default distributed_ddl_task_timeout=xxx command, where ‘xxx’ is the custom timeout in seconds.

How to Handle Syntax Error: set global on cluster default?

• Common Reason 1: The ClickHouse client parses the syntax, while set global on cluster default is syntax added on the server side. If the client version is not updated to match the server, this syntax will be intercepted by the client.

  Solution:

  • Use tools like JDBC Driver, which do not parse syntax on the client, such as DataGrip or DBeaver.
  • Write a JDBC program to execute the statement.

• Common Reason 2: set global on cluster default key = value; where “value” is a string, but the quotation marks are omitted.

  Solution: Enclose the string-type value in quotation marks.

How to Check Disk Space Usage for Each Table?

Execute the following query:

SELECT table, formatReadableSize(sum(bytes)) as size, min(min_date) as min_date, max(max_date) as max_date FROM system.parts WHERE active GROUP BY table; 

What Are Some Common System Tables?

How to Change the Maximum Memory Usage for a Query?

You can add to the settings in the execution statement:

settings max_memory_usage = XXX;

For example, change to 20G:

SET max_memory_usage = 20000000000; #20G