RE: Database too many connections

[I-A Kotopoulos, modifié il y a 9 années.]

I have been using my current installation for more than two months with no problem. Today on tomcat startup I keep receiving an error regarding the number of connections with mysql. I am not sure if I should look at portal's configuration, hibernate's configuration, tomcat's configuration or mysql configuration. The error appears on startup and can't get past it. The exception is the following:

[C3P0PooledConnectionPoolManager[identityToken->Rb9iEMA9]-HelperThread-#0][BasicResourcePool:1851] com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@937a331 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (3). Last acquisition attempt exception: 
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
	at sun.reflect.GeneratedConstructorAccessor49.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1110)
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2465)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2498)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2283)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:822)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.GeneratedConstructorAccessor33.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:404)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:317)
	at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146)
	at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:211)
	at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1086)
	at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073)
	at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44)
	at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1810)
	at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)</init></init>

regards

Ioannis

[David H Nebinger, modifié il y a 9 années.]

Let me guess - instead of using Service Builder your portlets are just using Hibernate directly?

The problem usually comes down to X portlets requiring their own Y connections (as defined for their connection pool).

Initially when you started it was only 1 portlet requiring Y connections and everything was cool.

The problem is, as you start adding other portlets, your required connections count keeps going up...

The connection pools probably have an initial connection count along with the max count and the idle count. Normally when your portlets are running they don't need all of their connections so they drop down to the idle count.

At startup, however, the connection pools are going to try and create the initial connection count which probably exceeds the number of connections mysql is configured to support.

Short term, if you change your initial count so that it matches the idle count, you may be able to get past the startup issue, but you are approaching an upper limit of connections that you'll soon hit if you suddenly are hit with a lot of activity or if you add more hibernate-based portlets.

[I-A Kotopoulos, modifié il y a 9 années.]

David, thank you for your immediate response. Well I am actually using the Service Builder..So any other guess? By the way were can I find the connection pool's configuration? Also, I am using a hosted MySQL instance. Do you think I may have hit their default max connections threshold?

[David H Nebinger, modifié il y a 9 années.]

The c3p0 pool settings are controlled by the spring xml files tied in w/ the service builder implementation.

You could be hitting a cxn limit if the hosted mysql has one. If so, you can lower the initial and max connections for c3p0 to keep you under the threshold. Note that c3p0 has defaults so they may not be listed directly, but you can add properties to manipulate the c3p0 connection pool.

[Murali Krishna, modifié il y a 9 années.]

HI,

check how many connections you are opening in the startup and idle time and number of sessions in the database.. this will give you clarity...

Murali

[I-A Kotopoulos, modifié il y a 9 années.]

Ok, I found out that I actually hit the server's default max cnxs (I host some other apps on the same server so that was easy..).
I would like to configure the portal's pool's properties. I found posts about adding the default values in portal-ext.properties file. However this seems to be ignored. Could that apply? Otherwise, David can you be more specific or do you have any reference to help me on configuring the "spring xml files"? which of the files needs to be altered and where is its location?
kind regards