Does anybody have experience of configuring Doctrine ORM against a Postgres DB with PGBouncer connection pooling? We use a Digital Ocean managed Postgres database, and are permitted 22 concurrent DB connections.
Our app will fail to connect if we have a lot of traffic which causes more than 22 simultaneous HTTP requests (each of which require a DB connection). We sometimes see errors such as FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute.
With our Symfony app, Doctrine will create a new DB connection when booting the app for a request, and then during kernel terminate the DB connection will be closed, therefore freeing it up for other requests. It's 1 DB connection per web request.
To mitigate this, we've enabled Digital Ocean's Postgres connection pooling, which multiplexes up to 5000 DB connections to a smaller set of real connections to the Postgres instance - this uses PGBouncer for the connection pooling. However, with PGBouncer in "transaction" mode, we started seeing errors such as ERROR: current transaction is aborted, commands ignored until end of transaction block .
I think for PHP applications, "transaction" mode is not appropriate given the app is booted each time a request is received.
We then switched PGBouncer to run in "session" mode, but this is resulting in warnings about timeouts closing PHP sessions (session data is also stored in Postgres) such as PHP Warning: Uncaught PDOException: SQLSTATE[HY000]: General error: 7 FATAL: query_wait_timeout\nSSL connection has been closed unexpectedly in /app/vendor/symfony/http-foundation/Session/Storage/Handler/PdoSessionHandler.php:564.
For now, we have removed connection pooling, but are again exposed to the small number of physical DB connections permitted. I can't believe this is not more of a common issue but Google is not revealing much beyond what I've described about switching PGBouncer from "transaction" mode to "session" mode.
Does anybody have experience of configuring Doctrine ORM against a Postgres DB with PGBouncer connection pooling? We use a Digital Ocean managed Postgres database, and are permitted 22 concurrent DB connections.
Our app will fail to connect if we have a lot of traffic which causes more than 22 simultaneous HTTP requests (each of which require a DB connection). We sometimes see errors such as FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute.
With our Symfony app, Doctrine will create a new DB connection when booting the app for a request, and then during kernel terminate the DB connection will be closed, therefore freeing it up for other requests. It's 1 DB connection per web request.
To mitigate this, we've enabled Digital Ocean's Postgres connection pooling, which multiplexes up to 5000 DB connections to a smaller set of real connections to the Postgres instance - this uses PGBouncer for the connection pooling. However, with PGBouncer in "transaction" mode, we started seeing errors such as ERROR: current transaction is aborted, commands ignored until end of transaction block .
I think for PHP applications, "transaction" mode is not appropriate given the app is booted each time a request is received.
We then switched PGBouncer to run in "session" mode, but this is resulting in warnings about timeouts closing PHP sessions (session data is also stored in Postgres) such as PHP Warning: Uncaught PDOException: SQLSTATE[HY000]: General error: 7 FATAL: query_wait_timeout\nSSL connection has been closed unexpectedly in /app/vendor/symfony/http-foundation/Session/Storage/Handler/PdoSessionHandler.php:564.
For now, we have removed connection pooling, but are again exposed to the small number of physical DB connections permitted. I can't believe this is not more of a common issue but Google is not revealing much beyond what I've described about switching PGBouncer from "transaction" mode to "session" mode.
Share Improve this question asked Jan 20 at 11:01 Benr77Benr77 2053 silver badges11 bronze badges1 Answer
Reset to default 0What you describe is intentional (by default) for the PdoSessionHandler. The database transaction is kept open (and therefore the connection) until the request as finished to implement the session locking.
This is not compatible with PGBouncer as you need real connections (database transactions) in this mode.
Nevertheless you can configure the PdoSessionHandler (symfony.com) and set lock_mode (the strategy for locking the database to avoid race conditions) to:
LOCK_NONE(no locking)LOCK_ADVISORY(application-level locking)LOCK_TRANSACTIONAL(row-level locking, default)
I'd suggest you experiment with these settings and PGBouncer if using a different session store / database is not an option.