Cassandra - poor performance with batch insert in single-node with single-table

user_2792256 0
Viewed 284

The context

I have just one Cassandra node, installed locally on my PC with Windows 10 (Core i5, 16GB ram, SSD drive).

I created a table like this:

CREATE KEYSPACE covid19 WITH replication = {
    'class':              'SimpleStrategy',
    'replication_factor': '1'
};


CREATE TABLE covid19.cases (
    pesel       text,
    test_date   date,
    result      boolean,
    PRIMARY KEY ((pesel), test_date)
)
WITH CLUSTERING ORDER BY (test_date DESC);

The pesel is unique, 10-digits id of a person.

Then I generated 10 000 rows of sample data, that looks like this:

INSERT INTO cases (pesel, test_date, result) VALUES ('0000000001', '2020-03-10', true);
INSERT INTO cases (pesel, test_date, result) VALUES ('0000000002', '2020-03-10', false);
INSERT INTO cases (pesel, test_date, result) VALUES ('0000000003', '2020-03-10', false);
INSERT INTO cases (pesel, test_date, result) VALUES ('0000000004', '2020-03-12', false);
INSERT INTO cases (pesel, test_date, result) VALUES ('0000000005', '2020-03-12', false);
INSERT INTO cases (pesel, test_date, result) VALUES ('0000000006', '2020-03-12', false);
...

Finally, I loaded the data using cqlsh: source 'cases.cql';

Problem 1

To load 10 000 rows it takes 51 seconds. Is that normal?

I was expecting inserts to Cassandra to be ultra fast, while this pretty much comparable with SQLite without transaction (59s). If I wrap inserts with BEGIN & COMMIT in SQLite, this takes less than a second. This brings us to another problem...

Problem 2

Batch inserting. Slow batch inserting. To single partition, on single node.

I wrapped inserts with BEGIN BATCH and APPLY BATCH;. After that, the source was taking so long, I stopped measuring after passing 4 minutes mark.

Yes, I am aware of wrong usage of batch inserts. As far as I understood, it is an anti-pattern to use batch insert if it would require inserts to different partitions, which makes sense. This is not the case here.

Why is batch inserting so slow on single node (thus single partition)?

What am I missing here?

cassandra-3.0 bulkinsert insert cassandra performance
1 Answers

Cassandra is not SQLite. It's not optimised for this use case (running on a single machine). It's optimised for horizontal scaling. You can run it locally, but usually that's only for testing. And I don't expect it to be optimised in any form for running on Windows.

See how Cassandra writes are working at https://blog.softwaremill.com/cassandra-writes-in-depth-6ea8d7581eb

To go into a bit more details, here is what will happen in your case for each insert:

  1. Insert row into memtable, which is not just an append, since it has to be kept sorted
  2. Append a row to the CommitLog (file on disk). This is an append, no look up, but still it's a disk operation.
  3. At some point there will be some flush operation(s). Memtables are written on disk, with other data computed and attached to them (Index, bloom filters). All commit log files are deleted.
  4. Your client code might not be multithreaded, so it will take a bit of time to get the response and send another insert.

Considering that you run this on your machine (in 16GB!), memtable might be small enough to trigger multiple flushes for 10000 rows. Also there might be some compactions started, depending on what you already have.

Notice that every step involves at least one disk write. Desktop SSDs are good, but not that good.

I checked a production Cassandra cluster; it gets 2000 writes/sec, and the mean latency for writes is less than 1ms, while also serving 2000 reads/sec with mean latency of 1.5ms. But this happens on Linux servers, with 60GB of RAM and NVME SSD.

Batch will be even worse due to additional work for coordinating. Batch will not be any improvement for your setup, with 1 node there is nothing to coordinate with. See https://medium.com/@foundev/cassandra-batch-loading-without-the-batch-keyword-40f00e35e23e

user_52090090
Related