About Message Sorting（source : kafka, sinks: clickhouse）

[yangshike]

Hello,
I recently encountered a problem，Data update failed。
Here is my data link：kafka <-- vector --> clickhouse
in my vector sink_config，I added the following parameters：
buffer.max_events = 100000
batch.max_bytes = 61440000
batch.timeout_secs = 30

my clickhouse table engine (Because my data may be updated. I need to keep the final insertion record，I used the clickHouse replacingmergetree feature to change the update to insert，):
create table xx (
id Int64,
name String
)ENGINE=ReplacingMergeTree()
order by id

When my writes and updates are almost at the same time（For the same id），I found that some data is in a saved insert state,
Normally, it should be like this：
insert table xx (id,name )values (1,'a')(2,'b')(3,'c')(1,'e')

But this question seems to be like this：

insert table xx (id,name )values (1,'e')(2,'b')(3,'c')(1,'a')
the record （1,'a'）was placed in the back on the same batch

By the way, I have confirmed that the data is stored in the order of events in Kafka
so, I want to know how the data is sorted in the vector buffer or batch。

[StephenWakely]

Can you share more about your config? Vector would typically maintain the order of events.

[yangshike]

hello, I have found the cause of the problem，
Because the first insertion statement was stuck. It took about 10 seconds, and during this process, the second insertion statement was already written in. Therefore, on the clickHouse side, the order of the two statements changed

┌─query_duration_ms─┬─query_id─────────────────────────────┬─type────────┬──────────event_time─┐
│ 0 │ ddf4af9b-c4a2-4cd3-8688-df9474d095c2 │ QueryStart │ 2023-09-07 02:33:46 │
│ 0 │ 76719e4a-0112-4d1d-8de3-45759c390db5 │ QueryStart │ 2023-09-07 02:33:56 │
│ 13 │ 76719e4a-0112-4d1d-8de3-45759c390db5 │ QueryFinish │ 2023-09-07 02:33:56 │
│ 10334 │ ddf4af9b-c4a2-4cd3-8688-df9474d095c2 │ QueryFinish │ 2023-09-07 02:33:56 │
└───────────────────┴──────────────────────────────────────┴─────────────┴─────────────────────┘

query id ddf4af9b-c4a2-4cd3-8688-df9474d095c2 start with '2023-09-07 02:33:46' but finished with '2023-09-07 02:33:56' , after with query_id '2023-09-07 02:33:56'

Is there a solution to similar problems?

For example, for single sink single thread execution? Will the next item be executed after the first one is completed?

[jszwedko]

Hi @yangshike !

The clickhouse sink will parallelize requests to ClickHouse. You can restrict this behavior by setting request.concurrency = 1 to limit the sink to one request at a time. This will, of course, negatively impact throughput. I'd suggest a better approach is to not limit request concurrency but instead sort by timestamp when querying the data.

[yangshike]

Can you share more about your config? Vector would typically maintain the order of events.

[pront]

Jesse's suggestion to set request.concurrency: 1 prevents overlapping requests from this sink. I verified on Vector 0.60.0 with a delayed local HTTP receiver that the second request waited for the first response. This can reduce throughput and does not guarantee end-to-end event ordering.