Why is the clickhouse-cpp much slower than clickhouse-client in insertion

[wangxi19]

I use clickhouse-client to batch insert 1 million rows, the inserting speed can reach to almost 140 thousands rows per sencond(although it is slow also), but when I use clickhouse-cpp in my program, I tested the inserting speed, It is only 10 thousands rows per second, I have modified clickhouse.cpp file to support continuous insertion without declaring 'INSERT INTO ... ' statement, the speed has growed up to 10 thousands rows per second from the original 600 rows per second.

[wangxi19]

was the clickhouse-client written by clickhouse-cpp?

[blinkov]

was the clickhouse-client written by clickhouse-cpp?

@wangxi19 no, they are completely independent.

[wangxi19]

was the clickhouse-client written by clickhouse-cpp?

@wangxi19 no, they are completely independent.

Hi, since it is so, how do I make clickhouse-cpp reach clickhouse-client's insertion speed, or are there any other cpp library I can use

[wangxi19]

was the clickhouse-client written by clickhouse-cpp?

@wangxi19 no, they are completely independent.

Hi, since it is so, how do I make clickhouse-cpp reach clickhouse-client's insertion speed, or are there any other cpp library I can use

are there two different low-layer insertion mechanisms between clickhouse-client and clickhouse-cpp?

[Enmk]

@wangxi19 Could you please provide a minimal viable example of what you are doing with both clickhouse client and clickhouse-cpp, so I have a chance to help you?

[wangxi19]

//this example is using clickhouse-cpp
//tabnm is using MergeTree, has 8192 index_granularity and partition by field1(DateTime)
//this below code snapshot is quite simple, because my working computer cann't connect to internet, so the complete code doesn't be shown, but this simple code snapshot is enough to descript the core logic

int main()
{
using namespace clickhouse;
Client c(ClientOptions().SetHost(host).SetPingBeforeQuery(false));
auto field1 = std::make_shared();
auto field2 = std::make_shared();
auto field3 = std::make_shared();
auto field4 = std::make_shared();
auto field5 = std::make_shared();
auto field6 = std::make_shared();
//..., total column numbers almost are 30

for (int cnt = 1; cnt<1000; cnt++) {
for (int i = 0; i < 10000; i++) {
field1->Append((std::time_t)(1577808000 + myRand()%31622399));
field2->Append((UInt32_t)myRand());
field3->Append((UInt32_t)myRand());
field4->Append((UInt32_t)myRand());
field5->Append((UInt32_t)myRand());
field6->Append((UInt32_t)myRand());
}

  Block b;
  b.AppendColumn("field1", field1);
  b.AppendColumn("field2", field2);
  b.AppendColumn("field3", field3);
  b.AppendColumn("field4", field4);
  b.AppendColumn("field5", field5);
  b.AppendColumn("field6", field6);
  
  field1->Clear();
  field2->Clear();
  field3->Clear();
  field4->Clear();
  field5->Clear();
  field6->Clear();
  
  client.Insert("tabnm", b);

}
}

//this example is using clickhouse-client
echo "INSERT INTO tabnm VALUES " > /tmp/sql.tbnm.1million
//tabnmRandom has same table structure as tabnm except the table engine, tabnmRandom is using GenerateRandom(1,64,64)
clickhouse-client -h host -f Values <<< "SELECT toDateTime('2020-01-01 00:00:00')+toIntervalDay(rand()%365), field2, field3, field4, field5, field6 from tabnmRandom limit 1000000" >> /tmp/sql.tbnm.1million
clickhouse-client -h host -f Values --send_logs_level=trace < /tmp/sql.tbnm.1million

[filimonov]

Do you measure insert time or generate time also?

[wangxi19]

Do you measure insert time or generate time also?

The generate time is so transient, the main time costing is in insertion time.
When I use multiple threads, the speed growed up to several ten thousands per second

[wangxi19]

Hi guys, I think I found the reason.
Same 1 million rows datas, Inserting from clickhouse-cpp and clickhouse-client, the back-end clickhouse-server's action is so different.
when I use clickhouse-client to do this clickhouse-client -h host -f Values --send_logs_level=trace < /tmp/sql.tbnm.1million, only 55 partitions have been created.
But when I use clickhouse-cpp (I program the demon by using clickhouse-cpp library), 1221 partitions have been created.
Why?

[wangxi19]

I think I need to read clickhouse-client source codes

[rpopescu]

@wangxi19 did you figure this issue out?

[filimonov]

Server merges several block if they came in single select (see min_insert_block_rows/bytes). Afair cickhouse-cpp sends single block in the single insert

[rpopescu]

thanks

[alexey-milovidov]

FYI clickhouse-client source code is located here: https://github.com/ClickHouse/ClickHouse/tree/master/programs/client

[rpopescu]

I know, I've checked it out already, the first thing I noticed is that clickhouse-cpp seems to be a clean-room implementation, from serialisation to network code, whereas the client is very involved with the main codebase, uses Poco for network, and probably other differences I've not noticed in 10 minutes.