Thread: Max. connections & overload

Max. connections & overload

From
Radu-Adrian Popescu
Date:
Hello !
This is a problem for the gurus, or maybe it's not even a problem...

Here goes :
I have a PostgreSql 7.2.b4 on a PIII with 128MB of ram, running RedHat
7.2 with a 2.4.9-13 kernel.
The system is running postgresql, Apache/PHP server, sshd, nfs and
cvspserver (CVS password-server).
I've set the shared memory size to 100985856, that's ~96 MB of ram.
In the postgresql.conf file i set maxconnections to 1000 and the shared
buffers to 2000. ipcs corectly reports the shmem goes to postgresql.
From my workstation i run a simple load-test program, that goes like
while(nrconn < 1000){
    conn = new CSqlConnection(to_server);
    conn->query("select * from a table with 3 rows"); // not much of     // a
load here
    conn->freeresult();
    // don't close DB connection, just free the resultset
}
Okay, the first time i hit the openfiles limit; but when i got passed
it, the box choked at about 550 simultaneous connections, when it
started to kill backends and then shut down.
Watching the server box with top i could see the the ram's flying away,
and the server will eat all the swap (256MB!), and when it's done, the
postgresql server cryes "fork() resource temporary unavailable" (that
would be memory, not tasks as i originally thought) and starts killing
backends.

Today i set the maxconnections to 600 and the buffers to 1200, and my
test code ran until it hit the 600 connections limit, when the server
politely refused any more connections. The server box was away for about
2 minutes, during which i suppose it was reclaiming memory and freeing
up swap.

Please don't flame me about the 128MB on a "server" as i call it, a
gigabyte it's on his way.

However i'd like to hear from someone who's done real life server
optimization for alot of incoming connections; am i missing smth here,
or doing it all wrong ?

I know Bruce Momjian has some good articles on pgsql optimization, and
they have been usefull so far, so don't send me back to those. I'd like
some guru/expert opinion here, about real system/db administration.

Regards,

--



Radu-Adrian Popescu
CSA, DBA, Programmer