Return-Path: X-Original-To: apmail-cassandra-user-archive@www.apache.org Delivered-To: apmail-cassandra-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 97B271739F for ; Sun, 3 May 2015 23:06:23 +0000 (UTC) Received: (qmail 48315 invoked by uid 500); 3 May 2015 23:06:19 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 48270 invoked by uid 500); 3 May 2015 23:06:19 -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 48260 invoked by uid 99); 3 May 2015 23:06:19 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 03 May 2015 23:06:19 +0000 X-ASF-Spam-Status: No, hits=3.5 required=5.0 tests=HTML_MESSAGE,SPF_PASS,URI_HEX X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: message received from 54.191.145.13 which is an MX secondary for user@cassandra.apache.org) Received: from [54.191.145.13] (HELO mx1-us-west.apache.org) (54.191.145.13) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 03 May 2015 23:06:15 +0000 Received: from mail-ie0-f177.google.com (mail-ie0-f177.google.com [209.85.223.177]) by mx1-us-west.apache.org (ASF Mail Server at mx1-us-west.apache.org) with ESMTPS id 1F95624B3F for ; Sun, 3 May 2015 23:05:55 +0000 (UTC) Received: by iejt8 with SMTP id t8so119507847iej.2 for ; Sun, 03 May 2015 16:05:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:sender:in-reply-to:references:date:message-id:subject :from:to:content-type; bh=kX79O7U4Sw69SHoVaSn25Iqffs1ZDlEV202sloWo2jU=; b=LSLOUgfFRsUKCOxyHKiocFdO0RgDEVR11bSU5aGH3zeav6hqJLtzI8OVrN5l/3+GkD wGrC68pJt5GZsx5AciSMl4uP937jGjkA/eGf5JesDa/w3otauqEp/wZBHRbv63RFNW9C Rd2UnQ2cxvbkedXsFVU+C1Y+jKbTaNUIUhDay9X1N3c0nSNPps1uOJstrrFrSk0g9dsJ VwqjVKBP0yStiMSu4NSbwypynHlB/CoXgpxgrHkIcN093bfBCHAFxsyTDZTZKvdosbJN sD1oK6kCXaX6HKlWjxYQUnLS4ooGDA8m1QFHSwi2GJLE1lhM6sRB+IWKiVHWxmKSXlzH n+sA== MIME-Version: 1.0 X-Received: by 10.50.66.227 with SMTP id i3mr9767209igt.47.1430694354131; Sun, 03 May 2015 16:05:54 -0700 (PDT) Sender: erickramirezonline@gmail.com Received: by 10.64.24.231 with HTTP; Sun, 3 May 2015 16:05:54 -0700 (PDT) In-Reply-To: <1430247364917-7600561.post@n2.nabble.com> References: <1430247364917-7600561.post@n2.nabble.com> Date: Mon, 4 May 2015 09:05:54 +1000 X-Google-Sender-Auth: W1t3pTqrFI261Ux8zIc2e95BtV0 Message-ID: Subject: Re: Denormalization leads to terrible, rather than better, Cassandra performance -- I am really puzzled From: Erick Ramirez To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=047d7bdc157ac29fea0515357ce5 X-Virus-Checked: Checked by ClamAV on apache.org --047d7bdc157ac29fea0515357ce5 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello, there. In relation to the Java driver, I would recommend updating to the latest version as there were a lot of issues reported in versions earlier that 2.0.9 were the driver is incorrectly marking nodes as down/not available. In fact, there is a new version of the driver being released in the next 24-48 hours that reverts JAVA-425 to resolve this issue. Cheers, Erick *Erick Ramirez* About Me about.me/erickramirezonline Make a difference today! * Reduce your carbon footprint * Give back to the community * Write free software On Wed, Apr 29, 2015 at 4:56 AM, dlu66061 wrote: > Cassandra gurus, I am really puzzled by my observations, and hope to get > some help explaining the results. Thanks in advance. > > I think it has always been advocated in Cassandra community that > de-normalization leads to better performance. I wanted to see how much > performance improvement it can offer, but the results were totally > opposite. The performance degraded dramatically for simultaneously reques= ts > for the same set of data. > > *Environment:* > > I have a Cassandra cluster consisting of 3 AWS m3.large instances, with > Cassandra 2.0.6 installed and pretty much default settings. My program is > written in Java using Java Driver 2.0.8. > > *Normalized case:* > > I have two tables created with the following 2 CQL statements > > CREATE TABLE event (event_id UUID, time_token timeuuid, =E2=80=A6=C2=AD 3= 0 other > attributes, =E2=80=A6=C2=AD PRIMARY KEY (event_id)) > > CREATE TABLE event_index (index_key text, time_token timeuuid, event_id > UUID, PRIMARY KEY (index_key, time_token)) > > In my program, given the proper index_key and a token range > (tokenLowerBound to tokenUpperBound), I first query the event_index table > > *Query 1:* > > SELECT * FROM event_index WHERE index_key in (=E2=80=A6=C2=AD) AND time_t= oken > > tokenLowerBound AND time_token <=3D tokenUpperBound ORDER BY time_token A= SC > LIMIT 2000 > > to get a list of event_ids and then run the following CQL to get the even= t > details. > > *Query 2:* > > SELECT * FROM event WHERE event_id IN (a list of event_ids from the above > query) > > I repeat the above process, with updated token range from the previous > run. This actually performs pretty well. > > In this normalized process, I have to *run 2 queries* to get data: the > first one should be very quick since it is getting a slice of an internal= ly > wide row. The second query may take long because it needs to hit up to 20= 00 > rows of event table. > > *De-normalized case:* > > What if we can attach event detail to the index and run just 1 query? Lik= e > Query 1, would it be much faster since it is also getting a slice of an > internally wide row? > > I created a third table that merged the above two tables together. Notice > the first three attributes and the PRIMARY KEY definition are exactly the > same as the "event_index" table. > > CREATE TABLE event_index_with_detail (index_key text, time_token timeuuid= , > event_id UUID, =E2=80=A6 30 other attributes, =E2=80=A6=C2=AD PRIMARY KEY= (index_key, > time_token)) > > Then I can just run the following query to achieve my goal, with the same > index and token range as in query 1: > > *Query 3:* > > SELECT * FROM event_index_with_detail WHERE index_key in (=E2=80=A6=C2=AD= ) AND > time_token > tokenLowerBound AND time_token <=3D tokenUpperBound ORDER BY > time_token ASC LIMIT 2000 > > *Performance observations* > > Using Java Driver 2.0.8, I wrote a program that runs Query 1 + Query 2 in > the normalized case, or Query 3 in the denormalized case. All queries is > set with LOCAL_QUORUM consistency level. > > Then I created 1 or more instances of the program to simultaneously > retrieve the SAME set of 1 million events stored in Cassandra. Each test > runs for 5 minutes, and the results are shown below. > > > > 1 instance > > 5 instances > > 10 instances > > Normalized > > 89 > > 315 > > 417 > > Denormalized > > 100 > > *43* > > *3* > > Note that the unit of measure is number of operations. So in the > normalized case, the programs runs 89 times and retrieves 178K events for= a > single instance, 315 times and 630K events to 5 instances (each instance > gets about 126K events), and 417 times and 834K events to 10 instances > simultaneously (each instance gets about 83.4K events). > > Well for the de-normalized case, the performance is little better for a > single instance case, in which the program runs 100 times and retrieves > 200K events. However, it turns sharply south for multiple simultaneous > instances. All 5 instances completed successfully only 43 operations > together, and all 10 instances completed successfully only 3 operations > together. For the latter case, the log showed that 3 instances each > retrieved 2000 events successfully, and 7 other instances retrieved 0. > > In the de-normalized case, the program reported a lot of exceptions like > below: > > com.datastax.driver.core.exceptions.ReadTimeoutException, Cassandra > timeout during read query at consistency LOCAL_QUORUM (2 responses were > required but only 1 replica responded) > > com.datastax.driver.core.exceptions.NoHostAvailableException, All host(s) > tried for query failed (no host was tried) > > I repeated the two cases back and forth several times, and the results > remained the same. > > I also observed CPU usage on the 3 Cassandra servers, and they were all > much higher for the de-normalized case. > > > > 1 instance > > 5 instances > > 10 instances > > Normalized > > 7% usr, 2% sys > > 30% usr, 8% sys > > 40% usr, 10% sys > > Denormalized > > 44% usr, 0.3% sys > > 65% usr, 1% sys > > 70% usr, 2% sys > > *Questions* > > This is really not what I expected, and I am puzzled and have not figured > out a good explanation. > > - Why are there so many exceptions in the de-normalized case? I would > think Cassandra should be able to handle simultaneous accesses to the = same > data. Why are there NO exceptions for the normalized case? I meant tha= t the > environments for the two cases are basically the same. > - Is (internally) wide row only good for small amount of data under > each column name? > - Or is it an issue with Java Driver? > - Or did I do something wrong? > > > ------------------------------ > View this message in context: Denormalization leads to terrible, rather > than better, Cassandra performance -- I am really puzzled > > Sent from the cassandra-user@incubator.apache.org mailing list archive > at > Nabble.com. > --047d7bdc157ac29fea0515357ce5 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hello, there.

In relation to the Java d= river, I would recommend updating to the latest version as there were a lot= of issues reported in versions earlier that 2.0.9 were the driver is incor= rectly marking nodes as down/not available.

In fac= t, there is a new version of the driver being released in the next 24-48 ho= urs that reverts=C2=A0JAVA-425= to resolve this issue.

Cheers,
Erick=

Erick Ramirez

On Wed, Apr 29, 2015 at 4:56 AM, dlu66061 <dlu66061@yahoo.com> wrote:

Cassandra gurus, I am really puzzled by my observations, and hope to get some help explaining the results. Thanks in advance.

I think it has always been advocated in Cassandra community that de-normalization leads to better performance. I wanted to se= e how much performance improvement it can offer, but the results were totally opposite. The performance degraded dramatically for simultaneously requests= for the same set of data.

Environment:

I have a Cassandra cluster consisting of 3 AWS m3.large instances, with Cassandra 2.0.6 installed and pretty much default settings.= My program is written in Java using Java Driver 2.0.8.

Normalized case:

I have two tables created with the following 2 CQL statements

CREATE TABLE event (event_id UUID, time_token timeuuid, =E2=80=A6=C2=AD 30 other attributes, =E2=80=A6= =C2=AD PRIMARY KEY (event_id))

CREATE TABLE event_index (index_key text, time_token timeuuid, event_id UUID,=C2=A0=C2=A0 PRIMARY KE= Y (index_key, time_token))

In my program, given the proper index_key and a token range (tokenLowerBound to tokenUpperBound), I first query the event_index t= able

Query 1:

SELECT * FROM event_index WHERE index_key in (=E2=80=A6=C2=AD) AND time_token > tokenLowerBound AND time= _token <=3D tokenUpperBound ORDER BY time_token ASC LIMIT 2000

to get a list of event_ids and then run the following CQL to get the event details.

Query 2:

SELECT * FROM event WHERE event_id IN (a list of event_ids from the above query)

I repeat the above process, with updated token range from the previous run. This actually performs pretty well.

In this normalized process, I have to run 2 queries to get data: the first one should be very quick since it is getting a slice= of an internally wide row. The second query may take long because it needs to = hit up to 2000 rows of event table.

De-normalized case:

What if we can attach event detail to the index and run just 1 query? Like Query 1, would it be much faster since it is also gettin= g a slice of an internally wide row?

I created a third table that merged the above two tables together. Notice the first three attributes and the PRIMARY KEY definition = are exactly the same as the "event_index" table.

CREATE TABLE event_index_with_detail (index_key text, time_token timeuuid, event_id UUID= , =E2=80=A6 30 other attributes, =E2=80=A6=C2=AD PRIMARY KEY (index_key, time_token))

Then I can just run the following query to achieve my goal, with the same index and token range as in query 1:

Query 3:

SELECT * FROM event_index_with_detail WHERE index_key in (=E2=80=A6=C2=AD) AND time_token= > tokenLowerBound AND time_token <=3D tokenUpperBound ORDER BY time_token = ASC LIMIT 2000

Performance observations

Using Java Driver 2.0.8, I wrote a program that runs Query 1 + Query 2 in the normalized case, or Query 3 in the denormalized ca= se. All queries is set with LOCAL_QUORUM consistency level.

Then I created 1 or more instances of the program to simultaneously retrieve the SAME set of 1 million events stored in Cassandr= a. Each test runs for 5 minutes, and the results are shown below.

=C2=A0

1 instance

5 instances

10 instances

Normalized

89

315

417

Denormalized

100

43

3

Note that the unit of measure is number of operations. So in the normalized case, the programs runs 89 times and retrieves 178K event= s for a single instance, 315 times and 630K events to 5 instances (each instance = gets about 126K events), and 417 times and 834K events to 10 instances simultaneously (each instance gets about 83.4K events).

Well for the de-normalized case, the performance is little better for a single instance case, in which the program runs 100 tim= es and retrieves 200K events. However, it turns sharply south for multiple simultaneous instances. All 5 instances completed successfully only 43 operations together, and all 10 instances completed successfully only 3 operations together. For the latter case, the log showed that 3 instances e= ach retrieved 2000 events successfully, and 7 other instances retrieved 0.

In the de-normalized case, the program reported a lot of exceptions like below:

com.datastax.driver.core.exceptions.ReadTime= outException, Cassandra timeout during read query at consistency LOCAL_QUORUM (2 response= s were required but only 1 replica responded)

com.datastax.driver.core.exceptions.NoHostAv= ailableException, All host(s) tried for query failed (no host was tried)

I repeated the two cases back and forth several times, and the results remained the same.

I also observed CPU usage on the 3 Cassandra servers, and they were all much higher for the de-normalized case.

=C2=A0

1 instance

5 instances

10 instances

Normalized

7% usr, 2% sys

30% usr, 8% sys

40% usr, 10% sys

Denormalized

44% usr, 0.3% sys

65% usr, 1% sys

70% usr, 2% sys

Questions

This is really not what I expected, and I am puzzled and have not figured out a good explanation.

  • Why are there so many exceptions in the de-normalized case? I would think Cassandra should be able to handle simultaneous accesses to the same data. Why are there NO exceptions for the normalized case? I meant tha= t the environments for the two cases are basically the same.
  • Is (internally) wide row only good for small amount of data under each column name?
  • Or is it an issue with Java Driver?
  • Or did I do something wrong?
=09 =09 =09
View this message in context: Denormalization leads to terrible, rather than better, Cassandr= a performance -- I am really puzzled
Sent from the cassandra-user@incubator.apache.org mail= ing list archive at Nabble.com.

--047d7bdc157ac29fea0515357ce5--