pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed[Fwd: postgres 8.4.1 number of connections]
5+ messages / 4 participants
[nested] [flat]
* [Fwd: postgres 8.4.1 number of connections]
@ 2010-08-26 20:29 Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
2010-08-26 21:13 ` Re: [Fwd: postgres 8.4.1 number of connections] Scott Marlowe <scott.marlowe@gmail.com>
2010-08-27 01:05 ` Re: [Fwd: postgres 8.4.1 number of connections] Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 5+ messages in thread
From: Maria L. Wilson @ 2010-08-26 20:29 UTC (permalink / raw)
To: pgsql-performance
-------- Original Message --------
Subject: postgres 8.4.1 number of connections
Date: Thu, 26 Aug 2010 14:25:47 -0500
From: Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
Reply-To: Wilson, Maria Louise (LARC-E301)[SCIENCE SYSTEMS
APPLICATIONS] <m.l.wilson@nasa.gov>
To: pgsql-performance-owner@postgresql.org
<pgsql-performance-owner@postgresql.org>
we have this application (using jboss/java/hibernate) on linux accessing
data on 3 postgres database servers using 8.4.1.
One of our many concerns has been the way we handle connections to the
database. java/hibernate handle their own pooling so I understand that
using anything else is out of the question. Our jboss configuration
currently defaults to 5 connections per database. On our main database
server, we handle 7 of the databases that this application uses. On
this one server, we usually average around 300 - 500 connections to the
server. In our postgres conf file on this particular machine we set the
max_connection parameter to 1000. If we set it to much lower we end up
with connection errors. Any comments or better way to handle this?
thanks, Maria Wilson
NASA, Langley Research Center
Hampton, Virginia 23666
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [Fwd: postgres 8.4.1 number of connections]
2010-08-26 20:29 [Fwd: postgres 8.4.1 number of connections] Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
@ 2010-08-26 21:13 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-27 07:54 ` Re: [Fwd: postgres 8.4.1 number of connections] Magnus Hagander <magnus@hagander.net>
2010-08-30 13:53 ` Re: [Fwd: postgres 8.4.1 number of connections] Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
1 sibling, 2 replies; 5+ messages in thread
From: Scott Marlowe @ 2010-08-26 21:13 UTC (permalink / raw)
To: Maria.L.Wilson-1@nasa.gov; +Cc: pgsql-performance
On Thu, Aug 26, 2010 at 2:29 PM, Maria L. Wilson
<Maria.L.Wilson-1@nasa.gov> wrote:
>
>
> we have this application (using jboss/java/hibernate) on linux accessing
> data on 3 postgres database servers using 8.4.1.
>
> One of our many concerns has been the way we handle connections to the
> database. java/hibernate handle their own pooling so I understand that
> using anything else is out of the question.
It's not impossible to put another connection pooler between hibernate
and your database server. May not be useful or better, but it's
possible.
> Our jboss configuration
> currently defaults to 5 connections per database.
I assume this is for each app server. How many app servers do you have?
> On our main database
> server, we handle 7 of the databases that this application uses. On
> this one server, we usually average around 300 - 500 connections to the
> server. In our postgres conf file on this particular machine we set the
> max_connection parameter to 1000. If we set it to much lower we end up
> with connection errors. Any comments or better way to handle this?
Other than breaking out the databases onto their own server, or trying
to pool to fewer connections, not really. OTOH, a db server with
enough memory can handle having that many connections as long as you
don't run into any thundering herd issues.
Oh, and you should really update your pgsql to 8.4.4. 8.4.1 had some
issues with heavy load I ran into last year about this time. Which is
why I'm still on 8.3.11
--
To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [Fwd: postgres 8.4.1 number of connections]
2010-08-26 20:29 [Fwd: postgres 8.4.1 number of connections] Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
2010-08-26 21:13 ` Re: [Fwd: postgres 8.4.1 number of connections] Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-08-27 07:54 ` Magnus Hagander <magnus@hagander.net>
1 sibling, 0 replies; 5+ messages in thread
From: Magnus Hagander @ 2010-08-27 07:54 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Maria.L.Wilson-1 <Maria.L.Wilson-1@nasa.gov>; pgsql-performance
On Thu, Aug 26, 2010 at 23:13, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Aug 26, 2010 at 2:29 PM, Maria L. Wilson
> <Maria.L.Wilson-1@nasa.gov> wrote:
>>
>>
>> we have this application (using jboss/java/hibernate) on linux accessing
>> data on 3 postgres database servers using 8.4.1.
>>
>> One of our many concerns has been the way we handle connections to the
>> database. java/hibernate handle their own pooling so I understand that
>> using anything else is out of the question.
>
> It's not impossible to put another connection pooler between hibernate
> and your database server. May not be useful or better, but it's
> possible.
I've been using pgbouncer in combination with the jboss connection
pooler several times, mainly for the reason that reconfiguring the
jboss connection poolers (particularly if you have lots of them) can
cause quite a bit of downtime. Just sticking a 1-1 mapping pgbouncer
in between with support for SUSPEND makes a lot of difference if you
switch master/slave on your replication /ha. It'll still break the
connections for jboss, but it'll recover from that a *lot* faster than
a reconfig.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [Fwd: postgres 8.4.1 number of connections]
2010-08-26 20:29 [Fwd: postgres 8.4.1 number of connections] Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
2010-08-26 21:13 ` Re: [Fwd: postgres 8.4.1 number of connections] Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-08-30 13:53 ` Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
1 sibling, 0 replies; 5+ messages in thread
From: Maria L. Wilson @ 2010-08-30 13:53 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Wilson, Maria Louise (LARC-E301)[SCIENCE SYSTEMS APPLICATIONS] <m.l.wilson@nasa.gov>; pgsql-performance
thanks for your response....
we have 3 app servers that attach to this one particular database server.
What kind of load issues did you find on 8.4.1? I'd be interested in
anything documented on it - this might make an upgrade a higher priority!
thanks, Maria
Scott Marlowe wrote:
> On Thu, Aug 26, 2010 at 2:29 PM, Maria L. Wilson
> <Maria.L.Wilson-1@nasa.gov> wrote:
>
>> we have this application (using jboss/java/hibernate) on linux accessing
>> data on 3 postgres database servers using 8.4.1.
>>
>> One of our many concerns has been the way we handle connections to the
>> database. java/hibernate handle their own pooling so I understand that
>> using anything else is out of the question.
>>
>
> It's not impossible to put another connection pooler between hibernate
> and your database server. May not be useful or better, but it's
> possible.
>
>
>> Our jboss configuration
>> currently defaults to 5 connections per database.
>>
>
> I assume this is for each app server. How many app servers do you have?
>
>
>> On our main database
>> server, we handle 7 of the databases that this application uses. On
>> this one server, we usually average around 300 - 500 connections to the
>> server. In our postgres conf file on this particular machine we set the
>> max_connection parameter to 1000. If we set it to much lower we end up
>> with connection errors. Any comments or better way to handle this?
>>
>
> Other than breaking out the databases onto their own server, or trying
> to pool to fewer connections, not really. OTOH, a db server with
> enough memory can handle having that many connections as long as you
> don't run into any thundering herd issues.
>
> Oh, and you should really update your pgsql to 8.4.4. 8.4.1 had some
> issues with heavy load I ran into last year about this time. Which is
> why I'm still on 8.3.11
>
> --
> To understand recursion, one must first understand recursion.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [Fwd: postgres 8.4.1 number of connections]
2010-08-26 20:29 [Fwd: postgres 8.4.1 number of connections] Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
@ 2010-08-27 01:05 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2010-08-27 01:05 UTC (permalink / raw)
To: Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>; +Cc: pgsql-performance
> One of our many concerns has been the way we handle connections to the
> database. java/hibernate handle their own pooling so I understand that
> using anything else is out of the question.
It's not out of the question, but it's probably not necessary.
> Our jboss configuration
> currently defaults to 5 connections per database. On our main database
> server, we handle 7 of the databases that this application uses. On
> this one server, we usually average around 300 - 500 connections to the
> server.
Do you have 10 to 20 application servers? Otherwise I don't understand
how you're getting 300 to 500 connections. Do you have any stats about
how many connections for each database each JBOSS server actually needs?
It seems unusual that it would need more than one or two.
Also, why does the application use 7 different databases?
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-08-30 13:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-26 20:29 [Fwd: postgres 8.4.1 number of connections] Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
2010-08-26 21:13 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-27 07:54 ` Magnus Hagander <magnus@hagander.net>
2010-08-30 13:53 ` Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
2010-08-27 01:05 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox