From user-return-432-apmail-phoenix-user-archive=phoenix.apache.org@phoenix.incubator.apache.org Wed Apr 2 01:02:20 2014 Return-Path: X-Original-To: apmail-phoenix-user-archive@minotaur.apache.org Delivered-To: apmail-phoenix-user-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 9F1F910F47 for ; Wed, 2 Apr 2014 01:02:20 +0000 (UTC) Received: (qmail 96459 invoked by uid 500); 2 Apr 2014 01:02:20 -0000 Delivered-To: apmail-phoenix-user-archive@phoenix.apache.org Received: (qmail 96420 invoked by uid 500); 2 Apr 2014 01:02:19 -0000 Mailing-List: contact user-help@phoenix.incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@phoenix.incubator.apache.org Delivered-To: mailing list user@phoenix.incubator.apache.org Received: (qmail 96412 invoked by uid 99); 2 Apr 2014 01:02:19 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Apr 2014 01:02:19 +0000 X-ASF-Spam-Status: No, hits=-1998.3 required=5.0 tests=ALL_TRUSTED,HTML_MESSAGE,RP_MATCHES_RCVD X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO mail.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with SMTP; Wed, 02 Apr 2014 01:02:18 +0000 Received: (qmail 95857 invoked by uid 99); 2 Apr 2014 01:01:58 -0000 Received: from minotaur.apache.org (HELO minotaur.apache.org) (140.211.11.9) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Apr 2014 01:01:58 +0000 Received: from localhost (HELO mail-yk0-f181.google.com) (127.0.0.1) (smtp-auth username jamestaylor, mechanism plain) by minotaur.apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Apr 2014 01:01:57 +0000 Received: by mail-yk0-f181.google.com with SMTP id 131so5331702ykp.12 for ; Tue, 01 Apr 2014 18:01:56 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=8G7mPcNq1m8nS5sfJDUdXAEGL5qOA+eBl4D/el2xIT4=; b=eZGRGvQ/QNw0K1hkt1hYvTcfgbTc6k9q3ESsllWwWDajuD+YnMFhm6dZoGtdVgqIN+ lM2DQdmmXcnRIjYofMZuxpnRhKa994a02dwc/XKC2WHK9etQlX6zkOSLz85W2Sald7Ar 37YEH6zcg+ddja8fTnG81zvOAj9ryL2NJgo47Xo8DeXuIejR/H492QvMhAOKvsscrg29 HCGU3dTyQ0DMoIRs2WX9qXkstas6gKtV57ZfOCuRYvow6hb8RyPcJLecHYlyOIKDntso ouRJC8HF+tk4hVG5CBa3EzlWxbE6j/totYgGaRi3M/gHM6nFVUEhMrHlcJNw5a/fIOT1 PFnQ== MIME-Version: 1.0 X-Received: by 10.236.75.162 with SMTP id z22mr49808798yhd.32.1396400516678; Tue, 01 Apr 2014 18:01:56 -0700 (PDT) Received: by 10.170.195.68 with HTTP; Tue, 1 Apr 2014 18:01:56 -0700 (PDT) In-Reply-To: References: Date: Tue, 1 Apr 2014 18:01:56 -0700 Message-ID: Subject: Re: Slow delete From: James Taylor To: "user@phoenix.incubator.apache.org" Content-Type: multipart/alternative; boundary=20cf300514bcc2cfd604f604d4c0 X-Virus-Checked: Checked by ClamAV on apache.org --20cf300514bcc2cfd604f604d4c0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Use the row value constructor syntax for this: $ delete from t where (a,b) in ((1,1), (1,2),(1,3)); Thanks, James On Tue, Apr 1, 2014 at 12:13 AM, Kristoffer Sj=F6gren wro= te: > Sorry for late reply. > > Here's the table > > $ create table t (a INTEGER not null, b INTEGER not null constraint pk > primary key(a,b)); > $ upsert into t values (1,1); > > I did single delete statement / transaction for a composite primary key. > Also tried to batch multiple delete statements / transaction. > > $ delete from t where a =3D 1 and b =3D 1; > > Can you show me an example of a IN ... WHERE DELETE statement? The > following does not seem to work? > > jdbc:phoenix:localhost> delete from t where a in (select a from t); > Error: com.salesforce.phoenix.parse.InParseNode[A, > com.salesforce.phoenix.parse.SubqueryParseNode@4b96fac5] (state=3D,code= =3D0) > > jdbc:phoenix:localhost> delete from t where (a,b) in (select a,b from t); > Error: > com.salesforce.phoenix.parse.InParseNode[com.salesforce.phoenix.parse.Row= ValueConstructorParseNode[A, > B], com.salesforce.phoenix.parse.SubqueryParseNode@51436be2] > (state=3D,code=3D0) > > > The following statement works, but the row is never deleted? Bug? > > $ delete from t where (a,b) in (1,1); > > The following statement does in fact work and also delete the row. > > $ delete from t where (a) in (1); > > > > > > > > On Fri, Mar 28, 2014 at 6:19 PM, James Taylor wro= te: > >> Hi Kristoffer, >> No, this isn't a known or normal issue. What version of Phoenix and HBas= e >> are you using? What does your DELETE statement look like? Perf-wise, it'= s >> best if you can: >> * Batch deletes (any mutation, really) to cut down on RPC traffic. The >> phoenix.mutate.batchSize config parameter controls that and defaults to >> 1000. >> * Run as few DELETE statements as possible. For example, if you're >> deleting by PK, then form a single DELETE statement with an IN clause in >> the WHERE clause. >> * Set auto commit on for your connection: conn.setAutoCommit(true) >> assuming you don't have to issue a DELETE statement for each row separat= ely. >> * If you do have to issue separate DELETE statement for each row, then >> keep auto commit as false and instead collect up batches of 1000 before >> issuing a commit. >> >> If you're doing all that, and it's still slow, check your logs for >> exceptions and perhaps try issuing a delete from the HBase shell against >> your table as a test. >> >> Thanks, >> James >> >> >> On Fri, Mar 28, 2014 at 8:46 AM, Kristoffer Sj=F6gren = wrote: >> >>> Hi >>> >>> I was doing some data migration today on phoenix and noticed that delet= e >>> ... where statements are _really_ slow (around 1 second per row). >>> >>> Is this a known issue? Our installation works fine for inserts and >>> selects. >>> >>> Cheers, >>> -Kristoffer >>> >> >> > --20cf300514bcc2cfd604f604d4c0 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Use the row value constructor syntax for this:

$ delete from t where (a,b) in ((1,1), (1,2),(1,3));

=
Thanks,
James

On Tue, Apr 1, 2014 at 12:13 AM, Kristoffer = Sj=F6gren <stoffe@gmail.com> wrote:
Sorry for late reply.

Here's the ta= ble

$ create table t (a INTEGER not null, b INTEGE= R not null constraint pk primary key(a,b));
$=A0upsert into t= values (1,1);

I did single delete statement / transaction for a compo= site primary key. Also tried to batch multiple delete statements / transact= ion.

$ delete from t where a =3D 1 and b =3D 1;

Can you show me an example of a IN ... WHERE DELETE sta= tement? The following does not seem to work?

= jdbc:phoenix:localhost> delete from t where a in (select a from t);
Error: com.salesforce.phoenix.parse.InParseNode[A, com.salesforce.phoe= nix.parse.SubqueryParseNode@4b96fac5] (state=3D,code=3D0)
<= br>
jdbc:phoenix:localhost> delete from t where (a,b) in = (select a,b from t);
Error: com.salesforce.phoenix.parse.InParseNode[com.salesforce.phoenix= .parse.RowValueConstructorParseNode[A, B], com.salesforce.phoenix.parse.Sub= queryParseNode@51436be2] (state=3D,code=3D0)


The following statement works, but the row is never deleted?= Bug?

$ delete from t where (a,b) in (1,1);

The following statement does in fact work and also d= elete the row.

$ delete from t where (a) in (1);







On Fri, Mar 28, 2014 at 6:19 PM, James Taylor <jamestaylor@apache.org= > wrote:
Hi=A0Kristoffer,
No, th= is isn't a known or normal issue. What version of Phoenix and HBase are= you using? What does your DELETE statement look like? Perf-wise, it's = best if you can:
* Batch deletes (any mutation, really) to cut down on RPC traffic. The= phoenix.mutate.batchSize config parameter controls that and defaults to 10= 00.
* Run as few DELETE statements as possible. For example, if you're= deleting by PK, then form a single DELETE statement with an IN clause in t= he WHERE clause.
* Set auto commit on for your connection: conn.s= etAutoCommit(true) assuming you don't have to issue a DELETE statement = for each row separately.
* If you do have to issue separate DELETE statement for each row, then= keep auto commit as false and instead collect up batches of 1000 before is= suing a commit.

If you're doing all that, and = it's still slow, check your logs for exceptions and perhaps try issuing= a delete from the HBase shell against your table as a test.

Thanks,
James


On Fri, Mar 28, 2014 at= 8:46 AM, Kristoffer Sj=F6gren <stoffe@gmail.com> wrote:
Hi

I was= doing some data migration today on phoenix and noticed that delete ... whe= re statements are _really_ slow (around 1 second per row).=A0

Is this a known issue? Our installation works fine for = inserts and selects.

Cheers,
-Kristoffer



--20cf300514bcc2cfd604f604d4c0--