Cassandra unpredictable failure depending on WHERE clause

Question

I am attempting to execute a SELECT statement against a large Cassandra table (10m rows) with various WHERE clauses. I am issuing these from the Datastax DevCenter application. The columns I am using in the where clause have secondary indexes.

The where clause looks like WHERE fileid = 18000 or alternatively WHERE fileid < 18000. In this example, the second where clause results in the error Unable to execute CQL script on 'connection1': Cassandra failure during read query at consistency ONE (1 responses were required but only 0 replica responded, 1 failed)

I have no idea why it is failing in this unpredictable manner. Any ideas?

NOTE: I am aware that this is a terrible idea, and Cassandra is not meant to be used in this way. I am issuing these queries and timing them to prove to others how inefficient Cassandra is for our use case compared to other solutions.

Whats your schema? Also don't use secondary index for that (small unique keys), it wont work well. Keep in mind secondary indexes are very rarely going to work well and thinking of them like an index in a relational DBs is dangerous. — Chris Lohfink

BernadetteD BernadetteD · Accepted Answer · 2019-07-02T21:39:38

Your query is probably failing because of a READ timeout (the timeout on waiting to read data). You could try updating the Cassandra.yaml with a larger read timeout time with read_request_timeout_in_ms: 200000 (for 200s) to give an output rather than an error. However, if you're trying to prove the inefficiency of Cassandra in your use case, this error seems like a pretty good way to do it.

Cassandra unpredictable failure depending on WHERE clause

1 Answers