Cassandra query using secondary index timedout

Question

I am facing timeout issue while executing query on Cassandra database. We have tried increasing the read timeout fields "read_request_timeout_in_ms", "range_request_timeout_in_ms" in cassandra.yaml, but still query timesout in 10secs.

Is there anyway we can increase the timeout value to 1-2 mins ?

Sample Product Table Schema:
 - product_id string (primary key)
 - product_name string
 - created_on timestamp (secondary index)
 - updated_on timestamp

Requirement: I want to query all the product which are created a particular day using 'created_on' field.

Sample Query: select * from "Product" where created_on > 1632906232 AND created_on < 1632906232

Note: Query uses the secondary index field in filter.

Environment details: Cassandra database with 2 node cluster setup.

Answer 1

The underlying problem is that range queries is expensive which is why it takes so long to complete. By the way, it looks like you posted the wrong query because you have the same value.

The default timeouts are in place to prevent nodes from getting overloaded by expensive queries so they don't go down. Increasing the server-side timeouts is not the right approach. And in your case, it's most likely the client-side timeout getting triggered.

You need to review your data model and create table instead partitioned by the creation date so it will perform better. Cheers!