You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by shahab <sh...@gmail.com> on 2014/03/25 16:36:02 UTC
Why "select count("*) from .." hangs ?
Hi,
I am quite new to Cassandra and trying to evaluate its feasibility for our
application.
In our application, we need to insert roughly 300000 sensor data every 30
seconds (basically we need to store time-series data). I wrote a simple
java code to insert 300000 random data every 30 seconds for 10 iterations,
and measured the number of entries in the table after each insertion.
But after iteration 8, (i.e. inserting 1500000 sensor data), the "select
count(') ...) throws time-out exception and doesn't work anymore. I even
tried to execute "select count(*)..." using Datastax DevCenter GUI, but I
got same result.
I am sure that I have missed something or misunderstood how Cassandra
works, but don't know really what? I do appreciate any hints.
best,
/Shahab
Re: Why "select count("*) from .." hangs ?
Posted by Arthur Zubarev <Ar...@Aol.com>.
I faced the same nuance in my early days with C*, specifically I got RPC timeouts on selecting data from CFs larger than 300 GB.
The typical remedy is to implement paging. So instead of using the CLI resort to a custom built client app.
Regards,
Arthur
From: shahab
Sent: Wednesday, March 26, 2014 4:34 AM
To: user@cassandra.apache.org
Subject: Re: Why "select count("*) from .." hangs ?
Thanks for the hints. I got a better picture of how to deal with "count" queries.
On Tue, Mar 25, 2014 at 7:01 PM, Robert Coli <rc...@eventbrite.com> wrote:
On Tue, Mar 25, 2014 at 8:36 AM, shahab <sh...@gmail.com> wrote:
But after iteration 8, (i.e. inserting 1500000 sensor data), the "select count(') ...) throws time-out exception and doesn't work anymore. I even tried to execute "select count(*)..." using Datastax DevCenter GUI, but I got same result.
All operations in Cassandra are subject to (various) timeouts, which by default are in the scale of single digit seconds.
If you attempt to do an operation (such as aggregates across large numbers of large objects) which cannot complete in this time, this is a strong indication that either your overall approach is inappropriate or, at very least, that your buckets are too large.
=Rob
Re: Why "select count("*) from .." hangs ?
Posted by shahab <sh...@gmail.com>.
Thanks for the hints. I got a better picture of how to deal with "count"
queries.
On Tue, Mar 25, 2014 at 7:01 PM, Robert Coli <rc...@eventbrite.com> wrote:
> On Tue, Mar 25, 2014 at 8:36 AM, shahab <sh...@gmail.com> wrote:
>
>> But after iteration 8, (i.e. inserting 1500000 sensor data), the
>> "select count(') ...) throws time-out exception and doesn't work anymore.
>> I even tried to execute "select count(*)..." using Datastax DevCenter GUI,
>> but I got same result.
>>
>
> All operations in Cassandra are subject to (various) timeouts, which by
> default are in the scale of single digit seconds.
>
> If you attempt to do an operation (such as aggregates across large numbers
> of large objects) which cannot complete in this time, this is a strong
> indication that either your overall approach is inappropriate or, at very
> least, that your buckets are too large.
>
> =Rob
>
>
Re: Why "select count("*) from .." hangs ?
Posted by Robert Coli <rc...@eventbrite.com>.
On Tue, Mar 25, 2014 at 8:36 AM, shahab <sh...@gmail.com> wrote:
> But after iteration 8, (i.e. inserting 1500000 sensor data), the "select
> count(') ...) throws time-out exception and doesn't work anymore. I even
> tried to execute "select count(*)..." using Datastax DevCenter GUI, but I
> got same result.
>
All operations in Cassandra are subject to (various) timeouts, which by
default are in the scale of single digit seconds.
If you attempt to do an operation (such as aggregates across large numbers
of large objects) which cannot complete in this time, this is a strong
indication that either your overall approach is inappropriate or, at very
least, that your buckets are too large.
=Rob
Re: Why "select count("*) from .." hangs ?
Posted by "ssivikt@gmail.com" <ss...@gmail.com>.
Hi,
What is your hardware, C* version, data structure and typical data size ?
On 03/25/2014 06:36 PM, shahab wrote:
> Hi,
>
> I am quite new to Cassandra and trying to evaluate its feasibility
> for our application.
> In our application, we need to insert roughly 300000 sensor data every
> 30 seconds (basically we need to store time-series data). I wrote a
> simple java code to insert 300000 random data every 30 seconds for 10
> iterations, and measured the number of entries in the table after
> each insertion.
> But after iteration 8, (i.e. inserting 1500000 sensor data), the
> "select count(') ...) throws time-out exception and doesn't work
> anymore. I even tried to execute "select count(*)..." using Datastax
> DevCenter GUI, but I got same result.
>
> I am sure that I have missed something or misunderstood how Cassandra
> works, but don't know really what? I do appreciate any hints.
>
> best,
> /Shahab
>
RE: Why "select count("*) from .." hangs ?
Posted by Pieter Callewaert <pi...@be-mobile.be>.
Hi Shalab,
Are you using anything in your WHERE clause of the query?
If not, you are doing a full scan of your data. In iteration 8 it will scan 1 500 000 entries, and the default time out value is pretty low.
If you do select count(*) from traffic_by_day where segment_id = 1 and day = 1 it should still work (and very fast).
If you really need the count your data is to insert it, and then increment a counter with the number of records you inserted.
That way insteady of doing a count(*) you just get the current counter value.
Kind regards,
Pieter
From: shahab [mailto:shahab.mokari@gmail.com]
Sent: dinsdag 25 maart 2014 17:19
To: user@cassandra.apache.org
Subject: Re: Why "select count("*) from .." hangs ?
Thanks.
I run it on a Linux Server, Dual Processor, Intel(R) Xeon(R) CPU E5440 @ 2.83GHz, 4 core each and 8 GM RAM.
Just to give an example of data inserted:
INSERT INTO traffic_by_day(segment_id, day, event_time, traffic_value)
VALUES (100, 84, '2013-04-03 07:02:00', 79);
Here is the schema:
CREATE TABLE traffic_by_day (
segment_id int,
day int,
event_time timestamp,
traffic_value int,
PRIMARY KEY ((segment_id, day), event_time)
) WITH
bloom_filter_fp_chance=0.010000 AND
caching='KEYS_ONLY' AND
comment='' AND
dclocal_read_repair_chance=0.000000 AND
gc_grace_seconds=864000 AND
index_interval=128 AND
read_repair_chance=0.100000 AND
replicate_on_write='true' AND
populate_io_cache_on_flush='false' AND
default_time_to_live=0 AND
speculative_retry='99.0PERCENTILE' AND
memtable_flush_period_in_ms=0 AND
compaction={'class': 'SizeTieredCompactionStrategy'} AND
compression={'sstable_compression': 'LZ4Compressor'};
On Tue, Mar 25, 2014 at 4:58 PM, Michael Shuler <mi...@pbandjelly.org>> wrote:
On 03/25/2014 10:36 AM, shahab wrote:
In our application, we need to insert roughly 300000 sensor data
every 30 seconds (basically we need to store time-series data). I
wrote a simple java code to insert 300000 random data every 30
seconds for 10 iterations, and measured the number of entries in the
table after each insertion. But after iteration 8, (i.e. inserting
1500000 sensor data), the "select count(') ...) throws time-out
exception and doesn't work anymore. I even tried to execute "select
count(*)..." using Datastax DevCenter GUI, but I got same result.
If you could post your schema, folks may be able to help a bit better. Your C* version couldn't hurt.
cqlsh> DESC KEYSPACE $your_ks;
--
Kind regards,
Michael
Re: Why "select count("*) from .." hangs ?
Posted by shahab <sh...@gmail.com>.
Thanks.
I run it on a Linux Server, Dual Processor, Intel(R) Xeon(R) CPU E5440 @
2.83GHz, 4 core each and 8 GM RAM.
Just to give an example of data inserted:
INSERT INTO traffic_by_day(segment_id, day, event_time, traffic_value)
VALUES (100, 84, '2013-04-03 07:02:00', 79);
Here is the schema:
CREATE TABLE traffic_by_day (
segment_id int,
day int,
event_time timestamp,
traffic_value int,
PRIMARY KEY ((segment_id, day), event_time)
) WITH
bloom_filter_fp_chance=0.010000 AND
caching='KEYS_ONLY' AND
comment='' AND
dclocal_read_repair_chance=0.000000 AND
gc_grace_seconds=864000 AND
index_interval=128 AND
read_repair_chance=0.100000 AND
replicate_on_write='true' AND
populate_io_cache_on_flush='false' AND
default_time_to_live=0 AND
speculative_retry='99.0PERCENTILE' AND
memtable_flush_period_in_ms=0 AND
compaction={'class': 'SizeTieredCompactionStrategy'} AND
compression={'sstable_compression': 'LZ4Compressor'};
On Tue, Mar 25, 2014 at 4:58 PM, Michael Shuler <mi...@pbandjelly.org>wrote:
> On 03/25/2014 10:36 AM, shahab wrote:
>
>> In our application, we need to insert roughly 300000 sensor data
>> every 30 seconds (basically we need to store time-series data). I
>> wrote a simple java code to insert 300000 random data every 30
>> seconds for 10 iterations, and measured the number of entries in the
>> table after each insertion. But after iteration 8, (i.e. inserting
>> 1500000 sensor data), the "select count(') ...) throws time-out
>> exception and doesn't work anymore. I even tried to execute "select
>> count(*)..." using Datastax DevCenter GUI, but I got same result.
>>
>
> If you could post your schema, folks may be able to help a bit better.
> Your C* version couldn't hurt.
>
> cqlsh> DESC KEYSPACE $your_ks;
>
> --
> Kind regards,
> Michael
>
Re: Why "select count("*) from .." hangs ?
Posted by Michael Shuler <mi...@pbandjelly.org>.
On 03/25/2014 10:36 AM, shahab wrote:
> In our application, we need to insert roughly 300000 sensor data
> every 30 seconds (basically we need to store time-series data). I
> wrote a simple java code to insert 300000 random data every 30
> seconds for 10 iterations, and measured the number of entries in the
> table after each insertion. But after iteration 8, (i.e. inserting
> 1500000 sensor data), the "select count(') ...) throws time-out
> exception and doesn't work anymore. I even tried to execute "select
> count(*)..." using Datastax DevCenter GUI, but I got same result.
If you could post your schema, folks may be able to help a bit better.
Your C* version couldn't hurt.
cqlsh> DESC KEYSPACE $your_ks;
--
Kind regards,
Michael