Thread: Postgres Server collapse
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
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 V8Which can be the problem?.Carlos
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 > >
[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.
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
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
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 > >
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.