Thread: connection pooling for postgres

Greetings,

Sorry for the elementary question but I am still relatively new to Java
and JDBC.

I have 4 clients running on different servers that connect to a Postgres
database, pull down some data, process this data and then insert it back
to the database into different tables.

Because several of the client servers are running on multiple CPUs, or
at least dual core, systems there are actually 10 threads on each client
that are doing the processing.  This means 10 connections per server.  I
think this is causing some resource problems and would like to see how
things run if I set up a connection pool for each client, of say 3
connections, and the processing threads would then use one of those 3
when it becomes available.

Where can I find good instructions or a tutorial on how to do connection
pooling for JDBC to a Postgres database on my client?

Is there a better way for me to approach this?

Thanks in advance,
James

James,

The internal pooling implementation that the driver provides is not
really production quality.

It's recommended you look at apache's dbcp.

Dave
On 11-Jan-07, at 9:28 AM, James Neff wrote:

> Greetings,
>
> Sorry for the elementary question but I am still relatively new to
> Java and JDBC.
>
> I have 4 clients running on different servers that connect to a
> Postgres database, pull down some data, process this data and then
> insert it back to the database into different tables.
>
> Because several of the client servers are running on multiple CPUs,
> or at least dual core, systems there are actually 10 threads on
> each client that are doing the processing.  This means 10
> connections per server.  I think this is causing some resource
> problems and would like to see how things run if I set up a
> connection pool for each client, of say 3 connections, and the
> processing threads would then use one of those 3 when it becomes
> available.
>
> Where can I find good instructions or a tutorial on how to do
> connection pooling for JDBC to a Postgres database on my client?
>
> Is there a better way for me to approach this?
>
> Thanks in advance,
> James
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

Dave,

Is DBCP in good shape these days?  We were disappointed with its
stability a few years back, although I guess there's been plenty of time
since then for things to improve . . .

-- Mark Lewis

On Thu, 2007-01-11 at 10:04 -0500, Dave Cramer wrote:
> James,
>
> The internal pooling implementation that the driver provides is not
> really production quality.
>
> It's recommended you look at apache's dbcp.
>
> Dave
> On 11-Jan-07, at 9:28 AM, James Neff wrote:
>
> > Greetings,
> >
> > Sorry for the elementary question but I am still relatively new to
> > Java and JDBC.
> >
> > I have 4 clients running on different servers that connect to a
> > Postgres database, pull down some data, process this data and then
> > insert it back to the database into different tables.
> >
> > Because several of the client servers are running on multiple CPUs,
> > or at least dual core, systems there are actually 10 threads on
> > each client that are doing the processing.  This means 10
> > connections per server.  I think this is causing some resource
> > problems and would like to see how things run if I set up a
> > connection pool for each client, of say 3 connections, and the
> > processing threads would then use one of those 3 when it becomes
> > available.
> >
> > Where can I find good instructions or a tutorial on how to do
> > connection pooling for JDBC to a Postgres database on my client?
> >
> > Is there a better way for me to approach this?
> >
> > Thanks in advance,
> > James
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >               http://www.postgresql.org/docs/faq
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

On Thursday 11 January 2007 15:28, James Neff wrote:
> Greetings,

> Where can I find good instructions or a tutorial on how to do connection
> pooling for JDBC to a Postgres database on my client?

http://www.mchange.com/projects/c3p0/index.html

c3p0 was designed to be butt-simple to use. Just put the jar file
[lib/c3p0-0.9.0.jar] in your application's effective CLASSPATH, and make a
DataSource like this:

import com.mchange.v2.c3p0.*;
       
...
       
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "org.postgresql.Driver" ); //loads the jdbc driver            
cpds.setJdbcUrl( "jdbc:postgresql://localhost/testdb" );
cpds.setUser("dbuser");                                  
cpds.setPassword("dbpassword");                                  
     
[Optional] If you want to turn on PreparedStatement pooling, you must also set
maxStatements and/or maxStatementsPerConnection(both default to 0):

cpds.setMaxStatements( 180 );                                  
     
 Do whatever you want with your DataSource, which will be backed by a
Connection pool set up with default parameters. You can bind the DataSource
to a JNDI name service, or use it directly, as you prefer.
 When you are done, you can clean up the DataSource you've created like this:

DataSources.destroy( cpds );
     
 That's it! The rest is detail.

hth
ido

This is another good option.

On 11-Jan-07, at 10:21 AM, Ido M. Tamir wrote:

> On Thursday 11 January 2007 15:28, James Neff wrote:
>> Greetings,
>
>> Where can I find good instructions or a tutorial on how to do
>> connection
>> pooling for JDBC to a Postgres database on my client?
>
> http://www.mchange.com/projects/c3p0/index.html
>
> c3p0 was designed to be butt-simple to use. Just put the jar file
> [lib/c3p0-0.9.0.jar] in your application's effective CLASSPATH, and
> make a
> DataSource like this:
>
> import com.mchange.v2.c3p0.*;
>
> ...
>
> ComboPooledDataSource cpds = new ComboPooledDataSource();
> cpds.setDriverClass( "org.postgresql.Driver" ); //loads the jdbc
> driver
> cpds.setJdbcUrl( "jdbc:postgresql://localhost/testdb" );
> cpds.setUser("dbuser");
> cpds.setPassword("dbpassword");
>
> [Optional] If you want to turn on PreparedStatement pooling, you
> must also set
> maxStatements and/or maxStatementsPerConnection(both default to 0):
>
> cpds.setMaxStatements( 180 );
>
>  Do whatever you want with your DataSource, which will be backed by a
> Connection pool set up with default parameters. You can bind the
> DataSource
> to a JNDI name service, or use it directly, as you prefer.
>  When you are done, you can clean up the DataSource you've created
> like this:
>
> DataSources.destroy( cpds );
>
>  That's it! The rest is detail.
>
> hth
> ido
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

Mark,

I use it all the time, never had any complaints other than lack of
logging.

Dave
On 11-Jan-07, at 10:10 AM, Mark Lewis wrote:

> Dave,
>
> Is DBCP in good shape these days?  We were disappointed with its
> stability a few years back, although I guess there's been plenty of
> time
> since then for things to improve . . .
>
> -- Mark Lewis
>
> On Thu, 2007-01-11 at 10:04 -0500, Dave Cramer wrote:
>> James,
>>
>> The internal pooling implementation that the driver provides is not
>> really production quality.
>>
>> It's recommended you look at apache's dbcp.
>>
>> Dave
>> On 11-Jan-07, at 9:28 AM, James Neff wrote:
>>
>>> Greetings,
>>>
>>> Sorry for the elementary question but I am still relatively new to
>>> Java and JDBC.
>>>
>>> I have 4 clients running on different servers that connect to a
>>> Postgres database, pull down some data, process this data and then
>>> insert it back to the database into different tables.
>>>
>>> Because several of the client servers are running on multiple CPUs,
>>> or at least dual core, systems there are actually 10 threads on
>>> each client that are doing the processing.  This means 10
>>> connections per server.  I think this is causing some resource
>>> problems and would like to see how things run if I set up a
>>> connection pool for each client, of say 3 connections, and the
>>> processing threads would then use one of those 3 when it becomes
>>> available.
>>>
>>> Where can I find good instructions or a tutorial on how to do
>>> connection pooling for JDBC to a Postgres database on my client?
>>>
>>> Is there a better way for me to approach this?
>>>
>>> Thanks in advance,
>>> James
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 3: Have you checked our extensive FAQ?
>>>
>>>               http://www.postgresql.org/docs/faq
>>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>