[
https://issues.apache.org/jira/browse/CASSANDRA-21836?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18138479#comment-18138479
]
Caleb Rackliffe edited comment on CASSANDRA-21836 at 11/5/26, 3:47 AM:
-----------------------------------------------------------------------
When a query ANDs two SAI predicates on non-primary-key (i.e. mutable) columns
and runs at a CL that requires reconciliation (anything but ONE/LOCAL_ONE), we
use a concept called non-strict filtering (see {{{}RowFilter.isStrict(){}}}).
We recognized in CASSANDRA-19018 that a single replica can't safely intersect
two mutable columns' indexes locally, because an unrepaired row can have the
two matching values split across replicas, and a strict local AND could
silently drop a row that only matches after coordinator-side reconciliation.
This is why {{QueryController.getIndexQueryResults()}} builds a
{{KeyRangeUnionIterator}} instead of a {{KeyRangeIntersectionIterator}} for the
unrepaired group, and leaves the real AND to be applied after merging.
This works, but it can really perform poorly when faced with extremely
non-selective predicates. If one side is not selective at all (ex. score >= 0,
which matches every row in the description above), the union is effectively the
whole table. {{ReplicaFilteringProtection.consumeEntirePartitions}} then has to
walk and validate that whole candidate set partition by partition, and every
partition that fails the real AND still costs a
ShortReadRowsProtection/ShortReadPartitionsProtection retry that re-enters
{{{}StorageAttachedIndexSearcher{}}}. That's cheap against Memtable indexes,
but not cheap against on-disk segments, and the cumulative cost of thousands of
these retries is what trips the read timeout.
tl;dr Non-strict mode has no cost bound tied to how large the degraded union
actually is.
On 6.0+, CASSANDRA-18112 allows us to select indexes to use at query time. In
this case we can use {{excluded_indexes = \{score_idx} with ALLOW FILTERING}}
to bypass the useless non-selective predicate on {{score}} and return a much
more reasonable set of rows to the coordinator. On 5.0, we don't have index
hints, so it's a little more annoying. You can just not index that column,
leave the predicate out of the CQL query and filter at the client, or drop to
ONE/LOCAL_ONE.
The core of the issue here is the lack of a more intelligent query planner that
can determine predicate selectivity up-front and automatically do what
{{exclude_indexes}} does without being explicitly asked. If that can be done
cheaply, it might be a reasonable mitigation for this class of problematic
queries. I'm going to dig into that a bit, but for now, we can say with
relative certainty that there isn't a pressing correctness or data loss issue
here.
was (Author: maedhroz):
When a query ANDs two SAI predicates on non-primary-key (i.e. mutable) columns
and runs at a CL that requires reconciliation (anything but ONE/LOCAL_ONE), we
use a concept called non-strict filtering (see {{RowFilter.isStrict()}}). We
recognized in CASSANDRA-19018 that a single replica can't safely intersect two
mutable columns' indexes locally, because an unrepaired row can have the two
matching values split across replicas, and a strict local AND could silently
drop a row that only matches after coordinator-side reconciliation. This is why
{{QueryController.getIndexQueryResults()}} builds a {{KeyRangeUnionIterator}}
instead of a {{KeyRangeIntersectionIterator}} for the unrepaired group, and
leaves the real AND to be applied after merging.
This works, but it can really perform poorly when faced with extremely
non-selective predicates. If one side is not selective at all (ex. score >= 0,
which matches every row in the description above), the union is effectively the
whole table. {{ReplicaFilteringProtection.consumeEntirePartitions}} then has to
walk and validate that whole candidate set partition by partition, and every
partition that fails the real AND still costs a
ShortReadRowsProtection/ShortReadPartitionsProtection retry that re-enters
{{StorageAttachedIndexSearcher}}. That's cheap against Memtable indexes, but
not cheap against on-disk segments, and the cumulative cost of thousands of
these retries is what trips the read timeout.
tl;dr Non-strict mode has no cost bound tied to how large the degraded union
actually is.
On 6.0+, CASSANDRA-18112 allows us to select indexes to use at query time. In
this case we can use {{excluded_indexes = {score_idx} with ALLOW FILTERING}} to
bypass the useless non-selective predicate on {{score}} and return a much more
reasonable set of rows to the coordinator. On 5.0, we don't have index hints,
so it's a little more annoying. You can just not index that column, leave the
predicate out of the CQL query and filter at the client, or drop to
ONE/LOCAL_ONE.
The core of the issue here is the lack of a more intelligent query planner that
can determine predicate selectivity up-front and automatically do what
{{exclude_indexes}} does without being explicitly asked. If that can be done
cheaply, it might be a reasonable mitigation for this class of problematic
queries. I'm going to dig into that a bit, but for now, we can say with
relative certainty that there isn't a pressing correctness or data loss issue
here.
> A paged SELECT that intersects two SAI indexes with AND times out when the
> consistency level requires more than one replica. Once the data is on disk,
> the same query returns immediately at ONE or when it is not paged.
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: CASSANDRA-21836
> URL: https://issues.apache.org/jira/browse/CASSANDRA-21836
> Project: Apache Cassandra
> Issue Type: Bug
> Components: Feature/SAI
> Reporter: Diego Sevilla Ruiz
> Assignee: Caleb Rackliffe
> Priority: Normal
> Attachments: docker-compose.yml, reproduce.py, reproduce.sh
>
>
> h3. Environment
> * Cassandra 5.0.9 (Docker image cassandra:5.0.9), the latest 5.0 release on
> 2026-11-01
> * 2 nodes, 16 tokens each, one datacenter, GossipingPropertyFileSnitch
> * Keyspace with SimpleStrategy, RF = 2
> * Python driver 3.30.1, and cqlsh
> * Reproduced with both cassandra.yaml and cassandra_latest.yaml (BIG and BTI
> formats)
>
> h3. Steps to reproduce
> The attached reproduce.sh runs the whole sequence with the attached
> docker-compose.yml and reproduce.py, and exits with status 1 when the timeout
> occurs. By hand:
> {{CREATE KEYSPACE ks WITH replication = \{'class': 'SimpleStrategy',
> 'replication_factor': 2};}}
> {{CREATE TABLE ks.q (id bigint, d timestamp, owner bigint, score int, title
> text, PRIMARY KEY (id, d));}}
> {{CREATE INDEX q_owner ON ks.q (owner) USING 'sai';}}
> {{CREATE INDEX q_score ON ks.q (score) USING 'sai';}}
> Insert 20,000 rows at LOCAL_QUORUM, with id = i, owner = i % 5000, score = i
> % 50, a fixed d and a short title. Wait for the indexes to be queryable and
> run nodetool flush ks on both nodes. Then, in cqlsh:
> {{CONSISTENCY LOCAL_QUORUM;}}
> {{SELECT id FROM ks.q WHERE owner = 7 AND score >= 0;}}
> h3. Expected
> 4 rows, as returned at ONE.
> h3. Actual
> {{{}ReadTimeout: Error from server: code=1200 [Coordinator node timed out
> waiting for replica nodes' responses] message="Operation timed out - received
> only 0 responses." info={'consistency': 'ONE', 'required_responses': 1,
> 'received_responses': 0{}}}}
> The error reports ONE although the query ran at LOCAL_QUORUM.
> h3. Results
> Synthetic data, randomly chosen owner values, LOCAL_QUORUM, page size 5000,
> owner = ? AND score >= 0:
> ||Rows||Indexes created||Configuration||Result||
> |100,000|after the load|cassandra.yaml|40 of 40 ReadTimeout|
> |100,000|after the load|cassandra_latest.yaml|40 of 40 ReadTimeout|
> |100,000|before the load|cassandra.yaml|40 of 40 ReadTimeout|
> |20,000|before the load|cassandra.yaml|40 of 40 ReadTimeout|
> |20,000|after the load|cassandra.yaml|40 of 40 ReadTimeout|
> In the 100,000 and 20,000 row runs with cassandra.yaml, the same owners
> returned correctly at ONE and at LOCAL_QUORUM without paging.
> Real dataset (about 197,000 rows, cassandra_latest.yaml, query of the same
> shape on owneruserid and score): 25 of 25 randomly chosen owners failed.
> h3. Trace
> The following sequence repeats, alternating between the two replicas, until
> the timeout. Taken from the real dataset:
> {{Executing read on stackoverflow.questions using indexes
> questions_owneruserid_sai,questions_score_sai}}
> {{Read 0 live rows and 0 tombstone cells}}
> {{Index query accessed memtable indexes, 6 SSTable indexes, and 6 segments,
> post-filtered 0 rows in 0 partitions, and took ...}}
> {{Requesting 5000 extra rows from
> Full(/172.20.0.2:7070,(8878228422593045259,-9114996066678673620]) for short
> read protection}}
> The requested range is the one that wraps around the ring. I do not know
> whether that is significant.
> h3. Other observations
> Real dataset, same query shape:
> ||Variation||Result||
> |LOCAL_QUORUM, QUORUM or TWO, paged|ReadTimeout|
> |Page size 10|ReadTimeout|
> |LIMIT 2|ReadTimeout|
> |LIMIT 10|ReadTimeout, after 10 s instead of 5 s|
> |ONE or LOCAL_ONE, paged|OK, a few milliseconds|
> |LOCAL_QUORUM, not paged|OK, 0.8 s|
> |Adding ALLOW FILTERING|ReadTimeout|
> |Equality instead of a range: owneruserid = 120 AND score = 3|ReadTimeout|
> |Both conditions, but no row matches both|OK|
> |One SAI index plus a non-indexed column with ALLOW FILTERING|OK|
> |One SAI index alone|OK|
> Synthetic data, 20,000 rows, cqlsh at LOCAL_QUORUM:
> ||Query||Page size 100 or 5000||Paging off||
> |owner = 7 AND score >= 0|ReadTimeout|OK, 4 rows|
> |owner = 7 AND score = 7|OK, 4 rows|OK, 4 rows|
> So equality did not fail on the synthetic data, where every row of an owner
> matches it, but did fail on the real dataset, where 1 of the owner's 4 rows
> matches. The cause of that difference has not been isolated.
> Before a flush, on 20,000 rows held in memtables, the equality query returned
> correctly. A separate test with partition key plus one SAI index also
> returned correctly at LOCAL_QUORUM, with the data in memory and on disk.
> h3. Not tested
> * trunk or any version other than 5.0.9
> * A single node or RF = 1
> * More than two nodes
> h3. Workaround
> Use ONE or LOCAL_ONE, disable paging for the query, or avoid intersecting two
> SAI indexes.
> h3. Related
> A web search found no ticket describing this loop. CASSANDRA-19007 covers
> multi-column filtering on out-of-sync replicas and may be related.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
