Multiple small inserts in clickhouse

Question

I have an event table (MergeTree) in clickhouse and want to run a lot of small inserts at the same time. However the server becomes overloaded and unresponsive. Moreover, some of the inserts are lost. There are a lot of records in clickhouse error log:

01:43:01.668 [ 16 ] <Error> events (Merger): Part 201 61109_20161109_240760_266738_51 intersects previous part

Is there a way to optimize such queries? I know I can use bulk insert for some types of events. Basically, running one insert with many records, which clickhouse handles pretty well. However, some of the events, such as clicks or opens could not be handled in this way.

The other question: why clickhouse decides that similar records exist, when they don't? There are similar records at the time of insert, which have the same fields as in index, but other fields are different.

From time to time I also receive the following error:

Caused by: ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, message: Connect to localhost:8123 [ip6-localhost/0:0:0:0:0:0:0:1] timed out, host: localhost, port: 8123; Connect to ip6-localhost:8123 [ip6-localhost/0:0:0:0:0:0:0:1] timed out
    ... 36 more

Mostly during project build when test against clickhouse database are run.

Superaltar

Answer 1

This is known issue when processing large number of small inserts into (non-replicated) MergeTree.

This is a bug, we need to investigate and fix.

For workaround, you should send inserts in larger batches, as recommended: about one batch per second: https://clickhouse.tech/docs/en/introduction/performance/#performance-when-inserting-data.

Civilian

we have events generated on on-off basis. Inserting them into CH in batches, means that we need to build a staging area for them, maybe a queue or some other temporary place. Is this hat we have to do, or there is another recommended way of inserting single rows? The second follow-up question: The doc states: "To improve performance, you can make multiple INSERT queries in parallel...". Does this mean that we can run parallel processes/threads, each inserting, and yet each process needs to do one batch per second in parallel? – Crass

Yes, you need to accumulate events in some queue or in in-process buffer of your service, and insert in batches. – Civilian

You could do many INSERTs in parallel; one batch per second is recommended in total across all threads. – Civilian

It's not a bug, it's an expected symptom of the database architecture. – Lutanist

Answer 2

Clickhouse has special type of tables for this - Buffer. It's stored in memory and allow many small inserts with out problem. We have near 200 different inserts per second - it works fine.

Buffer table:

CREATE TABLE logs.log_buffer (rid String, created DateTime, some String, d Date MATERIALIZED toDate(created))
ENGINE = Buffer('logs', 'log_main', 16, 5, 30, 1000, 10000, 1000000, 10000000);

Main table:

CREATE TABLE logs.log_main (rid String, created DateTime, some String, d Date) 
ENGINE = MergeTree(d, sipHash128(rid), (created, sipHash128(rid)), 8192);

Details in manual: https://clickhouse.yandex/docs/en/operations/table_engines/buffer/

Hidrosis

Other moment with Buffer tables - if you want to add new field in main table you cannot make it on the fly (. You should stop all and insert new field in main table and in buffer table, and then run all again – Hidrosis

Answer 3

I've had a similar problem, although not as bad - making ~20 inserts per second caused the server to reach a high loadavg, memory consumption and CPU use. I created a Buffer table which buffers the inserts in memory, and then they are flushed periodically to the "real" on-disk table. And just like magic, everything went quite: loadavg, memory and CPU usage came down to normal levels. The nice thing is that you can run queries against the buffer table, and get back matching rows from both memory and disk - so clients are unaffected by the buffering. See https://clickhouse.tech/docs/en/engines/table-engines/special/buffer/

Phenacite

Do you use Buffer for single-row inserts? Docs suggest not using even Buffer for multiple single-row inserts: Note that it doesn't make sense to insert data one row at a time, even for Buffer tables. I am trying to find a way to insert multiple single rows and I don't want to build a queue system myself. – Turkoman

@GokhanSari - the claim that "it doesn't make sense" is subjective. Perhaps for your use-case you'll find that it does, in fact, make sense. Just try it before trying to build a more complex mechanism! If you're not trying to insert hundreds of single rows every second, a buffer table will probably work just fine. – Phenacite

Answer 4

Alternatively, you can use something like https://github.com/nikepan/clickhouse-bulk: it will buffer multiple inserts and flush them all together according to user policy.

Wage

Answer 5

The design of clickhouse MergeEngines is not meant to take small writes concurrently. The MergeTree as much as I understands merges the parts of data written to a table into based on partitions and then re-organize the parts for better aggregated reads. If we do small writes often you would encounter another exception that Merge

Error: 500: Code: 252, e.displayText() = DB::Exception: Too many parts (300). Merges are processing significantly slow

When you would try to understand why the above exception is thrown the idea will be a lot clearer. CH needs to merge data and there is an upper limit as to how many parts can exist! And every write in a batch is added as a new part and then eventually merged with the partitioned table.

SELECT
    table, count() as cnt
FROM system.parts 
WHERE database = 'dbname' GROUP BY `table` order by cnt desc

The above query can help you monitor parts, observe while writing how the parts would increase and eventually merge down.

My best bet for the above would be buffering the data set and periodically flushing it to DB, but then that means no real-time analytics.

Using buffer is good, however please consider these points:

• If the server is restarted abnormally, the data in the buffer is lost.
• FINAL and SAMPLE do not work correctly for Buffer tables. These conditions are passed to the destination table, but are not used for processing data in the buffer
• When adding data to a Buffer, one of the buffers is locked. (So no reads)
• If the destination table is replicated, some expected characteristics of replicated tables are lost when writing to a Buffer table. (no deduplication)

Please read throughly, it's a special case engine: https://clickhouse.tech/docs/en/engines/table-engines/special/buffer/

Bessette