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 02B7A10B4E for ; Wed, 11 Dec 2013 15:04:19 +0000 (UTC) Received: (qmail 97281 invoked by uid 500); 11 Dec 2013 15:04:15 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 97254 invoked by uid 500); 11 Dec 2013 15:04:15 -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 97240 invoked by uid 99); 11 Dec 2013 15:04:14 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 11 Dec 2013 15:04:14 +0000 X-ASF-Spam-Status: No, hits=2.2 required=5.0 tests=HTML_MESSAGE,MIME_QP_LONG_LINE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of rwille@fold3.com designates 38.101.149.73 as permitted sender) Received: from [38.101.149.73] (HELO mx02.iarchives.com) (38.101.149.73) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 11 Dec 2013 15:04:10 +0000 Received: from mx02.iarchives.com (localhost [127.0.0.1]) by mx02.iarchives.com (Postfix) with ESMTP id D6032C10AB for ; Wed, 11 Dec 2013 08:03:49 -0700 (MST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed; d=fold3.com; h=date :subject:from:to:message-id:in-reply-to:mime-version :content-type; s=m1; bh=7b50KdzAp3AaeKIaFXHLTWbw+92+gzEVw+dAr6F7 Zhs=; b=IlTJFfKmGbG4ecf5jPjzzxmOC7ubmuvnPxp51BCOWw7TD6snoHxzU84J 9wvhmC0yDhB5Ne10BiwREFph/efFGoGG1vdRkKRztdsVW0JBGUf6wvvmHS89a9CV apCx3ZxkLZE/97iDYwDrYrOlo+6NNSnqPWMZHFsEBdtUGyI5ttY= Received: from PANDORA.iarchives.com (pandora.iarchives.com [192.168.100.88]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) by mx02.iarchives.com (Postfix) with ESMTPS id B7C77C0C04 for ; Wed, 11 Dec 2013 08:03:49 -0700 (MST) Received: from [10.88.88.10] (192.168.97.117) by PANDORA.iarchives.com (192.168.100.88) with Microsoft SMTP Server (TLS) id 14.1.438.0; Wed, 11 Dec 2013 08:03:59 -0700 User-Agent: Microsoft-MacOutlook/14.3.6.130613 Date: Wed, 11 Dec 2013 08:03:44 -0700 Subject: Re: What is the fastest way to get data into Cassandra 2 from a Java application? From: Robert Wille To: Message-ID: Thread-Topic: What is the fastest way to get data into Cassandra 2 from a Java application? In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="B_3469593829_14883644" X-Virus-Checked: Checked by ClamAV on apache.org --B_3469593829_14883644 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable Very good point. I=B9ve written code to do a very large number of inserts, bu= t I=B9ve only ever run it on a single-node cluster. I may very well find out when I run it against a multinode cluster that the performance benefits of large unlogged batches mostly go away. From: Sylvain Lebresne Reply-To: Date: Wednesday, December 11, 2013 at 6:52 AM To: "user@cassandra.apache.org" Subject: Re: What is the fastest way to get data into Cassandra 2 from a Java application? On Wed, Dec 11, 2013 at 1:52 PM, Robert Wille wrote: > Network latency is the reason why the batched query is fastest. One trip = to > Cassandra versus 1000. If you execute the inserts in parallel, then that > eliminates the latency issue. While it is true a batch will means only one client-server round trip, I'll note that provided you use the TokenAware load balancing policy, doing the parallelization client will save you intra-replica round-trips, which using a big batch won't. So that it might not be all that clear which ones is faster. And very large batches have the disadvantage that your are more likely to get a timeout (and if you do, you have to retry the whole batch, even though most of it has probably be inserted correctly). Overall, the best option probably has to do with parallelizing the inserts of reasonably sized batches, but what are the sizes for that is likely very use case dependent, you'll have to test. -- Sylvain =20 >=20 > From: Sylvain Lebresne > Reply-To: > Date: Wednesday, December 11, 2013 at 5:40 AM > To: "user@cassandra.apache.org" > Subject: Re: What is the fastest way to get data into Cassandra 2 from a= Java > application? >=20 > Then I suspect that this is artifact of your test methodology. Prepared > statements *are* faster than non prepared ones in general. They save some > parsing and some bytes on the wire. The savings will tend to be bigger fo= r > bigger queries, and it's possible that for very small queries (like the o= ne > you > are testing) the performance difference is somewhat negligible, but seein= g non > prepared statement being significantly faster than prepared ones almost s= urely > means you're doing wrong (of course, a bug in either the driver or C* is > always > possible, and always make sure to test recent versions, but I'm not aware= of > any such bug). >=20 > Are you sure you are warming up the JVMs (client and drivers) properly fo= r > instance. 1000 iterations is *really small*, if you're not warming things > up properly, you're not measuring anything relevant. Also, are you includ= ing > the preparation of the query itself in the timing? Preparing a query is n= ot > particulary fast, but it's meant to be done just once at the begining of = the > application lifetime. But with only 1000 iterations, if you include the > preparation in the timing, it's entirely possible it's eating a good chun= k of > the whole time. >=20 > But other prepared versus non-prepared, you won't get proper performance > unless > you parallelize your inserts. Unlogged batches is one way to do it (it's > really > all Cassandra does with unlogged batch, parallelizing). But as John Sanda > mentioned, another option is to do the parallelization client side, with > executeAsync.=20 >=20 > -- > Sylvain >=20 >=20 >=20 > On Wed, Dec 11, 2013 at 11:37 AM, David Tinker w= rote: >> Yes thats what I found. >>=20 >> This is faster: >>=20 >> for (int i =3D 0; i < 1000; i++) session.execute("INSERT INTO >> test.wibble (id, info) VALUES ('${"" + i}', '${"aa" + i}')") >>=20 >> Than this: >>=20 >> def ps =3D session.prepare("INSERT INTO test.wibble (id, info) VALUES (?, = ?)") >> for (int i =3D 0; i < 1000; i++) session.execute(ps.bind(["" + i, "aa" + >> i] as Object[])) >>=20 >> This is the fastest option of all (hand rolled batch): >>=20 >> StringBuilder b =3D new StringBuilder() >> b.append("BEGIN UNLOGGED BATCH\n") >> for (int i =3D 0; i < 1000; i++) { >> b.append("INSERT INTO ").append(ks).append(".wibble (id, info) >> VALUES ('").append(i).append("','") >> .append("aa").append(i).append("')\n") >> } >> b.append("APPLY BATCH\n") >> session.execute(b.toString()) >>=20 >>=20 >> On Wed, Dec 11, 2013 at 10:56 AM, Sylvain Lebresne >> wrote: >>> > >>>> >> This loop takes 2500ms or so on my test cluster: >>>> >> >>>> >> PreparedStatement ps =3D session.prepare("INSERT INTO perf_test.wibbl= e >>>> >> (id, info) VALUES (?, ?)") >>>> >> for (int i =3D 0; i < 1000; i++) session.execute(ps.bind("" + i, "aa"= + >>>> i)); >>>> >> >>>> >> The same loop with the parameters inline is about 1300ms. It gets >>>> >> worse if there are many parameters. >>> > >>> > >>> > Do you mean that: >>> > for (int i =3D 0; i < 1000; i++) >>> > session.execute("INSERT INTO perf_test.wibble (id, info) VALUES= (" + i >>> > + ", aa" + i + ")"); >>> > is twice as fast as using a prepared statement? And that the differen= ce >>> > is even greater if you add more columns than "id" and "info"? >>> > >>> > That would certainly be unexpected, are you sure you're not re-prepar= ing >>> the >>> > statement every time in the loop? >>> > >>> > -- >>> > Sylvain >>> > >>>> >> I know I can use batching to >>>> >> insert all the rows at once but thats not the purpose of this test.= I >>>> >> also tried using session.execute(cql, params) and it is faster but >>>> >> still doesn't match inline values. >>>> >> >>>> >> Composing CQL strings is certainly convenient and simple but is the= re >>>> >> a much faster way? >>>> >> >>>> >> Thanks >>>> >> David >>>> >> >>>> >> I have also posted this on Stackoverflow if anyone wants the points= : >>>> >> >>>> >>=20 >>>> http://stackoverflow.com/questions/20491090/what-is-the-fastest-way-to= -get- >>>> data-into-cassandra-2-from-a-java-application >>> > >>> > >>=20 >>=20 >>=20 >> -- >> http://qdb.io/ Persistent Message Queues With Replay and #RabbitMQ >> Integration >=20 --B_3469593829_14883644 Content-Type: text/html; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable
Very good point. I’ve w= ritten code to do a very large number of inserts, but I’ve only ever r= un it on a single-node cluster. I may very well find out when I run it again= st a multinode cluster that the performance benefits of large unlogged batch= es mostly go away.

From: Sylvain Lebresne <sylvain@data= stax.com>
Reply-To: <user@cassandra.apache.org>Date: Wednesday, December 11, 2013 a= t 6:52 AM
To: "user@cassandra.apache.org" <user@cassandra.apache.org>
Subject: Re: What is the fastest way to get data i= nto Cassandra 2 from a Java application?

= On Wed, Dec 11, 2013 at 1:52 PM, Robert Wille <rwille@fold3.com> wrot= e:
Network latency is the reason why the batched query is fastes= t. One trip to Cassandra versus 1000. If you execute the inserts in parallel= , then that eliminates the latency issue.

<= /div>
While it is true a batch will means only one client-server round t= rip, I'll note that provided you use the TokenAware load balancing policy, d= oing the parallelization client will save you intra-replica round-trips, whi= ch using a big batch won't. So that it might not be all that clear which one= s is faster. And very large batches have the disadvantage that your are more= likely to get a timeout (and if you do, you have to retry the whole batch, = even though most of it has probably be inserted correctly). Overall, the bes= t option probably has to do with parallelizing the inserts of reasonably siz= ed batches, but what are the sizes for that is likely very use case dependen= t, you'll have to test.

--
Sylvain
<= div>
 

=
From: Sylvain Lebresn= e <sylvain@datastax= .com>
Reply-To: <user@cassandra.apache.or= g>
Date: Wednesday, Decembe= r 11, 2013 at 5:40 AM
To: "user@cassandra.apache.or= g" <user@c= assandra.apache.org>
Subject: Re: What is the fastest way to get data into Cassandra 2 from a Java appl= ication?

Th= en I suspect that this is artifact of your test methodology. Prepared
<= div>statements *are* faster than non prepared ones in general. They save som= e
parsing and some bytes on the wire. The savings will tend to be = bigger for
bigger queries, and it's possible that for very small q= ueries (like the one you
are testing) the performance difference i= s somewhat negligible, but seeing non
prepared statement being sig= nificantly faster than prepared ones almost surely
means you're do= ing wrong (of course, a bug in either the driver or C* is always
p= ossible, and always make sure to test recent versions, but I'm not aware of<= /div>
any such bug).

Are you sure you are warmi= ng up the JVMs (client and drivers) properly for
instance. 1000 iterations is *really small*, if you're not warming things
up properly, you're not measuring anything relevant. Also, are you = including
the preparation of the query itself in the timing? Prepa= ring a query is not
particulary fast, but it's meant to be done ju= st once at the begining of the
application lifetime. But with only= 1000 iterations, if you include the
preparation in the timing, it= 's entirely possible it's eating a good chunk of
the whole time.

But other prepared versus non-prepared, you won't ge= t proper performance unless
you parallelize your inserts. Unlogged= batches is one way to do it (it's really
all Cassandra does with = unlogged batch, parallelizing). But as John Sanda
mentioned, anoth= er option is to do the parallelization client side, with
executeAs= ync. 

--
Sylvain



On Wed, Dec 11, 2013 at 11:37 AM, David Tinker <david.tinker= @gmail.com> wrote:
Yes thats what= I found.

This is faster:

for (int i =3D 0; i < 1000; i++) session.execute("INSERT INTO
tes= t.wibble (id, info) VALUES ('${"" + i}', '${"aa" + i}')")

Than this:

def ps =3D session.prepare("INSERT INTO test.wibble (id, info) VALUES (?, ?)"= )
for (int i =3D 0; i < 1000; i++) session.execute(ps.bind(["" + i,= "aa" +
i] as Object[]))

This is the fastest option of all (hand rolled batch):

StringBuilder b =3D new StringBuilder()
b.append("BEGIN UNLOGGED BATCH\n")
for (int i =3D 0; i < 1000; i++) {
    b.append("INSERT INTO ").append(ks).append(".wibble (id, info= )
VALUES ('").append(i).append("','")
            .append("aa").append(i).append("'= )\n")
}
b.append("APPLY BATCH\n")
session.execute(b.toString())


On Wed, Dec 11, 2013 at 10:56 AM, Sylvain Lebresne <sylvain@datastax.com> wrote:
>
>> This loop takes 2500ms or so on my test cluster:
>>
>> PreparedStatement ps =3D session.prepare("INSERT INTO perf_test.wibb= le
>> (id, info) VALUES (?, ?)")
>> for (int i =3D 0; i < 1000; i++) session.execute(ps.bind("" + i, = "aa" + i));
>>
>> The same loop with the parameters inline is about 1300ms. It gets<= br> >> worse if there are many parameters.
>
>
> Do you mean that:
>   for (int i =3D 0; i < 1000; i++)
>       session.execute("INSERT INTO perf_test.wibble (id= , info) VALUES (" + i
> + ", aa" + i + ")");
> is twice as fast as using a prepared statement? And that the differenc= e
> is even greater if you add more columns than "id" and "info"?
>
> That would certainly be unexpected, are you sure you're not re-prepari= ng the
> statement every time in the loop?
>
> --
> Sylvain
>
>> I know I can use batching to
>> insert all the rows at once but thats not the purpose of this test= . I
>> also tried using session.execute(cql, params) and it is faster but=
>> still doesn't match inline values.
>>
>> Composing CQL strings is certainly convenient and simple but is th= ere
>> a much faster way?
>>
>> Thanks
>> David
>>
>> I have also posted this on Stackoverflow if anyone wants the point= s:
>>
>> http://stackoverflow.com/questions/20491090/what-is-the-fastest-way-to-= get-data-into-cassandra-2-from-a-java-application
>
>



--
http://qdb.io/ Persistent Message Queues With Replay and #= RabbitMQ Integration


 --B_3469593829_14883644--