Обсуждение: 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 >