You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Suny kk <su...@gmail.com> on 2011/01/20 12:16:17 UTC
[DBCP] Too many connections issue...
Hi,
I've facing "Too many connections" issue and following are my DBCP
settings...
#####################################
# DBCP Connection Pool Settings
#####################################
# The connection properties that will be sent to our JDBC driver when
# establishing new connections.
dbcp.connectionProperties=""
# The default auto-commit state of connections created by this pool.
# [Default: true]
dbcp.defaultAutoCommit=true
# The default read-only state of connections created by this pool. If not
set
# then the setReadOnly method will not be called.
#dbcp.defaultReadOnly=
# The default catalog of connections created by this pool.
#dbcp.defaultCatalog=
# The initial number of connections that are created when the pool is
started.
# [Default: 0]
dbcp.initialSize=10
# The maximum number of active connections that can be allocated
# from this pool at the same time, or negative for no limit. [Default: 8]
dbcp.maxActive=500
# The maximum number of connections that can remain idle in the pool,
# without extra ones being released, or negative for no limit. [Default: 8]
dbcp.maxIdle=8
# The minimum number of connections that can remain idle in the pool,
without
# extra ones being created, or zero to create none. [Default: 0]
dbcp.minIdle=0
# The maximum number of milliseconds that the pool will wait (when there are
# no available connections) for a connection to be returned before throwing
an
# exception, or -1 to wait indefinitely. [Default: -1]
dbcp.maxWait=30000
# The SQL query that will be used to validate connections from this pool
before
# returning them to the caller. If specified, this query MUST be an SQL
SELECT
# statement that returns at least one row.
dbcp.validationQuery=SELECT 1
# The indication of whether objects will be validated before being borrowed
# from the pool. If the object fails to validate, it will be dropped from
the
# pool, and we will attempt to borrow another. [Default: true]
#
# NOTE - for a true value to have any effect, the validationQuery parameter
# must be set to a non-null string.
dbcp.testOnBorrow=true
# The indication of whether objects will be validated before being returned
to
# the pool. [Default: false]
#
# NOTE - for a true value to have any effect, the validationQuery parameter
# must be set to a non-null string.
dbcp.testOnReturn=false
# The indication of whether objects will be validated by the idle object
# evictor (if any). If an object fails to validate, it will be dropped from
the
# pool. [Default: false]
#
# NOTE - for a true value to have any effect, the validationQuery parameter
# must be set to a non-null string.
dbcp.testWhileIdle=false
# The number of milliseconds to sleep between runs of the idle object
evictor
# thread. When non-positive, no idle object evictor thread will be run.
# [Default: -1]
dbcp.timeBetweenEvictionRunsMillis=-1
# The number of objects to examine during each run of the idle object
evictor
# thread (if any). [Default: 3]
dbcp.numTestsPerEvictionRun=3
# The minimum amount of time an object may sit idle in the pool before it is
# eligable for eviction by the idle object evictor (if any).
# [Default: 180000 (30 min)]
dbcp.minEvictableIdleTimeMillis=180000
# A Collection of SQL statements that will be used to initialize physical
# connections when they are first created. These statements are executed
# only once - when the configured connection factory creates the connection.
# [Default: null]
#dbcp.connectionInitSqls=
# Enable prepared statement pooling for this pool. [Default: false]
dbcp.poolPreparedStatements=true
# The maximum number of open statements that can be allocated from the
# statement pool at the same time, or zero for no limit. [Default: 0]
dbcp.maxOpenPreparedStatements=25
# Controls if the PoolGuard allows access to the underlying connection.
# [Default: false]
dbcp.accessToUnderlyingConnectionAllowed=false
# Flag to remove abandoned connections if they exceed the
# removeAbandonedTimout. If set to true a connection is considered abandoned
# and eligible for removal if it has been idle longer than the
# removeAbandonedTimeout. Setting this to true can recover db connections
from
# poorly written applications which fail to close a connection.
# [Default: false]
dbcp.removeAbandoned=false
# Timeout in seconds before an abandoned connection can be removed.
# [Default: 300]
dbcp.removeAbandonedTimeout=300
# Flag to log stack traces for application code which abandoned a Statement
or
# Connection. Logging of abandoned Statements and Connections adds overhead
for
# every Connection open or new Statement because a stack trace has to be
# generated. [Default: false]
dbcp.logAbandoned=false
Re: [DBCP] Too many connections issue...
Posted by Phil Steitz <ph...@gmail.com>.
Does your application hold Statements / Connections for a long time
without using them? The symptoms below are consistent with the
physical database connection being closed due to inactivity timeout on
the server side.
Phil
On Wed, Jan 26, 2011 at 12:50 PM, Suny kk <su...@gmail.com> wrote:
> Now my application rising following error sometime and sometime working
> perfect, anyone of you ever faced such error?
>
> Caused by:
> com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No
> operations allowed after statement closed.
>
> at
> sun.reflect.GeneratedConstructorAccessor157.newInstance(Unknown Source)
>
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
>
> at
> java.lang.reflect.Constructor.newInstance(Constructor.java:513)
>
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
>
> at com.mysql.jdbc.Util.getInstance(Util.java:381)
>
> at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
>
> at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
>
> at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
>
> at
> com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:405)
>
> at
> com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3659)
>
> at
> com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:3616)
>
> at
> org.apache.commons.dbcp.DelegatingPreparedStatement.setInt(DelegatingPreparedStatement.java:117)
>
> at
> org.apache.commons.dbcp.DelegatingPreparedStatement.setInt(DelegatingPreparedStatement.java:117)
>
> at org.hibernate.type.IntegerType.set(IntegerType.java:64)
>
> at
> org.hibernate.type.NullableType.nullSafeSet(NullableType.java:154)
>
> at
> org.hibernate.type.NullableType.nullSafeSet(NullableType.java:136)
>
> at
> org.hibernate.loader.Loader.bindPositionalParameters(Loader.java:1732)
>
> at
> org.hibernate.loader.Loader.bindParameterValues(Loader.java:1703)
>
> at
> org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1593)
>
> at org.hibernate.loader.Loader.doQuery(Loader.java:696)
>
> at
> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
>
> at
> org.hibernate.loader.Loader.loadCollection(Loader.java:2019)
>
> ... 45 more
>
>
> (I'm just to tired to Google this issue therefore posted here. Good Night)
>
> On Wed, Jan 26, 2011 at 5:24 PM, Mark Shifman <ma...@yale.edu> wrote:
>
>> You may want to look at what connections your database thinks are open.
>> In Oracle, from a sql plus command line:
>>
>> select username, program, machine, osuser, logon_time, sid, serial#
>> from v$session where osuser != 'oracle' order by machine, logon_time;
>>
>> You can also do something similar with mysql but I don't remember the
>> sql. I suspect all databases have some way to tell which connections
>> are open.
>>
>> You can also watch the number of open connections grow if you haven't
>> appropriately closed the connections.
>>
>> mas
>>
>>
>>
>> On 01/25/2011 08:05 PM, Phil Steitz wrote:
>> > On Tue, Jan 25, 2011 at 4:39 PM, Suny kk <su...@gmail.com> wrote:
>> >> Hi Phil,
>> >>
>> >> Yes, I'm using BasiDataSource well, Is it because of my DBCP settings?
>> >> Remember I got this exception when I started my application on different
>> >> machine with same database.
>> >>
>> >
>> > Well, if the first instance starts and uses the full 500 max
>> > connections, then that could explain why the second instance cannot
>> > get any connections from the database (assuming the database itself
>> > cannot handle more than 500 connections).
>> >
>> > As I said below, the error that you are getting means that when the
>> > application starts the database does not have connections available to
>> > provide to the pool. You should check and confirm:
>> >
>> > 0) Is the application closing all connections that it opens? You can
>> > quickly max the pool connections if your code does not return
>> > connections to the pool. Your configuration allows 500 connections to
>> > be opened by the pool concurrently. You should observe the actual
>> > database engine connection counts and see if your app is getting near
>> > the max. Given that you have maxIdle set at 8, if you are hitting
>> > high connection count numbers either you have some very long-running
>> > queries and large load spikes, or your code is abandoning connections
>> > (not closing them).
>> >
>> > 1) Are other applications or processes consuming available database
>> connections?
>> >
>> > Phil
>> >
>> > ---------------------------------------------------------------------
>> > To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>> > For additional commands, e-mail: user-help@commons.apache.org
>> >
>>
>> --
>> Mark Shifman MD. Ph.D.
>> Yale Center for Medical Informatics
>> Phone (203)737-5219
>> mark.shifman@yale.edu
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>> For additional commands, e-mail: user-help@commons.apache.org
>>
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] Too many connections issue...
Posted by Suny kk <su...@gmail.com>.
Now my application rising following error sometime and sometime working
perfect, anyone of you ever faced such error?
Caused by:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No
operations allowed after statement closed.
at
sun.reflect.GeneratedConstructorAccessor157.newInstance(Unknown Source)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at
java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at
com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:405)
at
com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3659)
at
com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:3616)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.setInt(DelegatingPreparedStatement.java:117)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.setInt(DelegatingPreparedStatement.java:117)
at org.hibernate.type.IntegerType.set(IntegerType.java:64)
at
org.hibernate.type.NullableType.nullSafeSet(NullableType.java:154)
at
org.hibernate.type.NullableType.nullSafeSet(NullableType.java:136)
at
org.hibernate.loader.Loader.bindPositionalParameters(Loader.java:1732)
at
org.hibernate.loader.Loader.bindParameterValues(Loader.java:1703)
at
org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1593)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at
org.hibernate.loader.Loader.loadCollection(Loader.java:2019)
... 45 more
(I'm just to tired to Google this issue therefore posted here. Good Night)
On Wed, Jan 26, 2011 at 5:24 PM, Mark Shifman <ma...@yale.edu> wrote:
> You may want to look at what connections your database thinks are open.
> In Oracle, from a sql plus command line:
>
> select username, program, machine, osuser, logon_time, sid, serial#
> from v$session where osuser != 'oracle' order by machine, logon_time;
>
> You can also do something similar with mysql but I don't remember the
> sql. I suspect all databases have some way to tell which connections
> are open.
>
> You can also watch the number of open connections grow if you haven't
> appropriately closed the connections.
>
> mas
>
>
>
> On 01/25/2011 08:05 PM, Phil Steitz wrote:
> > On Tue, Jan 25, 2011 at 4:39 PM, Suny kk <su...@gmail.com> wrote:
> >> Hi Phil,
> >>
> >> Yes, I'm using BasiDataSource well, Is it because of my DBCP settings?
> >> Remember I got this exception when I started my application on different
> >> machine with same database.
> >>
> >
> > Well, if the first instance starts and uses the full 500 max
> > connections, then that could explain why the second instance cannot
> > get any connections from the database (assuming the database itself
> > cannot handle more than 500 connections).
> >
> > As I said below, the error that you are getting means that when the
> > application starts the database does not have connections available to
> > provide to the pool. You should check and confirm:
> >
> > 0) Is the application closing all connections that it opens? You can
> > quickly max the pool connections if your code does not return
> > connections to the pool. Your configuration allows 500 connections to
> > be opened by the pool concurrently. You should observe the actual
> > database engine connection counts and see if your app is getting near
> > the max. Given that you have maxIdle set at 8, if you are hitting
> > high connection count numbers either you have some very long-running
> > queries and large load spikes, or your code is abandoning connections
> > (not closing them).
> >
> > 1) Are other applications or processes consuming available database
> connections?
> >
> > Phil
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> > For additional commands, e-mail: user-help@commons.apache.org
> >
>
> --
> Mark Shifman MD. Ph.D.
> Yale Center for Medical Informatics
> Phone (203)737-5219
> mark.shifman@yale.edu
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
Re: [DBCP] Too many connections issue...
Posted by Mark Shifman <ma...@yale.edu>.
You may want to look at what connections your database thinks are open.
In Oracle, from a sql plus command line:
select username, program, machine, osuser, logon_time, sid, serial#
from v$session where osuser != 'oracle' order by machine, logon_time;
You can also do something similar with mysql but I don't remember the
sql. I suspect all databases have some way to tell which connections
are open.
You can also watch the number of open connections grow if you haven't
appropriately closed the connections.
mas
On 01/25/2011 08:05 PM, Phil Steitz wrote:
> On Tue, Jan 25, 2011 at 4:39 PM, Suny kk <su...@gmail.com> wrote:
>> Hi Phil,
>>
>> Yes, I'm using BasiDataSource well, Is it because of my DBCP settings?
>> Remember I got this exception when I started my application on different
>> machine with same database.
>>
>
> Well, if the first instance starts and uses the full 500 max
> connections, then that could explain why the second instance cannot
> get any connections from the database (assuming the database itself
> cannot handle more than 500 connections).
>
> As I said below, the error that you are getting means that when the
> application starts the database does not have connections available to
> provide to the pool. You should check and confirm:
>
> 0) Is the application closing all connections that it opens? You can
> quickly max the pool connections if your code does not return
> connections to the pool. Your configuration allows 500 connections to
> be opened by the pool concurrently. You should observe the actual
> database engine connection counts and see if your app is getting near
> the max. Given that you have maxIdle set at 8, if you are hitting
> high connection count numbers either you have some very long-running
> queries and large load spikes, or your code is abandoning connections
> (not closing them).
>
> 1) Are other applications or processes consuming available database connections?
>
> Phil
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
--
Mark Shifman MD. Ph.D.
Yale Center for Medical Informatics
Phone (203)737-5219
mark.shifman@yale.edu
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] Too many connections issue...
Posted by Phil Steitz <ph...@gmail.com>.
On Tue, Jan 25, 2011 at 4:39 PM, Suny kk <su...@gmail.com> wrote:
> Hi Phil,
>
> Yes, I'm using BasiDataSource well, Is it because of my DBCP settings?
> Remember I got this exception when I started my application on different
> machine with same database.
>
Well, if the first instance starts and uses the full 500 max
connections, then that could explain why the second instance cannot
get any connections from the database (assuming the database itself
cannot handle more than 500 connections).
As I said below, the error that you are getting means that when the
application starts the database does not have connections available to
provide to the pool. You should check and confirm:
0) Is the application closing all connections that it opens? You can
quickly max the pool connections if your code does not return
connections to the pool. Your configuration allows 500 connections to
be opened by the pool concurrently. You should observe the actual
database engine connection counts and see if your app is getting near
the max. Given that you have maxIdle set at 8, if you are hitting
high connection count numbers either you have some very long-running
queries and large load spikes, or your code is abandoning connections
(not closing them).
1) Are other applications or processes consuming available database connections?
Phil
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] Too many connections issue...
Posted by Suny kk <su...@gmail.com>.
Hi Phil,
Yes, I'm using BasiDataSource well, Is it because of my DBCP settings?
Remember I got this exception when I started my application on different
machine with same database.
Regards
On Sun, Jan 23, 2011 at 8:48 PM, Phil Steitz <ph...@gmail.com> wrote:
> On Sun, Jan 23, 2011 at 4:02 AM, Suny kk <su...@gmail.com> wrote:
> > I'm using DBCP 1.2.2 version there's no commons pool dependency in my
> > project and following is stack trace
> >
> > org.springframework.jdbc.UncategorizedSQLException : (Hibernate
> operation):
> >> encountered SQLException [Cannot create PoolableConnectionFactory (Data
> >> source rejected establishment of connection, message from server: "Too
> many
> >> connections")]; nested exception is
> >> org.apache.commons.dbcp.SQLNestedException: Cannot create
> >> PoolableConnectionFactory (Data source rejected establishment of
> connection,
> >> message from server: "Too many connections")
> >>
>
> The server is rejecting DBCP's initial attempt at connecting. The
> message, which is coming from the jdbc driver, appears to indicate
> that the server is out of connections when DBCP is trying to start.
> The stack trace indicates that this is happening on the first call to
> getConnection() (assuming you are using BasicDataSource).
>
> Phil
> >
> >
> > On Thu, Jan 20, 2011 at 4:02 PM, Phil Steitz <ph...@gmail.com>
> wrote:
> >
> >> On Thu, Jan 20, 2011 at 6:16 AM, Suny kk <su...@gmail.com> wrote:
> >> > Hi,
> >> >
> >> >
> >> > I've facing "Too many connections" issue and following are my DBCP
> >> > settings...
> >>
> >> What exactly is your problem - i.e., what exception / liveness issue
> >> are you seeing. Also what versions of commons dbcp and commons pool
> >> are you running?
> >>
> >> Phil
> >> >
> >> > #####################################
> >> > # DBCP Connection Pool Settings
> >> > #####################################
> >> >
> >> > # The connection properties that will be sent to our JDBC driver when
> >> > # establishing new connections.
> >> > dbcp.connectionProperties=""
> >> >
> >> > # The default auto-commit state of connections created by this pool.
> >> > # [Default: true]
> >> > dbcp.defaultAutoCommit=true
> >> >
> >> > # The default read-only state of connections created by this pool. If
> not
> >> > set
> >> > # then the setReadOnly method will not be called.
> >> > #dbcp.defaultReadOnly=
> >> >
> >> > # The default catalog of connections created by this pool.
> >> > #dbcp.defaultCatalog=
> >> >
> >> > # The initial number of connections that are created when the pool is
> >> > started.
> >> > # [Default: 0]
> >> > dbcp.initialSize=10
> >> >
> >> > # The maximum number of active connections that can be allocated
> >> > # from this pool at the same time, or negative for no limit. [Default:
> 8]
> >> > dbcp.maxActive=500
> >> >
> >> > # The maximum number of connections that can remain idle in the pool,
> >> > # without extra ones being released, or negative for no limit.
> [Default:
> >> 8]
> >> > dbcp.maxIdle=8
> >> >
> >> > # The minimum number of connections that can remain idle in the pool,
> >> > without
> >> > # extra ones being created, or zero to create none. [Default: 0]
> >> > dbcp.minIdle=0
> >> >
> >> > # The maximum number of milliseconds that the pool will wait (when
> there
> >> are
> >> > # no available connections) for a connection to be returned before
> >> throwing
> >> > an
> >> > # exception, or -1 to wait indefinitely. [Default: -1]
> >> > dbcp.maxWait=30000
> >> >
> >> > # The SQL query that will be used to validate connections from this
> pool
> >> > before
> >> > # returning them to the caller. If specified, this query MUST be an
> SQL
> >> > SELECT
> >> > # statement that returns at least one row.
> >> > dbcp.validationQuery=SELECT 1
> >> >
> >> > # The indication of whether objects will be validated before being
> >> borrowed
> >> > # from the pool. If the object fails to validate, it will be dropped
> from
> >> > the
> >> > # pool, and we will attempt to borrow another. [Default: true]
> >> > #
> >> > # NOTE - for a true value to have any effect, the validationQuery
> >> parameter
> >> > # must be set to a non-null string.
> >> > dbcp.testOnBorrow=true
> >> >
> >> > # The indication of whether objects will be validated before being
> >> returned
> >> > to
> >> > # the pool. [Default: false]
> >> > #
> >> > # NOTE - for a true value to have any effect, the validationQuery
> >> parameter
> >> > # must be set to a non-null string.
> >> > dbcp.testOnReturn=false
> >> >
> >> > # The indication of whether objects will be validated by the idle
> object
> >> > # evictor (if any). If an object fails to validate, it will be dropped
> >> from
> >> > the
> >> > # pool. [Default: false]
> >> > #
> >> > # NOTE - for a true value to have any effect, the validationQuery
> >> parameter
> >> > # must be set to a non-null string.
> >> > dbcp.testWhileIdle=false
> >> >
> >> > # The number of milliseconds to sleep between runs of the idle object
> >> > evictor
> >> > # thread. When non-positive, no idle object evictor thread will be
> run.
> >> > # [Default: -1]
> >> > dbcp.timeBetweenEvictionRunsMillis=-1
> >> >
> >> > # The number of objects to examine during each run of the idle object
> >> > evictor
> >> > # thread (if any). [Default: 3]
> >> > dbcp.numTestsPerEvictionRun=3
> >> >
> >> > # The minimum amount of time an object may sit idle in the pool before
> it
> >> is
> >> > # eligable for eviction by the idle object evictor (if any).
> >> > # [Default: 180000 (30 min)]
> >> > dbcp.minEvictableIdleTimeMillis=180000
> >> >
> >> > # A Collection of SQL statements that will be used to initialize
> physical
> >> > # connections when they are first created. These statements are
> executed
> >> > # only once - when the configured connection factory creates the
> >> connection.
> >> > # [Default: null]
> >> > #dbcp.connectionInitSqls=
> >> >
> >> > # Enable prepared statement pooling for this pool. [Default: false]
> >> > dbcp.poolPreparedStatements=true
> >> >
> >> > # The maximum number of open statements that can be allocated from the
> >> > # statement pool at the same time, or zero for no limit. [Default: 0]
> >> > dbcp.maxOpenPreparedStatements=25
> >> >
> >> > # Controls if the PoolGuard allows access to the underlying
> connection.
> >> > # [Default: false]
> >> > dbcp.accessToUnderlyingConnectionAllowed=false
> >> >
> >> > # Flag to remove abandoned connections if they exceed the
> >> > # removeAbandonedTimout. If set to true a connection is considered
> >> abandoned
> >> > # and eligible for removal if it has been idle longer than the
> >> > # removeAbandonedTimeout. Setting this to true can recover db
> connections
> >> > from
> >> > # poorly written applications which fail to close a connection.
> >> > # [Default: false]
> >> > dbcp.removeAbandoned=false
> >> >
> >> > # Timeout in seconds before an abandoned connection can be removed.
> >> > # [Default: 300]
> >> > dbcp.removeAbandonedTimeout=300
> >> >
> >> > # Flag to log stack traces for application code which abandoned a
> >> Statement
> >> > or
> >> > # Connection. Logging of abandoned Statements and Connections adds
> >> overhead
> >> > for
> >> > # every Connection open or new Statement because a stack trace has to
> be
> >> > # generated. [Default: false]
> >> > dbcp.logAbandoned=false
> >> >
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> >> For additional commands, e-mail: user-help@commons.apache.org
> >>
> >>
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
Re: [DBCP] Too many connections issue...
Posted by Phil Steitz <ph...@gmail.com>.
On Sun, Jan 23, 2011 at 4:02 AM, Suny kk <su...@gmail.com> wrote:
> I'm using DBCP 1.2.2 version there's no commons pool dependency in my
> project and following is stack trace
>
> org.springframework.jdbc.UncategorizedSQLException : (Hibernate operation):
>> encountered SQLException [Cannot create PoolableConnectionFactory (Data
>> source rejected establishment of connection, message from server: "Too many
>> connections")]; nested exception is
>> org.apache.commons.dbcp.SQLNestedException: Cannot create
>> PoolableConnectionFactory (Data source rejected establishment of connection,
>> message from server: "Too many connections")
>>
The server is rejecting DBCP's initial attempt at connecting. The
message, which is coming from the jdbc driver, appears to indicate
that the server is out of connections when DBCP is trying to start.
The stack trace indicates that this is happening on the first call to
getConnection() (assuming you are using BasicDataSource).
Phil
>
>
> On Thu, Jan 20, 2011 at 4:02 PM, Phil Steitz <ph...@gmail.com> wrote:
>
>> On Thu, Jan 20, 2011 at 6:16 AM, Suny kk <su...@gmail.com> wrote:
>> > Hi,
>> >
>> >
>> > I've facing "Too many connections" issue and following are my DBCP
>> > settings...
>>
>> What exactly is your problem - i.e., what exception / liveness issue
>> are you seeing. Also what versions of commons dbcp and commons pool
>> are you running?
>>
>> Phil
>> >
>> > #####################################
>> > # DBCP Connection Pool Settings
>> > #####################################
>> >
>> > # The connection properties that will be sent to our JDBC driver when
>> > # establishing new connections.
>> > dbcp.connectionProperties=""
>> >
>> > # The default auto-commit state of connections created by this pool.
>> > # [Default: true]
>> > dbcp.defaultAutoCommit=true
>> >
>> > # The default read-only state of connections created by this pool. If not
>> > set
>> > # then the setReadOnly method will not be called.
>> > #dbcp.defaultReadOnly=
>> >
>> > # The default catalog of connections created by this pool.
>> > #dbcp.defaultCatalog=
>> >
>> > # The initial number of connections that are created when the pool is
>> > started.
>> > # [Default: 0]
>> > dbcp.initialSize=10
>> >
>> > # The maximum number of active connections that can be allocated
>> > # from this pool at the same time, or negative for no limit. [Default: 8]
>> > dbcp.maxActive=500
>> >
>> > # The maximum number of connections that can remain idle in the pool,
>> > # without extra ones being released, or negative for no limit. [Default:
>> 8]
>> > dbcp.maxIdle=8
>> >
>> > # The minimum number of connections that can remain idle in the pool,
>> > without
>> > # extra ones being created, or zero to create none. [Default: 0]
>> > dbcp.minIdle=0
>> >
>> > # The maximum number of milliseconds that the pool will wait (when there
>> are
>> > # no available connections) for a connection to be returned before
>> throwing
>> > an
>> > # exception, or -1 to wait indefinitely. [Default: -1]
>> > dbcp.maxWait=30000
>> >
>> > # The SQL query that will be used to validate connections from this pool
>> > before
>> > # returning them to the caller. If specified, this query MUST be an SQL
>> > SELECT
>> > # statement that returns at least one row.
>> > dbcp.validationQuery=SELECT 1
>> >
>> > # The indication of whether objects will be validated before being
>> borrowed
>> > # from the pool. If the object fails to validate, it will be dropped from
>> > the
>> > # pool, and we will attempt to borrow another. [Default: true]
>> > #
>> > # NOTE - for a true value to have any effect, the validationQuery
>> parameter
>> > # must be set to a non-null string.
>> > dbcp.testOnBorrow=true
>> >
>> > # The indication of whether objects will be validated before being
>> returned
>> > to
>> > # the pool. [Default: false]
>> > #
>> > # NOTE - for a true value to have any effect, the validationQuery
>> parameter
>> > # must be set to a non-null string.
>> > dbcp.testOnReturn=false
>> >
>> > # The indication of whether objects will be validated by the idle object
>> > # evictor (if any). If an object fails to validate, it will be dropped
>> from
>> > the
>> > # pool. [Default: false]
>> > #
>> > # NOTE - for a true value to have any effect, the validationQuery
>> parameter
>> > # must be set to a non-null string.
>> > dbcp.testWhileIdle=false
>> >
>> > # The number of milliseconds to sleep between runs of the idle object
>> > evictor
>> > # thread. When non-positive, no idle object evictor thread will be run.
>> > # [Default: -1]
>> > dbcp.timeBetweenEvictionRunsMillis=-1
>> >
>> > # The number of objects to examine during each run of the idle object
>> > evictor
>> > # thread (if any). [Default: 3]
>> > dbcp.numTestsPerEvictionRun=3
>> >
>> > # The minimum amount of time an object may sit idle in the pool before it
>> is
>> > # eligable for eviction by the idle object evictor (if any).
>> > # [Default: 180000 (30 min)]
>> > dbcp.minEvictableIdleTimeMillis=180000
>> >
>> > # A Collection of SQL statements that will be used to initialize physical
>> > # connections when they are first created. These statements are executed
>> > # only once - when the configured connection factory creates the
>> connection.
>> > # [Default: null]
>> > #dbcp.connectionInitSqls=
>> >
>> > # Enable prepared statement pooling for this pool. [Default: false]
>> > dbcp.poolPreparedStatements=true
>> >
>> > # The maximum number of open statements that can be allocated from the
>> > # statement pool at the same time, or zero for no limit. [Default: 0]
>> > dbcp.maxOpenPreparedStatements=25
>> >
>> > # Controls if the PoolGuard allows access to the underlying connection.
>> > # [Default: false]
>> > dbcp.accessToUnderlyingConnectionAllowed=false
>> >
>> > # Flag to remove abandoned connections if they exceed the
>> > # removeAbandonedTimout. If set to true a connection is considered
>> abandoned
>> > # and eligible for removal if it has been idle longer than the
>> > # removeAbandonedTimeout. Setting this to true can recover db connections
>> > from
>> > # poorly written applications which fail to close a connection.
>> > # [Default: false]
>> > dbcp.removeAbandoned=false
>> >
>> > # Timeout in seconds before an abandoned connection can be removed.
>> > # [Default: 300]
>> > dbcp.removeAbandonedTimeout=300
>> >
>> > # Flag to log stack traces for application code which abandoned a
>> Statement
>> > or
>> > # Connection. Logging of abandoned Statements and Connections adds
>> overhead
>> > for
>> > # every Connection open or new Statement because a stack trace has to be
>> > # generated. [Default: false]
>> > dbcp.logAbandoned=false
>> >
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>> For additional commands, e-mail: user-help@commons.apache.org
>>
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] Too many connections issue...
Posted by Suny kk <su...@gmail.com>.
I'm using DBCP 1.2.2 version there's no commons pool dependency in my
project and following is stack trace
org.springframework.jdbc.UncategorizedSQLException : (Hibernate operation):
> encountered SQLException [Cannot create PoolableConnectionFactory (Data
> source rejected establishment of connection, message from server: "Too many
> connections")]; nested exception is
> org.apache.commons.dbcp.SQLNestedException: Cannot create
> PoolableConnectionFactory (Data source rejected establishment of connection,
> message from server: "Too many connections")
>
On Thu, Jan 20, 2011 at 4:02 PM, Phil Steitz <ph...@gmail.com> wrote:
> On Thu, Jan 20, 2011 at 6:16 AM, Suny kk <su...@gmail.com> wrote:
> > Hi,
> >
> >
> > I've facing "Too many connections" issue and following are my DBCP
> > settings...
>
> What exactly is your problem - i.e., what exception / liveness issue
> are you seeing. Also what versions of commons dbcp and commons pool
> are you running?
>
> Phil
> >
> > #####################################
> > # DBCP Connection Pool Settings
> > #####################################
> >
> > # The connection properties that will be sent to our JDBC driver when
> > # establishing new connections.
> > dbcp.connectionProperties=""
> >
> > # The default auto-commit state of connections created by this pool.
> > # [Default: true]
> > dbcp.defaultAutoCommit=true
> >
> > # The default read-only state of connections created by this pool. If not
> > set
> > # then the setReadOnly method will not be called.
> > #dbcp.defaultReadOnly=
> >
> > # The default catalog of connections created by this pool.
> > #dbcp.defaultCatalog=
> >
> > # The initial number of connections that are created when the pool is
> > started.
> > # [Default: 0]
> > dbcp.initialSize=10
> >
> > # The maximum number of active connections that can be allocated
> > # from this pool at the same time, or negative for no limit. [Default: 8]
> > dbcp.maxActive=500
> >
> > # The maximum number of connections that can remain idle in the pool,
> > # without extra ones being released, or negative for no limit. [Default:
> 8]
> > dbcp.maxIdle=8
> >
> > # The minimum number of connections that can remain idle in the pool,
> > without
> > # extra ones being created, or zero to create none. [Default: 0]
> > dbcp.minIdle=0
> >
> > # The maximum number of milliseconds that the pool will wait (when there
> are
> > # no available connections) for a connection to be returned before
> throwing
> > an
> > # exception, or -1 to wait indefinitely. [Default: -1]
> > dbcp.maxWait=30000
> >
> > # The SQL query that will be used to validate connections from this pool
> > before
> > # returning them to the caller. If specified, this query MUST be an SQL
> > SELECT
> > # statement that returns at least one row.
> > dbcp.validationQuery=SELECT 1
> >
> > # The indication of whether objects will be validated before being
> borrowed
> > # from the pool. If the object fails to validate, it will be dropped from
> > the
> > # pool, and we will attempt to borrow another. [Default: true]
> > #
> > # NOTE - for a true value to have any effect, the validationQuery
> parameter
> > # must be set to a non-null string.
> > dbcp.testOnBorrow=true
> >
> > # The indication of whether objects will be validated before being
> returned
> > to
> > # the pool. [Default: false]
> > #
> > # NOTE - for a true value to have any effect, the validationQuery
> parameter
> > # must be set to a non-null string.
> > dbcp.testOnReturn=false
> >
> > # The indication of whether objects will be validated by the idle object
> > # evictor (if any). If an object fails to validate, it will be dropped
> from
> > the
> > # pool. [Default: false]
> > #
> > # NOTE - for a true value to have any effect, the validationQuery
> parameter
> > # must be set to a non-null string.
> > dbcp.testWhileIdle=false
> >
> > # The number of milliseconds to sleep between runs of the idle object
> > evictor
> > # thread. When non-positive, no idle object evictor thread will be run.
> > # [Default: -1]
> > dbcp.timeBetweenEvictionRunsMillis=-1
> >
> > # The number of objects to examine during each run of the idle object
> > evictor
> > # thread (if any). [Default: 3]
> > dbcp.numTestsPerEvictionRun=3
> >
> > # The minimum amount of time an object may sit idle in the pool before it
> is
> > # eligable for eviction by the idle object evictor (if any).
> > # [Default: 180000 (30 min)]
> > dbcp.minEvictableIdleTimeMillis=180000
> >
> > # A Collection of SQL statements that will be used to initialize physical
> > # connections when they are first created. These statements are executed
> > # only once - when the configured connection factory creates the
> connection.
> > # [Default: null]
> > #dbcp.connectionInitSqls=
> >
> > # Enable prepared statement pooling for this pool. [Default: false]
> > dbcp.poolPreparedStatements=true
> >
> > # The maximum number of open statements that can be allocated from the
> > # statement pool at the same time, or zero for no limit. [Default: 0]
> > dbcp.maxOpenPreparedStatements=25
> >
> > # Controls if the PoolGuard allows access to the underlying connection.
> > # [Default: false]
> > dbcp.accessToUnderlyingConnectionAllowed=false
> >
> > # Flag to remove abandoned connections if they exceed the
> > # removeAbandonedTimout. If set to true a connection is considered
> abandoned
> > # and eligible for removal if it has been idle longer than the
> > # removeAbandonedTimeout. Setting this to true can recover db connections
> > from
> > # poorly written applications which fail to close a connection.
> > # [Default: false]
> > dbcp.removeAbandoned=false
> >
> > # Timeout in seconds before an abandoned connection can be removed.
> > # [Default: 300]
> > dbcp.removeAbandonedTimeout=300
> >
> > # Flag to log stack traces for application code which abandoned a
> Statement
> > or
> > # Connection. Logging of abandoned Statements and Connections adds
> overhead
> > for
> > # every Connection open or new Statement because a stack trace has to be
> > # generated. [Default: false]
> > dbcp.logAbandoned=false
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
Re: [DBCP] Too many connections issue...
Posted by Phil Steitz <ph...@gmail.com>.
On Thu, Jan 20, 2011 at 6:16 AM, Suny kk <su...@gmail.com> wrote:
> Hi,
>
>
> I've facing "Too many connections" issue and following are my DBCP
> settings...
What exactly is your problem - i.e., what exception / liveness issue
are you seeing. Also what versions of commons dbcp and commons pool
are you running?
Phil
>
> #####################################
> # DBCP Connection Pool Settings
> #####################################
>
> # The connection properties that will be sent to our JDBC driver when
> # establishing new connections.
> dbcp.connectionProperties=""
>
> # The default auto-commit state of connections created by this pool.
> # [Default: true]
> dbcp.defaultAutoCommit=true
>
> # The default read-only state of connections created by this pool. If not
> set
> # then the setReadOnly method will not be called.
> #dbcp.defaultReadOnly=
>
> # The default catalog of connections created by this pool.
> #dbcp.defaultCatalog=
>
> # The initial number of connections that are created when the pool is
> started.
> # [Default: 0]
> dbcp.initialSize=10
>
> # The maximum number of active connections that can be allocated
> # from this pool at the same time, or negative for no limit. [Default: 8]
> dbcp.maxActive=500
>
> # The maximum number of connections that can remain idle in the pool,
> # without extra ones being released, or negative for no limit. [Default: 8]
> dbcp.maxIdle=8
>
> # The minimum number of connections that can remain idle in the pool,
> without
> # extra ones being created, or zero to create none. [Default: 0]
> dbcp.minIdle=0
>
> # The maximum number of milliseconds that the pool will wait (when there are
> # no available connections) for a connection to be returned before throwing
> an
> # exception, or -1 to wait indefinitely. [Default: -1]
> dbcp.maxWait=30000
>
> # The SQL query that will be used to validate connections from this pool
> before
> # returning them to the caller. If specified, this query MUST be an SQL
> SELECT
> # statement that returns at least one row.
> dbcp.validationQuery=SELECT 1
>
> # The indication of whether objects will be validated before being borrowed
> # from the pool. If the object fails to validate, it will be dropped from
> the
> # pool, and we will attempt to borrow another. [Default: true]
> #
> # NOTE - for a true value to have any effect, the validationQuery parameter
> # must be set to a non-null string.
> dbcp.testOnBorrow=true
>
> # The indication of whether objects will be validated before being returned
> to
> # the pool. [Default: false]
> #
> # NOTE - for a true value to have any effect, the validationQuery parameter
> # must be set to a non-null string.
> dbcp.testOnReturn=false
>
> # The indication of whether objects will be validated by the idle object
> # evictor (if any). If an object fails to validate, it will be dropped from
> the
> # pool. [Default: false]
> #
> # NOTE - for a true value to have any effect, the validationQuery parameter
> # must be set to a non-null string.
> dbcp.testWhileIdle=false
>
> # The number of milliseconds to sleep between runs of the idle object
> evictor
> # thread. When non-positive, no idle object evictor thread will be run.
> # [Default: -1]
> dbcp.timeBetweenEvictionRunsMillis=-1
>
> # The number of objects to examine during each run of the idle object
> evictor
> # thread (if any). [Default: 3]
> dbcp.numTestsPerEvictionRun=3
>
> # The minimum amount of time an object may sit idle in the pool before it is
> # eligable for eviction by the idle object evictor (if any).
> # [Default: 180000 (30 min)]
> dbcp.minEvictableIdleTimeMillis=180000
>
> # A Collection of SQL statements that will be used to initialize physical
> # connections when they are first created. These statements are executed
> # only once - when the configured connection factory creates the connection.
> # [Default: null]
> #dbcp.connectionInitSqls=
>
> # Enable prepared statement pooling for this pool. [Default: false]
> dbcp.poolPreparedStatements=true
>
> # The maximum number of open statements that can be allocated from the
> # statement pool at the same time, or zero for no limit. [Default: 0]
> dbcp.maxOpenPreparedStatements=25
>
> # Controls if the PoolGuard allows access to the underlying connection.
> # [Default: false]
> dbcp.accessToUnderlyingConnectionAllowed=false
>
> # Flag to remove abandoned connections if they exceed the
> # removeAbandonedTimout. If set to true a connection is considered abandoned
> # and eligible for removal if it has been idle longer than the
> # removeAbandonedTimeout. Setting this to true can recover db connections
> from
> # poorly written applications which fail to close a connection.
> # [Default: false]
> dbcp.removeAbandoned=false
>
> # Timeout in seconds before an abandoned connection can be removed.
> # [Default: 300]
> dbcp.removeAbandonedTimeout=300
>
> # Flag to log stack traces for application code which abandoned a Statement
> or
> # Connection. Logging of abandoned Statements and Connections adds overhead
> for
> # every Connection open or new Statement because a stack trace has to be
> # generated. [Default: false]
> dbcp.logAbandoned=false
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org