Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) - Mailing list 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@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