Re: tombstones - however there are no deletes
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
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
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
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
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
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
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
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)
