Horton

Thread

  1. Pgbouncer discard all

    Nicola Contu <nicola.contu@gmail.com> — 2018-10-10T15:03:54Z

    Hello,
we are running pgbouncer 1.9.1 connected to postgres 10.5

Sometimes we are seeing a lot of waiting connections with this query :

DISCARD ALL

This is our pgbouncer config :

[databases]
dev = host=10.10.10.1 port=5432 dbname=dev auth_user=pgbouncer pool_size=120

[pgbouncer]
listen_port = 6543
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/users.txt
auth_query = select uname,phash from user_lookup($1)
logfile = /var/log/pgbouncer.log
pidfile = /home/postgres/pgbouncer.pid
admin_users = admin
user = postgres
max_db_connections = 220
log_connections = 0
log_disconnections = 0


Do you think this can depend on the server_idle_timeout default config
value?

Thanks a lot,
Nicola

  2. Re: Pgbouncer discard all

    Fabio Pardi <f.pardi@portavita.eu> — 2018-10-10T15:31:00Z

    Hi Nicola,

I am of the impression that the problem of having waiting clients does not depend from server_idle_timeout

How is the load on both ends of pgbouncer? High? Low? No activity? A lot of content switch perhaps?

I think that a pool_size of 120 is very high, unless you have an XXL database server. Too high numbers will bring lower performances.

Also i think it would be of interest to know the values you are using for:

default_pool_size  min_pool_size and reserve_pool_size

and of main importance is to actually know what are the active connections doing. Are those all active? Did perhaps somebody forgot to close the connection to the db in your application and they are only waiting to be timed out?

About 'DISCARD ALL', that is the default 'server_reset_query'.

regards,

fabio pardi


On 10/10/18 17:03, Nicola Contu wrote:
> Hello,
> we are running pgbouncer 1.9.1 connected to postgres 10.5
>
> Sometimes we are seeing a lot of waiting connections with this query :
>
> DISCARD ALL
>
> This is our pgbouncer config :
>
> [databases]
> dev = host=10.10.10.1 port=5432 dbname=dev auth_user=pgbouncer pool_size=120
>
> [pgbouncer]
> listen_port = 6543
> listen_addr = *
> auth_type = md5
> auth_file = /etc/pgbouncer/users.txt
> auth_query = select uname,phash from user_lookup($1)
> logfile = /var/log/pgbouncer.log
> pidfile = /home/postgres/pgbouncer.pid
> admin_users = admin
> user = postgres
> max_db_connections = 220
> log_connections = 0
> log_disconnections = 0
>
>
> Do you think this can depend on the server_idle_timeout default config value?
>
> Thanks a lot,
> Nicola

  3. Re: Pgbouncer discard all

    Nicola Contu <nicola.contu@gmail.com> — 2018-10-16T12:12:06Z

    Hello,
is this normal? can anyone help?

Thanks a lot for your help in advance.

Nicola

Il giorno mer 10 ott 2018 alle ore 17:03 Nicola Contu <
nicola.contu@gmail.com> ha scritto:

> Hello,
> we are running pgbouncer 1.9.1 connected to postgres 10.5
>
> Sometimes we are seeing a lot of waiting connections with this query :
>
> DISCARD ALL
>
> This is our pgbouncer config :
>
> [databases]
> dev = host=10.10.10.1 port=5432 dbname=dev auth_user=pgbouncer
> pool_size=120
>
> [pgbouncer]
> listen_port = 6543
> listen_addr = *
> auth_type = md5
> auth_file = /etc/pgbouncer/users.txt
> auth_query = select uname,phash from user_lookup($1)
> logfile = /var/log/pgbouncer.log
> pidfile = /home/postgres/pgbouncer.pid
> admin_users = admin
> user = postgres
> max_db_connections = 220
> log_connections = 0
> log_disconnections = 0
>
>
> Do you think this can depend on the server_idle_timeout default config
> value?
>
> Thanks a lot,
> Nicola
>

  4. RE: Pgbouncer discard all

    Scot Kreienkamp <scot.kreienkamp@la-z-boy.com> — 2018-10-16T12:59:10Z

    Are you sure they’re actually waiting?  Don’t forget 10.5 will show the last query executed even if the connection is idle.  I believe discard all would be the last command the pgbouncer would send to the database when the client is done as it resets the connection for the next client.  So what you’re describing would seem to be expected behavior.

Try this to see if the queries are actually waiting:

select * from pg_stat_activity where wait_event_type is not null or wait_event is not null;




Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: Scot.Kreienkamp@la-z-boy.com
From: Nicola Contu [mailto:nicola.contu@gmail.com]
Sent: Tuesday, October 16, 2018 8:12 AM
To: pgsql-general@lists.postgresql.org
Cc: Alessandro Aste <Alessandro.aste@gtt.net>
Subject: Re: Pgbouncer discard all

Hello,
is this normal? can anyone help?

Thanks a lot for your help in advance.

Nicola

Il giorno mer 10 ott 2018 alle ore 17:03 Nicola Contu <nicola.contu@gmail.com<mailto:nicola.contu@gmail.com>> ha scritto:
Hello,
we are running pgbouncer 1.9.1 connected to postgres 10.5

Sometimes we are seeing a lot of waiting connections with this query :

DISCARD ALL

This is our pgbouncer config :

[databases]
dev = host=10.10.10.1 port=5432 dbname=dev auth_user=pgbouncer pool_size=120

[pgbouncer]
listen_port = 6543
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/users.txt
auth_query = select uname,phash from user_lookup($1)
logfile = /var/log/pgbouncer.log
pidfile = /home/postgres/pgbouncer.pid
admin_users = admin
user = postgres
max_db_connections = 220
log_connections = 0
log_disconnections = 0


Do you think this can depend on the server_idle_timeout default config value?

Thanks a lot,
Nicola

This message is intended only for the individual or entity to which it is addressed.  It may contain privileged, confidential information which is exempt from disclosure under applicable laws.  If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information.  If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.

  5. Re: Pgbouncer discard all

    Martin Marques <martin.marques@2ndquadrant.com> — 2018-10-16T14:22:04Z

    El 16/10/18 a las 09:59, Scot Kreienkamp escribió:
> Are you sure they’re actually waiting?  Don’t forget 10.5 will show the
> last query executed even if the connection is idle.  I believe discard
> all would be the last command the pgbouncer would send to the database
> when the client is done as it resets the connection for the next
> client.  So what you’re describing would seem to be expected behavior. 

He might have been referring to client waiting. That is visible in the
pgbouncer pseudo-database

OTOH if the the waiting is seen in pg_stat_activity, then pgbouncer has
nothing to do. The connection has already been assigned to the client
and the waiting is happening on the database server, not the pooler.

Regards,

-- 
Martín Marqués                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

  6. Re: Pgbouncer discard all

    Nicola Contu <nicola.contu@gmail.com> — 2018-10-16T15:12:24Z

     2492534808 | dev    |   7355 | 1833427130 | pgbouncer       |
           | 10.151.2.145   |                 |       60570 | 2018-10-16
14:13:05.151015+00 |                               | 2018-10-16
15:10:40.309993+00 | 2018-10-16 15:10:40.310038+00 | Client
  | ClientRead          | idle   |             |              | DISCARD ALL

They are idle actually.
Will they count as client connection on the total amount set on the
postgres.conf?

Il giorno mar 16 ott 2018 alle ore 16:22 Martín Marqués <
martin.marques@2ndquadrant.com> ha scritto:

> El 16/10/18 a las 09:59, Scot Kreienkamp escribió:
> > Are you sure they’re actually waiting?  Don’t forget 10.5 will show the
> > last query executed even if the connection is idle.  I believe discard
> > all would be the last command the pgbouncer would send to the database
> > when the client is done as it resets the connection for the next
> > client.  So what you’re describing would seem to be expected behavior.
>
> He might have been referring to client waiting. That is visible in the
> pgbouncer pseudo-database
>
> OTOH if the the waiting is seen in pg_stat_activity, then pgbouncer has
> nothing to do. The connection has already been assigned to the client
> and the waiting is happening on the database server, not the pooler.
>
> Regards,
>
> --
> Martín Marqués                http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

  7. Re: Pgbouncer discard all

    Martin Marques <martin.marques@2ndquadrant.com> — 2018-10-16T15:33:56Z

    El 16/10/18 a las 12:12, Nicola Contu escribió:
>  2492534808 | dev    |   7355 | 1833427130 | pgbouncer       |         
>                | 10.151.2.145   |                 |       60570 |
> 2018-10-16 14:13:05.151015+00 |                               |
> 2018-10-16 15:10:40.309993+00 | 2018-10-16 15:10:40.310038+00 | Client
>   | ClientRead          | idle   |             |              | DISCARD ALL
> 
> They are idle actually.
> Will they count as client connection on the total amount set on the
> postgres.conf?

Yes, but that's the idea of having a pooler in between the app and the
server. Whenever the app needs a new connection it will reuse one from
the pool, instead of having to open a new connection.

pgbouncer will eventually close the connection if it stays in the pool
for longer than server_lifetime or server_idle_timeout.

Regards,

-- 
Martín Marqués                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

  8. RE: Pgbouncer discard all

    Scot Kreienkamp <scot.kreienkamp@la-z-boy.com> — 2018-10-16T15:35:30Z

    If they are visible in pg_stat_activity then yes.  Likewise, if they are only visible in pgbouncer, then no.

The last query being discard all means that PGBouncer has returned the connection to the pool to make it available to the next client that needs it.  So what you’re seeing sounds to me like expected behavior.


Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: Scot.Kreienkamp@la-z-boy.com
From: Nicola Contu [mailto:nicola.contu@gmail.com]
Sent: Tuesday, October 16, 2018 11:12 AM
To: martin.marques@2ndquadrant.com
Cc: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>; pgsql-general@lists.postgresql.org; Alessandro Aste <Alessandro.aste@gtt.net>
Subject: Re: Pgbouncer discard all

 2492534808 | dev    |   7355 | 1833427130 | pgbouncer       |                         | 10.151.2.145   |                 |       60570 | 2018-10-16 14:13:05.151015+00 |                               | 2018-10-16 15:10:40.309993+00 | 2018-10-16 15:10:40.310038+00 | Client
  | ClientRead          | idle   |             |              | DISCARD ALL

They are idle actually.
Will they count as client connection on the total amount set on the postgres.conf?

Il giorno mar 16 ott 2018 alle ore 16:22 Martín Marqués <martin.marques@2ndquadrant.com<mailto:martin.marques@2ndquadrant.com>> ha scritto:
El 16/10/18 a las 09:59, Scot Kreienkamp escribió:
> Are you sure they’re actually waiting?  Don’t forget 10.5 will show the
> last query executed even if the connection is idle.  I believe discard
> all would be the last command the pgbouncer would send to the database
> when the client is done as it resets the connection for the next
> client.  So what you’re describing would seem to be expected behavior.

He might have been referring to client waiting. That is visible in the
pgbouncer pseudo-database

OTOH if the the waiting is seen in pg_stat_activity, then pgbouncer has
nothing to do. The connection has already been assigned to the client
and the waiting is happening on the database server, not the pooler.

Regards,

--
Martín Marqués                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

This message is intended only for the individual or entity to which it is addressed.  It may contain privileged, confidential information which is exempt from disclosure under applicable laws.  If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information.  If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.

  9. Re: Pgbouncer discard all

    Fabio Pardi <f.pardi@portavita.eu> — 2018-10-18T14:15:05Z

    @Nicola, while I'm glad you got further help.. I was asking myself it you maybe missed my answer to your question? (mail from 10/10/2018 at 15:31)

https://www.postgresql.org/message-id/a22f8385-2a49-30a7-b1d2-fc743c2f3245%40portavita.eu

regards,

fabio pardi



On 16/10/2018 14:12, Nicola Contu wrote:
> Hello,
> is this normal? can anyone help?
>
> Thanks a lot for your help in advance.
>
> Nicola
>
> Il giorno mer 10 ott 2018 alle ore 17:03 Nicola Contu <nicola.contu@gmail.com <mailto:nicola.contu@gmail.com>> ha scritto:
>
>     Hello,
>     we are running pgbouncer 1.9.1 connected to postgres 10.5
>
>     Sometimes we are seeing a lot of waiting connections with this query :
>
>     DISCARD ALL
>
>     This is our pgbouncer config :
>
>     [databases]
>     dev = host=10.10.10.1 port=5432 dbname=dev auth_user=pgbouncer pool_size=120
>
>     [pgbouncer]
>     listen_port = 6543
>     listen_addr = *
>     auth_type = md5
>     auth_file = /etc/pgbouncer/users.txt
>     auth_query = select uname,phash from user_lookup($1)
>     logfile = /var/log/pgbouncer.log
>     pidfile = /home/postgres/pgbouncer.pid
>     admin_users = admin
>     user = postgres
>     max_db_connections = 220
>     log_connections = 0
>     log_disconnections = 0
>
>
>     Do you think this can depend on the server_idle_timeout default config value?
>
>     Thanks a lot,
>     Nicola
>