Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

Here is the output of OS limits

postgres@symds-pg:~ $ ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 790527
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 4096
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16384
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited


Thanks
Prabhjot

-----Original Message-----
From: ktm@rice.edu [mailto:ktm@rice.edu]
Sent: Thursday, June 18, 2015 10:16 AM
To: Sheena, Prabhjot
Cc: pgsql-performance@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

On Thu, Jun 18, 2015 at 05:09:10PM +0000, Sheena, Prabhjot wrote:
> Guys
>          I have an issue going on with PGBOUNCER which is slowing down
> the site
>
> PGBOUNCER VERSION:  pgbouncer-1.5.4-2.el6 (Hosted on separate machine) (16 cpu) 98GB RAM
> DATABASE VERION:         postgresql 9.3
>
> When the total client connections to pgbouncer are close to 1000, site application works fine but when  the total
clientconnections crosses 1150 site application starts showing slowness. 
>
> Here is an example of output
>
> postgres@symds-pg:~ $ netstat -atnp | grep 5432  | wc (Not all
> processes could be identified, non-owned process info will not be
> shown, you would have to be root to see it all.)
>     960    6720  104640
>
>
> As you can see total connections are like 960 right now my site application is working fine. When connections crosses
1150 and even though I see lot of available connections coz my  default_pool_size is set high to 250  but still the
applicationgets slow. Database performance  on the other end is great with no slow running queries or anything. So the
onlyplace I can think the issue is at PGBOUNCER end. 
>

Hi Prabhjot,

This is classic behavior when you have a 1024 file limit. When you are below that number, it work fine. Above that
number,you must wait for a connection to close and exit before you can connect which will cause a delay. See what
ulimithas to say? 

Regards,
Ken