CODEWITHSUNDEEP
clickhouse
Chocorean
Chocorean

Reputation: 878

ClickHouse - Inserting more than a hundred entries per query

I do not figure out how to increase the max number of entries per query. I would like to insert a thousand entries per query, and the default value is 100.

According to the doc, the parameter max_partitions_per_insert_block defines the limit of simultaneous entries.

I've tried to modify it from the ClickHouse client, but my insertion still fails :

$ clickhouse-client
my-virtual-machine :) set max_partitions_per_insert_block=1000

*SET* max_partitions_per_insert_block = 1000

Ok.

0 rows in set. Elapsed: 0.001 sec.

Moreover, this is no max_partitions_per_insert_block field in the /etc/clickhouse-server/config.xml file.

After modifying max_partitions_per_insert_block, I've tried to insert my data, but I'm stuck with this error :

infi.clickhouse_orm.database.ServerError: Code: 252, e.displayText() = 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). (version 19.5.3.8 (official build))

EDIT: I'm still stuck with this. I cannot even manually set the parameter to the value I want with SET max_partitions_per_insert_block = 1000: the value is changed but goes back to 100 after exiting and reopening clickhouse-client (even with sudo, so it does not look like a permission problem).

Upvotes: 2

Views: 9312

Answers (3)

Dmitry Polovinkin
Dmitry Polovinkin

Reputation: 180

Just in case, there is another way to solve it while running the INSERT query itself.

You can set the value for this max_partitions_per_insert_block setting like this:

INSERT INTO the_table
SETTINGS max_partitions_per_insert_block = 1000
SELECT * FROM ...

Upvotes: 1

Сергей Потехин
Сергей Потехин

Reputation: 21

Most likely you should change the partitioning scheme. Each partition generates several files on the file system, which can lead to disruption of the OS. In addition, this may be the cause of long mergers.

Upvotes: 1

Chocorean
Chocorean

Reputation: 878

I figured it out when reading again the documentation, especially this document. I have recognized in the web profile settings I saw in the system.settings table. I just tried to insert the following in my default's profile, reloaded, and my insert of a thousand entries wen well : <max_partitions_per_insert_block>1000</max_partitions_per_insert_block>

I guess it was obvious for some, but probably not for unexperimented people.

Upvotes: 5

Related Questions