On Tue, Feb 28, 2017 at 10:00 AM, Lisandro <rostagnolisandro@gmail.com> wrote:
> Hi Steve, thanks for your help.
> Your comment made me realise that maybe the problem is my pgBouncer
> configuration, specifically default_pool_size. It took me a while to
> understand pgbouncer, and I still had some doubts when I configured it. Now
> I undesrtand better.
>
> I connect to all databases with the same user. However, I had set
> default_pool_size=10. So with more than 150 databases, it was very probable
> that postgresql reach max_connections=250 limit.
>
> I didn't have set reserve_pool_timeout or max_db_connections, but docs say
> their default values are reserve_pool_timeout=5 seconds,
> max_db_connections=unlimited.
>
> I've reviewed pgBouncer configuration and did some search. I've found this
> thread where the first person that responds gives a great explanation abount
> how pgbouncer do its maths:
> https://github.com/pgbouncer/pgbouncer/issues/174
>
> So, what I did for now was to set this in pgbouncer configuration:
> default_pool_size=1
> min_pool_size = 0
> server_idle_timeout = 30
> reserve_pool_size = 10
> reserve_pool_timeout = 5
> max_db_connections = 5
>
>
> I'll keep monitoring with this setup, but I can already tell you that the
> total number of connections in postgres has significantly reduced (from ~100
> to ~60). So I guess the problem was a bad setup of pgbouncer.
Those seem pretty reasonable. Note that if you need to you CAN set
default pool size and other settings per database etc. So if you have
a more active db that needs more connections etc you can adjust these
as needed per db and it will override the default overall settings.
As for monitoring I'd suggest setting up Nagios or Zabbix. They both
can give you some nice pretty graphs of what your system looks like
over time so you can do simple trend analysis and alerting to avoid
these problems in the future.