Very slow inserts using asyncInsert

[t0k4rt]

Dear community,
We are considering using clickhouse at our company but found very strange and slow ingestion rate.
I cannot get past around 17 inserts per seconds using asyncInserts with wait enabled (as
advised in clickhouse documentation).

When trying async insert example (slightly modified):

func AsyncInsert(chConn driver.Conn) error {
	ctx := context.Background()

	defer func() {
		chConn.Exec(ctx, "DROP TABLE example")
	}()
	chConn.Exec(ctx, `DROP TABLE IF EXISTS example`)
	const ddl = `
		CREATE TABLE example (
			  Col1 UInt64
			, Col2 String
			, Col3 Array(UInt8)
			, Col4 DateTime
		) ENGINE = MergeTree
		ORDER BY (Col4, Col1);
	`

	if err := chConn.Exec(ctx, ddl); err != nil {
		return err
	}
	ts := time.Now()
	for i := 0; i < 100; i++ {
		if err := chConn.AsyncInsert(ctx, `INSERT INTO example VALUES (
			?, ?, ?, now()
		)`, true, i, "Golang SQL database driver", []uint8{1, 2, 3, 4, 5, 6, 7, 8, 9}); err != nil {
			return err
		}
	}
	timing := time.Since(ts) / 100
	fmt.Printf("AsyncInsert took %v\n", timing)
	return nil
}

Connection options are:

Settings: clickhouse.Settings{
	"max_execution_time": 60,
	"max_insert_threads": 4,
	"max_threads":        8,
},
Compression: &clickhouse.Compression{
	Method: clickhouse.CompressionLZ4,
},
DialTimeout:      time.Duration(10) * time.Second,
MaxOpenConns:     10,
MaxIdleConns:     5,
ConnMaxLifetime:  time.Duration(10) * time.Minute,
ConnOpenStrategy: clickhouse.ConnOpenInOrder,
BlockBufferSize:  10,

Server and client versions:

ClickHouse client version 24.9.1.3278 (official build).
Connected to ClickHouse server version 24.9.1.

I get a rather constant 60ms time per inserts on my dev machine.
I tried also on a dedicated server with the same behaviour and timings.
Is this normal ?
Should Async insert be avoided in favor of another method ?
Thanks for your help

[jkaflik]

Hi @t0k4rt

Did you check the insert time without the async insert? Do you need wait for query? You might want to get familiar with a documentation around async insert: https://clickhouse.com/docs/en/cloud/bestpractices/asynchronous-inserts

[t0k4rt]

Dear @jkaflik Thanks for you guidance !
Indeed I tested insertions using Exec and it's much faster (around 4ms per inserts on my dev machine). But most of the insert examples are using either AsyncInsert or Batch insert so it does not look like exec is the preferred way for inserting data.

I already read the documentation about async inserts but I'm still confused. What is the advantage of using asyncInserts if it does not lead to better ingestion rate ?

When wait is set to false indeed I get fast ingestion (around 800 inserts/s) but that is not the advised usage according to the doc : Our strong recommendation is to use async_insert=1,wait_for_async_insert=1 if using asynchronous inserts.

The wait bool parameter correspond to the wait_for_async_insert=1 parameters so it should be set to true but that leads to very slow insertion rate.

Maybe AsyncInserts is meant to be used with lots of goroutine like this ? :

	var wg sync.WaitGroup
	for i := 0; i < 500; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			if err := chConn.AsyncInsert(ctx, `INSERT INTO example VALUES (
				?, ?, ?, now()
			)`, true, i, "Golang SQL database driver", []uint8{1, 2, 3, 4, 5, 6, 7, 8, 9}); err != nil {
				fmt.Println(err)
			}
		}()
	}
	wg.Wait()

nota bene: I also tested Batch inserts, it's clearly faster but it does not fit (yet) with our architecture.