You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Phil Luckhurst <ph...@powerassure.com> on 2014/06/11 11:24:02 UTC
RPC timeout paging secondary index query results
Is paging through the results of a secondary index query broken in Cassandra
2.0.7 or are we doing something wrong?
We have table with a few hundred thousand records and an indexed
low-cardinality column. The relevant bits of the table definition are shown
below
CREATE TABLE measurement (
measurement_id uuid,
controller_id uuid,
...
PRIMARY KEY (measurement_id)
);
CREATE INDEX ON measurement(controller_id);
We originally got the timeout when trying to page through the results of a
'SELECT * FROM measurement WHERE controller_id = xxx-xxx-xxx' query using
the java driver 2.0.2 but we can also consistently reproduce the problem
with CQLSH.
In CQLSH we can start paging through the measurement_id entries a 1000 at a
time for a specific controller_id by using the token() method, e.g.
SELECT measurement_id, token(measurement_id) FROM measurement WHERE
controller_id = 0167bfa6-0918-47ba-8b65-dcccecbcd79f AND
token(measurement_id) >= -8975013189301561463 LIMIT 1000;
This works for 8 queries but consistently fails with an RPC timeout for rows
8000-9000. If from row 8000 we start using a smaller LIMIT size we can get
to approx row 8950 but at that point we get the timeout even if we set
'LIMIT 10'. Looking at the trace output it seems to be seems to be doing
thousands of queries on the index table for every request even if we set
'LIMIT 1' - almost as if it's starting from the beginning of the index for
each page request?
It all seems very similar to CASSANDRA-5975
<https://issues.apache.org/jira/browse/CASSANDRA-5975> but that is marked
as resolved in Cassandra 2.0.1. For example this query for a single record
SELECT measurement_id, token(measurement_id) FROM measurement WHERE
controller_id = 0167bfa6-0918-47ba-8b65-dcccecbcd79f AND
token(measurement_id) = -8947401969768490998;
works fine and produces approx 60 lines of trace output. If we simply add
'LIMIT 1' to the statement the trace output is approx 70,000 lines!
It looks like we may have to give up on using secondary indexes but it would
be nice to know if what we are trying to do is correct and should work.
Thanks
Phil
--
View this message in context: http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/RPC-timeout-paging-secondary-index-query-results-tp7595078.html
Sent from the cassandra-user@incubator.apache.org mailing list archive at Nabble.com.
Re: RPC timeout paging secondary index query results
Posted by Phil Luckhurst <ph...@powerassure.com>.
Ken Hancock wrote
> You didn't post any timings, only when it started failing so it's unclear
> whether performance is dropping off or scaling in some sort of linear or
> non-linear fashion. Second the recommendation to do some traces which
> should be much more telling.
I'm afraid I've not yet had time to pursue this any further. I take your
point about the traces but the fact performance drops off so quickly to the
point where we can't complete any queries means that in this case secondary
indexes as they stand are not going to work for us so we are going to have
to rework our data model to avoid them.
--
View this message in context: http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/RPC-timeout-paging-secondary-index-query-results-tp7595078p7595486.html
Sent from the cassandra-user@incubator.apache.org mailing list archive at Nabble.com.
Re: RPC timeout paging secondary index query results
Posted by Ken Hancock <ke...@schange.com>.
You didn't post any timings, only when it started failing so it's unclear
whether performance is dropping off or scaling in some sort of linear or
non-linear fashion. Second the recommendation to do some traces which
should be much more telling.
On Fri, Jun 13, 2014 at 3:34 AM, Phil Luckhurst <
phil.luckhurst@powerassure.com> wrote:
> But would you expect performance to drop off so quickly? At 250,000 records
> we can still page through the query with LIMIT 50000 but when adding an
> additional 50,000 records we can't page past the first 10,000 records even
> if we drop to LIMIT 10.
>
> What about the case where we add 100,000 records for each indexed value?
> When we do this for 2 values, i.e. 200,000 records with 2 indexed values,
> we
> can query all 100,000 records for one of the values using LIMIT 100000. If
> we add a third indexed value with another 100,000 records then we can't
> page
> through any of the indexed values even though the original 2 that worked
> previously have not changed.
>
> Phil
>
>
>
> --
> View this message in context:
> http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/RPC-timeout-paging-secondary-index-query-results-tp7595078p7595126.html
> Sent from the cassandra-user@incubator.apache.org mailing list archive at
> Nabble.com.
>
Re: RPC timeout paging secondary index query results
Posted by Phil Luckhurst <ph...@powerassure.com>.
But would you expect performance to drop off so quickly? At 250,000 records
we can still page through the query with LIMIT 50000 but when adding an
additional 50,000 records we can't page past the first 10,000 records even
if we drop to LIMIT 10.
What about the case where we add 100,000 records for each indexed value?
When we do this for 2 values, i.e. 200,000 records with 2 indexed values, we
can query all 100,000 records for one of the values using LIMIT 100000. If
we add a third indexed value with another 100,000 records then we can't page
through any of the indexed values even though the original 2 that worked
previously have not changed.
Phil
--
View this message in context: http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/RPC-timeout-paging-secondary-index-query-results-tp7595078p7595126.html
Sent from the cassandra-user@incubator.apache.org mailing list archive at Nabble.com.
Re: RPC timeout paging secondary index query results
Posted by Robert Coli <rc...@eventbrite.com>.
On Thu, Jun 12, 2014 at 9:18 AM, Phil Luckhurst <
phil.luckhurst@powerassure.com> wrote:
> The problem appears to be directly related to number of entries in the
> index.
> I started with an empty table and added 50,000 entries at a time with the
> same indexed value.
All requests in Cassandra are subject to timeouts which default to small
numbers of seconds.
Secondary index queries are slower than non-secondary index queries. Low
cardinality is not a great case for them.
If you turn on tracing, it might inform your performance picture. My hunch
is that you are not seeing a bug, you are seeing the inevitable performance
characteristics of a very low cardinality Cassandra Secondary Index.
=Rob
Re: RPC timeout paging secondary index query results
Posted by Phil Luckhurst <ph...@powerassure.com>.
The problem appears to be directly related to number of entries in the index.
I started with an empty table and added 50,000 entries at a time with the
same indexed value. I was able to page through the results of a query that
used the secondary index with 250,000 records in the table using a LIMIT
50000 clause. When I added another 50,000 to take it up to 300,000 entries I
started getting the RPC timeout and could not page past the first 10,000
records even if I dropped the LIMIT to 10 records at a time.
I then changed the test slightly and added two batches of 100,000 records
with two different indexed values. With 200,000 records in total I could
query all 100,000 records for one of the indexed values with a single query
with LIMIT 100000. However, as soon as I added a third batch of 100,000
records with a different indexed value the queries again started to timeout
so that I couldn't page through the results even with a smaller LIMIT size.
It appears that in our configuration a secondary index can start to fail
when it reaches approx 300,000 entries. I'll open a JIRA.
--
View this message in context: http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/RPC-timeout-paging-secondary-index-query-results-tp7595078p7595110.html
Sent from the cassandra-user@incubator.apache.org mailing list archive at Nabble.com.
Re: RPC timeout paging secondary index query results
Posted by Robert Coli <rc...@eventbrite.com>.
On Wed, Jun 11, 2014 at 12:43 PM, Phil Luckhurst <
phil.luckhurst@powerassure.com> wrote:
> It just seems that what we are trying to do here is
> such basic functionality of an index that I thought we must be doing
> something wrong for it to appear to be this broken.
>
To be clear, I did not read or assess your issue, it may in fact be a bug
in Secondary Indexes. If you can repro reliably, I would file a JIRA to
determine if it is or not..
=Rob
Re: RPC timeout paging secondary index query results
Posted by Phil Luckhurst <ph...@powerassure.com>.
Thanks Rob.
I understand that we will probably end up either creating our own index or
duplicating the data and we have done that to remove a reliance on secondary
indexes in other places. It just seems that what we are trying to do here is
such basic functionality of an index that I thought we must be doing
something wrong for it to appear to be this broken.
Phil
--
View this message in context: http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/RPC-timeout-paging-secondary-index-query-results-tp7595078p7595092.html
Sent from the cassandra-user@incubator.apache.org mailing list archive at Nabble.com.
Re: RPC timeout paging secondary index query results
Posted by DuyHai Doan <do...@gmail.com>.
I like the "- Provides the illusion that you are using a RDBMS." part ;-)
On Wed, Jun 11, 2014 at 8:52 PM, Robert Coli <rc...@eventbrite.com> wrote:
> On Wed, Jun 11, 2014 at 2:24 AM, Phil Luckhurst <
> phil.luckhurst@powerassure.com> wrote:
>
>> Is paging through the results of a secondary index query broken in
>> Cassandra
>> 2.0.7 or are we doing something wrong?
>>
>
> General feedback on questions of this type :
>
>
> http://mail-archives.apache.org/mod_mbox/incubator-cassandra-user/201405.mbox/%3CCAEDUwd1i2BwJ-PAFE1qhjQFZ=qz2vA_vXWo_jDYCmS8EvkBSLQ@mail.gmail.com%3E
>
> =Rob
>
>
Re: RPC timeout paging secondary index query results
Posted by Robert Coli <rc...@eventbrite.com>.
On Wed, Jun 11, 2014 at 2:24 AM, Phil Luckhurst <
phil.luckhurst@powerassure.com> wrote:
> Is paging through the results of a secondary index query broken in
> Cassandra
> 2.0.7 or are we doing something wrong?
>
General feedback on questions of this type :
http://mail-archives.apache.org/mod_mbox/incubator-cassandra-user/201405.mbox/%3CCAEDUwd1i2BwJ-PAFE1qhjQFZ=qz2vA_vXWo_jDYCmS8EvkBSLQ@mail.gmail.com%3E
=Rob