ApsaraDB for ClickHouse:FAQ

ApsaraDB for ClickHouse fixes stability bugs in the community version and provides resource queues to set resource usage priorities by user role.

Try these solutions:

• Check network connectivity. Use ping to check network reachability and telnet to verify that database ports 3306 and 8123 are open.

• Verify your ClickHouse whitelist configuration. Configure a whitelist.

• Confirm the public IP address of your client machine. On an office network, this address can change frequently, so use an IP lookup service such as whatsmyip.

In versions 20.3 and 20.8, the system automatically validates the connection when you create an external table. Successful table creation confirms network connectivity. If table creation fails, common reasons include:

• The target endpoint and ClickHouse are not in the same VPC, which prevents network connectivity.

• The MySQL server uses a whitelist. You must add ClickHouse to the MySQL whitelist.

Common causes of connection failures and their solutions:

• Cause: The network environment is misconfigured. You can connect over an internal network only if your application and the instance are in the same VPC. If they are in different VPCs, you must enable a public endpoint and connect over the public network.

  Solution: Enable public network access. Apply for and release public IP addresses.

• Cause: The client's IP address is not in the instance whitelist.

  Solution: Add the client IP to the whitelist. Configure a whitelist.

• Cause: The security group for your ECS instance blocks traffic on the required port.

  Solution: Update the ECS security group rules. Security group operation guide.

• Cause: A corporate firewall is blocking the connection.

  Solution: Modify your firewall rules to allow outbound traffic to the instance.

• Cause: The username or password in the connection string contains special characters, such as !@#$%^&*()_+=. The client might not interpret these characters correctly, which causes a connection failure.

  Solution: You must URL-encode any special characters in the connection string. The encoding rules are as follows:

  ! : %21
  @ : %40
  # : %23
  $ : %24
  % : %25
  ^ : %5e
  & : %26
  * : %2a
  ( : %28
  ) : %29
  _ : %5f
  + : %2b
  = : %3d

  For example, if your password is ab@#c, the encoded password in the connection string must be ab%40%23c.

• Cause: ApsaraDB for ClickHouse automatically provisions a pay-as-you-go Server Load Balancer (SLB) instance for your cluster. If your Alibaba Cloud account has overdue payments, the SLB instance may be suspended, making your ApsaraDB for ClickHouse instance inaccessible.

  Solution: Check your Alibaba Cloud account for overdue payments and pay any outstanding balance to restore the service.

To resolve this issue, do one of the following:

• Split large files in OSS into smaller files before importing them.

• Vertically scale your cluster to increase its memory. Vertical and horizontal scaling for Community Edition clusters.

Common causes and solutions:

• Cause 1: Suboptimal parameter configuration. ClickHouse performs best with a large batch size and a small number of concurrent operations. A single batch can often contain tens or even hundreds of thousands of rows, depending on your average row size. As a general guideline, you can estimate based on 100 bytes per row, but you should adjust this value based on your actual data characteristics.

  Solution: Set the number of concurrent operations to 10 or fewer. Experiment with different parameter values to find the optimal configuration for your workload.

• Cause 2: Insufficient resources in the DataWorks exclusive resource group. The ECS instances in the exclusive resource group may be undersized. Insufficient CPU or memory can limit the number of concurrent operations and the available network egress bandwidth. Likewise, setting a large batch size on a machine with limited memory can trigger frequent Java garbage collection (GC) in the DataWorks process, which slows down performance.

  Solution: Check the DataWorks output logs for the ECS instance specifications.

• Cause 3: The bottleneck is at the data source.

  Solution: In the DataWorks output logs, search for the totalWaitReaderTime and totalWaitWriterTime metrics. If totalWaitReaderTime is significantly higher than totalWaitWriterTime, the bottleneck is on the read side (the data source), not the write side.

• Cause 4: Use of a public network endpoint. A public network endpoint has limited bandwidth and is unsuitable for high-performance data import or export operations.

  Solution: Switch to a VPC network endpoint.

• Cause 5: Presence of dirty data. Normally, data is written in a batch. However, if dirty data is encountered, the current batch write fails. DataX then falls back to a row-by-row insertion mode. This fallback generates excessive data parts and drastically reduces write performance.

  You can use the following two methods to check for dirty data.

  • Check for error messages. If the log contains a Cannot parse error, this indicates dirty data.

    You can use the following SQL query to find exceptions.

    SELECT written_rows, written_bytes, query_duration_ms, event_time, exception
    FROM system.query_log
    WHERE event_time BETWEEN '2021-11-22 22:00:00' AND '2021-11-22 23:00:00' AND lowerUTF8(query) LIKE '%insert into <table_name>%' and type != 'QueryStart' and exception_code != 0
    ORDER BY event_time DESC LIMIT 30;

  • Monitor the batch row count. If the number of rows per batch drops to 1, this strongly indicates that DataX has encountered dirty data and switched to row-by-row mode.

    You can use the following SQL query to check the row count.

    SELECT written_rows, written_bytes, query_duration_ms, event_time
    FROM system.query_log
    WHERE event_time BETWEEN '2021-11-22 22:00:00' AND '2021-11-22 23:00:00' AND lowerUTF8(query) LIKE '%insert into <table_name>%' and type != 'QueryStart'
    ORDER BY event_time DESC LIMIT 30;

  Solution: Identify and correct or remove the dirty data from the data source.

Perform the following checks:

1. Check the query_log system table for errors during the import. If any errors exist, data loss has likely occurred.

2. Check if your table engine supports deduplication. For example, if you use the ReplacingMergeTree table engine, the number of rows in ClickHouse may be lower than in Hive because this engine removes duplicates.

3. Verify the accuracy of the number of rows in the Hive source, as the source's initial count may be inaccurate.

Check the following:

1. Check the query_log system table for errors that occurred during the import. If errors are present, data loss may have occurred.

2. Check if your table engine performs data deduplication. For example, the ReplacingMergeTree engine removes duplicate entries, which lowers the row count in ClickHouse compared to Kafka.

3. Check if the kafka_skip_broken_messages parameter is enabled in your Kafka external table configuration. If enabled, ClickHouse skips messages that fail to parse, reducing the row count in ClickHouse compared to Kafka.

Use one of these methods:

• Export files using the ClickHouse Client. Migrate data from a self-managed ClickHouse instance to ApsaraDB for ClickHouse Community Edition.

• Use the remote function.

  INSERT INTO <destination_table> SELECT * FROM remote('<connection_string>', '<database>', '<table>', '<username>', '<password>');

Solution: Follow these steps to resynchronize the MySQL data.

1. Delete the table that stopped synchronizing.

   DROP TABLE <table_name> ON cluster default;

   Note

   In this command, table_name is the name of the table that stopped synchronizing. If the table is a distributed table, you must delete both the distributed table and its local table.

2. Restart the synchronization process.

   ALTER database <database_name> ON cluster default MODIFY SETTING skip_unsupported_tables = 1;

   Note

   In this command, <database_name> is the name of the synchronized database in ApsaraDB for ClickHouse.

• Cause: High memory usage.

  Solution: Adjust the max_insert_threads parameter to reduce memory usage.

• Cause: Using an insert into select statement to import data between ClickHouse clusters.

  Solution: Migrate the data by importing it from files. Migrate data from a self-managed ClickHouse instance to ApsaraDB for ClickHouse Community Edition.

The ClickHouse server tracks memory usage at multiple levels. For a single query, a memory tracker aggregates the memory usage of all its query threads. This tracker then reports to an overall memory tracker. The solution depends on the specific error you encounter.

• An error with Memory limit (for query) means the query failed because it consumed too much memory, exceeding its limit of 70% of the total instance memory capacity. To resolve this, perform a vertical upgrade to increase the instance memory capacity.

• An error with Memory limit (for total) means the overall memory usage on the instance has exceeded the system-wide limit of 90% of the total instance memory capacity. This error can result from high query concurrency or resource-intensive background asynchronous tasks, such as primary key merge tasks that run after data writes. First, try reducing query concurrency. If the error persists, perform a vertical upgrade to increase the instance memory capacity.

The default maximum query concurrency for a server is 100. Modify this value in the console:

1. Log in to the ApsaraDB for ClickHouse console.

2. On the Clusters page, select Clusters of Community-compatible Edition, and then click the target cluster ID.

3. In the left-side navigation pane, click Parameter Configuration.

4. On the Parameter Configuration page, click the edit icon in the Parameter Value column for the max_concurrent_queries parameter.

5. Enter the new value in the dialog box and click OK.

6. Click Submit Parameters.

7. Click OK.

Consider the following solutions:

• Check if you are using a multi-node cluster. In a multi-node cluster, you must create a distributed table. Write to and query this table for consistent results. Otherwise, each query may hit a different shard. Create a distributed table.

• Check if you are using a master-replica cluster. In a master-replica cluster, use tables with a Replicated series table engine to synchronize data between replicas. Otherwise, each query may hit a different replica, leading to inconsistent results. Table engines.

Common causes and solutions:

• Cause 1: The table creation process is incorrect. A ClickHouse distributed cluster does not have native distributed DDL semantics. If you use a create table statement in a self-managed ClickHouse cluster, the statement may return a success message, but the table is created only on the currently connected server. When you connect to a different server, the table is not visible.

  Solution:

  1. When you create a table, use the create table <table_name> on cluster default statement. The on cluster default clause broadcasts the statement to all nodes in the default cluster.

     CREATE TABLE test ON cluster default (a UInt64) Engine = MergeTree() ORDER BY tuple();

  2. Create another table that uses the distributed table engine on top of the test table.

     CREATE TABLE test_dis ON cluster default AS test Engine = Distributed(default, default, test, cityHash64(a));

• Cause 2: The ReplicatedMergeTree table is misconfigured. The ReplicatedMergeTree table engine is an enhanced version of the MergeTree table engine that provides master-replica synchronization. On a single-replica instance, you can only create tables with the MergeTree engine. On a master-replica instance, you must create tables with the ReplicatedMergeTree engine.

  Solution: When you create a table in a master-replica instance, use ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}') or ReplicatedMergeTree() to configure the ReplicatedMergeTree table engine. The arguments in ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}') are part of a fixed template and should not be modified.

A primary key merge must meet the following two prerequisites to function correctly.

• The ORDER BY key must include the partition key of the storage table. A primary key merge does not occur across different partitions.

• For a distributed table, the ORDER BY key must include the sharding key, which is determined by the hash algorithm. A primary key merge does not occur across different nodes.

The following table describes common optimize commands and their behavior.

Here are common causes and their solutions.

• Cause 1: Data TTL eviction occurs during the primary key merge phase. If a data part is not merged for an extended period, the system cannot evict the expired data within it.

  Solution:

  • You can manually trigger a merge task by running optimize final or optimize a specific partition.

  • When creating a table, you can set parameters such as merge_with_ttl_timeout and ttl_only_drop_parts to merge data parts that contain expired data more frequently.

• Cause 2: If a table's TTL is modified or added, existing data parts may have missing or incorrect TTL information. This may also prevent the eviction of expired data.

  Solution:

  • You can regenerate TTL information by running alter table materialize ttl.

  • You can update the TTL information by running optimize partition.

To modify a local table, execute a DDL statement. For a distributed table, the procedure depends on whether data is currently being written to it.

• If no data is being written to the table, modify the local table first, and then modify the distributed table.

• If data is being written to the table, the procedure depends on the type of modification.

  Type	Actions
  Adding a nullable column	Modify the local table. Modify the distributed table.
  Modifying a column's data type (to a convertible type)
  Dropping a nullable column	Modify the distributed table. Modify the local table.
  Adding a non-nullable column	Stop writing data. Execute SYSTEM FLUSH DISTRIBUTED on the distributed table. Modify the local table. Modify the distributed table. Resume writing data.
  Dropping a non-nullable column
  Renaming a column

Try these solutions:

• Wait for the operation to complete.

• Try terminating the query in the console.

• Cause 1: The ClickHouse client parses syntax. However, set global on cluster default is a server-side syntax. If the client version is incompatible with the server, the client blocks this statement.

  Solution:

  • Use a tool that does not perform client-side syntax parsing. Examples include JDBC-based tools like DataGrip and DBeaver.

  • Write a JDBC program to execute the statement.

• Cause 2: In the set global on cluster default key = value; statement, the value is a string but is not enclosed in quotation marks.

  Solution: Enclose the string value in quotation marks.

To view the disk space used by each table, run 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; 

The following is an example query:

SELECT * FROM system.disks;

Use the following query:

SELECT * FROM system.parts WHERE disk_name = 'cold_disk';

To move a data partition to cold storage, run the following statement:

ALTER TABLE table_name MOVE PARTITION partition_expr TO DISK 'cold_disk';

Common causes include:

• A query triggering an OOM error.

• An instance restart triggered by a configuration change.

• An instance restart following an upgrade or downgrade.

The following table describes common system tables and their functions.

User-level parameters correspond to configuration items in the users.xml file. To modify a parameter, run the following statement.

SET global ON cluster default ${key}=${value};

Specify the quota parameter in the settings of an execution statement:

settings max_memory_usage = XXX;

In a dual-replica or single-replica multi-node cluster, the write node has higher CPU usage and memory usage than other nodes during heavy write operations. Usage balances out across the nodes once the data is synchronized.

• Problem description:

  How to view detailed system logs to troubleshoot errors or identify potential issues.

• Solution:

  1. Check the text_log.level parameter for your cluster and do one of the following:

     1. If text_log.level is empty, text logging is disabled. To enable it, set a value for this parameter.

     2. If text_log.level has a value, verify that the log level meets your requirements. If not, modify the parameter to the desired level.

     Configure config.xml parameters.

  2. Connect to the target database. Connect to a database.

  3. Run the following statement to view and analyze the logs.

     SELECT * FROM system.text_log;

If the destination cluster and data source are in the same VPC and region, verify that the IP address of each is in the other's whitelist.

• To configure the ClickHouse whitelist, see Set a Whitelist.

• For other data sources, see their respective product documentation.

If the preceding conditions are not met, first establish connectivity by using a suitable network solution. Then, add the IP address of each to the other's whitelist.

Yes, you can migrate a ClickHouse Community Edition cluster to an Enterprise Edition cluster.

You can migrate data between Enterprise Edition and Community Edition clusters using the remote function or by exporting and importing data files. Migrate data from a self-managed ClickHouse cluster to Alibaba Cloud ClickHouse Community-Compatible Edition.

By default, Enterprise Edition 24.5 or later instances use the new query analyzer. This analyzer delivers better performance but may not be backward-compatible with some legacy SQL statements. If you encounter a parsing error, revert to the legacy analyzer. Learn more about the new analyzer.

SET allow_experimental_analyzer = 0;

The suspend feature is only available for Enterprise Edition clusters. ClickHouse Community Edition clusters cannot be suspended. To suspend an Enterprise Edition cluster, go to the Enterprise Edition Clusters page. Select the target region in the top-left corner. In the cluster list, find the target cluster and click > Suspend in the Actions column.