Correct use of BatchStatement for inserts

[Kostas Chalikias]

Hello everyone, I recently discovered in my client logs, after some data went missing, that some of my queries (batches of prepared insert statements) are timing out. Looking at the server logs I noticed complaints about the batches being too big (sometimes > 1MB) and after some further reading on the internet that "I'm using batches all wrong"™. 

I've now set out to fix all of the above and it looks like in order to use batches properly you essentially need to batch inserts on the same partition key together. Unfortunately my schema doesn't really let me do that as it looks like this:

CREATE TABLE dev.data (
    identifier text,
    time timestamp,
    value text,
    PRIMARY KEY (identifier, time)
) WITH CLUSTERING ORDER BY (time ASC)

In my case each insert statement in the batch is for a unique identifier, for the same time so

insert into dev.data ('id1', t1, 'foo') 

insert into dev.data ('id2', t1, 'bar') 

insert into dev.data ('id3', t1, 'bob') 

etc...

Had I known about this earlier I could have probably split identifier in two parts where one would only vary a little between my identifiers and use that as the partition key but it's a bit too late now. My current idea is that perhaps if I hash the identifiers locally and mod the result by the number of nodes in the cluster, I can still batch the inserts in a meaningful way but that feels like a hack so wanted to get some more opinions on this - any help is appreciated. It feels like I need to do some batching to save myself the round trip times. Obviously I will also making sure batches don't go over a certain size.

Thanks!

Kostas

[Alan Boudreault]

Hello,

The recommendation you read is right. It is importnat to avoid big batches that touch too many partitions (for performance reason, since the coordinator has more work to do). Can you explain what is your *batch* use case? Are you using logged batches for atomicity or just unlogged batches? And why? If you are using unlogged batches, a possible solution could simply be to set a max batch_size, and send multiple batch requests rather than a big one. Some links 

--
You received this message because you are subscribed to the Google Groups "DataStax Python Driver for Apache Cassandra User Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to python-driver-user+unsub...@lists.datastax.com.

--

Alan Boudreault

Software Engineer (Drivers) | alan.bo...@datastax.com

[Alan Boudreault]

Some link that could be interesting for you:

* http://www.planetcassandra.org/blog/the-importance-of-single-partition-operations-in-cassandra/

* https://www.youtube.com/watch?v=wF3Ec1rdWgc&list=PLm-EPIkBI3YoiA-02vufoEj4CgYvIQgIk

To unsubscribe from this group and stop receiving emails from it, send an email to python-driver-user+unsubscribe@lists.datastax.com.

--

Alan Boudreault

Software Engineer (Drivers) | alan.bo...@datastax.com

[Adam Holmberg]

I agree with what Alan has said on this thread. I'm just adding a few ideas since I wrote this last Friday and forgot to send.

-------------------------------------------------------

Your best bet is probably to just run the inserts concurrently using 

http://datastax.github.io/python-driver/api/cassandra/concurrent.html#cassandra.concurrent.execute_concurrent_with_args

This is a good way to utilize token-aware routing, and concurrency across the cluster without much complexity for the application. 

Alternatives:

If your (replication factor) ~= (node count), you would do alright just splitting your batch into smaller batches. 

If you want to go for ultimate efficiency, you can bind all statements, then group into batches by Cluster.metadata.get_rerplicas(keyspace, bound_statement.routing_key)[0]. You could then run those batches concurrently as well.

Regards,

Adam Holmberg

--

You received this message because you are subscribed to the Google Groups "DataStax Python Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to python-driver-user+unsub...@lists.datastax.com.

[Kostas Chalikias]

Hello again, appreciate the responses.

Alan - My batch use case was simply me thinking that it made sense to batch the inserts to minimize network hops, not realizing I was doing it wrong and was adding the atomicity overhead as well as the extra work of coordinating the insert across multiple nodes. I was using logged batches - I think - as they are the default. I should have read the manual!

Adam - yes my replication factor == node count actually but I wouldn't like to design around that as it could change. I've done some benchmarking and removing batching completely and using TopicAwarePolicy does not seem to slow the client down using execute_async, even though I imagine the data will take longer to get to the server. I also noticed much lower CPU usage on the server machines while running a long streak of inserts. I think I will go with that and if it's not enough I will go with the client side grouping using Cluster.metadata.get_replicas assuming this doesn't slow my client down too much.

Thanks for your help!

Kostas

--

To unsubscribe from this group and stop receiving emails from it, send an email to python-driver-user+unsubscribe@lists.datastax.com.