phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristoffer Sjögren <sto...@gmail.com>
Subject Re: Slow delete
Date Tue, 01 Apr 2014 07:13:08 GMT
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 = 1 and b = 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=,code=0)

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.SubqueryParseNode@51436be2] (state=,code=0)


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 <jamestaylor@apache.org>wrote:

> Hi Kristoffer,
> No, this 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
> 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 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
> 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ögren <stoffe@gmail.com>wrote:
>
>> Hi
>>
>> I was doing some data migration today on phoenix and noticed that delete
>> ... 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
>>
>
>

Mime
View raw message