Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id A60B1200B8B for ; Tue, 4 Oct 2016 14:10:14 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id A358D160AC9; Tue, 4 Oct 2016 12:10:14 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 990C3160AC5 for ; Tue, 4 Oct 2016 14:10:13 +0200 (CEST) Received: (qmail 55816 invoked by uid 500); 4 Oct 2016 12:10:12 -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 55806 invoked by uid 99); 4 Oct 2016 12:10:12 -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; Tue, 04 Oct 2016 12:10:12 +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 BB5D9C18C2 for ; Tue, 4 Oct 2016 12:10:11 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.379 X-Spam-Level: ** X-Spam-Status: No, score=2.379 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, RCVD_IN_SORBS_SPAM=0.5, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd1-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id ldIhIfHtwl7I for ; Tue, 4 Oct 2016 12:10:08 +0000 (UTC) Received: from mail-vk0-f42.google.com (mail-vk0-f42.google.com [209.85.213.42]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTPS id D57B85F368 for ; Tue, 4 Oct 2016 12:10:07 +0000 (UTC) Received: by mail-vk0-f42.google.com with SMTP id z126so187638731vkd.0 for ; Tue, 04 Oct 2016 05:10:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to; bh=iagde8lrtvl87CF1VeZM20V+ysoQ6IIgLyG7XPVMbzU=; b=jwIpr585oxjYvKSbDKbSsPlqYqN+pVxVuEfpKLTj3w0l7d9RqhljkjA8Ls/p8S7txz LR2h77SnDZPse5v4v66o2+iriFWaEZkWHOblpZ8dH9DK4THXzBv1q8GtkosnTsiXHlx4 9vZxXZTod8oCZCNvCiTyiuUEk4jqTMRSUGwj8IxAy/TE45QAkQReSiz9lx81+ausLRHZ DEix+NcoWDIgzSuK4aVRdfabTvrxjQ+n8Nie6tbKWT3YsQTbrp+f1Qcuv4qdd++QfSgv 0XkSp7nqp7CRLgKTRSlgOa1NSve/62VcKNpKTMVoImtc9ZcQq/R59ghM0g8PcLy3arv/ nj9g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to; bh=iagde8lrtvl87CF1VeZM20V+ysoQ6IIgLyG7XPVMbzU=; b=OoDuJKXmdNV2yzovF2vRwQWTCX/Vcoq5OaKn72a1yZ+MrH/14PA7lO5d8wqy9zi9fz LXYsGnA6ErU2FfY/kBCd2BOdPUFv7g5WxujnLpi5o+rmTJe0NMYDz7KjqtwXelMgxIbj 4V5C2CDJfAG97ke6R+eKTWCyF1U/L4KiNEWVe/zLsgR1jbd7JOnOD3dI83nz4GGvGRsJ sr8o/x9OX+BxMXrK1WDmHjT/7YXooYvJZGyaLQakEgeOqHeyPr2wnV8rLyGC/+me+oX9 zfANGPe/h03xry3H0QP8zd3co8zYo1WqTgUqpSbl0MRSkEak6kW3gCEmEFEMWEQWiY3k fy0A== X-Gm-Message-State: AA6/9RnxKq3IG5/OOUF9I2CpMkXsJN7ltlFnFc9FeBBuD8rXaM354sQS23BjffvPODkc7bl1qmoKAcFhN0Zcng== X-Received: by 10.31.97.67 with SMTP id v64mr1793267vkb.119.1475583001294; Tue, 04 Oct 2016 05:10:01 -0700 (PDT) MIME-Version: 1.0 Received: by 10.103.148.217 with HTTP; Tue, 4 Oct 2016 05:09:40 -0700 (PDT) In-Reply-To: References: From: DuyHai Doan Date: Tue, 4 Oct 2016 14:09:40 +0200 Message-ID: Subject: Re: Efficient model for a sorting To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=94eb2c096d96a0fd39053e08f0e0 archived-at: Tue, 04 Oct 2016 12:10:14 -0000 --94eb2c096d96a0fd39053e08f0e0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable MV is right now your best choice for this kind of sorting behavior. Secondary index (whatever the impl, SASI or Lucene) has a cost of scatter-gather if your cluster scale out. With MV you're at least guaranteed to hit a single node everytime On Tue, Oct 4, 2016 at 1:56 PM, Dorian Hoxha wrote= : > Can you use the lucene index https://github.com/Stratio/ > cassandra-lucene-index ? > > On Tue, Oct 4, 2016 at 1:27 PM, Benjamin Roth > wrote: > >> Hi! >> >> I have a frequently used pattern which seems to be quite costly in CS. >> The pattern is always the same: I have a unique key and a sorting by a >> different field. >> >> To give an example, here a real life example from our model: >> CREATE TABLE visits.visits_in ( >> user_id int, >> user_id_visitor int, >> created timestamp, >> PRIMARY KEY (user_id, user_id_visitor) >> ) WITH CLUSTERING ORDER BY (user_id_visitor ASC) >> >> CREATE MATERIALIZED VIEW visits.visits_in_sorted_mv AS >> SELECT user_id, created, user_id_visitor >> FROM visits.visits_in >> WHERE user_id IS NOT NULL AND created IS NOT NULL AND user_id_visito= r >> IS NOT NULL >> PRIMARY KEY (user_id, created, user_id_visitor) >> WITH CLUSTERING ORDER BY (created DESC, user_id_visitor DESC) >> >> This simply represents people, that visited my profile sorted by date >> desc but only one entry per visitor. >> Other examples with the same pattern could be a whats-app-like inbox >> where the last message of each sender is shown by date desc. There are l= ots >> of examples for that pattern. >> >> E.g. in redis I'd just use a sorted set, where the key could be like >> "visits_${user_id}", set key would be user_id_visitor and score >> the created timestamp. >> In MySQL I'd create the table with PK on user_id + user_id_visitor and >> create an index on user_id + created >> In C* i use an MV. >> >> Is this the most efficient approach? >> I also could have done this without an MV but then the situation in our >> app would be far more complex. >> I know that denormalization is a common pattern in C* and I don't >> hesitate to use it but in this case, it is not as simple as it's not an >> append-only case but updates have to be handled correctly. >> If it is the first visit of a user, it's that simple, just 2 inserts in >> base table + denormalized table. But on a 2nd or 3rd visit, the 1st or 2= nd >> visit has to be deleted from the denormalized table before. Otherwise th= e >> visit would not be unique any more. >> Handling this case without an MV requires a lot more effort, I guess eve= n >> more effort than just using an MV. >> 1. You need kind of app-side locking to deal with race conditions >> 2. Read before write is required to determine if an old record has to be >> deleted >> 3. At least CL_QUORUM is required to make sure that read before write is >> always consistent >> 4. Old record has to be deleted on update >> >> I guess, using an MV here is more efficient as there is less roundtrip >> between C* and the app to do all that and the MV does not require strong >> consistency as MV updates are always local and are eventual consistent w= hen >> the base table is. So there is also no need for distributed locks. >> >> I ask all this as we now use CS 3.x and have been advised that 3.x is >> still not considered really production ready. >> >> I guess in a perfect world, this wouldn't even require an MV if SASI >> indexes could be created over more than 1 column. E.g. in MySQL this cas= e >> is nothing else than a BTree. AFAIK SASI indices are also BTrees, filter= ing >> by Partition Key (which should to be done anyway) and sorting by a field >> would perfectly do the trick. But from the docs, this is not possible ri= ght >> now. >> >> Does anyone see a better solution or are all my assumptions correct? >> >> -- >> Benjamin Roth >> Prokurist >> >> Jaumo GmbH =C2=B7 www.jaumo.com >> Wehrstra=C3=9Fe 46 =C2=B7 73035 G=C3=B6ppingen =C2=B7 Germany >> Phone +49 7161 304880-6 =C2=B7 Fax +49 7161 304880-1 >> AG Ulm =C2=B7 HRB 731058 =C2=B7 Managing Director: Jens Kammerer >> > > --94eb2c096d96a0fd39053e08f0e0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
MV is right now your best choice for this kind of sorting = behavior.

Secondary index (whatever the impl, SASI or Lu= cene) has a cost of scatter-gather if your cluster scale out. With MV you&#= 39;re at least guaranteed to hit a single node everytime

On Tue, Oct 4, 2016 at 1= :56 PM, Dorian Hoxha <dorian.hoxha@gmail.com> wrote:
Can you use the lucene ind= ex https://github.com/Stratio/cassandra-lucene-index ?

<= div class=3D"gmail_quote">On Tue, Oct 4, 2016 at 1:27 PM, Benjamin Roth <benjamin.roth@jaumo.com> wrote:
Hi!

I have a frequ= ently used pattern which seems to be quite costly in CS. The pattern is alw= ays the same: I have a unique key and a sorting by a different field.
=

To give an example, here a real life example from our m= odel:
CREATE TABLE visits.vi= sits_in (
=C2=A0 =C2=A0 user_id int,
=C2=A0 =C2=A0 user= _id_visitor int,
=C2=A0 =C2=A0 created timestamp,
=C2= =A0 =C2=A0 PRIMARY KEY (user_id, user_id_visitor)
) WITH CLUSTERI= NG ORDER BY (user_id_visitor ASC)

CREATE MATERIALIZED VIEW v= isits.visits_in_sorted_mv AS
=C2=A0 =C2=A0 SELECT user_id, create= d, user_id_visitor
=C2=A0 =C2=A0 FROM visits.visits_in
= =C2=A0 =C2=A0 WHERE user_id IS NOT NULL AND created IS NOT NULL AND user_id= _visitor IS NOT NULL
=C2=A0 =C2=A0 PRIMARY KEY (user_id, created,= user_id_visitor)
=C2=A0 =C2=A0 WITH CLUSTERING ORDER BY (created= DESC, user_id_visitor DESC)

This simply represent= s people, that visited my profile sorted by date desc but only one entry pe= r visitor.
Other examples with the same pattern could be a whats-= app-like inbox where the last message of each sender is shown by date desc.= There are lots of examples for that pattern.

E.g. in redis I'd just use a sorted set, where the key could be= like "visits_${user_id}", set key would be=C2=A0user_id_visitor=C2=A0and score the=C2=A0created=C2= =A0timestamp.
In MySQL I'd create the table with PK on us= er_id + user_id_visitor and create an index on user_id + created
= In C* i use an MV.

Is this the most efficient appr= oach?
I also could have done this without an MV but then the situ= ation in our app would be far more complex.
I know that denormali= zation is a common pattern in C* and I don't hesitate to use it but in = this case, it is not as simple as it's not an append-only case but upda= tes have to be handled correctly.
If it is the first visit of a u= ser, it's that simple, just 2 inserts in base table + denormalized tabl= e. But on a 2nd or 3rd visit, the 1st or 2nd visit has to be deleted from t= he denormalized table before. Otherwise the visit would not be unique any m= ore.
Handling this case without an MV requires a lot more effort,= I guess even more effort than just using an MV.=C2=A0
1. You nee= d kind of app-side locking to deal with race conditions
2. Read b= efore write is required to determine if an old record has to be deleted
3. At least CL_QUORUM is required to make sure that read before writ= e is always consistent
4. Old record has to be deleted on update<= /div>

I guess, using an MV here is more efficient as the= re is less roundtrip between C* and the app to do all that and the MV does = not require strong consistency as MV updates are always local and are event= ual consistent when the base table is. So there is also no need for distrib= uted locks.

I ask all this as we now use CS 3.x an= d have been advised that 3.x is still not considered really production read= y.

I guess in a perfect world, this wouldn't e= ven require an MV if SASI indexes could be created over more than 1 column.= E.g. in MySQL this case is nothing else than a BTree. AFAIK SASI indices a= re also BTrees, filtering by Partition Key (which should to be done anyway)= and sorting by a field would perfectly do the trick. But from the docs, th= is is not possible right now.

Does anyone= see a better solution or are all my assumptions correct?

--
Benjamin Roth
Prokurist

Jaumo Gmb= H =C2=B7 www.jaumo.com
Wehrstra=C3=9Fe 46 =C2=B7 73035 G=C3=B6ppingen =C2=B7 Germany
Phon= e +49 7161 304880-6 =C2=B7 Fax +49 7161 304880-1<= br>AG Ulm =C2=B7 HRB 731058 =C2=B7 Managing Director: Jens Kammerer


--94eb2c096d96a0fd39053e08f0e0--