Re: JdbcDataSource's connections

[Leif Mortenson <le...@silveregg.co.jp>]

Braginsky, Leo wrote:

>Hello all,
>
>I'm using JdbcDataSource class to pool connections. It's been working pretty
>well - I loaded the system on Solaris box (single CPU, 500Mhz, 1GB) with 100
>concurrent users via LoadRunner. I wonder if I can get answers to the
>following questions:
>
Can you explain your test a little more?  Do mean that you have 100 
threads which each call getConnection on the DataSource and use the 
connection for the duration?  Or that you have 100 threads each calling 
getConnection followed be a query and then a close(), returning the 
connection to the pool.   I assume the later.

>1) The min(initial) and max pool sizes are configurable which is great. But
>I don't see how I can set the pool "grow" parameter. What's the default? How
>can I change it?
>
The grow parameter can not be set manually.  It is set to be the value 
of max / 4 or 1, whichever is greater.

(The ResourceLimitingJdbcDataSource, described below, does not implement 
a grow size, or allow for a min value because of its trimming 
optimizations.)

>2) Until when do the connections in the pool stay open? Is there a timeout
>or until the app shuts down?
>
The JdbcDataSource is built on top of a HardResourceLimitingPool, and 
will not destroy any objects stored in the pool until the pool is disposed.

I had wanted old connections to be closed to conserve DB resources when 
they were not needed in addition to having calls to getConnection() 
block when max Connections were already in use.  The first was difficult 
and the second would have required a lot of redesign work, so I 
implemented a new class of pools and a resulting DataSource called the 
ResourceLimitingJdbcDataSource.  This class is located in the datasource 
package of the Scratchpad.   It is compatible with the JdbcDataSource, 
but understands some additional optional parameters.  Take a look at the 
Javadocs for details.  It can be enabled by simply changing the class of 
the DataSource in your roles.xml file.

The ResourceLimitingJdbcDataSource allows you to specify a trimming 
interval at which Connections which have gone unused for a period of 
time will be marked as old and then closed.   Once again, take a look at 
the Javadocs for details on how the trimming works.  It is actually a 
feature of the underlying ResourceLimitingPool class

>
>
>3) What might cause this exception:
>DEBUG   10122   [ehrconte] ():
>
>>org.apache.avalon.excalibur.datasource.JdbcConnection: could not be
>>instantiated.
>>java.lang.InstantiationException: Ran out of resources to instantiate 
>>
This is the main reason that I initially implemented the 
ResourceLimitingJdbcDataSource.  JdbcDataSource being based on the 
HardResourceLimitingPool will throw an excaption when get() is called 
when max objects are already outstanding.

The ResourceLimitingPool can be configured to work this way as well but 
by default it will cause get() request to block until another thread has 
called put.  It allows for a timeout to be set as well, though it is 
indefinite by default.

>4) Can anyone who's been using this set of classes tell me what load he has
>been applying (in number of concurrent users) and what the resutls were? 
>
This is tough to answer because it will very GREATLY depending on the 
type of database you are using as well as what kind of queries you are 
doing.  Both the JdbcDataSource and ResourceLimitingJdbcDataSource 
classes are written on efficient pools.  They may have some slight 
performance differences when their underlying pools are tested with 
pools of in memory objects, but in the case of DataSource pools, the 
bottle neck is not the pool, but rather the database.   As a general 
rule, the pools are optimized for the case where Connections are closed 
within a short interval of being obtained from the pool.  Your code 
should not hang on to them for long periods of time as that will prevent 
other threads for accessing them when they are idle.

I have found that in general an application can serve a large number of 
users, say 100, with a database pool with a max of 5 (if blocking is 
enabled)  To prevent errors, you would max to be higher if you are using 
the JdbcDataSource.  This really depends on what your application is 
doing with the connections and how long each query takes to execute though.

Also, most databases perform better when the number of open connections 
is kept to a minimum.  I believe MySQL is set to only allow 50 
connections by default and PostgreSQL  is 32 in the RedHat distribution 
and 64 for Debian.  So being able to Trim the connection count to what 
is really needed rather than maining the maximum number up to any point 
is important, in my opinion. :-)

We could really use some performance data so that we can answer this 
part a little better, I'll give this some thought.

Let me know if there are any of your questions that I missed.
Cheers,
Leif



--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>