Clickhouse-Kafka-Connect have less through put

[amolsr]

Is your feature request related to a problem? Please describe.
We need to build a dimension table for a ClickHouse dataset that undergoes frequent updates. However, the current throughput is limited to approximately 300-500 messages per second, which is significantly lower than the recommended batch insertion guidelines of 10,000 to 100,000 rows. This low throughput is a bottleneck for maintaining the table efficiently.

Describe the solution you'd like
Develop a native TCP/IP-based connector optimized for high-throughput data ingestion into ClickHouse. This connector should leverage ClickHouse's native protocol to efficiently batch and insert large volumes of data, enabling adherence to the recommended batch sizes and significantly improving performance.

[Paultagoras]

Hi @amolsr ! Have you tried adjusting these settings?

We regularly see folks using the connector with more than 500 messages/second so it should be possible.

[amolsr]

Yeah I have tried using the setting you have mentioned. Still, the through put is very less.

[amolsr]

I used https://housepower.github.io/ClickHouse-Native-JDBC/ driver in one of the use case to achieve high throughput as majority of click-house driver is very slow.

[Paultagoras]

Hmm, I'm curious to know:

• Roughly how many messages are you looking to send
• Roughly how big is each message
• How many partitions does the topic have?
• What format is the data in? (e.g. Avro, Protobuf, JSON, etc...)

[amolsr]

Answering to your question :

Roughly how many messages are you looking to send - in a batch of 10,000 to 1 lakh
Roughly how big is each message about 1 kb
How many partitions does the topic have? 1 as increasing partition will decrease the clickhouse performance.
What format is the data in? (e.g. Avro, Protobuf, JSON, etc...) json

[Paultagoras]

Are the messages single-line JSON or "pretty" JSON? If they're single-line entries (meaning no new-line characters) you may get significant improvements from using the String convertor (with JSON format)

[amolsr]

I tried with the single line only. Still no effect on the throughput. We tried with multiple partitions as well, but that did't worked as well.

[Paultagoras]

@amolsr Could I see an example of your connector configuration? Might help see where the issue is

[amolsr]

Clickhouse connector Infomation

{
   "name": "clickhouse-connect",
   "config": {
       "connector.class": "com.clickhouse.kafka.connect.ClickHouseSinkConnector",
       "tasks.max": "50",
       "consumer.override.max.poll.records": "10000",
       "consumer.override.max.partition.fetch.bytes": "10485760",
       "database": "ad_hoc",
       "errors.retry.timeout": "60",
       "exactlyOnce": "false",
       "hostname": "********",
       "port": "8123",
       "username": "******",
       "password": "******",
       "topics": "cdc.****",
       "value.converter": "org.apache.kafka.connect.json.JsonConverter",
       "value.converter.schemas.enable": "true",
       "clickhouseSettings": "",
       "transforms": "flatten",
       "transforms.flatten.type": "org.apache.kafka.connect.transforms.Flatten$Value",
       "transforms.flatten.delimiter": "_",
       "errors.tolerance": "all",
       "errors.deadletterqueue.topic.name": "cdc.****"
   }
}

My SQL Connector Configuration

{
   "name": "Staging-mysql-connect",
   "config": {
       "connector.class": "io.debezium.connector.mysql.MySqlConnector",
       "topic.prefix": "cdc",
       "database.user": "********",
       "snapshot.mode": "never",
       "schema.history.internal.kafka.topic": "cdc.history",
       "database.server.id": "1",
       "database.hostname": "***********",
       "database.password": "*********",
       "schema.history.internal.kafka.bootstrap.servers": "*******:9094",
       "table.include.list": "cdc.Users",
       "database.include.list": "cdc",
       "column.exclude.list": cdc.Users.phone,phone",
       "skip.messages.without.change": "true"
   }
}

These are the configuration we are using.

[amolsr]

We are using Jmeter Setup for performance bench marking.

[amolnaukri]

awaiting resolution.