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

My guess is you are hitting an open file ulimit. Add ulimit -n 50000 to
the start of whatever you use to start pgbouncer (init script, etc..)

On Thu, Jun 18, 2015 at 1:10 PM Sheena, Prabhjot <
Prabhjot(dot)Singh(at)classmates(dot)com> 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 client connections crosses 1150
> site application starts showing slowness.
>
>
>
> Here is an example of output
>
>
>
> postgres(at)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 application gets slow. Database performance on the
> other end is great with no slow running queries or anything. So the only
> place I can think the issue is at PGBOUNCER end.
>
>
>
> pgbouncer=# show config;
>
> key | value | changeable
>
> ---------------------------+----------------------------------+------------
>
> job_name | pgbouncer | no
>
> conffile | /etc/pgbouncer/pgbouncer.ini | yes
>
> logfile | /var/log/pgbouncer.log | yes
>
> pidfile | /var/run/pgbouncer/pgbouncer.pid | no
>
> listen_addr | * | no
>
> listen_port | 5432 | no
>
> listen_backlog | 128 | no
>
> unix_socket_dir | /tmp | no
>
> unix_socket_mode | 511 | no
>
> unix_socket_group | | no
>
> auth_type | md5 | yes
>
> auth_file | /etc/pgbouncer/userlist.txt | yes
>
> pool_mode | transaction | yes
>
> max_client_conn | 3000 | yes
>
> default_pool_size | 250 | yes
>
> min_pool_size | 0 | yes
>
> reserve_pool_size | 0 | yes
>
> reserve_pool_timeout | 5 | yes
>
> syslog | 0 | yes
>
> syslog_facility | daemon | yes
>
> syslog_ident | pgbouncer | yes
>
> user | | no
>
> autodb_idle_timeout | 3600 | yes
>
> server_reset_query | | yes
>
> server_check_query | select 1 | yes
>
> server_check_delay | 30 | yes
>
> query_timeout | 0 | yes
>
> query_wait_timeout | 0 | yes
>
> client_idle_timeout | 0 | yes
>
> client_login_timeout | 60 | yes
>
> idle_transaction_timeout | 0 | yes
>
> server_lifetime | 3600 | yes
>
> server_idle_timeout | 600 | yes
>
> server_connect_timeout | 15 | yes
>
> server_login_retry | 15 | yes
>
> server_round_robin | 0 | yes
>
> suspend_timeout | 10 | yes
>
> ignore_startup_parameters | extra_float_digits | yes
>
> disable_pqexec | 0 | no
>
> dns_max_ttl | 15 | yes
>
> dns_zone_check_period | 0 | yes
>
> max_packet_size | 2147483647 | yes
>
> pkt_buf | 2048 | no
>
> sbuf_loopcnt | 5 | yes
>
> tcp_defer_accept | 1 | yes
>
> tcp_socket_buffer | 0 | yes
>
> tcp_keepalive | 1 | yes
>
> tcp_keepcnt | 0 | yes
>
> tcp_keepidle | 0 | yes
>
> tcp_keepintvl | 0 | yes
>
> verbose | 0 | yes
>
> admin_users | postgres | yes
>
> stats_users | stats, postgres | yes
>
> stats_period | 60 | yes
>
> log_connections | 1 | yes
>
> log_disconnections | 1 | yes
>
> log_pooler_errors | 1 | yes
>
>
>
>
>
> Thanks
>
> Prabhjot
>
>
>