30

I have a Java client that pushes (INSERT) records in batch to Cassandra cluster. The elements in the batch all have the same row key, so they all will be placed in the same node. Also I don't need the transaction to be atomic so I've been using unlogged batch.

The number of INSERT commands in each batch depends on different factors, but can be anything between 5 to 50000. First I just put as many commands as I had in one batch and submitted it. This threw com.datastax.driver.core.exceptions.InvalidQueryException: Batch too large. Then I used a cap of 1000 INSERT per batch, and then down to 300. I noticed I'm just randomly guessing without knowing exactly where this limit comes from, which can cause trouble down the road.

My question is, what is this limit? Can I modify it? How can I know how many elements can be placed in a batch? When my batch is "full"?

Alexis Wilke
  • 19,179
  • 10
  • 84
  • 156
m.hashemian
  • 1,786
  • 2
  • 15
  • 31

3 Answers3

36

I would recommend not increasing the cap, and just splitting into multiple requests. Putting everything in a giant single request will negatively impact the coordinator significantly. Having everything in one partition can improve the throughput in some sized batches by reducing some latency, but batches are never meant to be used to improve performance. So trying to optimize to get maximum throughput by using different batch sizes will depend largely on use case/schema/nodes and will require specific testing, since there's generally a cliff on the size where it starts to degrade.

There is a

# Fail any batch exceeding this value. 50kb (10x warn threshold) by default.
batch_size_fail_threshold_in_kb: 50

option in your cassandra.yaml to increase it, but be sure to test to make sure your actually helping and not hurting your throughput.

Björn Jacobs
  • 4,033
  • 4
  • 28
  • 47
Chris Lohfink
  • 16,150
  • 1
  • 29
  • 38
  • That's what I was looking for, thanks. Do you know what is the best way to monitor the size of the batch in the client? – m.hashemian Jan 10 '16 at 13:19
  • 2
    Depends on the driver you are using, but in the java-driver you can use getValues() on each individual statement in your batch which returns you an Array of ByteBuffers for which you can use the remaining() method to get the size of the Buffers individually and sum them all up, but in general I wouldn't recommend doing that. You should not be creating super large batches, just large enough where you can feel that you are nowhere near close to that limit. – Andy Tolbert Jan 10 '16 at 22:32
  • well there are bunch of things here. C* preaches design by column instead of rows and C* says 2B columns per partition but empirically we know the sweet spot is 100MB. so even with 100MB partition and if the default size of a batch is 50KB thats like 100MB/50KB=3125 requests to retrieve a 100MB partition so that way too many requests. – user1870400 Nov 02 '16 at 21:31
  • Does Batching still need to go through the coordinator even today? Why cant we use token aware policy and batch all rows of a single partition (assume data size of all rows is 100MB) to the right node directly? if not what would be the optimal batch size to retrieve 100MB partition in few requests? – user1870400 Nov 02 '16 at 21:34
  • @user1870400 You don't need to use batch queries if you are going to retrieve an entire partition. Just use 1 query with paging. If you are writing the partition, 3000 requests for 100MB seems fine to me. – user3711864 May 30 '17 at 09:43
5

Looking at the Cassandra logs you'll be able to spot things like:

ERROR 19:54:13 Batch for [matches] is of size 103.072KiB, exceeding specified threshold of 50.000KiB by 53.072KiB. (see batch_size_fail_threshold_in_kb)

fivetwentysix
  • 7,379
  • 9
  • 40
  • 58
  • @user1870400, I am using this config ..cassandra.concurrent.writes=1500 cassandra.output.batch.size.bytes=2056 cassandra.output.batch.grouping.key=partition cassandra.output.consistency.level=ANY cassandra.output.batch.grouping.buffer.size=3000 cassandra.output.throughput_mb_per_sec=25 – BdEngineer Nov 23 '18 at 08:57
  • @user1870400 where i have 200 million records x each record of 100 bytes....it is taking 2hrs to load this data ....so how to fine tune the above parameters ? – BdEngineer Nov 23 '18 at 08:58
4

I fixed this issue by changing the CHUNKSIZE to a lower value (for exemple 1) https://docs.datastax.com/en/cql/3.1/cql/cql_reference/copy_r.html

COPY mytable FROM 'mybackup' WITH CHUNKSIZE = 1;

The operation is much slower but at least it work now

Etienne Cha
  • 345
  • 3
  • 11