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 4C40310E80 for ; Wed, 19 Feb 2014 15:27:34 +0000 (UTC) Received: (qmail 54683 invoked by uid 500); 19 Feb 2014 15:27:31 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 54655 invoked by uid 500); 19 Feb 2014 15:27:30 -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 54647 invoked by uid 99); 19 Feb 2014 15:27:30 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 19 Feb 2014 15:27:30 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy includes SPF record at spf.trusted-forwarder.org) Received: from [209.85.217.182] (HELO mail-lb0-f182.google.com) (209.85.217.182) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 19 Feb 2014 15:27:24 +0000 Received: by mail-lb0-f182.google.com with SMTP id w7so401014lbi.27 for ; Wed, 19 Feb 2014 07:27:04 -0800 (PST) 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:date :message-id:subject:from:to:content-type; bh=tRgrKsmWcraQJGIHEx6CLc7LOSxZThngExVvJiZisqA=; b=Ypx4fl/sU8o6IhBGBI27BzjYCErnmdcjl/XvJTRU5Y7CdjrVTfP5qGC75qGsR7lz2H GmIMY77rpE/l98kyqH4HGfelpn7rQHzH9wZy9GReTksBioWtYulGpu1h/vbIjZTJAR9e XTaR6UAglkTPI0FJ1b+kp6adGzwEf5YtKUTtv6x4getQisr0kKNVyTq0VVl2msrMwPZH eY9j7zfH6RhTi3aKaUUu9tWxFqSCdQVlIyVhv1txscON//+360r/UyTF9noUGt64QxWz TrMi1lXpyH3rfGeNHzmYu91fbirZoGZIE/D/iAH/6Qel6Mi6Vpsl2W69tsdogxRzrKSw tc1g== X-Gm-Message-State: ALoCoQn/WMH6CaqFJq9ZJo0wkrSSa4a+J3Cb1lc8R0FU+eDykFh2UjecTCr801fj0iFKjr8q396B MIME-Version: 1.0 X-Received: by 10.112.161.2 with SMTP id xo2mr738973lbb.80.1392823623936; Wed, 19 Feb 2014 07:27:03 -0800 (PST) Received: by 10.112.95.167 with HTTP; Wed, 19 Feb 2014 07:27:03 -0800 (PST) In-Reply-To: References: Date: Wed, 19 Feb 2014 09:27:03 -0600 Message-ID: Subject: Re: Performance problem with large wide row inserts using CQL From: Nate McCall To: Cassandra Users Content-Type: multipart/alternative; boundary=001a11c334ae57049304f2c405f2 X-Virus-Checked: Checked by ClamAV on apache.org --001a11c334ae57049304f2c405f2 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi R=FCdiger, I just saw this after I answered on the SO thread: http://stackoverflow.com/questions/21778671/cassandra-how-to-insert-a-new-w= ide-row-with-good-performance-using-cql/21884943#21884943 On Wed, Feb 19, 2014 at 8:57 AM, John Sanda wrote: > From a quick glance at your code, it looks like you are preparing your > insert statement multiple times. You only need to prepare it once. I woul= d > expect to see some improvement with that change. > > > On Wed, Feb 19, 2014 at 5:27 AM, R=FCdiger Klaehn wro= te: > >> Hi all, >> >> I am evaluating Cassandra for satellite telemetry storage and analysis. = I >> set up a little three node cluster on my local development machine and >> wrote a few simple test programs. >> >> My use case requires storing incoming telemetry updates in the database >> at the same rate as they are coming in. A telemetry update is a map of >> name/value pairs that arrives at a certain time. >> >> The idea is that I want to store the data as quickly as possible, and >> then later store it in an additional format that is more amenable to >> analysis. >> >> The format I have chosen for my test is the following: >> >> CREATE TABLE IF NOT EXISTS test.wide ( >> time varchar, >> name varchar, >> value varchar, >> PRIMARY KEY (time,name)) >> WITH COMPACT STORAGE >> >> The layout I want to achieve with this is something like this: >> >> +-------+-------+-------+-------+-------+-------+ >> | | name1 | name2 | name3 | ... | nameN | >> | time +-------+-------+-------+-------+-------+ >> | | val1 | val2 | val3 | ... | valN | >> +-------+-------+-------+-------|-------+-------+ >> >> (Time will at some point be some kind of timestamp, and value will becom= e >> a blob. But this is just for initial testing) >> >> The problem is the following: I am getting very low performance for bulk >> inserts into the above table. In my test program, each insert has a new, >> unique time and creates a row with 10000 name/value pairs. This should m= ap >> into creating a new row in the underlying storage engine, correct? I do >> that 1000 times and measure both time per insert and total time. >> >> I am getting about 0.5s for each insert of 10000 name/value pairs, which >> is much lower than the rate at which the telemetry is coming in at my >> system. I have read a few previous threads on this subject and am using >> batch prepared statements for maximum performance ( >> https://issues.apache.org/jira/browse/CASSANDRA-4693 ). But that does >> not help. >> >> Here is the CQL benchmark: >> https://gist.github.com/rklaehn/9089304#file-cassandratestminimized-scal= a >> >> I have written the exact same thing using the thrift API of astyanax, an= d >> I am getting much better performance. Each insert of 10000 name/values >> takes 0.04s using a ColumnListMutation. When I use async calls for both >> programs, as suggested by somebody on Stackoverflow, the difference gets >> even larger. The CQL insert remains at 0.5s per insert on average, where= as >> the astyanax ColumnListMutation approach takes 0.01s per insert on >> average, even on my test cluster. That's the kind of performance I need. >> >> Here is the thrift benchmark, modified from an ast example: >> https://gist.github.com/rklaehn/9089304#file-astclient-java >> >> I realize that running on a test cluster on localhost is not a 100% >> realistic test. But nevertheless you would expect both tests to have >> roughly similar performance. >> >> I saw a few suggestions to create a table with CQL and fill it using the >> thrift API. For example in this thread >> http://mail-archives.apache.org/mod_mbox/cassandra-user/201309.mbox/%3C5= 23334B8.8070802@gmail.com%3E. But I would very much prefer to use pure CQL = for this. It seems that the >> thrift API is considered deprecated, so I would not feel comfortable >> starting a new project using a legacy API. >> >> I already posted a question on SO about this, but did not get any >> satisfactory answer. Just general performance tuning tips that do nothin= g >> to explain the difference between the CQL and thrift approaches. >> >> http://stackoverflow.com/questions/21778671/cassandra-how-to-insert-a-ne= w-wide-row-with-good-performance-using-cql >> >> Am I doing something wrong, or is this a fundamental limitation of CQL. >> If the latter is the case, what's the plan to mitigate the issue? >> >> There is a JIRA issue about this ( >> https://issues.apache.org/jira/browse/CASSANDRA-5959 ), but it is marked >> as a duplicate of https://issues.apache.org/jira/browse/CASSANDRA-4693 . >> But according to my benchmarks batch prepared statements do not solve th= is >> issue! >> >> I would really appreciate any help on this issue. The telemetry data I >> would like to import into C* for testing contains ~2*10^12 samples, wher= e >> each sample consists of time, value and status. If quick batch insertion= is >> not possible, I would not even be able to insert it in an acceptable tim= e. >> >> best regards, >> >> R=FCdiger >> > > > > -- > > - John > --=20 ----------------- Nate McCall Austin, TX @zznate Co-Founder & Sr. Technical Consultant Apache Cassandra Consulting http://www.thelastpickle.com --001a11c334ae57049304f2c405f2 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi=A0R=FCdiger,=A0


On Wed,= Feb 19, 2014 at 8:57 AM, John Sanda <john.sanda@gmail.com> wrote:
From a quick glance at your= code, it looks like you are preparing your insert statement multiple times= . You only need to prepare it once. I would expect to see some improvement = with that change.


On Wed, Feb 19, 2014 at 5:27 AM, R=FCdig= er Klaehn <rklaehn@gmail.com> wrote:
Hi all,

I am evaluating Cassandra for satellit= e telemetry storage and analysis. I set up a little three node cluster on m= y local development machine and wrote a few simple test programs.

My use case requires storing incoming telemetry updates in the databas= e at the same rate as they are coming in. A telemetry update is a map of na= me/value pairs that arrives at a certain time.

The idea is that I wa= nt to store the data as quickly as possible, and then later store it in an = additional format that is more amenable to analysis.

The format I have chosen for my test is the following:
CREATE TABLE IF NOT EXISTS test.wide ( time varchar, name varchar, value varchar, PRIMARY KEY (time,name)) WITH COMPACT STORAGE
The layout I want to achieve with th= is is something like this:
+-------+-------+-------+-------+-=
------+-------+
|       | name1 | name2 | name3 | ...   | nameN |
| time  +-------+-------+-------+-------+-------+
|       | val1  | val2  | val3  | ...   | valN  |
+-------+-------+-------+-------|-------+-------+
(Time will at= some point be some kind of timestamp, and value will become a blob. But th= is is just for initial testing)

The problem is the following: I am g= etting very low performance for bulk inserts into the above table. In my te= st program, each insert has a new, unique time and creates a row with 10000= name/value pairs. This should map into creating a new row in the underlyin= g storage engine, correct? I do that 1000 times and measure both time per i= nsert and total time.

I am getting about 0.5s for each insert of 10000 name/value = pairs, which is much lower than the rate at which the telemetry is coming i= n at my system. I have read a few previous threads on this subject and am u= sing batch prepared statements for maximum performance ( https://is= sues.apache.org/jira/browse/CASSANDRA-4693 ). But that does not help.

I have written the exact same thing using the thrift API of asty= anax, and I am getting much better performance. Each insert of 10000 name/v= alues takes 0.04s using a ColumnListMutation. When I use async= calls for both programs, as suggested by somebody on Stackoverflow, the di= fference gets even larger. The CQL insert remains at 0.5s per insert on ave= rage, whereas the astyanax ColumnListMutation approach takes 0.01s per insert on average, even on my test cluster. That's the kind of= performance I need.

Here is the thrift benchmark, modified from an ast example: = https://gist.github.com/rklaehn/9089304#file-astclient-java<= /a>

I saw a few suggestions = to create a table with CQL and fill it using the thrift API. For example in= this thread http:/= /mail-archives.apache.org/mod_mbox/cassandra-user/201309.mbox/%3C523334B8.8= 070802@gmail.com%3E . But I would very much prefer to use pure CQL for = this. It seems that the thrift API is considered deprecated, so I would not= feel comfortable starting a new project using a legacy API.

I already posted a question on SO about this, but did not ge= t any satisfactory answer. Just general performance tuning tips that do not= hing to explain the difference between the CQL and thrift approaches.
http:= //stackoverflow.com/questions/21778671/cassandra-how-to-insert-a-new-wide-r= ow-with-good-performance-using-cql

Am I doing something wrong, or is this a fundamen= tal limitation of CQL. If the latter is the case, what's the plan to mi= tigate the issue?

There is a JIRA issue about this ( https://issues.apache.org/jira/browse/CASSANDRA-5959 ), but it is m= arked as a duplicate of https://issues.apache.org/jira/browse/CASSA= NDRA-4693 . But according to my benchmarks batch prepared statements do= not solve this issue!

I would really appreciate any help on this issue. The teleme= try data I would like to import into C* for testing contains ~2*10^12 sampl= es, where each sample consists of time, value and status. If quick batch in= sertion is not possible, I would not even be able to insert it in an accept= able time.

best regards,

R=FCdiger



--

- John



--
-----------------
Nate McCall
Austin, TX
@zznate

Co-Fo= under & Sr. Technical Consultant
Apache Cassandra Consulting
http://www.thelastpi= ckle.com
--001a11c334ae57049304f2c405f2--