Re: more than 1000 connections - Mailing list pgsql-sql

Jorge Medina wrote:
> I have many trouble's with server, because my webmail(roundcube) works
> with the db and the machine only have 2G of RAM

2GB of RAM can go a long way. It looks like this webmail app is sensible
enough to use IMAP for mail store access, so you don't have to worry
about messages in the database. That should give you quite a bit of
working memory for other things.

It would help to know how large your database is - both in physical
on-disk size, and how large an SQL dump (with pg_dump) of the database is.

> but collapse with 60
> concurrent connections, I try with persistent connections and the same
> problem

First: PHP's persistent database connections are a *bad* answer. They
handle variable spiky very poorly, and they're wasteful of database
server resources.

Look into using a connection pooler like pgpool.

As for "collapse"... that's completely uninformative.

- Describe the symptoms of a collapse from the user's perspective. What stops working, or slows down? How? When? Does
itrecover?
 

- Describe the symptoms of a collapse from the admin's perspective. How does the server behave? What is in the error
logs?What do the usual performance monitoring tools tell you?
 

Start with:

- Does the server run low on memory? Does it have too many processes struggling for time on too few CPUs? Is it
stallingdue to disk I/O bottlenecks?
 

- In `free -m' do you see increasing swap usage?

- If you follow `vmstat 1' output, do you see increasing swap activity as load increases? Does disk throughput increase
ordecrease when the server "collapses"? What is CPU activity like when the server "collapses"?
 

- In `top', what processes are keeping the server's CPUs busy? Are any particular processes hogging CPU time when the
server"collapses"?
 

- In `top', what are the states of the apache and postgres processes of interest? If you see lots of processes in the D
statethen you might well have disk I/O bandwidth or scheduling issues.
 

- If you use `tail -f' to follow BOTH the apache and postgresql logs (in different terminals), do you see anything
change,any warnings/errors, etc as the server "collapses"?
 

- Does the server recover from "collapse" when load is reduced?

- Can you still ping the server when it has "collapsed"?

- Can you still ssh into the server when it has "collapsed"? Use `ssh -v' so you can see the progress of the
connection.

Also, please provide a spec for your server hardware, including:

- CPU type and number of CPUs/cores

- Operating system/distro and version

- PostgreSQL version

- Where you got PostgreSQL from/how you installed it

- Model of RAID controller you use and its configuration eg RAID 5, RAID 10, etc.

- Disk info:
-- Number of disks
-- Interface of disk(s) eg SATA, PATA, SAS, SCSI
-- Capacity of disk(s)
-- Spindle speed of disk(s), eg 7200RPM, 10kRPM, 15kRPM

Finally:

- We know the physical RAM is 2GB, but how much RAM is free when the server is idle? Use `free -m' and read the value
forfree memory on the +-buffers/cache line.
 

[I'm going to put together a "so, you think we're psychic" email
template on the wiki soon, as the number of people who don't provide the
basic information needed to usefully answer most questions seems to be
continually increasing.]

> I need configure a pool of connection or something.

Yes, I'd say so. What is preventing you from trying that? What is your
question?

--
Craig Ringer