clickhouse的too many part问题

clickhouse踩坑记录 · Yuque

what():  DB::Exception: Too many partitions for single INSERT block (more than 100). The limit is controlled by 'max_partitions_per_insert_block' setting. Large number of partitions is a common misconception. It will lead to severe negative performance impact, including slow server startup, slow INSERT queries and slow SELECT queries. Recommended total number of partitions for a table is under 1000..10000. Please note, that partitioning is not intended to speed up SELECT queries (ORDER BY key is sufficient to make range queries fast). Partitions are intended for data manipulation (DROP PARTITION, etc)

上面这个报错我测试中发现，我在C++的API中 插入一条语句，这个语句里头的partition有105个就会报错，如果放到100个以下就不报错，所以限制单条语句中 parition不要过多就可以了，数据量到是没什么影响，比如你按照交易品种划分parition，一次插入5个品种，一条数据量20000行可以正常插入

经过测试，这个东西是异步merge的，所以很难调试，我经过大量测试得出的结论如下：

toomanypart

数据库已有数据量越多越容易报这个错

尽量降低插入频率，比如有1万条记录，100秒插入 和 1万条记录每秒插100条，插的频率比较高 数据量比较小   容易出现这个报错

多表同时插入和单表插入出这个报错的概率感觉差不多，多partition和单partition出报错的差别也不大，所以结论就是往某个表插入尽量降低插入速度就行了。

(我测试了多partition和多表同时插入崩溃的次数和时间基本一样，所以就是拉大插入的间隔，每次插入数据量大一些，如果还有这个问题，就把参数调大)

config.xml参数
<merge_tree>
<parts_to_delay_insert>600</parts_to_delay_insert>
<parts_to_throw_insert>600</parts_to_throw_insert>
<max_delay_to_insert>2</max_delay_to_insert>
<max_suspicious_broken_parts>5</max_suspicious_broken_parts>
</merge_tree>

如果遇到Too many partitions for single INSERT block (more than 100). The limit is controlled by 'max_partitions_per_insert_block' setting

修改user配置文件中的

<default>
<access_management>1</access_management>
<max_partitions_per_insert_block>0</max_partitions_per_insert_block>
</default>