Connection Pooling

[Chase Preuninger]

What is the best way to create/get a database pool for a serious web
application?

 

[Arne Vajhøj]

What is the best way to create/get a database pool for a serious web
application?

Use the database connection pool capability in the app server.

Tomcat, JBoss, WebSphere, WebLogic etc. all supports it.

Arne

 

[kuassi.mensah]

Use the database connection pool capability in the app server.

Tomcat, JBoss, WebSphere, WebLogic etc. all supports it.

Arne

The problem with middle-tier connection pools is that they cannot span
JVMs or midlet0er instances. Oracle's Database Resident Connecton Pool
http://www.oracle.com/technology/tech/php/pdf/php-scalability-ha-twp.pdf
solves this problem; unfortunately it is not (yet) exposed to Java
only PHP and Ruby/Rails (primarily because these are process based not
thread based).

Kuassi

 

[Arne Vajhøj]

The problem with middle-tier connection pools is that they cannot span
JVMs or midlet0er instances.

That is not a problem. It is an advantage. Because interacting with pool
is then a local call.

Oracle's Database Resident Connecton Pool
http://www.oracle.com/technology/tech/php/pdf/php-scalability-ha-twp.pdf
solves this problem; unfortunately it is not (yet) exposed to Java
only PHP and Ruby/Rails (primarily because these are process based not
thread based).

That solution is used not because it is a better solution, but
because the traditional Java/.NET/C++ solution does not work with PHP.

You can use DRCP from Java.

http://www.oracle.com/technology/pu...ase-11g-top-features/11g-caching-pooling.html

describes how to specify the JDBC connection URL.

I think the interest from Java will be low. A local pool is faster. The
only benefit of a central pool is if the workload tend to be uneven
distributed among app servers - in that case a central pool will
use less resources.

Arne

 

[kuassi.mensah]

The problem with middle-tier connection pools is that they cannot span

That is not a problem. It is an advantage. Because interacting with pool
is then a local call.

The saving of local call to connection pool versus remote call to the
connection broker, is epsilon compared to the saving in terms of
resource brought by DRCP.

That solution is used not because it is a better solution, but
because the traditional Java/.NET/C++ solution does not work with PHP.

Sure, unlike PHP and other dynamic languages, Java/.NET/C++ are not in
desperate need for a connection pool.

You can use DRCP from Java.

Nope, the article you are referring to is wrong; and i am as we speak
asking the author to fix the misleading JDBC URL with DRCP.

http://www.oracle.com/technology/pub/articles/oracle-database-11g-top...

describes how to specify the JDBC connection URL.

I think the interest from Java will be low. A local pool is faster.

I will argue with this opinion. Think about a large web application
with thousands of middle-tier (let's assume 4000) with each their own
connection pool. Even if each middle-tier only allocate few
connections (let's settle for 5); you end up with 20,000 pre-allocated
connections!!! Assuming that only 50% of all connections are busy at
a point in time and that this is not uniform across all middle-tiers
(how could it be?); you will be wasting 10,000 connections.

Kuassi

 

[Guest]

The saving of local call to connection pool versus remote call to the
connection broker, is epsilon compared to the saving in terms of
resource brought by DRCP.



Sure, unlike PHP and other dynamic languages, Java/.NET/C++ are not in
desperate need for a connection pool.


Nope, the article you are referring to is wrong; and i am as we speak
asking the author to fix the misleading JDBC URL with DRCP.




I will argue with this opinion. Think about a large web application
with thousands of middle-tier (let's assume 4000) with each their own
connection pool. Even if each middle-tier only allocate few
connections (let's settle for 5); you end up with 20,000 pre-allocated
connections!!! Assuming that only 50% of all connections are busy at
a point in time and that this is not uniform across all middle-tiers
(how could it be?); you will be wasting 10,000 connections.

Kuassi

Hi Kuassi!

It seems to me that any DBMS-resident 'pooling' or any change that
makes a client's wait to get a new real connection shorter, would
be a good thing, and unless it imposes some new limit on the number
of connections, it seems it would be beneficial independently of
whether there is any pooling at the client side. For any client
architecture and distribution, it would be always important to
design a client-side pool to collect only the number of connections
actually needed by the client, so I would say that the problem of
over pre-allocation can be avoided, and indeed many pool allow a
discharge of unneeded connections after a time limit, so a good
client-side pool can maintain only what it needs.
Is a DBMS-resident connection 'pooling' a good answer for the
application profile you suggest, with 4000 middle-tier clients,
each averaging 10,000 in-use connections, and a random flux of
making up to 10,000 more as needed and closing them when not?
Joe

 

[kuassi.mensah]

Hi Kuassi!

It seems to me that any DBMS-resident 'pooling' or any change that
makes a client's wait to get a new real connection shorter, would
be a good thing, and unless it imposes some new limit on the number
of connections, it seems it would be beneficial independently of
whether there is any pooling at the client side. For any client
architecture and distribution, it would be always important to
design a client-side pool to collect only the number of connections
actually needed by the client, so I would say that the problem of
over pre-allocation can be avoided, and indeed many pool allow a
discharge of unneeded connections after a time limit, so a good
client-side pool can maintain only what it needs.
Is a DBMS-resident connection 'pooling' a good answer for the
application profile you suggest, with 4000 middle-tier clients,
each averaging 10,000 in-use connections, and a random flux of
making up to 10,000 more as needed and closing them when not?
Joe

There is a socket creation (to the connection broker) during the very
first connection request from a client/middle-tier. When the
connection is returned to the pool, the socket is retained so that
subsequent connection requests do not pay that one-time socket
creation price.

DRCP does not impose a limit on the number of connections that a
client may request from the pool.

Yes, DRCP addresses the problem of over-allocation of database
connections (and the memory consumption) as a result of each client-
side middle-tier sizing its pool to avoid wait time. In a non-
centralized pooling (hundreds/thousands of middle-tier pools), there
is no way to meet the average in-use connections without over-sizing
or incurring expensive database connections creation/destruction.

Kuassi