Re: Count(*) is not working

2017-02-20 Thread Sylvain Lebresne 
I guess I misspoke, sorry. It is true that count() as any other query is
still governed by the read timeout and any count that has to process a lot
of data will take a long time and will require a high timeout set to not
timeout (true of every aggregation query as it happens).

I guess I responded too quickly to "if you want a reliable count" because
for a while count() was actually OOMing for large partition/queries, which
is not true anymore, and my brain made a connection that wasn't there since
that's not what Kurt said.

So sorry for the noise.

On Mon, Feb 20, 2017 at 2:47 PM, Benjamin Roth 
wrote:

> +1 I also encountered timeouts many many times (using DS DevCenter).
> Roughly this occured when count(*) > 1.000.000
>
> 2017-02-20 14:42 GMT+01:00 Edward Capriolo :
>
>> Seems worth it to file a bug since some here are under the impression it
>> almost always works and others are under the impression it almost never
>> works.
>>
>> On Friday, February 17, 2017, kurt greaves  wrote:
>>
>>> really... well that's good to know. it still almost never works though.
>>> i guess every time I've seen it it must have timed out due to tombstones.
>>>
>>> On 17 Feb. 2017 22:06, "Sylvain Lebresne"  wrote:
>>>
>>> On Fri, Feb 17, 2017 at 11:54 AM, kurt greaves 
>>> wrote:
>>>
 if you want a reliable count, you should use spark. performing a count
 (*) will inevitably fail unless you make your server read timeouts and
 tombstone fail thresholds ridiculous

>>>
>>> That's just not true. count(*) is paged internally so while it is not
>>> particular fast, it shouldn't require bumping neither the read timeout nor
>>> the tombstone fail threshold in any way to work.
>>>
>>> In that case, it seems the partition does have many tombstones (more
>>> than live rows) and so the tombstone threshold is doing its job of warning
>>> about it.
>>>
>>>

 On 17 Feb. 2017 04:34, "Jan"  wrote:

> Hi,
>
> could you post the output of nodetool cfstats for the table?
>
> Cheers,
>
> Jan
>
> Am 16.02.2017 um 17:00 schrieb Selvam Raman:
>
> I am not getting count as result. Where i keep on getting n number of
> results below.
>
> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
> LIMIT 100 (see tombstone_warn_threshold)
>
> On Thu, Feb 16, 2017 at 12:37 PM, Jan Kesten  wrote:
>
>> Hi,
>>
>> do you got a result finally?
>>
>> Those messages are simply warnings telling you that c* had to read
>> many tombstones while processing your query - rows that are deleted but 
>> not
>> garbage collected/compacted. This warning gives you some explanation why
>> things might be much slower than expected because per 100 rows that count
>> c* had to read about 15 times rows that were deleted already.
>>
>> Apart from that, count(*) is almost always slow - and there is a
>> default limit of 10.000 rows in a result.
>>
>> Do you really need the actual live count? To get a idea you can
>> always look at nodetool cfstats (but those numbers also contain deleted
>> rows).
>>
>>
>> Am 16.02.2017 um 13:18 schrieb Selvam Raman:
>>
>> Hi,
>>
>> I want to know the total records count in table.
>>
>> I fired the below query:
>>select count(*) from tablename;
>>
>> and i have got the below output
>>
>> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
>> keysace.table WHERE token(id) > 
>> token(test:ODP0144-0883E-022R-002/047-052)
>> LIMIT 100 (see tombstone_warn_threshold)
>>
>> Read 100 live rows and 1435 tombstone cells for query SELECT * FROM
>> keysace.table WHERE token(id) > token(test:2565-AMK-2) LIMIT 100 (see
>> tombstone_warn_threshold)
>>
>> Read 96 live rows and 1385 tombstone cells for query SELECT * FROM
>> keysace.table WHERE token(id) > token(test:-2220-UV033/04) LIMIT 100 (see
>> tombstone_warn_threshold).
>>
>>
>>
>>
>> Can you please help me to get the total count of the table.
>>
>> --
>> Selvam Raman
>> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>>
>>
>
>
> --
> Selvam Raman
> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>
>
>
>>>
>>>
>>
>> --
>> Sorry this was sent from mobile. Will do less grammar and spell check
>> than usual.
>>
>
>
>
> --
> Benjamin Roth
> Prokurist
>
> Jaumo GmbH · www.jaumo.com
> Wehrstraße 46 · 73035 Göppingen · Germany
> Phone +49 7161 304880-6 <+49%207161%203048806> · Fax +49 7161 304880-1
> <+49%207161%203048801>
> AG Ulm · HRB 731058 · Managing Director: Jens Kammerer
>

Re: Count(*) is not working

2017-02-20 Thread Benjamin Roth 
+1 I also encountered timeouts many many times (using DS DevCenter).
Roughly this occured when count(*) > 1.000.000

2017-02-20 14:42 GMT+01:00 Edward Capriolo :

> Seems worth it to file a bug since some here are under the impression it
> almost always works and others are under the impression it almost never
> works.
>
> On Friday, February 17, 2017, kurt greaves  wrote:
>
>> really... well that's good to know. it still almost never works though. i
>> guess every time I've seen it it must have timed out due to tombstones.
>>
>> On 17 Feb. 2017 22:06, "Sylvain Lebresne"  wrote:
>>
>> On Fri, Feb 17, 2017 at 11:54 AM, kurt greaves 
>> wrote:
>>
>>> if you want a reliable count, you should use spark. performing a count
>>> (*) will inevitably fail unless you make your server read timeouts and
>>> tombstone fail thresholds ridiculous
>>>
>>
>> That's just not true. count(*) is paged internally so while it is not
>> particular fast, it shouldn't require bumping neither the read timeout nor
>> the tombstone fail threshold in any way to work.
>>
>> In that case, it seems the partition does have many tombstones (more than
>> live rows) and so the tombstone threshold is doing its job of warning about
>> it.
>>
>>
>>>
>>> On 17 Feb. 2017 04:34, "Jan"  wrote:
>>>
 Hi,

 could you post the output of nodetool cfstats for the table?

 Cheers,

 Jan

 Am 16.02.2017 um 17:00 schrieb Selvam Raman:

 I am not getting count as result. Where i keep on getting n number of
 results below.

 Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
 keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
 LIMIT 100 (see tombstone_warn_threshold)

 On Thu, Feb 16, 2017 at 12:37 PM, Jan Kesten  wrote:

> Hi,
>
> do you got a result finally?
>
> Those messages are simply warnings telling you that c* had to read
> many tombstones while processing your query - rows that are deleted but 
> not
> garbage collected/compacted. This warning gives you some explanation why
> things might be much slower than expected because per 100 rows that count
> c* had to read about 15 times rows that were deleted already.
>
> Apart from that, count(*) is almost always slow - and there is a
> default limit of 10.000 rows in a result.
>
> Do you really need the actual live count? To get a idea you can always
> look at nodetool cfstats (but those numbers also contain deleted rows).
>
>
> Am 16.02.2017 um 13:18 schrieb Selvam Raman:
>
> Hi,
>
> I want to know the total records count in table.
>
> I fired the below query:
>select count(*) from tablename;
>
> and i have got the below output
>
> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
> LIMIT 100 (see tombstone_warn_threshold)
>
> Read 100 live rows and 1435 tombstone cells for query SELECT * FROM
> keysace.table WHERE token(id) > token(test:2565-AMK-2) LIMIT 100 (see
> tombstone_warn_threshold)
>
> Read 96 live rows and 1385 tombstone cells for query SELECT * FROM
> keysace.table WHERE token(id) > token(test:-2220-UV033/04) LIMIT 100 (see
> tombstone_warn_threshold).
>
>
>
>
> Can you please help me to get the total count of the table.
>
> --
> Selvam Raman
> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>
>


 --
 Selvam Raman
 "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"



>>
>>
>
> --
> Sorry this was sent from mobile. Will do less grammar and spell check than
> usual.
>



-- 
Benjamin Roth
Prokurist

Jaumo GmbH · www.jaumo.com
Wehrstraße 46 · 73035 Göppingen · Germany
Phone +49 7161 304880-6 · Fax +49 7161 304880-1
AG Ulm · HRB 731058 · Managing Director: Jens Kammerer

Re: Count(*) is not working

2017-02-20 Thread Edward Capriolo 
Seems worth it to file a bug since some here are under the impression it
almost always works and others are under the impression it almost never
works.

On Friday, February 17, 2017, kurt greaves  wrote:

> really... well that's good to know. it still almost never works though. i
> guess every time I've seen it it must have timed out due to tombstones.
>
> On 17 Feb. 2017 22:06, "Sylvain Lebresne"  > wrote:
>
> On Fri, Feb 17, 2017 at 11:54 AM, kurt greaves  > wrote:
>
>> if you want a reliable count, you should use spark. performing a count
>> (*) will inevitably fail unless you make your server read timeouts and
>> tombstone fail thresholds ridiculous
>>
>
> That's just not true. count(*) is paged internally so while it is not
> particular fast, it shouldn't require bumping neither the read timeout nor
> the tombstone fail threshold in any way to work.
>
> In that case, it seems the partition does have many tombstones (more than
> live rows) and so the tombstone threshold is doing its job of warning about
> it.
>
>
>>
>> On 17 Feb. 2017 04:34, "Jan" > > wrote:
>>
>>> Hi,
>>>
>>> could you post the output of nodetool cfstats for the table?
>>>
>>> Cheers,
>>>
>>> Jan
>>>
>>> Am 16.02.2017 um 17:00 schrieb Selvam Raman:
>>>
>>> I am not getting count as result. Where i keep on getting n number of
>>> results below.
>>>
>>> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
>>> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
>>> LIMIT 100 (see tombstone_warn_threshold)
>>>
>>> On Thu, Feb 16, 2017 at 12:37 PM, Jan Kesten >> > wrote:
>>>
 Hi,

 do you got a result finally?

 Those messages are simply warnings telling you that c* had to read many
 tombstones while processing your query - rows that are deleted but not
 garbage collected/compacted. This warning gives you some explanation why
 things might be much slower than expected because per 100 rows that count
 c* had to read about 15 times rows that were deleted already.

 Apart from that, count(*) is almost always slow - and there is a
 default limit of 10.000 rows in a result.

 Do you really need the actual live count? To get a idea you can always
 look at nodetool cfstats (but those numbers also contain deleted rows).


 Am 16.02.2017 um 13:18 schrieb Selvam Raman:

 Hi,

 I want to know the total records count in table.

 I fired the below query:
select count(*) from tablename;

 and i have got the below output

 Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
 keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
 LIMIT 100 (see tombstone_warn_threshold)

 Read 100 live rows and 1435 tombstone cells for query SELECT * FROM
 keysace.table WHERE token(id) > token(test:2565-AMK-2) LIMIT 100 (see
 tombstone_warn_threshold)

 Read 96 live rows and 1385 tombstone cells for query SELECT * FROM
 keysace.table WHERE token(id) > token(test:-2220-UV033/04) LIMIT 100 (see
 tombstone_warn_threshold).




 Can you please help me to get the total count of the table.

 --
 Selvam Raman
 "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"


>>>
>>>
>>> --
>>> Selvam Raman
>>> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>>>
>>>
>>>
>
>

-- 
Sorry this was sent from mobile. Will do less grammar and spell check than
usual.

Re: Count(*) is not working

2017-02-17 Thread kurt greaves 
really... well that's good to know. it still almost never works though. i
guess every time I've seen it it must have timed out due to tombstones.

On 17 Feb. 2017 22:06, "Sylvain Lebresne"  wrote:

On Fri, Feb 17, 2017 at 11:54 AM, kurt greaves  wrote:

> if you want a reliable count, you should use spark. performing a count (*)
> will inevitably fail unless you make your server read timeouts and
> tombstone fail thresholds ridiculous
>

That's just not true. count(*) is paged internally so while it is not
particular fast, it shouldn't require bumping neither the read timeout nor
the tombstone fail threshold in any way to work.

In that case, it seems the partition does have many tombstones (more than
live rows) and so the tombstone threshold is doing its job of warning about
it.


>
> On 17 Feb. 2017 04:34, "Jan"  wrote:
>
>> Hi,
>>
>> could you post the output of nodetool cfstats for the table?
>>
>> Cheers,
>>
>> Jan
>>
>> Am 16.02.2017 um 17:00 schrieb Selvam Raman:
>>
>> I am not getting count as result. Where i keep on getting n number of
>> results below.
>>
>> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
>> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
>> LIMIT 100 (see tombstone_warn_threshold)
>>
>> On Thu, Feb 16, 2017 at 12:37 PM, Jan Kesten  wrote:
>>
>>> Hi,
>>>
>>> do you got a result finally?
>>>
>>> Those messages are simply warnings telling you that c* had to read many
>>> tombstones while processing your query - rows that are deleted but not
>>> garbage collected/compacted. This warning gives you some explanation why
>>> things might be much slower than expected because per 100 rows that count
>>> c* had to read about 15 times rows that were deleted already.
>>>
>>> Apart from that, count(*) is almost always slow - and there is a default
>>> limit of 10.000 rows in a result.
>>>
>>> Do you really need the actual live count? To get a idea you can always
>>> look at nodetool cfstats (but those numbers also contain deleted rows).
>>>
>>>
>>> Am 16.02.2017 um 13:18 schrieb Selvam Raman:
>>>
>>> Hi,
>>>
>>> I want to know the total records count in table.
>>>
>>> I fired the below query:
>>>select count(*) from tablename;
>>>
>>> and i have got the below output
>>>
>>> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
>>> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
>>> LIMIT 100 (see tombstone_warn_threshold)
>>>
>>> Read 100 live rows and 1435 tombstone cells for query SELECT * FROM
>>> keysace.table WHERE token(id) > token(test:2565-AMK-2) LIMIT 100 (see
>>> tombstone_warn_threshold)
>>>
>>> Read 96 live rows and 1385 tombstone cells for query SELECT * FROM
>>> keysace.table WHERE token(id) > token(test:-2220-UV033/04) LIMIT 100 (see
>>> tombstone_warn_threshold).
>>>
>>>
>>>
>>>
>>> Can you please help me to get the total count of the table.
>>>
>>> --
>>> Selvam Raman
>>> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>>>
>>>
>>
>>
>> --
>> Selvam Raman
>> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>>
>>
>>

Re: Count(*) is not working

2017-02-17 Thread Sagar Jambhulkar 
+1 for using spark for counts.

On Feb 17, 2017 4:25 PM, "kurt greaves"  wrote:

> if you want a reliable count, you should use spark. performing a count (*)
> will inevitably fail unless you make your server read timeouts and
> tombstone fail thresholds ridiculous
>
> On 17 Feb. 2017 04:34, "Jan"  wrote:
>
>> Hi,
>>
>> could you post the output of nodetool cfstats for the table?
>>
>> Cheers,
>>
>> Jan
>>
>> Am 16.02.2017 um 17:00 schrieb Selvam Raman:
>>
>> I am not getting count as result. Where i keep on getting n number of
>> results below.
>>
>> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
>> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
>> LIMIT 100 (see tombstone_warn_threshold)
>>
>> On Thu, Feb 16, 2017 at 12:37 PM, Jan Kesten  wrote:
>>
>>> Hi,
>>>
>>> do you got a result finally?
>>>
>>> Those messages are simply warnings telling you that c* had to read many
>>> tombstones while processing your query - rows that are deleted but not
>>> garbage collected/compacted. This warning gives you some explanation why
>>> things might be much slower than expected because per 100 rows that count
>>> c* had to read about 15 times rows that were deleted already.
>>>
>>> Apart from that, count(*) is almost always slow - and there is a default
>>> limit of 10.000 rows in a result.
>>>
>>> Do you really need the actual live count? To get a idea you can always
>>> look at nodetool cfstats (but those numbers also contain deleted rows).
>>>
>>>
>>> Am 16.02.2017 um 13:18 schrieb Selvam Raman:
>>>
>>> Hi,
>>>
>>> I want to know the total records count in table.
>>>
>>> I fired the below query:
>>>select count(*) from tablename;
>>>
>>> and i have got the below output
>>>
>>> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
>>> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
>>> LIMIT 100 (see tombstone_warn_threshold)
>>>
>>> Read 100 live rows and 1435 tombstone cells for query SELECT * FROM
>>> keysace.table WHERE token(id) > token(test:2565-AMK-2) LIMIT 100 (see
>>> tombstone_warn_threshold)
>>>
>>> Read 96 live rows and 1385 tombstone cells for query SELECT * FROM
>>> keysace.table WHERE token(id) > token(test:-2220-UV033/04) LIMIT 100 (see
>>> tombstone_warn_threshold).
>>>
>>>
>>>
>>>
>>> Can you please help me to get the total count of the table.
>>>
>>> --
>>> Selvam Raman
>>> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>>>
>>>
>>
>>
>> --
>> Selvam Raman
>> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>>
>>
>>

Re: Count(*) is not working

2017-02-17 Thread siddharth verma 
Hi,
We faced this issue too.
You could try with reduced paging size, so that tombstone threshold isn't
breached.

try using "paging 500" in cqlsh
[ https://docs.datastax.com/en/cql/3.3/cql/cql_reference/cqlshPaging.html ]

Similarly paging size could be set in java driver as well

This is a work around.
For this warning, do review your data model once.

Regards


On Fri, Feb 17, 2017 at 4:36 PM, Sylvain Lebresne 
wrote:

> On Fri, Feb 17, 2017 at 11:54 AM, kurt greaves 
> wrote:
>
>> if you want a reliable count, you should use spark. performing a count
>> (*) will inevitably fail unless you make your server read timeouts and
>> tombstone fail thresholds ridiculous
>>
>
> That's just not true. count(*) is paged internally so while it is not
> particular fast, it shouldn't require bumping neither the read timeout nor
> the tombstone fail threshold in any way to work.
>
> In that case, it seems the partition does have many tombstones (more than
> live rows) and so the tombstone threshold is doing its job of warning about
> it.
>
>
>>
>> On 17 Feb. 2017 04:34, "Jan"  wrote:
>>
>>> Hi,
>>>
>>> could you post the output of nodetool cfstats for the table?
>>>
>>> Cheers,
>>>
>>> Jan
>>>
>>> Am 16.02.2017 um 17:00 schrieb Selvam Raman:
>>>
>>> I am not getting count as result. Where i keep on getting n number of
>>> results below.
>>>
>>> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
>>> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
>>> LIMIT 100 (see tombstone_warn_threshold)
>>>
>>> On Thu, Feb 16, 2017 at 12:37 PM, Jan Kesten  wrote:
>>>
 Hi,

 do you got a result finally?

 Those messages are simply warnings telling you that c* had to read many
 tombstones while processing your query - rows that are deleted but not
 garbage collected/compacted. This warning gives you some explanation why
 things might be much slower than expected because per 100 rows that count
 c* had to read about 15 times rows that were deleted already.

 Apart from that, count(*) is almost always slow - and there is a
 default limit of 10.000 rows in a result.

 Do you really need the actual live count? To get a idea you can always
 look at nodetool cfstats (but those numbers also contain deleted rows).


 Am 16.02.2017 um 13:18 schrieb Selvam Raman:

 Hi,

 I want to know the total records count in table.

 I fired the below query:
select count(*) from tablename;

 and i have got the below output

 Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
 keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
 LIMIT 100 (see tombstone_warn_threshold)

 Read 100 live rows and 1435 tombstone cells for query SELECT * FROM
 keysace.table WHERE token(id) > token(test:2565-AMK-2) LIMIT 100 (see
 tombstone_warn_threshold)

 Read 96 live rows and 1385 tombstone cells for query SELECT * FROM
 keysace.table WHERE token(id) > token(test:-2220-UV033/04) LIMIT 100 (see
 tombstone_warn_threshold).




 Can you please help me to get the total count of the table.

 --
 Selvam Raman
 "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"


>>>
>>>
>>> --
>>> Selvam Raman
>>> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>>>
>>>
>>>
>


-- 
Siddharth Verma
(Visit https://github.com/siddv29/cfs for a high speed cassandra full table
scan)

Re: Count(*) is not working

2017-02-17 Thread Sylvain Lebresne 
On Fri, Feb 17, 2017 at 11:54 AM, kurt greaves  wrote:

> if you want a reliable count, you should use spark. performing a count (*)
> will inevitably fail unless you make your server read timeouts and
> tombstone fail thresholds ridiculous
>

That's just not true. count(*) is paged internally so while it is not
particular fast, it shouldn't require bumping neither the read timeout nor
the tombstone fail threshold in any way to work.

In that case, it seems the partition does have many tombstones (more than
live rows) and so the tombstone threshold is doing its job of warning about
it.


>
> On 17 Feb. 2017 04:34, "Jan"  wrote:
>
>> Hi,
>>
>> could you post the output of nodetool cfstats for the table?
>>
>> Cheers,
>>
>> Jan
>>
>> Am 16.02.2017 um 17:00 schrieb Selvam Raman:
>>
>> I am not getting count as result. Where i keep on getting n number of
>> results below.
>>
>> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
>> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
>> LIMIT 100 (see tombstone_warn_threshold)
>>
>> On Thu, Feb 16, 2017 at 12:37 PM, Jan Kesten  wrote:
>>
>>> Hi,
>>>
>>> do you got a result finally?
>>>
>>> Those messages are simply warnings telling you that c* had to read many
>>> tombstones while processing your query - rows that are deleted but not
>>> garbage collected/compacted. This warning gives you some explanation why
>>> things might be much slower than expected because per 100 rows that count
>>> c* had to read about 15 times rows that were deleted already.
>>>
>>> Apart from that, count(*) is almost always slow - and there is a default
>>> limit of 10.000 rows in a result.
>>>
>>> Do you really need the actual live count? To get a idea you can always
>>> look at nodetool cfstats (but those numbers also contain deleted rows).
>>>
>>>
>>> Am 16.02.2017 um 13:18 schrieb Selvam Raman:
>>>
>>> Hi,
>>>
>>> I want to know the total records count in table.
>>>
>>> I fired the below query:
>>>select count(*) from tablename;
>>>
>>> and i have got the below output
>>>
>>> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
>>> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
>>> LIMIT 100 (see tombstone_warn_threshold)
>>>
>>> Read 100 live rows and 1435 tombstone cells for query SELECT * FROM
>>> keysace.table WHERE token(id) > token(test:2565-AMK-2) LIMIT 100 (see
>>> tombstone_warn_threshold)
>>>
>>> Read 96 live rows and 1385 tombstone cells for query SELECT * FROM
>>> keysace.table WHERE token(id) > token(test:-2220-UV033/04) LIMIT 100 (see
>>> tombstone_warn_threshold).
>>>
>>>
>>>
>>>
>>> Can you please help me to get the total count of the table.
>>>
>>> --
>>> Selvam Raman
>>> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>>>
>>>
>>
>>
>> --
>> Selvam Raman
>> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>>
>>
>>

Re: Count(*) is not working

2017-02-17 Thread kurt greaves 
if you want a reliable count, you should use spark. performing a count (*)
will inevitably fail unless you make your server read timeouts and
tombstone fail thresholds ridiculous

On 17 Feb. 2017 04:34, "Jan"  wrote:

> Hi,
>
> could you post the output of nodetool cfstats for the table?
>
> Cheers,
>
> Jan
>
> Am 16.02.2017 um 17:00 schrieb Selvam Raman:
>
> I am not getting count as result. Where i keep on getting n number of
> results below.
>
> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
> LIMIT 100 (see tombstone_warn_threshold)
>
> On Thu, Feb 16, 2017 at 12:37 PM, Jan Kesten  wrote:
>
>> Hi,
>>
>> do you got a result finally?
>>
>> Those messages are simply warnings telling you that c* had to read many
>> tombstones while processing your query - rows that are deleted but not
>> garbage collected/compacted. This warning gives you some explanation why
>> things might be much slower than expected because per 100 rows that count
>> c* had to read about 15 times rows that were deleted already.
>>
>> Apart from that, count(*) is almost always slow - and there is a default
>> limit of 10.000 rows in a result.
>>
>> Do you really need the actual live count? To get a idea you can always
>> look at nodetool cfstats (but those numbers also contain deleted rows).
>>
>>
>> Am 16.02.2017 um 13:18 schrieb Selvam Raman:
>>
>> Hi,
>>
>> I want to know the total records count in table.
>>
>> I fired the below query:
>>select count(*) from tablename;
>>
>> and i have got the below output
>>
>> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
>> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
>> LIMIT 100 (see tombstone_warn_threshold)
>>
>> Read 100 live rows and 1435 tombstone cells for query SELECT * FROM
>> keysace.table WHERE token(id) > token(test:2565-AMK-2) LIMIT 100 (see
>> tombstone_warn_threshold)
>>
>> Read 96 live rows and 1385 tombstone cells for query SELECT * FROM
>> keysace.table WHERE token(id) > token(test:-2220-UV033/04) LIMIT 100 (see
>> tombstone_warn_threshold).
>>
>>
>>
>>
>> Can you please help me to get the total count of the table.
>>
>> --
>> Selvam Raman
>> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>>
>>
>
>
> --
> Selvam Raman
> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>
>
>

Re: Count(*) is not working

2017-02-16 Thread Jan 
Hi,

could you post the output of nodetool cfstats for the table?

Cheers,

Jan


Am 16.02.2017 um 17:00 schrieb Selvam Raman:
> I am not getting count as result. Where i keep on getting n number of
> results below.
>
> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
> keysace.table WHERE token(id) >
> token(test:ODP0144-0883E-022R-002/047-052) LIMIT 100 (see
> tombstone_warn_threshold)
>
> On Thu, Feb 16, 2017 at 12:37 PM, Jan Kesten  > wrote:
>
> Hi,
>
> do you got a result finally?
>
> Those messages are simply warnings telling you that c* had to read
> many tombstones while processing your query - rows that are
> deleted but not garbage collected/compacted. This warning gives
> you some explanation why things might be much slower than expected
> because per 100 rows that count c* had to read about 15 times rows
> that were deleted already.
>
> Apart from that, count(*) is almost always slow - and there is a
> default limit of 10.000 rows in a result.
>
> Do you really need the actual live count? To get a idea you can
> always look at nodetool cfstats (but those numbers also contain
> deleted rows).
>
>
> Am 16.02.2017 um 13:18 schrieb Selvam Raman:
>> Hi,
>>
>> I want to know the total records count in table.
>>
>> I fired the below query:
>>select count(*) from tablename;
>>
>> and i have got the below output
>>
>> Read 100 live rows and 1423 tombstone cells for query SELECT *
>> FROM keysace.table WHERE token(id) >
>> token(test:ODP0144-0883E-022R-002/047-052) LIMIT 100 (see
>> tombstone_warn_threshold)
>>
>> Read 100 live rows and 1435 tombstone cells for query SELECT *
>> FROM keysace.table WHERE token(id) > token(test:2565-AMK-2) LIMIT
>> 100 (see tombstone_warn_threshold)
>>
>> Read 96 live rows and 1385 tombstone cells for query SELECT *
>> FROM keysace.table WHERE token(id) > token(test:-2220-UV033/04)
>> LIMIT 100 (see tombstone_warn_threshold).
>>
>>
>>
>>
>> Can you please help me to get the total count of the table.
>>
>> -- 
>> Selvam Raman
>> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>
>
>
>
> -- 
> Selvam Raman
> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"

Re: Count(*) is not working

2017-02-16 Thread Selvam Raman 
I am not getting count as result. Where i keep on getting n number of
results below.

Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
LIMIT 100 (see tombstone_warn_threshold)

On Thu, Feb 16, 2017 at 12:37 PM, Jan Kesten  wrote:

> Hi,
>
> do you got a result finally?
>
> Those messages are simply warnings telling you that c* had to read many
> tombstones while processing your query - rows that are deleted but not
> garbage collected/compacted. This warning gives you some explanation why
> things might be much slower than expected because per 100 rows that count
> c* had to read about 15 times rows that were deleted already.
>
> Apart from that, count(*) is almost always slow - and there is a default
> limit of 10.000 rows in a result.
>
> Do you really need the actual live count? To get a idea you can always
> look at nodetool cfstats (but those numbers also contain deleted rows).
>
>
> Am 16.02.2017 um 13:18 schrieb Selvam Raman:
>
> Hi,
>
> I want to know the total records count in table.
>
> I fired the below query:
>select count(*) from tablename;
>
> and i have got the below output
>
> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
> LIMIT 100 (see tombstone_warn_threshold)
>
> Read 100 live rows and 1435 tombstone cells for query SELECT * FROM
> keysace.table WHERE token(id) > token(test:2565-AMK-2) LIMIT 100 (see
> tombstone_warn_threshold)
>
> Read 96 live rows and 1385 tombstone cells for query SELECT * FROM
> keysace.table WHERE token(id) > token(test:-2220-UV033/04) LIMIT 100 (see
> tombstone_warn_threshold).
>
>
>
>
> Can you please help me to get the total count of the table.
>
> --
> Selvam Raman
> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>
>


-- 
Selvam Raman
"லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"

Re: Count(*) is not working

2017-02-16 Thread Cogumelos Maravilha 
With C* 3.10

cqlsh ip --request-timeout=60
Connected to x at 10.10.10.10:9042.
[cqlsh 5.0.1 | Cassandra 3.10 | CQL spec 3.4.4 | Native protocol v4]
Use HELP for help.
cqlsh> USE  ;
cqlsh:> SELECT count(*) from table;
   
 count
-
 3572579


On 02/16/2017 12:27 PM, Selvam Raman wrote:
> I am using cassandra 3.9.
>
> Primary Key:
> id text;
>
> On Thu, Feb 16, 2017 at 12:25 PM, Cogumelos Maravilha
> > wrote:
>
> C* version please and partition key.
>
>
> On 02/16/2017 12:18 PM, Selvam Raman wrote:
>> Hi,
>>
>> I want to know the total records count in table.
>>
>> I fired the below query:
>>select count(*) from tablename;
>>
>> and i have got the below output
>>
>> Read 100 live rows and 1423 tombstone cells for query SELECT *
>> FROM keysace.table WHERE token(id) >
>> token(test:ODP0144-0883E-022R-002/047-052) LIMIT 100 (see
>> tombstone_warn_threshold)
>>
>> Read 100 live rows and 1435 tombstone cells for query SELECT *
>> FROM keysace.table WHERE token(id) > token(test:2565-AMK-2) LIMIT
>> 100 (see tombstone_warn_threshold)
>>
>> Read 96 live rows and 1385 tombstone cells for query SELECT *
>> FROM keysace.table WHERE token(id) > token(test:-2220-UV033/04)
>> LIMIT 100 (see tombstone_warn_threshold).
>>
>>
>>
>>
>> Can you please help me to get the total count of the table.
>>
>> -- 
>> Selvam Raman
>> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>
>
>
>
> -- 
> Selvam Raman
> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"

Re: Count(*) is not working

2017-02-16 Thread Selvam Raman 
I am using cassandra 3.9.

Primary Key:
id text;

On Thu, Feb 16, 2017 at 12:25 PM, Cogumelos Maravilha <
cogumelosmaravi...@sapo.pt> wrote:

> C* version please and partition key.
>
> On 02/16/2017 12:18 PM, Selvam Raman wrote:
>
> Hi,
>
> I want to know the total records count in table.
>
> I fired the below query:
>select count(*) from tablename;
>
> and i have got the below output
>
> Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
> keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
> LIMIT 100 (see tombstone_warn_threshold)
>
> Read 100 live rows and 1435 tombstone cells for query SELECT * FROM
> keysace.table WHERE token(id) > token(test:2565-AMK-2) LIMIT 100 (see
> tombstone_warn_threshold)
>
> Read 96 live rows and 1385 tombstone cells for query SELECT * FROM
> keysace.table WHERE token(id) > token(test:-2220-UV033/04) LIMIT 100 (see
> tombstone_warn_threshold).
>
>
>
>
> Can you please help me to get the total count of the table.
>
> --
> Selvam Raman
> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>
>
>


-- 
Selvam Raman
"லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"

Count(*) is not working

2017-02-16 Thread Selvam Raman 
Hi,

I want to know the total records count in table.

I fired the below query:
   select count(*) from tablename;

and i have got the below output

Read 100 live rows and 1423 tombstone cells for query SELECT * FROM
keysace.table WHERE token(id) > token(test:ODP0144-0883E-022R-002/047-052)
LIMIT 100 (see tombstone_warn_threshold)

Read 100 live rows and 1435 tombstone cells for query SELECT * FROM
keysace.table WHERE token(id) > token(test:2565-AMK-2) LIMIT 100 (see
tombstone_warn_threshold)

Read 96 live rows and 1385 tombstone cells for query SELECT * FROM
keysace.table WHERE token(id) > token(test:-2220-UV033/04) LIMIT 100 (see
tombstone_warn_threshold).




Can you please help me to get the total count of the table.

-- 
Selvam Raman
"லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"

select count query not working at cassandra 2.0.0

2013-09-20 Thread Katsutoshi 
I would like to use select count query.
Although it was work at Cassandra 1.2.9, but there is a situation which
does not work at Cassandra 2.0.0.
so, If some row is deleted, 'select count query' seems to return the wrong
value.
Did anything change by Cassandra 2.0.0 ? or Have I made a mistake ?

My test procedure is as follows:

### At Cassandra 1.2.9

1) create table, and insert two rows

```
cqlsh:test CREATE TABLE count_hash_test (key text, value text, PRIMARY KEY
(key));
cqlsh:test INSERT INTO count_hash_test (key, value) VALUES ('key1',
'value');
cqlsh:test INSERT INTO count_hash_test (key, value) VALUES ('key2',
'value');
```

2) do a select count query, it returns 2 which is expected

```
cqlsh:test SELECT * FROM count_hash_test;

 key  | value
--+---
 key1 | value
 key2 | value

cqlsh:test SELECT COUNT(*) FROM count_hash_test;

 count
---
 2
```

3) delete one row

```
cqlsh:test DELETE FROM count_hash_test WHERE key='key1';
```

4) do a select count query, it returns 1 which is expected

```
cqlsh:test SELECT * FROM count_hash_test;

 key  | value
--+---
 key2 | value

cqlsh:test SELECT COUNT(*) FROM count_hash_test;

 count
---
 1
```

### At Cassandra 2.0.0

1) create table, and insert two rows

```
cqlsh:test CREATE TABLE count_hash_test (key text, value text, PRIMARY KEY
(key));
cqlsh:test INSERT INTO count_hash_test (key, value) VALUES ('key1',
'value');
cqlsh:test INSERT INTO count_hash_test (key, value) VALUES ('key2',
'value');
```

2) do a select count query, it returns 2  which is expected

```
cqlsh:test SELECT * FROM count_hash_test;

 key  | value
--+---
 key1 | value
 key2 | value

cqlsh:test SELECT COUNT(*) FROM count_hash_test;

 count
---
 2
```

3) delete one row

```
cqlsh:test DELETE FROM count_hash_test WHERE key='key1';
```

4) do a select count query, but it returns 0 which is NOT expected

```
cqlsh:test SELECT * FROM count_hash_test;

 key  | value
--+---
 key2 | value

cqlsh:test SELECT COUNT(*) FROM count_hash_test;

 count
---
 0
```

Could anyone help me for this? thanks.

Katsutoshi