PgBouncer and idle connections

Question

I'm trying to scale my infrastructure.

There is a Php 7.4 app which uses persistent connections.

There is a PgBouncer which the app connects to with the default parameters.

/pgbouncer.ini

[databases]
XXXXXXX

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 4040
unix_socket_dir =
user = postgres
auth_file = /etc/pgbouncer/userlist.txt
auth_type = md5
ignore_startup_parameters = extra_float_digits
logfile = /var/log/pgbouncer/pgbouncer.log
# Log settings
admin_users = postgres

And a standard PostgreSQL in a cloud environnement.

I am facing a strange problem where when I load test the app with 50 users ( I am currently testing on small servers in a dev environnement ) a lot are facing this error:

pg_query(): Query failed: ERROR: query_wait_timeout server closed the connection unexpectedly 

In facts, when I use SHOW POOLS in PgBouncer here it what it shows:

       database       |         user         | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
----------------------+----------------------+-----------+------------+-----------+---------+---------+-----------+----------+---------+------------+-----------
 XXXXXXXXXXXXXXXXXXXX | XXXXXXXXXXXXXXXXXXXX |        20 |         31 |        20 |       0 |       0 |         0 |        0 |     120 |     106966 | session
 pgbouncer            | pgbouncer            |         1 |          0 |         0 |       0 |       0 |         0 |        0 |       0 |          0 | statement

This explains the errors, because there are too much waiting clients. But here is the funny thing, at the exact same time ( multiple time in facts to be sure ) I ran the following query on the targeted PostgreSQL:

SELECT *
FROM pg_stat_activity
ORDER BY pid desc;

What I saw is a bunch of idle clients, and sometimes one / two / three of them showing as active an processing queries ... ! I expected all the 20 clients to be working to serve all the waiting PgBouncer clients ..

( And the idle ones stay idle between 10 to 20secondes before going active )

Also, it is important to mention that all the servers were at 30% CPU 20% RAM so, it seems (?) this is not a resources issue.

Any ideas of what is going wrong ? I think something is not normal.

Answer 1

Problem solved, it appears php's persistent connections don't work well with PgBouncer. Just turn off persistent connections and everything will run smoothly. :)