-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 
  Previous topic | Next topic 
Author Message
otis
 Post subject: Running out of pooled DB connections
PostPosted: Fri Apr 23, 2004 2:28 pm 
Beginner
Beginner

Joined: Wed Dec 03, 2003 10:59 am
Posts: 47
Hello,

I am running out of pooled database connections, despite what I think it proper use of openSession/close and reconnect/disconnect.
I am using Hibernate 2.1.1 and DBCP that comes with it.
My Hibernate Session operations are as follows:
    1. request comes in, I get existing session for the user if I have one, or get a new one via SessionFactory's openSession() method

    2. request is done, and I call session.disconnect() method
Assume no errors happen, because I did create my tests so there are no errors.
What I am observing is this:
    1. request comes in, I assign it an existing or new session, run queries, and diconnect it
    (all this I see in my logs)

    2. I check the connection pool (in my case: ps -auxwww| grep -c postgres ), and I see 1 connection to the DB. That looks fine - that is that one DB connection that's sitting in the connection pool now, waiting to be re-used.

    3. However, that is not fine, because, a new request comes in, and I re-use the session, detect that session is not connected (!session.isConnected()), so I reconnect it by calling session.reconnect(). I thought that this would just reuse that connection from 1 above. However, that does not happen - see 4 below.

    4. ps -auxwww | grep -c postgres -- I see 2 connections now! Why?

I made 2 sequential, separate requests, used the same Hibernate Session. The first time I got it via SessionFactory's openSession() method, the second time I re-used it. Both time I called disconnect() on it.
Shouldn't I have just 1 connection waiting in the pool at this point?
It's not that I'm stingy with connections - my connection pool is getting exhausted! :(

Code:
Caused by: net.sf.hibernate.JDBCException: Cannot open connection
        at net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:260)
        at net.sf.hibernate.impl.SessionImpl.connect(SessionImpl.java:3157)
        at net.sf.hibernate.impl.SessionImpl.connection(SessionImpl.java:3140)
        at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:57)
        at net.sf.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:67)
        at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:727)
        at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:717)
        at net.sf.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:1322)
        at com.simpy.db.BaseDAOHibernate.storeObject(BaseDAOHibernate.java:287)
        ... 31 more
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted, cause: Timeout waiting for idle object
        at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:148)
        at net.sf.hibernate.connection.DBCPConnectionProvider.getConnection(DBCPConnectionProvider.java:41)
        at net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:257)
        ... 39 more
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
        at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:801)
        at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:140)
        ... 41 more


Thank you,
Otis
Top
 Profile  
 
anthony
 Post subject:
PostPosted: Fri Apr 23, 2004 2:37 pm 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
otis, you're really mistaken all this stuff

all is joined, respect threadlocal session, understand what disconnexion mean, don't forget about transaction and last advice, use proxool not dbcp, dbcp is buggy
Top
 Profile  
 
otis
 Post subject:
PostPosted: Fri Apr 23, 2004 2:48 pm 
Beginner
Beginner

Joined: Wed Dec 03, 2003 10:59 am
Posts: 47
delpouve wrote:
otis, you're really mistaken all this stuff

all is joined, respect threadlocal session, understand what disconnexion mean, don't forget about transaction and last advice, use proxool not dbcp, dbcp is buggy


Could you please be a bit more specific? Is my use of openSession(), reconnect(), and disconnect() wrong?

I'm doing my testing in a test environment.
1 user only
1 request at a time

- initial state: no Hibernate Sessions, no DB connections
- request #1: openSession (new Session)
- post request #1: Session disconnected, 1 connection in the pool
- wait, have a coffee
- request #2: use the same Session, it was disconnected, so call reconnect() on it
- post request #2: Session disconnected, 2 connections in the pool

If I keep repeating this, I will get 3,4,5,....N connections, and I will run out of pooled DB connections.
My feeling is that something is not returning connections back to the pool. Possible?
Why didn't Session in request #2 use that existing idle session from the pool? Should that happen?

What am I supposed to do to return DB connections back to the pool, and to re-use idle connections from the pool?
I thought:
-- session.reconnect() -- gets idle connection from the pool
-- session.disconnect() -- returns the connection to the pool

Apparently, though, that is not happening.

Thanks,
Otis
Top
 Profile  
 
anthony
 Post subject:
PostPosted: Fri Apr 23, 2004 3:29 pm 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
1- apply exactly threadlocal session (keep out the user<-->session you described in http://forum.hibernate.org/viewtopic.php?t=930246)
2- switch to proxool

and give the result of your test
Top
 Profile  
 
lagcisco
 Post subject:
PostPosted: Fri Apr 23, 2004 5:18 pm 
Regular
Regular

Joined: Tue Aug 26, 2003 6:59 pm
Posts: 89
Location: Somewhere in the Ghetto
what about c3p0? has that compare to dbcp and proxool?
Top
 Profile  
 
bertrand
 Post subject:
PostPosted: Fri May 28, 2004 5:35 pm 
Proxool Developer
Proxool Developer

Joined: Tue Aug 26, 2003 10:42 am
Posts: 373
Location: Belgium
lagcisco wrote:
what about c3p0? has that compare to dbcp and proxool?


always use Proxool when you can ;)
Top
 Profile  
 
baliukas
 Post subject:
PostPosted: Sat May 29, 2004 1:58 am 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
Probably it will be out of topic, but I found it is very good way not to use any pool too.
In some cases this optimizationis very small, but produces a lot of problems. If you open connection once per thread then it is not a very big overhead. In my case most of web applications run on "big" machine, DB and web server is on the same machime. It takes 3 - 5 ms. to connect and authenticate (the most simple authentication method is configured on dedicated server and accepts local connections only), maxThreads on web server is less than maxConnections on DB so app never blocks to wait for connection, never goes out of connections and it never needs to validate or to find dead connections (memory leak bug in DB can not cause big problems too).
I am do not know about your use case, but this configuration is typical for my applications.
Probably it is possible to optimize this way with pool too (always let pool to grow), Doe's somebody has good results for pool without blocking ?
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Sat May 29, 2004 2:06 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
I have seen connection and authentication take MUCH longer than that against remote databases!
Top
 Profile  
 
baliukas
 Post subject:
PostPosted: Sat May 29, 2004 3:35 am 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
gavin wrote:
I have seen connection and authentication take MUCH longer than that against remote databases!

Yes, it can take a few secundes to connect to remote database and LDAP authentication. There is no way to perform and scale without pool in ths case.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.