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.
Problem solved, it appears php's persistent connections don't work well with PgBouncer. Just turn off persistent connections and everything will run smoothly. :)