pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
10+ messages / 5 participants
[nested] [flat]
* PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
@ 2015-06-18 17:09 Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 17:16 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
2015-06-18 17:17 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Will Platnick <wplatnick@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Sheena, Prabhjot @ 2015-06-18 17:09 UTC (permalink / raw)
To: pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
2015-06-18 17:09 PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
@ 2015-06-18 17:16 ` ktm@rice.edu <ktm@rice.edu>
2015-06-18 17:41 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
1 sibling, 1 reply; 10+ messages in thread
From: ktm@rice.edu @ 2015-06-18 17:16 UTC (permalink / raw)
To: Sheena, Prabhjot <Prabhjot.Singh@classmates.com>; +Cc: pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
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 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.
>
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 ulimit has to
say?
Regards,
Ken
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
2015-06-18 17:09 PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 17:16 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
@ 2015-06-18 17:41 ` Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 18:10 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
0 siblings, 1 reply; 10+ messages in thread
From: Sheena, Prabhjot @ 2015-06-18 17:41 UTC (permalink / raw)
To: ktm@rice.edu <ktm@rice.edu>; +Cc: pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
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 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.
>
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 ulimit has to say?
Regards,
Ken
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
2015-06-18 17:09 PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 17:16 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
2015-06-18 17:41 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
@ 2015-06-18 18:10 ` ktm@rice.edu <ktm@rice.edu>
2015-06-18 19:19 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
0 siblings, 1 reply; 10+ messages in thread
From: ktm@rice.edu @ 2015-06-18 18:10 UTC (permalink / raw)
To: Sheena, Prabhjot <Prabhjot.Singh@classmates.com>; +Cc: pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Thu, Jun 18, 2015 at 05:41:01PM +0000, Sheena, Prabhjot wrote:
> 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
>
I would bump your open files as was suggested in your pgbouncer start
script.
Regards,
Ken
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
2015-06-18 17:09 PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 17:16 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
2015-06-18 17:41 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 18:10 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
@ 2015-06-18 19:19 ` Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 19:46 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Jerry Sievers <gsievers19@comcast.net>
2015-06-18 19:59 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Igor Neyman <ineyman@perceptron.com>
2015-06-18 20:02 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
0 siblings, 3 replies; 10+ messages in thread
From: Sheena, Prabhjot @ 2015-06-18 19:19 UTC (permalink / raw)
To: ktm@rice.edu <ktm@rice.edu>; Will Platnick <wplatnick@gmail.com>; +Cc: pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Hi Ken/ Will
I have checked the ulimit value and we are nowhere hitting the max 4096 that we have currently set. Is there any other explanation why we should be thinking of bumping it to like ulimit -n 50000 ( Add ulimit -n 50000 to the start of whatever you use to start pgbouncer (init script, etc..)) even though we are not reaching 4096 max value
Regards
Prabhjot Singh
-----Original Message-----
From: ktm@rice.edu [mailto:ktm@rice.edu]
Sent: Thursday, June 18, 2015 11:10 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:41:01PM +0000, Sheena, Prabhjot wrote:
> 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
>
I would bump your open files as was suggested in your pgbouncer start script.
Regards,
Ken
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
2015-06-18 17:09 PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 17:16 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
2015-06-18 17:41 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 18:10 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
2015-06-18 19:19 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
@ 2015-06-18 19:46 ` Jerry Sievers <gsievers19@comcast.net>
2015-06-18 20:26 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2 siblings, 1 reply; 10+ messages in thread
From: Jerry Sievers @ 2015-06-18 19:46 UTC (permalink / raw)
To: Sheena\, Prabhjot <Prabhjot.Singh@classmates.com>; +Cc: ktm\@rice.edu <ktm@rice.edu>; Will Platnick <wplatnick@gmail.com>; pgsql-performance; pgsql-general\@postgresql.org <pgsql-general@postgresql.org>
"Sheena, Prabhjot" <Prabhjot.Singh@classmates.com> writes:
> Hi Ken/ Will
>
> I have checked the ulimit value and we are nowhere hitting the max
> 4096 that we have currently set. Is there any other explanation why
> we should be thinking of bumping it to like ulimit -n 50000 ( Add
> ulimit -n 50000 to the start of whatever you use to start pgbouncer
> (init script, etc..)) even though we are not reaching 4096 max value
If I can assume you're running on linux, best you get limits readout
from...
/proc/$PID-of-bouncer-process/limits
Best not to trust that run time env of interactive shell is same as
where bouncer launched from.
FWIW
> Regards
> Prabhjot Singh
>
>
>
> -----Original Message-----
> From: ktm@rice.edu [mailto:ktm@rice.edu]
> Sent: Thursday, June 18, 2015 11:10 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:41:01PM +0000, Sheena, Prabhjot wrote:
>> 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
>>
>
> I would bump your open files as was suggested in your pgbouncer start script.
>
> Regards,
> Ken
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
2015-06-18 17:09 PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 17:16 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
2015-06-18 17:41 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 18:10 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
2015-06-18 19:19 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 19:46 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Jerry Sievers <gsievers19@comcast.net>
@ 2015-06-18 20:26 ` Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
0 siblings, 0 replies; 10+ messages in thread
From: Sheena, Prabhjot @ 2015-06-18 20:26 UTC (permalink / raw)
To: Jerry Sievers <gsievers19@comcast.net>; +Cc: ktm@rice.edu <ktm@rice.edu>; Will Platnick <wplatnick@gmail.com>; pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Here is the output of pid
postgres@symds-pg:~ $ cat /proc/15610/limits
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
Max file size unlimited unlimited bytes
Max data size unlimited unlimited bytes
Max stack size 10485760 unlimited bytes
Max core file size 0 0 bytes
Max resident set unlimited unlimited bytes
Max processes 16384 16384 processes
Max open files 4096 4096 files
Max locked memory 32768 32768 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 790527 790527 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Thanks
Prabhjot Singh
-----Original Message-----
From: Jerry Sievers [mailto:gsievers19@comcast.net]
Sent: Thursday, June 18, 2015 12:47 PM
To: Sheena, Prabhjot
Cc: ktm@rice.edu; Will Platnick; pgsql-performance@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
"Sheena, Prabhjot" <Prabhjot.Singh@classmates.com> writes:
> Hi Ken/ Will
>
> I have checked the ulimit value and we are nowhere hitting the max
> 4096 that we have currently set. Is there any other explanation why
> we should be thinking of bumping it to like ulimit -n 50000 ( Add
> ulimit -n 50000 to the start of whatever you use to start pgbouncer
> (init script, etc..)) even though we are not reaching 4096 max value
If I can assume you're running on linux, best you get limits readout from...
/proc/$PID-of-bouncer-process/limits
Best not to trust that run time env of interactive shell is same as where bouncer launched from.
FWIW
> Regards
> Prabhjot Singh
>
>
>
> -----Original Message-----
> From: ktm@rice.edu [mailto:ktm@rice.edu]
> Sent: Thursday, June 18, 2015 11:10 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:41:01PM +0000, Sheena, Prabhjot wrote:
>> 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
>>
>
> I would bump your open files as was suggested in your pgbouncer start script.
>
> Regards,
> Ken
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
2015-06-18 17:09 PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 17:16 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
2015-06-18 17:41 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 18:10 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
2015-06-18 19:19 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
@ 2015-06-18 19:59 ` Igor Neyman <ineyman@perceptron.com>
2 siblings, 0 replies; 10+ messages in thread
From: Igor Neyman @ 2015-06-18 19:59 UTC (permalink / raw)
To: Sheena, Prabhjot <Prabhjot.Singh@classmates.com>; ktm@rice.edu <ktm@rice.edu>; Will Platnick <wplatnick@gmail.com>; +Cc: pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Sheena, Prabhjot
Sent: Thursday, June 18, 2015 3:19 PM
To: ktm@rice.edu; Will Platnick
Cc: pgsql-performance@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
Hi Ken/ Will
I have checked the ulimit value and we are nowhere hitting the max 4096 that we have currently set. Is there any other explanation why we should be thinking of bumping it to like ulimit -n 50000 ( Add ulimit -n 50000 to the start of whatever you use to start pgbouncer (init script, etc..)) even though we are not reaching 4096 max value
Regards
Prabhjot Singh
-----Original Message-----
From: ktm@rice.edu [mailto:ktm@rice.edu]
Sent: Thursday, June 18, 2015 11:10 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:41:01PM +0000, Sheena, Prabhjot wrote:
> 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
>
I would bump your open files as was suggested in your pgbouncer start script.
Regards,
Ken
---
Why are you so sure that it is PgBouncer causing slowness?
You, said, bouncer pool_size is set to 250. How many cores do you have on your db server?
Also, why are you running bouncer on a separate machine? It is very "light-weight", so running it on the db server wouldn't require much additional resource, but will eliminate some network traffic that you have with the current configuration.
Regards,
Igor Neyman
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
2015-06-18 17:09 PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 17:16 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
2015-06-18 17:41 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 18:10 ` Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) ktm@rice.edu <ktm@rice.edu>
2015-06-18 19:19 ` Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
@ 2015-06-18 20:02 ` ktm@rice.edu <ktm@rice.edu>
2 siblings, 0 replies; 10+ messages in thread
From: ktm@rice.edu @ 2015-06-18 20:02 UTC (permalink / raw)
To: Sheena, Prabhjot <Prabhjot.Singh@classmates.com>; +Cc: Will Platnick <wplatnick@gmail.com>; pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Thu, Jun 18, 2015 at 07:19:13PM +0000, Sheena, Prabhjot wrote:
> Hi Ken/ Will
>
> I have checked the ulimit value and we are nowhere hitting the max 4096 that we have currently set. Is there any other explanation why we should be thinking of bumping it to like ulimit -n 50000 ( Add ulimit -n 50000 to the start of whatever you use to start pgbouncer (init script, etc..)) even though we are not reaching 4096 max value
>
> Regards
> Prabhjot Singh
>
Hi,
Try attaching to the pgbouncer with strace and see if you are getting any particular
errors. Do you have a /etc/security/limits.d directory? And if so, what is in it?
We found a nice default ulimit of 1024 for all non-root users. :(
Regards,
Ken
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
2015-06-18 17:09 PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
@ 2015-06-18 17:17 ` Will Platnick <wplatnick@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Will Platnick @ 2015-06-18 17:17 UTC (permalink / raw)
To: pgsql-performance
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.Singh@classmates.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@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
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2015-06-18 20:26 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-06-18 17:09 PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 17:16 ` ktm@rice.edu <ktm@rice.edu>
2015-06-18 17:41 ` Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 18:10 ` ktm@rice.edu <ktm@rice.edu>
2015-06-18 19:19 ` Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 19:46 ` Jerry Sievers <gsievers19@comcast.net>
2015-06-18 20:26 ` Sheena, Prabhjot <Prabhjot.Singh@classmates.com>
2015-06-18 19:59 ` Igor Neyman <ineyman@perceptron.com>
2015-06-18 20:02 ` ktm@rice.edu <ktm@rice.edu>
2015-06-18 17:17 ` Will Platnick <wplatnick@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox