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 <samarth.jain@gmail.com> 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 <samarth@apache.org>:
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?