Re: tombstones - however there are no deletes

2020-08-21 Thread Alex Ott
Btw, if you seen the number of tombstones that is a multiply of number of
scanned rows, like in your case - that’s a explicit signal of either null
inserts, or non frozen collections...

On Fri 21. Aug 2020 at 20:21, Attila Wind  wrote:

>
>
>
>
>
>
>
>
>
>
> right! silly me (regarding "can't have null for clustering
>
> column") :-)
>
>
> OK code is modified, we stopped using NULL on that column. In a
>
> few days we will see if this was the cause.
>
>
> Thanks for the useful info eveyrone! Helped a lot!
>
>
>
>
> Attila Wind
>
>
>
>
>
>
>
> http://www.linkedin.com/in/attilaw
>
>
> Mobile: +49 176 43556932
>
>
>
>
>
>
>
>
>
>
>
>
> 21.08.2020 11:04 keltezéssel, Alex Ott
>
> írta:
>
>
>
>
>
>
>
>
>
>
> inserting null for any column will generate the tombstone
>
> (and you can't have null for clustering column, except case
>
> when it's an empty partition with static column).
>
>
> if you're really inserting the new data, not overwriting
>
> existing one - use UNSET instead of null
>
>
>
>
>
>
>
>
>
>
>
> On Fri, Aug 21, 2020 at 10:45
>
> AM Attila Wind   wrote:
>
>
>
>
>
>>
>>
>>
>> Thanks a lot! I will process every pointers you gave -
>>
>> appreciated!
>>
>>
>>
>>
>> 1. we do have collection column in that table but that is
>>
>> (we have only 1 column) a frozen Map - so I guess
>>
>> "Tombstones are also implicitly created any time you
>>
>> insert or update a row which has an (unfrozen) collection
>>
>> column: list<>, map<> or set<>.  This
>>
>> has to be done in order to ensure the new write replaces
>>
>> any existing collection entries." does not really apply
>>
>> here
>>
>>
>> 2. "Isn’t it so that explicitly
>>
>> setting a column to NULL also result in a tombstone"
>>
>>
>> Is this true for all columns? or just clustering key cols?
>>
>>
>> Because if for all cols (which would make sense maybe to
>>
>> me more) then we found the possible reason.. :-)
>>
>>
>> As we do have an Integer coulmn there which is actually
>>
>> NULL often (and so far in all cases)
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>  Attila
>>
>> Wind
>>
>>
>>
>>
>>
>>
>>
>> http://www.linkedin.com/in/attilaw
>>
>>
>> Mobile: +49 176 43556932
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> 21.08.2020 09:49 keltezéssel, Oleksandr Shulgin írta:
>>
>>
>>
>>
>>
>>
>>
>>
>> On Fri, Aug 21, 2020 at 9:43 AM Tobias
>>
>> Eriksson 
>>
>> wrote:
>>
>>
>>
>>
>>
>>
>>
>>>
>>>
>>>
>>>
>>>
>>> Isn’t it
>>>
>>> so that explicitly setting a column to NULL
>>>
>>> also result in a tombstone
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>>
>> True, thanks for pointing that out!
>>
>>
>>
>>
>>
>>
>>
>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Then as
>>>
>>> mentioned the use of list,set,map can also
>>>
>>> result in tombstones
>>>
>>>
>>> See
>>>
>>>
>>> https://www.instaclustr.com/cassandra-collections-hidden-tombstones-and-how-to-avoid-them/
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>>
>> And A. Ott has already mentioned both these
>>
>> possible reasons :-)
>>
>>
>>
>>
>>
>>
>>
>> --
>>
>>
>> Alex
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
>
>
>
>
>
>
>
> --
>
>
>
>
>
>
> With best wishes,Alex Ott
>
>
> http://alexott.net/
>
>
> Twitter: alexott_en (English), alexott (Russian)
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

-- 
With best wishes,Alex Ott
http://alexott.net/
Twitter: alexott_en (English), alexott (Russian)


Re: tombstones - however there are no deletes

2020-08-21 Thread Attila Wind

right! silly me (regarding "can't have null for clustering column") :-)

OK code is modified, we stopped using NULL on that column. In a few days 
we will see if this was the cause.


Thanks for the useful info eveyrone! Helped a lot!

Attila Wind

http://www.linkedin.com/in/attilaw
Mobile: +49 176 43556932


21.08.2020 11:04 keltezéssel, Alex Ott írta:
inserting null for any column will generate the tombstone (and you 
can't have null for clustering column, except case when it's an empty 
partition with static column).
if you're really inserting the new data, not overwriting existing one 
- use UNSET instead of null


On Fri, Aug 21, 2020 at 10:45 AM Attila Wind  
wrote:


Thanks a lot! I will process every pointers you gave - appreciated!

1. we do have collection column in that table but that is (we have
only 1 column) a frozen Map - so I guess "Tombstones are also
implicitly created any time you insert or update a row which has
an (unfrozen) collection column: list<>, map<> or set<>.  This has
to be done in order to ensure the new write replaces any existing
collection entries." does not really apply here

2. "Isn’t it so that explicitly setting a column to NULL also
result in a tombstone"
Is this true for all columns? or just clustering key cols?
Because if for all cols (which would make sense maybe to me more)
then we found the possible reason.. :-)
As we do have an Integer coulmn there which is actually NULL often
(and so far in all cases)


 Attila Wind

http://www.linkedin.com/in/attilaw
Mobile: +49 176 43556932


21.08.2020 09:49 keltezéssel, Oleksandr Shulgin írta:

On Fri, Aug 21, 2020 at 9:43 AM Tobias Eriksson
mailto:tobias.eriks...@qvantel.com>> wrote:

Isn’t it so that explicitly setting a column to NULL also
result in a tombstone


True, thanks for pointing that out!

Then as mentioned the use of list,set,map can also result in
tombstones

See

https://www.instaclustr.com/cassandra-collections-hidden-tombstones-and-how-to-avoid-them/


And A. Ott has already mentioned both these possible reasons :-)

--
Alex




--
With best wishes,                    Alex Ott
http://alexott.net/
Twitter: alexott_en (English), alexott (Russian)


Re: tombstones - however there are no deletes

2020-08-21 Thread Alex Ott
inserting null for any column will generate the tombstone (and you can't
have null for clustering column, except case when it's an empty partition
with static column).
if you're really inserting the new data, not overwriting existing one - use
UNSET instead of null

On Fri, Aug 21, 2020 at 10:45 AM Attila Wind  wrote:

> Thanks a lot! I will process every pointers you gave - appreciated!
>
> 1. we do have collection column in that table but that is (we have only 1
> column) a frozen Map - so I guess "Tombstones are also implicitly created
> any time you insert or update a row which has an (unfrozen) collection
> column: list<>, map<> or set<>.  This has to be done in order to ensure the
> new write replaces any existing collection entries." does not really apply
> here
>
> 2. "Isn’t it so that explicitly setting a column to NULL also result in a
> tombstone"
> Is this true for all columns? or just clustering key cols?
> Because if for all cols (which would make sense maybe to me more) then we
> found the possible reason.. :-)
> As we do have an Integer coulmn there which is actually NULL often (and so
> far in all cases)
>
>
>  Attila Wind
>
> http://www.linkedin.com/in/attilaw
> Mobile: +49 176 43556932
>
>
> 21.08.2020 09:49 keltezéssel, Oleksandr Shulgin írta:
>
> On Fri, Aug 21, 2020 at 9:43 AM Tobias Eriksson <
> tobias.eriks...@qvantel.com> wrote:
>
>> Isn’t it so that explicitly setting a column to NULL also result in a
>> tombstone
>>
>
> True, thanks for pointing that out!
>
> Then as mentioned the use of list,set,map can also result in tombstones
>>
>> See
>> https://www.instaclustr.com/cassandra-collections-hidden-tombstones-and-how-to-avoid-them/
>>
>
> And A. Ott has already mentioned both these possible reasons :-)
>
> --
> Alex
>
>

-- 
With best wishes,Alex Ott
http://alexott.net/
Twitter: alexott_en (English), alexott (Russian)


Re: tombstones - however there are no deletes

2020-08-21 Thread Attila Wind

Thanks a lot! I will process every pointers you gave - appreciated!

1. we do have collection column in that table but that is (we have only 
1 column) a frozen Map - so I guess "Tombstones are also implicitly 
created any time you insert or update a row which has an (unfrozen) 
collection column: list<>, map<> or set<>.  This has to be done in order 
to ensure the new write replaces any existing collection entries." does 
not really apply here


2. "Isn’t it so that explicitly setting a column to NULL also result in 
a tombstone"

Is this true for all columns? or just clustering key cols?
Because if for all cols (which would make sense maybe to me more) then 
we found the possible reason.. :-)
As we do have an Integer coulmn there which is actually NULL often (and 
so far in all cases)



 Attila Wind

http://www.linkedin.com/in/attilaw
Mobile: +49 176 43556932


21.08.2020 09:49 keltezéssel, Oleksandr Shulgin írta:
On Fri, Aug 21, 2020 at 9:43 AM Tobias Eriksson 
mailto:tobias.eriks...@qvantel.com>> wrote:


Isn’t it so that explicitly setting a column to NULL also result
in a tombstone


True, thanks for pointing that out!

Then as mentioned the use of list,set,map can also result in
tombstones

See

https://www.instaclustr.com/cassandra-collections-hidden-tombstones-and-how-to-avoid-them/


And A. Ott has already mentioned both these possible reasons :-)

--
Alex



Re: tombstones - however there are no deletes

2020-08-21 Thread Oleksandr Shulgin
On Fri, Aug 21, 2020 at 9:43 AM Tobias Eriksson 
wrote:

> Isn’t it so that explicitly setting a column to NULL also result in a
> tombstone
>

True, thanks for pointing that out!

Then as mentioned the use of list,set,map can also result in tombstones
>
> See
> https://www.instaclustr.com/cassandra-collections-hidden-tombstones-and-how-to-avoid-them/
>

And A. Ott has already mentioned both these possible reasons :-)

--
Alex


Re: tombstones - however there are no deletes

2020-08-21 Thread Tobias Eriksson
Isn’t it so that explicitly setting a column to NULL also result in a tombstone
Then as mentioned the use of list,set,map can also result in tombstones
See 
https://www.instaclustr.com/cassandra-collections-hidden-tombstones-and-how-to-avoid-them/
-Tobias


From: Oleksandr Shulgin 
Reply to: "user@cassandra.apache.org" 
Date: Friday, 21 August 2020 at 09:36
To: User , "attila.wind@swf.technology" 

Subject: Re: tombstones - however there are no deletes

On Fri, Aug 21, 2020 at 7:57 AM Attila Wind  wrote:

Hi Cassandra Gurus,

Recently I captured a very interesting warning in the logs saying

2020-08-19 08:08:32.492 [cassandra-client-keytiles_data_webhits-nio-worker-2] 
WARN  com.datastax.driver.core.RequestHandler - Query '[3 bound values] select 
* from visit_sess
ion_by_start_time_v4 where container_id=? and first_action_time_frame_id >= ? 
and first_action_time_frame_id <= ?;' generated server side warning(s): Read 
6628 live rows and
6628 tombstone cells for query SELECT * FROM 
keytiles_data_webhits.visit_session_by_start_time_v4 WHERE container_id = 
5YzsPfE2Gcu8sd-76626 AND first_action_time_frame_id > 4
43837 AND first_action_time_frame_id <= 443670 AND user_agent_type > 
browser-mobile AND unique_webclient_id > 045d1683-c702-48bd-9d2b-dcf1ca87ac7c 
AND first_action_ts > 15978
15766 LIMIT 6628 (see tombstone_warn_threshold)

What makes this interesting to me is the fact we never issue not even row level 
deletes but any kind of deletes against this table for now
So I'm wondering what can result in tombstone creation in Cassandra - apart 
from explicit DELETE queries and TTL setup...

My suspicion is (but I'm not sure) that as we are going with "select *" read 
strategy, then calculate everything in-memory, eventually writing back with 
kinda "update *" queries to Cassandra in this table (so not updating just a few 
columns but everything) can lead to these... Can it?
I tried to search around this sympthom but was not successful - so decided to 
ask you guys maybe someone can give us a pointer...

Some more info:

  *   the table does not have TTL set - this mechanism is turned off
  *   the LIMIT param in upper query comes from paging size
  *   we are using Cassandra4 alpha3
  *   we also have a few similarly built tables where we follow the above 
described "update *" policy on write path - however those tables are counter 
tables... when we mass-read them into memory we also go with "select *" logic 
reading up tons of rows. The point is we never saw such a warning for these 
counter tables however we are handling them same fashion... ok counter tables 
work differently but still interesting to me why those never generated things 
like this
Tombstones are also implicitly created any time you insert or update a row 
which has an (unfrozen) collection column: list<>, map<> or set<>.  This has to 
be done in order to ensure the new write replaces any existing collection 
entries.

From an in-depth blog post from TLP: 
https://thelastpickle.com/blog/2016/07/27/about-deletes-and-tombstones.html

Note: When using collections, range tombstones will be generated by INSERT and 
UPDATE operations every time you are using an entire collection, and not 
updating parts of it. Inserting a collection over an existing collection, 
rather than appending it or updating only an item in it, leads to range 
tombstones insert followed by the insert of the new values for the collection. 
This DELETE operation is hidden leading to some weird and frustrating 
tombstones issues.

I'd check if you have any of those in the table involved and see if you could 
use a frozen collection instead, for example.

--
Alex



Re: tombstones - however there are no deletes

2020-08-21 Thread Oleksandr Shulgin
On Fri, Aug 21, 2020 at 7:57 AM Attila Wind  wrote:

> Hi Cassandra Gurus,
>
> Recently I captured a very interesting warning in the logs saying
>
> 2020-08-19 08:08:32.492
> [cassandra-client-keytiles_data_webhits-nio-worker-2] WARN
> com.datastax.driver.core.RequestHandler - Query '[3 bound values] select *
> from visit_sess
> ion_by_start_time_v4 where container_id=? and first_action_time_frame_id
> >= ? and first_action_time_frame_id <= ?;' generated server side
> warning(s):
> *Read 6628 live rows and 6628 tombstone cells* for query SELECT * FROM
> keytiles_data_webhits.visit_session_by_start_time_v4 WHERE container_id =
> 5YzsPfE2Gcu8sd-76626 AND first_action_time_frame_id > 4
> 43837 AND first_action_time_frame_id <= 443670 AND user_agent_type >
> browser-mobile AND unique_webclient_id >
> 045d1683-c702-48bd-9d2b-dcf1ca87ac7c AND first_action_ts > 15978
> 15766 LIMIT 6628 (see tombstone_warn_threshold)
>
> What makes this interesting to me is the fact we never issue not even row
> level deletes but any kind of deletes against this table for now
> So I'm wondering what can result in tombstone creation in Cassandra -
> apart from explicit DELETE queries and TTL setup...
>
> My suspicion is (but I'm not sure) that as we are going with "select *"
> read strategy, then calculate everything in-memory, eventually writing back
> with kinda "update *" queries to Cassandra in this table (so not updating
> just a few columns but everything) can lead to these... Can it?
> I tried to search around this sympthom but was not successful - so decided
> to ask you guys maybe someone can give us a pointer...
>
> Some more info:
>
>- the table does not have TTL set - this mechanism is turned off
>- the LIMIT param in upper query comes from paging size
>- we are using Cassandra4 alpha3
>- we also have a few similarly built tables where we follow the above
>described "update *" policy on write path - however those tables are
>counter tables... when we mass-read them into memory we also go with
>"select *" logic reading up tons of rows. The point is we never saw such a
>warning for these counter tables however we are handling them same
>fashion... ok counter tables work differently but still interesting to me
>why those never generated things like this
>
> Tombstones are also implicitly created any time you insert or update a row
which has an (unfrozen) collection column: list<>, map<> or set<>.  This
has to be done in order to ensure the new write replaces any existing
collection entries.

>From an in-depth blog post from TLP:
https://thelastpickle.com/blog/2016/07/27/about-deletes-and-tombstones.html

Note: When using collections, range tombstones will be generated by INSERT
and UPDATE operations every time you are using an entire collection, and
not updating parts of it. Inserting a collection over an existing
collection, rather than appending it or updating only an item in it, leads
to range tombstones insert followed by the insert of the new values for the
collection. This DELETE operation is hidden leading to some weird and
frustrating tombstones issues.


I'd check if you have any of those in the table involved and see if you
could use a frozen collection instead, for example.

--
Alex


Re: tombstones - however there are no deletes

2020-08-21 Thread Alex Ott
Tombstones could be not only generated by deletes. this happens when you:

   - When insert or full update of a non-frozen collection occurs, such as
   replacing the value of the column with another value like the UPDATE table
   SET field = new_value …, Cassandra inserts a tombstone marker to prevent
   possible overlap with previous data even if data did not previously exist.
   A large number of tombstones can significantly affect read performance.
   - When you insert null explicitly, instead of using UNSET for missing
   data.


On Fri, Aug 21, 2020 at 7:57 AM Attila Wind  wrote:

> Hi Cassandra Gurus,
>
> Recently I captured a very interesting warning in the logs saying
>
> 2020-08-19 08:08:32.492
> [cassandra-client-keytiles_data_webhits-nio-worker-2] WARN
> com.datastax.driver.core.RequestHandler - Query '[3 bound values] select *
> from visit_sess
> ion_by_start_time_v4 where container_id=? and first_action_time_frame_id
> >= ? and first_action_time_frame_id <= ?;' generated server side
> warning(s):
> *Read 6628 live rows and 6628 tombstone cells* for query SELECT * FROM
> keytiles_data_webhits.visit_session_by_start_time_v4 WHERE container_id =
> 5YzsPfE2Gcu8sd-76626 AND first_action_time_frame_id > 4
> 43837 AND first_action_time_frame_id <= 443670 AND user_agent_type >
> browser-mobile AND unique_webclient_id >
> 045d1683-c702-48bd-9d2b-dcf1ca87ac7c AND first_action_ts > 15978
> 15766 LIMIT 6628 (see tombstone_warn_threshold)
>
> What makes this interesting to me is the fact we never issue not even row
> level deletes but any kind of deletes against this table for now
> So I'm wondering what can result in tombstone creation in Cassandra -
> apart from explicit DELETE queries and TTL setup...
>
> My suspicion is (but I'm not sure) that as we are going with "select *"
> read strategy, then calculate everything in-memory, eventually writing back
> with kinda "update *" queries to Cassandra in this table (so not updating
> just a few columns but everything) can lead to these... Can it?
> I tried to search around this sympthom but was not successful - so decided
> to ask you guys maybe someone can give us a pointer...
>
> Some more info:
>
>- the table does not have TTL set - this mechanism is turned off
>- the LIMIT param in upper query comes from paging size
>- we are using Cassandra4 alpha3
>- we also have a few similarly built tables where we follow the above
>described "update *" policy on write path - however those tables are
>counter tables... when we mass-read them into memory we also go with
>"select *" logic reading up tons of rows. The point is we never saw such a
>warning for these counter tables however we are handling them same
>fashion... ok counter tables work differently but still interesting to me
>why those never generated things like this
>
> thanks!
> --
> Attila Wind
>
> http://www.linkedin.com/in/attilaw
> Mobile: +49 176 43556932
>
>
>

-- 
With best wishes,Alex Ott
http://alexott.net/
Twitter: alexott_en (English), alexott (Russian)