Cassandra unlimited row response

user_10442949 0
Viewed 433

I'm using the Cassandra python driver working containing a table with 38500000 rows. I'm benchmarking various databases with a product I'm we're developing. To benchmark selection I use a filter that should return around half the rows with 3 selection columns (then I vary the selection columns to see which perform best). However when working with Cassandra I only get 57016 results each time per query (which must be some cap). I've tried using SimpleStatement and setting fetch_size = 38500000 but this didn't work. I was wondering if there's some parameter I should be changing as I can't find anything in the documentation apart from how Cassandra breaks down pages that go beyond the fetch_size.

[EDIT]

To add some more information the query returns:

cassandra.ReadFailure: Error from server: code=1300 [Replica(s) failed to execute read] message="Operation failed - received 0 responses and 1 failures" info={'consistency': 'LOCAL_ONE', 'required_responses': 1, 'received_responses': 0, 'failures': 1} after I used session.default_fetch_size = None. Then after increasing tombstones to tombstone_failure_threshold: 10000000 and letting gc_grace_seconds = 0 I still get the same error. I've also gone into Cluster.py and set the _default_timeout = 1000 but that didn't lead to anything.

[EDIT 2] Going through the debug console the error is: <SELECT * FROM mydb.facttable WHERE int64 > 0 LIMIT 38500000>, total time 5007 msec, timeout 5000 msec, so again where can I modify the timeout 5000msec parameter?

cassandra python database
1 Answers

Hy there.

Good news is I think I can explain your situation and problems but the bad news is I don't think there is an easy fix.

To give you the short answer and solution:
The read fails because Cassandra has safeguards in place to prevent "malfunctioning" or "rogue" queries from compromising the performance of the whole cluster. For this reason there is a read timeout (as you discovered yourself in EDIT2) that defaults to 5000ms. If you need to increase that value, you need to modify read_request_timeout_in_ms in cassandra.yaml.

This however is not a real solution.

Now, for the long answer:
From what I understand you are targeting with your query 38M rows that you want Cassandra to filter and then return approximately 19M rows. I could see this value achieved on production hardware with well designed tables and a lot of fine-tuning. Given the info in EDIT1, it looks like you are using a one node cluster so I imagine this is a small development node maybe even hosted on your laptop/pc. While sharing the table schema and query could have provided more info and lead to more advice, overall the problem remains that you are trying to run a task on a clearly under-powered cluster(node actually).
From you data, the node was able to process just over 20k rows per second(and return 10k) before timing out. From my experience this seems consistent with a low resource devel node. Extrapolating from this, it would take your node around half an hour to go trough 38M records assuming it will have the memory to do so and no other configuration will pose problems (if I didn't messe up the calculations). That is a very long time for a query, even for an administrative one. When trying to work with so many rows you should not only try to have proportional HW to run the queries against but also:

  1. Avoid full scans (you should always try and hit a single partition, if that is not the case you should probably rethink the DB model);
  2. Most partitions should be under 10MB and all should be under 100MB;
  3. Partitions while hard-capped at 2 billion cells((no. of rows) * (avg. no. of cells per row)) should usually remain under 100k cells.

Given the 3 "best practices" generally accepted as such, and the data you provided, I am inclined to believe that you are not implementing at least 2 of the 3. That and combined with under-powered HW leads to a lack of a simple solution (like just increasing the timeout value).
My suggestion to really resolve the problem is:

  • If you are only doing this to test your code or different approaches, then use a proportional amount of data with the power of the devel "server" compared to your production one. You should still make sure the model you are using cold be effectively run in production given that the large amount of required rows make me think there might be a problem with the DB model.
  • Open another question with information about your situation, current schema and desired outcome and you could receive help on modeling your use-case. While Cassandra can be very powerful when used right, it can be extremely inefficient when badly used.

Hope this helped,
Cheers.

user_128116060
Related