From user-return-3678-apmail-phoenix-user-archive=phoenix.apache.org@phoenix.apache.org Sun Sep 6 16:36:30 2015 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 EEA7717792 for ; Sun, 6 Sep 2015 16:36:30 +0000 (UTC) Received: (qmail 53870 invoked by uid 500); 6 Sep 2015 16:36:25 -0000 Delivered-To: apmail-phoenix-user-archive@phoenix.apache.org Received: (qmail 53824 invoked by uid 500); 6 Sep 2015 16:36:25 -0000 Mailing-List: contact user-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@phoenix.apache.org Delivered-To: mailing list user@phoenix.apache.org Received: (qmail 53814 invoked by uid 99); 6 Sep 2015 16:36:25 -0000 Received: from Unknown (HELO spamd4-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 06 Sep 2015 16:36:25 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd4-us-west.apache.org (ASF Mail Server at spamd4-us-west.apache.org) with ESMTP id 2D283C0252 for ; Sun, 6 Sep 2015 16:36:25 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.879 X-Spam-Level: ** X-Spam-Status: No, score=2.879 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=3, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd4-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-us-west.apache.org ([10.40.0.8]) by localhost (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id YJg-sb0PZs8G for ; Sun, 6 Sep 2015 16:36:23 +0000 (UTC) Received: from mail-wi0-f172.google.com (mail-wi0-f172.google.com [209.85.212.172]) by mx1-us-west.apache.org (ASF Mail Server at mx1-us-west.apache.org) with ESMTPS id D229F20382 for ; Sun, 6 Sep 2015 16:36:22 +0000 (UTC) Received: by wicfx3 with SMTP id fx3so61240443wic.0 for ; Sun, 06 Sep 2015 09:36:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :content-type; bh=8bz0SrcoWtpLsBDxrGNAhJhlGRk8qaKFgLgO9AybFYg=; b=kjMeJ7g0J9nQwofClc33L/YCb6Ba1Bx4sz2Y34Vfq0B4apqNCAJjjGi+/VAdSFEF81 mN7Sj32tkDrDluAKPKU5FKS0R/CgS1mKtI9deGqRsU/IwQxPf4ekQk1xo7oQtgR6gfAV bWR6wLR7ZU+gQpUddRzUa4wojIiTwd/zQ8tQdVhq88+k4ielzFZYIFK/2jeI4eg6isaH vfbuM2n3wi3ernDgSXd2I49HN0mXdUxc291ld85LaBydIVFY3i93J9c6VxBNX6/OmRV3 K9ZjnwwOjTOxUq0TR1q/p50EuNhgCNIXYq7sHwT7xY74FOLqIXdpSW5oxFjnWi/NJPKQ M9kQ== X-Received: by 10.195.11.202 with SMTP id ek10mr27814627wjd.12.1441557381432; Sun, 06 Sep 2015 09:36:21 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.127.210 with HTTP; Sun, 6 Sep 2015 09:36:01 -0700 (PDT) In-Reply-To: References: From: Serega Sheypak Date: Sun, 6 Sep 2015 18:36:01 +0200 Message-ID: Subject: Re: Phoenix JDBC in web-app, what is the right pattern? To: user@phoenix.apache.org Content-Type: multipart/alternative; boundary=047d7b874cdaa4cec3051f16bbfe --047d7b874cdaa4cec3051f16bbfe Content-Type: text/plain; charset=UTF-8 Hi, approach above doesn't fit web-app. There are multiple simultaneous upserts comes from different threads. So the only thing is to put connection into thread-local and use one connection per thread. try (Connection conn = DriverManager.getConnection(url)) { conn.setAutoCommit(false); int batchSize = 0; int commitSize = 1000; // number of rows you want to commit per batch. Change this value according to your needs. try (Statement stmt = conn.prepareStatement(upsert)) { stmt.set ... while (there are records to upsert) { stmt.executeUpdate(); batchSize++; if (batchSize % commitSize == 0) { conn.commit(); } } conn.commit(); // commit the last batch of records } 2015-09-03 22:28 GMT+02:00 Jaime Solano : > Thanks for the explanation, Samarth! > > On Thu, Sep 3, 2015 at 4:11 PM, Samarth Jain wrote: > >> Hi Jaime, >> >> Phoenix is an embedded JDBC driver i.e. the connections are not meant to >> be pooled. Creating phoenix connections is a light weight operation. This >> is because all phoenix connections to a cluster from the Phoenix JDBC >> driver use the same underlying HConnection. >> >> If you end up pooling Phoenix connections then you will likely run into >> unexpected issues. It is likely that a request will leave a Phoenix >> connection not in a kosher state for the next request to use it. >> >> On Thu, Sep 3, 2015 at 12:58 PM, Jaime Solano >> wrote: >> >>> Hi Samarth, >>> In our project we were thinking about using Tomcat JDBC Connection Pool, >>> to handle Phoenix connections. You're saying this type of approach should >>> be avoided? What other approach should be followed if, in the scenario >>> Serega described, you experience a heavy load of users, all trying to >>> upsert at the same time (therefore, a lot of connections) ?? >>> Also, can you expand a little bit more on the implications of having a >>> pooling mechanism for Phoenix connections? >>> Thanks in advance! >>> -Jaime >>> >>> On Thu, Sep 3, 2015 at 3:35 PM, Samarth Jain >>> wrote: >>> >>>> Yes. PhoenixConnection implements java.sql.Connection. >>>> >>>> On Thu, Sep 3, 2015 at 12:34 PM, Serega Sheypak < >>>> serega.sheypak@gmail.com> wrote: >>>> >>>>> >Phoenix doesn't cache connections. You shouldn't pool them and you >>>>> shouldn't share them with multiple threads. >>>>> We are talking about java.sql.Connection, right? >>>>> >>>>> 2015-09-03 21:26 GMT+02:00 Samarth Jain : >>>>> >>>>>> Your pattern is correct. >>>>>> >>>>>> Phoenix doesn't cache connections. You shouldn't pool them and you >>>>>> shouldn't share them with multiple threads. >>>>>> >>>>>> For batching upserts, you could do something like this: >>>>>> >>>>>> You can do this via phoenix by doing something like this: >>>>>> >>>>>> try (Connection conn = DriverManager.getConnection(url)) { >>>>>> conn.setAutoCommit(false); >>>>>> int batchSize = 0; >>>>>> int commitSize = 1000; // number of rows you want to commit per >>>>>> batch. Change this value according to your needs. >>>>>> try (Statement stmt = conn.prepareStatement(upsert)) { >>>>>> stmt.set ... >>>>>> while (there are records to upsert) { >>>>>> stmt.executeUpdate(); >>>>>> batchSize++; >>>>>> if (batchSize % commitSize == 0) { >>>>>> conn.commit(); >>>>>> } >>>>>> } >>>>>> conn.commit(); // commit the last batch of records >>>>>> } >>>>>> >>>>>> You don't want commitSize to be too large since Phoenix client keeps >>>>>> the uncommitted rows in memory till they are sent over to HBase. >>>>>> >>>>>> >>>>>> >>>>>> On Thu, Sep 3, 2015 at 12:19 PM, Serega Sheypak < >>>>>> serega.sheypak@gmail.com> wrote: >>>>>> >>>>>>> Hi, I'm using phoenix in java web-application. App does upsert or >>>>>>> select by primary key. >>>>>>> What is the right pattern to do it? >>>>>>> - I create new connection for each request >>>>>>> - prepare and execute statement >>>>>>> - close stmt >>>>>>> - close connection >>>>>>> >>>>>>> Does phoenix caches connections internally? What is the right way to >>>>>>> batch upserts in current case? >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>> >> > --047d7b874cdaa4cec3051f16bbfe Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi, approach above doesn&#= 39;t fit web-app. There are multiple simultaneous upserts comes from differ= ent threads.
So the only thing is to p= ut connection into thread-local and use one connection per thread.

try = (Connection conn =3D DriverManager.getConnection(url)) {
conn.setAutoCommit(false);
int batchSize =3D 0;
int commi= tSize =3D 1000; // number of rows you want to=C2=A0commit=C2=A0per batch. C= hange this value according to your needs.
try (Statement stmt =3D conn.prepareStatement(upsert)) {
stmt.set ...
wh= ile (there are records to=C2=A0upsert) {
=C2=A0 =C2=A0 =C2=A0stmt.executeUpdate();
=C2=A0 =C2=A0 =C2=A0batchSize++;
=C2=A0 =C2=A0 =C2=A0if (batchSize % commitSize =3D=3D 0) {
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 conn.commit();
=C2=A0 =C2=A0 =C2=A0}
}
conn.commit(); //= =C2=A0commit=C2=A0the last batch of records
}


2015-09-03 22:28 GMT+02:00 Jaim= e Solano <jdjsolano@gmail.com>:
Thanks for the explanation, Samarth!

On Thu, Sep 3, 2015 at 4:11 PM, Samarth Jain <samarth@ap= ache.org> wrote:
Hi Jaime,

Phoenix is an embedded JDBC driver i.= e. the connections are not meant to be pooled. Creating phoenix connections= is a light weight operation. This is because all phoenix connections to a = cluster from the Phoenix JDBC driver use the same underlying HConnection.= =C2=A0

If you end up pooling Phoenix connections t= hen you will likely run into unexpected issues. It is likely that a request= will leave a Phoenix connection not in a kosher state for the next request= to use it.

On Thu, Sep 3, 2015 at 12:58 PM, Jaime Solano &= lt;jdjsolano@gmail= .com> wrote:
Hi Samarth,
In our project we were thinking about using Tomcat JDBC = Connection Pool, to handle Phoenix connections. You're saying this type= of approach should be avoided? What other approach should be followed if, = in the scenario Serega described, you experience a heavy load of users, all= trying to upsert at the same time (therefore, a lot of connections) ??
Also, can you expand a little bit more on the implications of having= a pooling mechanism for Phoenix connections?
Thanks in advance!<= /div>
-Jaime

On Thu, Se= p 3, 2015 at 3:35 PM, Samarth Jain <samarth.jain@gmail.com> wrote:
Yes. Phoenix= Connection implements java.sql.Connection.

On Thu, Sep 3, 2015 at 12:34 PM, S= erega Sheypak <serega.sheypak@gmail.com> wrote:
>Phoenix doesn't cache connections. You should= n't pool them and you shouldn't share them with multiple threads.
We are talkin= g about java.sql.Connection, right?

2015-09-03 21:26 GMT+02:00 = Samarth Jain <samarth@apache.org>:
Your pattern is correct.=C2=A0

Phoenix doesn't cache connections. You shouldn't pool them and yo= u shouldn't share them with multiple threads.

For ba= tching upserts, you could do something like this:

= You can do this via phoenix by doing somet= hing like this:

try (Connection conn =3D DriverManager.getConnection(= url)) {
conn.setAutoCommit(false);
int batchSize =3D 0;
int commitSize =3D 1000; // number of rows you want to=C2= =A0commit=C2=A0per batch. Change this value according to your = needs.
try (Statement stmt =3D conn.pr= epareStatement(upsert)) {
stmt.set ...=
while (there are records to=C2=A0upsert) {
=C2=A0 =C2=A0 =C2= =A0stmt.executeUpdate();
=C2=A0 =C2=A0= =C2=A0batchSize++;
=C2=A0 =C2=A0 =C2= =A0if (batchSize % commitSize =3D=3D 0) {
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 conn.commit();
=C2=A0 =C2=A0 =C2=A0}
}
conn.commit(); //=C2=A0commit=C2=A0the last batch of records
}

=
You don't want commitSize to be too lar= ge since Phoenix client keeps the uncommitted rows in memory till they are = sent over to HBase.=C2=A0



On Thu, Sep 3, 2015 at 12:19 PM, Serega Sheypak <serega= .sheypak@gmail.com> wrote:
Hi, I'm using =C2=A0phoenix in java web-application. App does upsert= or select by primary key.
What is the right pattern to do it?
- I create new connection for each request
- prepare and execut= e statement
- close stmt
- close connection
<= br>
Does phoenix caches connections internally? What is the right= way to batch upserts in current case?







--047d7b874cdaa4cec3051f16bbfe--