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 81FB310C92 for ; Tue, 23 Apr 2013 15:22:27 +0000 (UTC) Received: (qmail 52314 invoked by uid 500); 23 Apr 2013 15:22:24 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 52292 invoked by uid 500); 23 Apr 2013 15:22:24 -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 52283 invoked by uid 99); 23 Apr 2013 15:22:24 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 23 Apr 2013 15:22:24 +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: domain of edlinuxguru@gmail.com designates 209.85.212.177 as permitted sender) Received: from [209.85.212.177] (HELO mail-wi0-f177.google.com) (209.85.212.177) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 23 Apr 2013 15:22:18 +0000 Received: by mail-wi0-f177.google.com with SMTP id hj19so924924wib.10 for ; Tue, 23 Apr 2013 08:21:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:in-reply-to:references:date:message-id :subject:from:to:content-type; bh=4Ay2JUJn6QN4s6IdNRQ8J/Do3MtfZqgkfkyUmEw1K9w=; b=B56NkbzHeci4bvPLzdoQ47FtmpKQoeC9fCxpHrybg7+41+HyGKHIB3uV6n11zUAkKZ etzs/YYyalpyNSo8D+OEv1ZO3c64x1L49E8D+RWWgxo41MOQLVOP52OOXp4kzOCss9zn XTIo6BSLyiR0lkiw+xqJiuvgHOy/hU3lPXlFxL3sHnybVH6hYr4GOR8DczpcUQM5ZRGT 1ljYhp1wgJfB55d4Ypa2eoZs1MyfmkUdWBDIHC7dHTIGKIW+2xINLh34pyiDL+8xUP2/ sk/V/DiCyRqawuwxlqVPBbjLqAWV9uthIhipkkwpHyD295CZt3YqFv8c5ZBrLr8WlrHK sOiw== MIME-Version: 1.0 X-Received: by 10.180.97.233 with SMTP id ed9mr20945436wib.32.1366730518475; Tue, 23 Apr 2013 08:21:58 -0700 (PDT) Received: by 10.194.170.34 with HTTP; Tue, 23 Apr 2013 08:21:58 -0700 (PDT) In-Reply-To: References: <5173C4A8.30106@gmail.com> Date: Tue, 23 Apr 2013 11:21:58 -0400 Message-ID: Subject: Re: Prepared Statement - cache duration (CQL3 - Cassandra 1.2.4) From: Edward Capriolo To: "user@cassandra.apache.org" Content-Type: multipart/alternative; boundary=f46d044306aa0ebc8004db08bf53 X-Virus-Checked: Checked by ClamAV on apache.org --f46d044306aa0ebc8004db08bf53 Content-Type: text/plain; charset=ISO-8859-1 Having to catch the exception and parse it is a bit ugly, however this is close to what someone might do with an SQLException to determine if the error was transient etc. If there is an error code it is possible that it could be added as an optional property of the InvalidRequestException in future versions. Switching to the "binany protocol" is not a method in thrift, it means your not using thrift at all. On Tue, Apr 23, 2013 at 11:13 AM, Stuart Broad wrote: > Hi Edward, > > Thanks for your reply - I was already using the prepare/execute cql > methods that you suggested. My problem is that these methods 'mask' the > PreparedQueryNotFoundException as an InvalidRequestException. At present I > catch the InvalidRequestException (when cassandra has been re-started) and > check the message text to figure out if I need to rebuild the prepared > queries (rather than building each time I call). > > Sylvain had suggested that I use the binary protocol as the exceptions are > more explicit so I am trying to determine how this can be done (I don't see > any obvious methods other than the cql ones for calling prepared > statements). > > Regards, > > Stuart > > > On Tue, Apr 23, 2013 at 4:05 PM, Edward Capriolo wrote: > >> Thrift has a prepare_cql call which returns an ID. Then it has an >> exececute_cql call which takes the id and a map or variable bindings. >> >> >> On Tue, Apr 23, 2013 at 10:29 AM, Stuart Broad wrote: >> >>> Hi all, >>> >>> I just realised that the binary protocol is the low-level thrift api >>> that I was originally using (Cassandra.Client>> get / insert ...). How can >>> a prepared statement be called through the thrift api (i.e. not the cql >>> methods)? >>> >>> Cheers, >>> >>> Stuart >>> >>> >>> On Tue, Apr 23, 2013 at 11:48 AM, Stuart Broad wrote: >>> >>>> Hi Sylvain, >>>> >>>> Thanks for your response. I am handling the >>>> 'PreparedQueryNotFoundException' more for the case of a cassandra re-start >>>> (rather then expecting to build 100000 statements). >>>> >>>> I am not familiar with the binary protocol - which class/methods should >>>> I look at? >>>> >>>> Regards, >>>> >>>> Stuart >>>> >>>> >>>> >>>> On Tue, Apr 23, 2013 at 11:29 AM, Sylvain Lebresne < >>>> sylvain@datastax.com> wrote: >>>> >>>>> In thrift, a lot of exceptions (like PreparedQueryNotFoundException) >>>>> are simply returned as InvalidRequestException. The reason for that was a >>>>> mix of not wanting to change the thrift API too much and because we didn't >>>>> knew how to return a lot of different exception with thrift without making >>>>> it horrible to work with. So you'll probably have to parse strings here >>>>> indeed. >>>>> >>>>> This will be cleaner/less fragile if you use the binary protocol as >>>>> exceptions are more fined grained there. >>>>> >>>>> Though taking a step back (and without saying that you shouldn't >>>>> handle the case where a query is not prepared on the node you contact), if >>>>> you're really considering preparing more than 100000 statements, I'd >>>>> suggest that it might be worth benchmarking whether using prepared >>>>> statements in your case is really going to be worth the trouble. Just >>>>> saying. >>>>> >>>>> -- >>>>> Sylvain >>>>> >>>>> >>>>> >>>>> On Tue, Apr 23, 2013 at 12:14 PM, Stuart Broad wrote: >>>>> >>>>>> Hi Sorin, >>>>>> >>>>>> The PreparedQueryNotFoundException is not thrown from >>>>>> Cassandra.Client>>execute_prepared_cql3_query method. I created some >>>>>> prepared statements and then re-started cassandra and received the >>>>>> following exception: >>>>>> >>>>>> InvalidRequestException(why: Prepared query with ID 1124421588 not >>>>>> found (either the query was not prepared on this host (maybe the host has >>>>>> been restarted?) or you have prepared more than 100000 queries and queries >>>>>> 1124421588 has been evicted from the internal cache)) >>>>>> >>>>>> The best I have been able to come up with is the following: >>>>>> >>>>>> try { >>>>>> client.execute_prepared_cql3_query(psId, bindValues, >>>>>> ..); >>>>>> } catch (InvalidRequestException invEx) { >>>>>> String why = invEx.getWhy(); >>>>>> CLogger.logger().warning(why); >>>>>> if(why.startsWith("Prepared query with ID")) { >>>>>> rebuildPreparedStatement(preparedStatement); >>>>>> client.execute_prepared_cql3_query(psId, >>>>>> bindValues, ..); >>>>>> } else { >>>>>> throw invEx; >>>>>> } >>>>>> } >>>>>> >>>>>> Obviously this is pretty fragile and would break if the cassandra >>>>>> message was changed...but it least it works for now! >>>>>> >>>>>> Cheers, >>>>>> >>>>>> Stuart >>>>>> >>>>>> >>>>>> On Sun, Apr 21, 2013 at 11:51 AM, Sorin Manolache wrote: >>>>>> >>>>>>> On 2013-04-19 13:57, Stuart Broad wrote: >>>>>>> >>>>>>>> Hi, >>>>>>>> >>>>>>>> I am using Cassandra.Client >>>>>>>> prepare_cql3_query/execute_**prepared_cql3_query to create and run >>>>>>>> some >>>>>>>> prepared statements. It is working well but I am unclear as to how >>>>>>>> long >>>>>>>> the server side 'caches' the prepared statements. Should a prepared >>>>>>>> statement be prepared for every new Cassandra.Client? Based on my >>>>>>>> limited testing it seems like I can create some prepared statements >>>>>>>> in >>>>>>>> one Cassandra.Client and use in another but I am not sure how >>>>>>>> reliable/lasting this is i.e. If I called the prepared statement >>>>>>>> again >>>>>>>> the next day would it still exist? What about if cassandra was >>>>>>>> re-started? >>>>>>>> >>>>>>>> _Background:_ >>>>>>>> >>>>>>>> I am creating prepared statements for batch updates of pre-defined >>>>>>>> lengths (e.g. 10000, 1000, 500, 250, 50, 10, 1) and wanted to know >>>>>>>> if >>>>>>>> these could just be set up once. We felt that using the prepared >>>>>>>> statements was easier than escaping values within a CQL statement >>>>>>>> and >>>>>>>> probably more performant. >>>>>>>> >>>>>>>> Thanks in advance for your help. >>>>>>>> >>>>>>>> >>>>>>> I've looked in Cassandra's code (v1.2.3). The cache of prepared >>>>>>> statements has a size of 100,000. So if you prepare more than 100 thousand >>>>>>> statements, the least recently used ones will vanish. You'll get the >>>>>>> exception PreparedQueryNotFoundException**, code 0x2500. >>>>>>> >>>>>>> Regards, >>>>>>> Sorin >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> > --f46d044306aa0ebc8004db08bf53 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Having to catch the exception and parse it is a bit u= gly, however this is close to what someone might do with an SQLException to= determine if the error was transient etc. =A0If there is an error code it = is possible that it could be added as an optional property of the InvalidRe= questException in future versions.=A0

Switching to the "binany protocol" is n= ot a method in thrift, it means your not using thrift at all.



On Tue, Apr 23, 2013 at 11:13 AM, Stuart Broad <stuart@moogsoft.com&= gt; wrote:
Hi Edward,

Thanks for your reply - I wa= s already using the prepare/execute cql methods that you suggested. =A0My p= roblem is that these methods 'mask' the PreparedQueryNotFoundExcept= ion as an InvalidRequestException. =A0At present I catch the InvalidRequest= Exception (when cassandra has been re-started) and check the message text t= o figure out if I need to rebuild the prepared queries (rather than buildin= g each time I call).

Sylvain had suggested that I use the binary protocol as= the exceptions are more explicit so I am trying to determine how this can = be done (I don't see any obvious methods other than the cql ones for ca= lling prepared statements).

Regards,

Stuart


On Tue, Apr 23, = 2013 at 4:05 PM, Edward Capriolo <edlinuxguru@gmail.com>= wrote:
Thrift has a prepare_cql ca= ll which returns an ID. Then it has an exececute_cql call which takes the i= d and a map or variable bindings.


On = Tue, Apr 23, 2013 at 10:29 AM, Stuart Broad <stuart@moogsoft.com&g= t; wrote:
Hi all,

<= div>I just realised that the binary protocol is the low-level thrift api th= at I was originally using (Cassandra.Client>> get / insert ...). =A0H= ow can a prepared statement be called through the thrift api (i.e. not the = cql methods)?

Cheers,

Stuart


On Tue, Apr 23, 2= 013 at 11:48 AM, Stuart Broad <stuart@moogsoft.com> wrote:=
Hi Sylvain,

<= div>Thanks for your response. =A0I am handling the 'PreparedQueryNotFou= ndException' more for the case of a cassandra re-start (rather then exp= ecting to build 100000 statements).

I am not familiar with the binary protocol - which clas= s/methods should I look at?

Regards,
Stuart



On Tue, Apr 23, 2013 at 11:29 AM, Sylvain Lebresne <sylv= ain@datastax.com> wrote:
In thrift, a lot of excepti= ons (like PreparedQueryNotFoundException) are simply returned as InvalidReq= uestException. The reason for that was a mix of not wanting to change the t= hrift API too much and because we didn't knew how to return a lot of di= fferent exception with thrift without making it horrible to work with. So y= ou'll probably have to parse strings here indeed.

This will be cleaner/less fragile if you use the binary prot= ocol as exceptions are more fined grained there.

T= hough taking a step back (and without saying that you shouldn't handle = the case where a query is not prepared on the node you contact), if you'= ;re really considering preparing more than=A0100000 statements, I'd sug= gest that it might be worth benchmarking whether using prepared statements = in your case is really going to be worth the trouble. Just saying.

--
Sylvain



On Tue, Apr= 23, 2013 at 12:14 PM, Stuart Broad <stuart@moogsoft.com> = wrote:
Hi Sorin,

The PreparedQueryNotFoundException is not thrown from Cassandra.Client>= ;>execute_prepared_cql3_query method. =A0I created some prepared stateme= nts and then re-started cassandra and received the following exception:

InvalidRequestException(why:=A0Prepared query with ID 1= 124421588 not found (either the query was not prepared on this host (maybe = the host has been restarted?) or you have prepared more than 100000 queries= and queries 1124421588 has been evicted from the internal cache))

The best I have been able to come up with is the follow= ing:

=A0 =A0 =A0 =A0 =A0 =A0 try=A0{
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 client.execute_prepared_cql3_query(psId, = bindValues, ..);
=A0 =A0 =A0 =A0 =A0 =A0 }=A0catch (InvalidRequestException invEx)=A0{<= /div>
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 String why =3D invEx.getWhy();
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 CLogger.logger().warning(why);
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 if(why.startsWith("Prepared quer= y with ID"))=A0{
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 rebuildPreparedStatement(prepa= redStatement);
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 client.exe= cute_prepared_cql3_query(psId, bindValues, ..);
=A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 }=A0else=A0{
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 throw invEx;
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 }
=A0 =A0 =A0 =A0 =A0 =A0 }<= /div>

Obviously this is pretty fragile and would break i= f the cassandra message was changed...but it least it works for now!
<= div>
Cheers,

Stuart


On Sun, Apr 21, = 2013 at 11:51 AM, Sorin Manolache <sorinm@gmail.com> wrote:
On 2013-04-19 13:57, Stuart Broad wrote= :
Hi,

I am using Cassandra.Client
prepare_cql3_query/execute_prepared_cql3_query to create and run som= e
prepared statements. =A0It is working well but I am unclear as to how long<= br> the server side 'caches' the prepared statements. =A0Should a prepa= red
statement be prepared for every new Cassandra.Client? =A0Based on my
limited testing it seems like I can create some prepared statements in
one Cassandra.Client and use in another but I am not sure how
reliable/lasting this is i.e. =A0If I called the prepared statement again the next day would it still exist? =A0What about if cassandra was re-starte= d?

_Background:_

I am creating prepared statements for batch updates of pre-defined
lengths (e.g. 10000, 1000, 500, 250, 50, 10, 1) and wanted to know if
these could just be set up once. =A0We felt that using the prepared
statements was easier than escaping values within a CQL statement and
probably more performant.

Thanks in advance for your help.


I've looked in Cassandra's code (v1.2.3). The cache of prepared sta= tements has a size of 100,000. So if you prepare more than 100 thousand sta= tements, the least recently used ones will vanish. You'll get the excep= tion PreparedQueryNotFoundException, code 0x2500.

Regards,
Sorin









--f46d044306aa0ebc8004db08bf53--