PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

From: "Sheena, Prabhjot" <Prabhjot(dot)Singh(at)classmates(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
Date: 2015-06-18 17:09:10
Message-ID: 13E39D789E19FF4E9A87F6D456F3F9820123C607@SEAMBX02.sea.corp.int.untd.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message ktm@rice.edu 2015-06-18 17:16:23 Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
Previous Message David G. Johnston 2015-06-18 16:11:54 Re: PL/pgSQL: How to return two columns and multiple rows

Browse pgsql-performance by date

  From Date Subject
Next Message ktm@rice.edu 2015-06-18 17:16:23 Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
Previous Message Tomas Vondra 2015-06-17 14:47:28 Re: PATCH: adaptive ndistinct estimator v4