Inserting bulk data in crate

CrateDB
1

Hi Guys

So, from a migration activity, we have recently upgraded the data from 4.8.1 to 5.10.16
We have restored most of the tables from the old to the new cluster.

Recently, we started facing challenges while doing operations from the cluster which was not encountered in the older crateDB mostly in COPY FROM operations

I wanted to confirm the standards of bulk inserts in crateDB among the options:

  1. single insert with multiple rows
  2. multiple single row inserts
  3. COPY FROM commands

As per my understanding single insert with multiple rows (500 - 1K ) should be the optimal one but wanted to confirm

2

Hi @arnab32,

For continuously ongoing ingestion, bulk inserts are usually the best choice (HTTP bulk insert or PostgreSQL prepared statements). The documentation has a whole section covering the different considerations: Fast Inserts - CrateDB: Guide

Can you elaborate what type of issues you are observing?
Please also be aware that CrateDB 5.10 has reached its end of life and is no longer supported and maintained. The most recent minor release is 6.3.

Best
Niklas

3

Hi @hammerhead

There are few bottlenecks which we are currently facing.

The recent activity which we did was to upgrade the crate cluster to 4.8.1 to 5.10.16 Since the data was huge (approximately 15 TB) and the majority of our tables were created in 4.8.1, we were able to directly restore the data from the old to the new cluster by taking a backup.

However, we are facing few bottlenecks with the same data which the older cluster was somehow able to handle even with same data volume and query patterns.

Here are a few optimisations done from our side:

  1. There was one big data job which was running like ~ 1K single inserts. I made the change to do bulk insert with 1K entries in a single query which actually helped in optimizing the job which runs every 45 minutes

  2. There was a table which had lot of dynamic object fields and all of them were getting indexed. We were doing COPY FROM operation and it was failing everytime. So i disabled the fields as ignored as the query patterns was not actually doing any filter on those. Hence, we were able to copy the data successfully

Current issue:

Even with the changes in place, there are muitple jobs which write and read data simultaneously across mutiple tables. The cluster gets heavily loaded and system load reaches to about System load: 6.92 and queries becoming unresponsive.

Upon analysis, we found that few nodes (3 data nodes) are sometime running with extremely high system load (33.3) with even general select queries waiting in-queue, failing or being unresponsive.

We exepected some kind of improvement after migrating to the newer cluster but the new cluster is unable the same query patterns and data load which the old one was able to handle

There are a total of
11 worker nodes : m5.4x large node
3 master nodes : m5.large
41 data nodes : m5.4x large

Please let me know other areas to look into or any other details required

Thanks and Regards
Arnab