From user-return-63637-archive-asf-public=cust-asf.ponee.io@cassandra.apache.org Wed Apr 10 09:14:16 2019 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [207.244.88.153]) by mx-eu-01.ponee.io (Postfix) with SMTP id F0609180626 for ; Wed, 10 Apr 2019 11:14:15 +0200 (CEST) Received: (qmail 29245 invoked by uid 500); 10 Apr 2019 09:14:04 -0000 Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cassandra.apache.org Delivered-To: mailing list user@cassandra.apache.org Received: (qmail 29006 invoked by uid 99); 10 Apr 2019 09:14:03 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 10 Apr 2019 09:14:03 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id 692A7C2ADF for ; Wed, 10 Apr 2019 09:14:03 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.8 X-Spam-Level: * X-Spam-Status: No, score=1.8 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, SPF_PASS=-0.001, URIBL_BLOCKED=0.001] autolearn=disabled Authentication-Results: spamd1-us-west.apache.org (amavisd-new); dkim=pass (1024-bit key) header.d=instaclustr.com Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id Jy8wPGCQYieK for ; Wed, 10 Apr 2019 09:14:01 +0000 (UTC) Received: from mail-wr1-f65.google.com (mail-wr1-f65.google.com [209.85.221.65]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id D5E8861145 for ; Wed, 10 Apr 2019 09:14:00 +0000 (UTC) Received: by mail-wr1-f65.google.com with SMTP id j9so2000206wrn.6 for ; Wed, 10 Apr 2019 02:14:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=instaclustr.com; s=instaclustr.email.auth; h=from:mime-version:subject:date:references:to:in-reply-to:message-id; bh=YyX1D1qJqNEr7wJHnYW5QwN7AIJYOdbVkANO8Sr6Vzo=; b=CrkR5mWBAro0Wj/RGfMcpIN9K/PqURZDKd6R2sdlOJrwzeRxMkXsJ9z7t5g2fgDBxU c0nob9HKWwrxr87YKUS0PaLoV8oIg6ftoggi/78PE4d2NiS1k017m6pc326yhk8wVgVR 3IlyFkHSeqWxkUYrRI/Pba+icGiZEumwuLwIk= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:mime-version:subject:date:references:to :in-reply-to:message-id; bh=YyX1D1qJqNEr7wJHnYW5QwN7AIJYOdbVkANO8Sr6Vzo=; b=pbxdi16DauLX2iLDz1Fx8VwvDxo8VKeDGRvFHqXM5QbtZQcFF6+lhBIMEvFFuZAQ12 DW6hgK3ZDGtvKClYIBINP4187mDjewG3JvhhTM6ZaSb0RmlGr5LQgnEPT/3qL9pkbZB8 OwCQLY9PMH8iiNVJVQhb5w6U7XFxeV7o4QhB/gpz13KiP7fgna4Zz0o82hLIR6aj3DBb k7YFGEbJETkNYXaOZlYwZhkQng1QCT9rmqweGsDKnCsMz6VR+R4pXj88Dl1+RHBC/1qM KYFx+lW0UWTdExOF9uFeXKu3bQDdSJ0NNkwLmoZWJzEGQPHn/LNDLT6ZkqtPc5uYkCKs FSkw== X-Gm-Message-State: APjAAAXUSQ6w8JTsV4CFee4cdw6E0g0OiAmz+FLdZ02pnJs48LLL3qsz c/t7aGQKpfuhFRJe5Be7Gd/BbedYbEw= X-Google-Smtp-Source: APXvYqxL9P+vHWtv64euYpSAOS65ROhCyR+JhueRHZurvArEzrYIoctAE6/eeBLb/d1vCVh58kN7ag== X-Received: by 2002:adf:ed90:: with SMTP id c16mr28201341wro.74.1554887640299; Wed, 10 Apr 2019 02:14:00 -0700 (PDT) Received: from [192.168.0.28] (cpc96818-rdng27-2-0-cust678.15-3.cable.virginm.net. [86.30.202.167]) by smtp.gmail.com with ESMTPSA id n11sm57289316wrt.63.2019.04.10.02.13.59 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 10 Apr 2019 02:13:59 -0700 (PDT) From: Alok Dwivedi Content-Type: multipart/alternative; boundary="Apple-Mail=_CB990117-0A16-4DB1-BB0D-465959F979B4" Mime-Version: 1.0 (Mac OS X Mail 11.5 \(3445.9.1\)) Subject: Re: Questions about C* performance related to tombstone Date: Wed, 10 Apr 2019 10:13:59 +0100 References: To: user@cassandra.apache.org In-Reply-To: Message-Id: X-Mailer: Apple Mail (2.3445.9.1) --Apple-Mail=_CB990117-0A16-4DB1-BB0D-465959F979B4 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Your delete query=20 >> "DELETE FROM myTable WHERE course_id =3D 'C' AND assignment_id =3D = 'A1';=E2=80=9D. will generate multi row range tombstones. Since you are reading entire = partition which effectively will be read in pages (slice query = equivalent) you may get tombstones in certain pages depending upon how = much deletes you are doing. However looking at your use case I don=E2=80=99= t think you will end with very high ratio of deleted to live data so = normal deletes should be fine as is already pointed out below. Note that = range tombstones are more effective storage space wise as they have = start/end range rather than deleted info for every deleted row. So I = also don=E2=80=99t think your workaround of using =E2=80=98active=E2=80=99= flag is really needed unless its for auditing. Another thing to note is = if you have a use case where you want to be more aggressive in evicting = tombstones then here are some settings worth exploring - tombstone_threshold - unchecked_tombstone_compaction -tombstone_compaction_interval Additionally gc_grace_seconds can be looked at but it must be handled = very carefully as we must ensure that repair completes in an interval = less than this setting to prevent any deleted data reappearing.=20 Regards Alok > On 9 Apr 2019, at 15:56, Jon Haddad wrote: >=20 > Normal deletes are fine. >=20 > Sadly there's a lot of hand wringing about tombstones in the generic > sense which leads people to try to work around *every* case where > they're used. This is unnecessary. A tombstone over a single row > isn't a problem, especially if you're only fetching that one row back. > Tombstones can be quite terrible under a few conditions: >=20 > 1. When a range tombstone shadows hundreds / thousands / millions of > rows. This wasn't even detectable prior to Cassandra 3 unless you > were either looking for it specifically or were doing CPU profiling: > = http://thelastpickle.com/blog/2018/07/05/undetectable-tombstones-in-apache= -cassandra.html > 2. When rows were frequently created then deleted, and scanned over. > This is the queue pattern that we detest so much. > 3. When they'd be created as a side effect from over writing > collections. This is an accident typically. >=20 > The 'active' flag is good if you want to be able to go back and look > at old deleted assignments. If you don't care about that, use a > normal delete. >=20 > Jon >=20 > On Tue, Apr 9, 2019 at 7:00 AM Li, George = wrote: >>=20 >> Hi, >>=20 >> I have a table defined like this: >>=20 >> CREATE TABLE myTable ( >> course_id text, >> assignment_id text, >> assignment_item_id text, >> data text, >> boolean active, >> PRIMARY KEY (course_id, assignment_id, assignment_item_id) >> ); >> i.e. course_id as the partition key and assignment_id, = assignment_item_id as clustering keys. >>=20 >> After data is populated, some delete queries by course_id and = assignment_id occurs, e.g. "DELETE FROM myTable WHERE course_id =3D 'C' = AND assignment_id =3D 'A1';". This would create tombstones so query = "SELECT * FROM myTable WHERE course_id =3D 'C';" would be affected, = right? Would query "SELECT * FROM myTable WHERE course_id =3D 'C' AND = assignment_id =3D 'A2';" be affected too? >>=20 >> For query "SELECT * FROM myTable WHERE course_id =3D 'C';", to = workaround the tombstone problem, we are thinking about not doing hard = deletes, instead doing soft deletes. So instead of doing "DELETE FROM = myTable WHERE course_id =3D 'C' AND assignment_id =3D 'A1';", we do = "UPDATE myTable SET active =3D false WHERE course_id =3D 'C' AND = assignment_id =3D 'A1';". Then in the application, we do query "SELECT * = FROM myTable WHERE course_id =3D 'C';" and filter out records that have = "active" equal to "false". I am not really sure this would improve = performance because C* still has to scan through all records with the = partition key "C". It is just instead of scanning through X records + Y = tombstone records with hard deletes that generate tombstones, it now = scans through X + Y records with soft deletes and no tombstones. Am I = right? >>=20 >> Thanks. >>=20 >> George >=20 > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-unsubscribe@cassandra.apache.org > For additional commands, e-mail: user-help@cassandra.apache.org >=20 --Apple-Mail=_CB990117-0A16-4DB1-BB0D-465959F979B4 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Your = delete query 
"DELETE FROM myTable WHERE course_id =3D 'C' AND = assignment_id =3D 'A1';=E2=80=9D.
will generate = multi row range tombstones. Since you are reading entire partition which = effectively will be read in pages (slice query equivalent) you may get = tombstones in certain pages depending upon how much deletes you are = doing. However looking at your use case I don=E2=80=99t think you will = end with very high ratio of deleted to live data so normal deletes = should be fine as is already pointed out below. Note that range = tombstones are more effective storage space wise as they have start/end = range rather than deleted info for every deleted row. So I also don=E2=80=99= t think  your workaround of using =E2=80=98active=E2=80=99 flag is = really needed unless its for auditing. Another thing to note is if you = have a use case where you want to be more aggressive in evicting = tombstones then here are some settings worth = exploring
- tombstone_threshold
- unchecked_tombstone_compaction
-tombstone_compaction_interval
Additionally gc_grace_seconds can be looked at but it must be = handled very carefully as we must ensure that repair completes in an = interval less than this setting to prevent any deleted data = reappearing. 

Regards
Alok


On 9 Apr 2019, at 15:56, Jon Haddad <jon@jonhaddad.com> = wrote:

Normal deletes are fine.

Sadly = there's a lot of hand wringing about tombstones in the generic
sense which leads people to try to work around *every* case = where
they're used.  This is unnecessary.  A = tombstone over a single row
isn't a problem, especially if = you're only fetching that one row back.
Tombstones can be = quite terrible under a few conditions:

1. = When a range tombstone shadows hundreds / thousands / millions of
rows.  This wasn't even detectable prior to Cassandra 3 = unless you
were either looking for it specifically or were = doing CPU profiling:
http://thelastpickle.com/blog/2018/07/05/undetectable-tombstone= s-in-apache-cassandra.html
2. When rows were = frequently created then deleted, and scanned over.
This is = the queue pattern that we detest so much.
3. When they'd = be created as a side effect from over writing
collections. =  This is an accident typically.

The = 'active' flag is good if you want to be able to go back and look
at old deleted assignments.  If you don't care about = that, use a
normal delete.

Jon

On Tue, Apr 9, 2019 at 7:00 = AM Li, George <guangxing.li@pearson.com> wrote:

Hi,

I have a table defined like this:

CREATE TABLE myTable (
course_id = text,
assignment_id text,
assignment_item_id = text,
data text,
boolean active,
PRIMARY KEY (course_id, assignment_id, assignment_item_id)
);
i.e. course_id as the partition key and = assignment_id, assignment_item_id as clustering keys.

After data is populated, some delete queries by course_id and = assignment_id occurs, e.g. "DELETE FROM myTable WHERE course_id =3D 'C' = AND assignment_id =3D 'A1';". This would create tombstones so query = "SELECT * FROM myTable WHERE course_id =3D 'C';" would be affected, = right? Would query "SELECT * FROM myTable WHERE course_id =3D 'C' AND = assignment_id =3D 'A2';" be affected too?

For= query "SELECT * FROM myTable WHERE course_id =3D 'C';", to workaround = the tombstone problem, we are thinking about not doing hard deletes, = instead doing soft deletes. So instead of doing "DELETE FROM myTable = WHERE course_id =3D 'C' AND assignment_id =3D 'A1';", we do "UPDATE = myTable SET active =3D false WHERE course_id =3D 'C' AND assignment_id =3D= 'A1';". Then in the application, we do query "SELECT * FROM myTable = WHERE course_id =3D 'C';" and filter out records that have "active" = equal to "false". I am not really sure this would improve performance = because C* still has to scan through all records with the partition key = "C". It is just instead of scanning through X records + Y tombstone = records with hard deletes that generate tombstones, it now scans through = X + Y records with soft deletes and no tombstones. Am I right?

Thanks.

George

---------------------------------------------------------------= ------
To unsubscribe, e-mail: = user-unsubscribe@cassandra.apache.org
For additional = commands, e-mail: user-help@cassandra.apache.org


= --Apple-Mail=_CB990117-0A16-4DB1-BB0D-465959F979B4--