Optimizing ClickHouse Table Partitioning

Updated on 2024-10-09 GMT+08:00

View PDF

Troubleshooting

Log in to the ClickHouse client and check whether abnormal merge exists.
select database, table, elapsed, progress, merge_type from system.merges;
Do not perform the INSERT operation too frequently, do not insert a small amount of data, and increase the interval for inserting data.
The data table partitions are not properly allocated. As a result, too many partitions are generated and data tables need to be re-partitioned.

If the MERGE operations are not triggered or slow, adjust the following parameters to accelerate them.

For details, see Accelerating ClickHouse Merge.

Configuration Item	Reference Value
max_threads	Number of CPU cores x 2
background_pool_size	Number of CPU cores
merge_max_block_size	The value is an integer multiple of 8192 and is adjusted based on the CPU and memory resources.
cleanup_delay_period	Set this parameter to a value that is appropriately less than the default value 30.

Changing the Value of parts_to_throw_insert

CAUTION:

Increase the value of this parameter only in special scenarios. This configuration acts as a warning for potential issues to some extent. If the cluster hardware resources are insufficient and this configuration is not adjusted properly, potential service issues cannot be detected in a timely manner, which may cause other faults and increase the difficulty of fault recovery.

For versions earlier MRS 3.2.0, log in to FusionInsight Manager and choose Cluster > Services > ClickHouse. Click Configurations then All Configurations. Click ClickHouseServer > Customization, find the clickhouse-config-customize parameter, add the configuration in Table 1, save it, and restart the service.
For MRS 3.2.0 and later versions, log in to FusionInsight Manager, choose Cluster > ClickHouse > Configurations > All Configurations, search for and change the value of merge_tree.parts_to_throw_insert, save the configurations, and restart the service.

**Table 1** Parameters
Name	Value
merge_tree.parts_to_throw_insert	Memory of ClickHouse instances/32 GB x 300 (conservative estimation)

Verify the modification.

Log in to the ClickHouse client and run the select * from system.merge_tree_settings where name = 'parts_to_throw_insert'; command.

Parent topic: ClickHouse Performance Tuning

Previous topic: ClickHouse Performance Tuning

Next topic: Accelerating ClickHouse Merge

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot