Thread: [GENERAL] pgbouncer increase pool_size, reload does not work

hello!

i have a question for pgbouncer in case someone has faced this and there is
a solution available.

i have a db with pgbouncer where the pool_size is set to 50 (i ve noticed
this using both session/transaction modes). Sometimes there are waiting
sessions b/c more than 50 want to execute sth at once and usually each query
is not so fast. So let's say i want to increase the pool_size from 50 to 70.
I change the config file and then login to pgbouncer where i run a
'reload;'. Now when i check the 'show config' i see the new value in the
'default_pool_size' parameter.

BUT

the problem is that the amount of servers is still 50 and does not go up to
70. At the same time there are clients in a 'waiting' state. If i do a
restart then it will work and the servers will be able to go up to 70.

I ve tried these in different databases and it doesnt happen all the time
but it does happen pretty often.
Has anyone else seen this? if so, is there a solution (except restarting) or
explanation?


thank you in advance



--
View this message in context:
http://postgresql.nabble.com/pgbouncer-increase-pool-size-reload-does-not-work-tp5942273.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Thu, 2 Feb 2017 04:27:45 -0700 (MST)
alexanderfelipewo <alexanderfelipewo@gmail.com> wrote:

> hello!
>
> i have a question for pgbouncer in case someone has faced this and there is
> a solution available.
>
> i have a db with pgbouncer where the pool_size is set to 50 (i ve noticed
> this using both session/transaction modes). Sometimes there are waiting
> sessions b/c more than 50 want to execute sth at once and usually each query
> is not so fast. So let's say i want to increase the pool_size from 50 to 70.
> I change the config file and then login to pgbouncer where i run a
> 'reload;'. Now when i check the 'show config' i see the new value in the
> 'default_pool_size' parameter.
>
> BUT
>
> the problem is that the amount of servers is still 50 and does not go up to
> 70. At the same time there are clients in a 'waiting' state. If i do a
> restart then it will work and the servers will be able to go up to 70.
>
> I ve tried these in different databases and it doesnt happen all the time
> but it does happen pretty often.
> Has anyone else seen this? if so, is there a solution (except restarting) or
> explanation?

That's because internally, it seems each pool (pair of role/db) is set up when
it is created, eg. during the very first connexion.

Even if the default pool_size can be changed with a simple reload,
you will have to wait for your pool to be fully deallocated before it
can be recreated with the new pool_size value.

I didn't dig too much in the code, maybe a core hacker of pgbouncer can provide
some better details and tips about this.

On 02/02/2017 03:27 AM, alexanderfelipewo wrote:
> hello!
>
> i have a question for pgbouncer in case someone has faced this and there is
> a solution available.
>
> i have a db with pgbouncer where the pool_size is set to 50 (i ve noticed
> this using both session/transaction modes). Sometimes there are waiting
> sessions b/c more than 50 want to execute sth at once and usually each query
> is not so fast. So let's say i want to increase the pool_size from 50 to 70.
> I change the config file and then login to pgbouncer where i run a
> 'reload;'. Now when i check the 'show config' i see the new value in the
> 'default_pool_size' parameter.
>
> BUT
>
> the problem is that the amount of servers is still 50 and does not go up to
> 70. At the same time there are clients in a 'waiting' state. If i do a
> restart then it will work and the servers will be able to go up to 70.
>
> I ve tried these in different databases and it doesnt happen all the time
> but it does happen pretty often.
> Has anyone else seen this? if so, is there a solution (except restarting) or
> explanation?

Would this work?:

https://pgbouncer.github.io/faq.html#how-to-upgrade-pgbouncer-without-dropping-connections

>
>
> thank you in advance
>
>
>
> --
> View this message in context:
http://postgresql.nabble.com/pgbouncer-increase-pool-size-reload-does-not-work-tp5942273.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 2 Feb 2017 16:12:06 -0800
Adrian Klaver <adrian.klaver@aklaver.com> wrote:

> On 02/02/2017 03:27 AM, alexanderfelipewo wrote:
> > hello!
> >
> > i have a question for pgbouncer in case someone has faced this and there is
> > a solution available.
> >
> > i have a db with pgbouncer where the pool_size is set to 50 (i ve noticed
> > this using both session/transaction modes). Sometimes there are waiting
> > sessions b/c more than 50 want to execute sth at once and usually each query
> > is not so fast. So let's say i want to increase the pool_size from 50 to 70.
> > I change the config file and then login to pgbouncer where i run a
> > 'reload;'. Now when i check the 'show config' i see the new value in the
> > 'default_pool_size' parameter.
> >
> > BUT
> >
> > the problem is that the amount of servers is still 50 and does not go up to
> > 70. At the same time there are clients in a 'waiting' state. If i do a
> > restart then it will work and the servers will be able to go up to 70.
> >
> > I ve tried these in different databases and it doesnt happen all the time
> > but it does happen pretty often.
> > Has anyone else seen this? if so, is there a solution (except restarting) or
> > explanation?
>
> Would this work?:
>
> https://pgbouncer.github.io/faq.html#how-to-upgrade-pgbouncer-without-dropping-connections

Maybe, but I couldn't give it a try as my pgbouncer is managed by Systemd.

unfortunately i cannot try the restart with -R either but i ll check the
source code.
thank you all



--
View this message in context:
http://postgresql.nabble.com/pgbouncer-increase-pool-size-reload-does-not-work-tp5942273p5942711.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.