0

Tomcat Connection Pools

asked 2014-06-25 09:56:48 +0800

IngoB gravatar image IngoB flag of Germany
256 6

Hi,

I want to use the Tomcat Connection Pool to handle ZK's DB Connections. I tried it with 5 maxActive (testcase), BUT AFTER! 4x doSQL() calls ZK is doing "nothing" and showing "progressing" ... I have to handle 200 Connections at the same time, so why is ZK/Tomcat not handling more than 5 connections?

Any help appreciated :/

------- CODE ------------

So I got Eclipse Kepler SR2 + Tomcat 6.0.

I added a "context.xml" to the /META-INF Folder

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    <Resource name="jdbc/localdb" auth="Container"
        type="javax.sql.DataSource" username="a" password="b"
        driverClassName="org.postgresql.Driver"
        url="jdbc:postgresql://localhost:5432/mydb"
        maxActive="5" maxIdle="1" />
</Context>

I created a class for the DataConnection

public class DataConnection {
    private DataSource dataSource;
    public DataConnection() {
    }

    private void connectToDataBase() {
        try {
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");
            dataSource = (DataSource) envContext.lookup("jdbc/localdb");
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() {
        try {
            if (dataSource == null || dataSource.getConnection().isClosed()) {
                connectToDataBase();
            }
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}

And i tried a preparedStatement

private DataConnection dataConnection;

@Init
public void init() {
    dataConnection = new DataConnection();
}

private void doSQL(int pk) {
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        Connection connection = null;
        try {
            String selectSQL = "SELECT * FROM myTable WHERE pk=?";
            connection = dataConnection.getConnection();
            preparedStatement = connection.prepareStatement(selectSQL);
            preparedStatement.setInt(1, pk);

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != resultSet) {
                    resultSet.close();
                    System.out.println("resultSet.close();");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (null != preparedStatement) {
                    preparedStatement.close();
                    System.out.println("preparedStatement.close();");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (null != connection) {
                    connection.close();
                    System.out.println("connection.close();");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
delete flag offensive retag edit

6 Answers

Sort by ยป oldest newest most voted
1

answered 2014-06-29 10:18:00 +0800

Bobzk gravatar image Bobzk
428 1 5

Just to clarify for anyone reading this - standard Tomcat connection pooling works just fine, if you use it correctly.

You don't have to use HikariCP or anything else (not to say of course anything else is necessarily bad).

We've been using standard tomcat connection pools for years with great success.

link publish delete flag offensive edit
1

answered 2014-06-25 10:51:41 +0800

Bobzk gravatar image Bobzk
428 1 5

Yes, trying to understand code now - my understanding is that "dataSource = (DataSource) envContext.lookup("jdbc/localdb");" will actually get a connection. Therefore the "return dataSource.getConnection();" is incorrect.

However I have to preface all my comments with - I use Jython - more than Java so am rusty with my java. Java expert not around at moment.

Have a try with PSI Probe (http://psi-probe.googlecode.com/) to see what connections are in use after just one get of a connection (and in theory one release).

So for whatever reason you are not releasing them.

link publish delete flag offensive edit
0

answered 2014-06-25 10:30:38 +0800

Bobzk gravatar image Bobzk
428 1 5

First, I would change the maxActive to 20 - Postgres/tomcat can handle 20 no problem.

Second I would remove the maxidle - don't see the point.

Third (and the big one). You should get a connection, run your SQL statement, then release connection. That way you only hold a connection for a few milliseconds (or however long your SQL statement takes).

The whole point about connections is you want to share them. To do this you MUST release them asap.

Make sure in you always release (in your "finally") even on errors.

Assuming you do this, then there should be no problems.

link publish delete flag offensive edit
0

answered 2014-06-25 10:40:21 +0800

IngoB gravatar image IngoB flag of Germany
256 6

Yes, these are not the final values. 5 maxActive was for test purpose only. Maxidle can be removed too.

Have you read my code? I acquire a connection in doSQL()

connection = dataConnection.getConnection();

and finally i release it. So I don't get, why after 4 calls of "doSQL()", ZK is progressing and nothing is working :<

link publish delete flag offensive edit
0

answered 2014-06-25 11:53:00 +0800

Bobzk gravatar image Bobzk
428 1 5

Is the problem

     if (dataSource == null || dataSource.getConnection().isClosed()) {
        connectToDataBase();

Won't this get 2 connections. I assume you are trying to see if the existing connection is closed but you will get another connection (which will be closed), then you do connectToDatabase which will get another connection. Should it not be "connection.isClosed()"

Maybe instead after close, you set to null and check for nulls?

link publish delete flag offensive edit
0

answered 2014-06-26 07:46:28 +0800

IngoB gravatar image IngoB flag of Germany
256 6

updated 2014-06-26 07:48:06 +0800

I really don't know. I was kind of in a hurry and couldn't fix it, so I tried

https://github.com/brettwooldridge/HikariCP

and I really like it. For anyone else who is using connection pools, try it out.

Thank you Bobzk for your help :)

P.S.: http://psi-probe.googlecode.com/ is really good!

link publish delete flag offensive edit
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!

[hide preview]

Question tools

Follow
1 follower

RSS

Stats

Asked: 2014-06-25 09:56:48 +0800

Seen: 27 times

Last updated: Jun 29 '14

Support Options
  • Email Support
  • Training
  • Consulting
  • Outsourcing
Learn More