Thread: Postgres Server collapse

Postgres Server collapse

From
"Sistemasvi"
Date:
Hello.
 
I have a postsgresql database with max 500 connections. All development is in Java (View, Model, controller model). The problem is when I have high traffic (about 10 AM) when the database server collapse. The server turn too slow as It haven´t memory and the swap memory increase too much. I must kill all postgres connections and restart postmaster service.
 
I have only one connection by web session. It is good or bad?
I have already increased the number of connections to 800 in the postgres and sysctl configuration file.
 
My server is linux Red Hat and Postgres database V8
 
Which can be the problem?.
 
 
Carlos
 
 

Re: Postgres Server collapse

From
Dave Cramer
Date:
Carlos,

You should be using a connection pool, if you don't have the memory to support 800 connections then it just isn't going to work.

Dave
On 27-Nov-06, at 10:19 AM, Sistemasvi wrote:

Hello.
 
I have a postsgresql database with max 500 connections. All development is in Java (View, Model, controller model). The problem is when I have high traffic (about 10 AM) when the database server collapse. The server turn too slow as It haven´t memory and the swap memory increase too much. I must kill all postgres connections and restart postmaster service.
 
I have only one connection by web session. It is good or bad?
I have already increased the number of connections to 800 in the postgres and sysctl configuration file.
 
My server is linux Red Hat and Postgres database V8
 
Which can be the problem?.
 
 
Carlos
 
 


Re: Postgres Server collapse

From
Mark Lewis
Date:
Keeping one connection per session is a no-no if you need your site to
scale.  You'll want to use some kind of database connection pooling
instead.  Remember that sessions only expire after a certain amount of
inactivity.  Depending on your servlet container, the timeouts will be
between 20 and 60 minutes by default.  This means that if your DB server
can only handle, say, 250 connections, then you can't handle more than
250/60 = 4.17 new sessions per minute.  And you do have code to clean up
if the session times out, right?

-- Mark Lewis

On Mon, 2006-11-27 at 10:19 -0500, Sistemasvi wrote:
> Hello.
>
> I have a postsgresql database with max 500 connections. All
> development is in Java (View, Model, controller model). The problem is
> when I have high traffic (about 10 AM) when the database server
> collapse. The server turn too slow as It haven´t memory and the swap
> memory increase too much. I must kill all postgres connections and
> restart postmaster service.
>
> I have only one connection by web session. It is good or bad?
> I have already increased the number of connections to 800 in the
> postgres and sysctl configuration file.
>
> My server is linux Red Hat and Postgres database V8
>
> Which can be the problem?.
>
>
> Carlos
>
>

Re: Postgres Server collapse

From
Csaba Nagy
Date:
[snip]
> I have only one connection by web session. It is good or bad?
> I have already increased the number of connections to 800 in the
> postgres and sysctl configuration file.

You should probably google for "connection pool" in general to see what
that means, and pick a solution for JDBC. 800 connections is way too
much, we have here ~100 in real use at any time for a busy (read: with
many users and web sessions) application.

Cheers,
Csaba.



Re: Postgres Server collapse

From
imad
Date:
On 11/27/06, Sistemasvi <sistemasvi@aerogal.com.ec> wrote:
>
>
> Hello.
>
> I have a postsgresql database with max 500 connections. All development is
> in Java (View, Model, controller model). The problem is when I have high
> traffic (about 10 AM) when the database server collapse. The server turn too
> slow as It haven´t memory and the swap memory increase too much. I must kill
> all postgres connections and restart postmaster service.
>
> I have only one connection by web session. It is good or bad?

One connection only? Then why did you increased the number of connections
to 800? Sorry, I might be missing some point here.


> I have already increased the number of connections to 800 in the postgres
> and sysctl configuration file.
>
> My server is linux Red Hat and Postgres database V8
>
> Which can be the problem?.
>
>
> Carlos
>
>

--Imad
www.EnterpriseDB.com

Re: Postgres Server collapse

From
Michael Paesold
Date:
imad schrieb:
> On 11/27/06, Sistemasvi <sistemasvi@aerogal.com.ec> wrote:
>>
>>
>> Hello.
>>
>> I have a postsgresql database with max 500 connections. All
>> development is
>> in Java (View, Model, controller model). The problem is when I have high
>> traffic (about 10 AM) when the database server collapse. The server
>> turn too
>> slow as It haven´t memory and the swap memory increase too much. I
>> must kill
>> all postgres connections and restart postmaster service.
>>
>> I have only one connection by web session. It is good or bad?
>
> One connection only? Then why did you increased the number of connections
> to 800? Sorry, I might be missing some point here.

Strip "only" to understand the sentence. ;-)

Carlos has one open DB connection for each open "web" (i.e. Webbrowser)
session, which means that there are way too many connections open for a
great number of currently waiting sessions. A connection pool should
alleviate the problems.

Best Regards
Michael Paesold


Re: Postgres Server collapse

From
imad
Date:
Ok sorry.
I missed that "session" thing.

--Imad
www.EnterpriseDB.com


On 11/28/06, Michael Paesold <mpaesold@gmx.at> wrote:
> imad schrieb:
> > On 11/27/06, Sistemasvi <sistemasvi@aerogal.com.ec> wrote:
> >>
> >>
> >> Hello.
> >>
> >> I have a postsgresql database with max 500 connections. All
> >> development is
> >> in Java (View, Model, controller model). The problem is when I have high
> >> traffic (about 10 AM) when the database server collapse. The server
> >> turn too
> >> slow as It haven´t memory and the swap memory increase too much. I
> >> must kill
> >> all postgres connections and restart postmaster service.
> >>
> >> I have only one connection by web session. It is good or bad?
> >
> > One connection only? Then why did you increased the number of connections
> > to 800? Sorry, I might be missing some point here.
>
> Strip "only" to understand the sentence. ;-)
>
> Carlos has one open DB connection for each open "web" (i.e. Webbrowser)
> session, which means that there are way too many connections open for a
> great number of currently waiting sessions. A connection pool should
> alleviate the problems.
>
> Best Regards
> Michael Paesold
>
>

Re: Postgres Server collapse

From
Altaf Malik
Date:
This is because you open one connection per session and the number of sessions incease. :) It would be better if you use ConnectionPooling instead of managing your own session.
Otherwise, do the following:-
 
1. Reduce your session timeout
2. Make sure no connection remains open
3. It would be better if you make a single connection in your application and manage it somehow
 
It seems like your sessions are never killed, or at the end of session, you never close your existing session.
 
--Altaf Malik


imad <immaad@gmail.com> wrote:
On 11/27/06, Sistemasvi wrote:
>
>
> Hello.
>
> I have a postsgresql database with max 500 connections. All development is
> in Java (View, Model, controller model). The problem is when I have high
> traffic (about 10 AM) when the database server collapse. The server turn too
> slow as It haven´t memory and the swap memory increase too much. I must kill
> all postgres connections and restart postmaster service.
>
> I have only one connection by web session. It is good or bad?

One connection only? Then why did you increased the number of connections
to 800? Sorry, I might be missing some point here.


> I have already increased the number of connections to 800 in the postgres
> and sysctl configuration file.
>
> My server is linux Red Hat and Postgres database V8
>
> Which can be the problem?.
>
>
> Carlos
>
>

--Imad
www.EnterpriseDB.com

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Want to start your own business? Learn how on Yahoo! Small Business.