Skip site navigation (1) Skip section navigation (2)

Re: more than 1000 connections

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Jorge Medina <jorge(at)bsdchile(dot)cl>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: more than 1000 connections
Date: 2008-08-07 02:05:47
Message-ID: 489A587B.7010102@postnewspapers.com.au (view raw or flat)
Thread:
Lists: 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 it recover?

- 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 stalling due 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
  or decrease 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 state then 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
  for free 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

In response to

pgsql-sql by date

Next:From: Yura GalDate: 2008-08-07 06:50:34
Subject: Re: How to creat tables using record ID in for loop
Previous:From: RagnarDate: 2008-08-06 22:35:49
Subject: Re: How to creat tables using record ID in for loop

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group