Pooler between servers and database

Place a Connection Pooler in front of the Database and point every App Server at it.

Move up when#

The Database card reads that it is refusing queries for too many connections while its CPU is not full.
Adding an App Server, or deploying, makes the errors worse.

The Design#

Drag to pan. Hold Ctrl or Cmd and scroll to zoom.

At 4.00k Users the Simulation solves this Design to Apdex 0.77, p99 293 ms, 22.9% errors and $1.52/s Burn.

Where	Setting	Value	Why
App Server	Runtime	php_fpm	PHP-FPM keeps a fixed pool of workers, and every worker holds its own Database connection.
App Server	Workers	60	A big pool is common. Two of them ask the Database for more connections than it allows.
App Server	Instance size	small	Small, so CPU is not what fails.
App Server	Runtime	php_fpm	The same stack as the first server.
App Server	Workers	60	The same pool size.
App Server	Instance size	small	Small, so CPU is not what fails.
Database	Instance size	small	Small is plenty of CPU. Its connection limit is what runs out.

Research this Design needs: Load balancing, PHP-FPM.

Drag to pan. Hold Ctrl or Cmd and scroll to zoom.

At 4.00k Users the Simulation solves this Design to Apdex 1.00, p99 299 ms, 0.0% errors and $1.64/s Burn.

Where	Setting	Value	Why
App Server	Runtime	php_fpm	Unchanged from the Design before.
App Server	Workers	60	Unchanged. The pool is not what shrinks.
App Server	Instance size	small	Unchanged.
App Server	Runtime	php_fpm	Unchanged.
App Server	Workers	60	Unchanged.
App Server	Instance size	small	Unchanged.
Connection Pooler	Mode	transaction	Transaction mode hands a Database connection to a client only while a transaction runs, so many workers share a few connections. Code that keeps session state must stop doing it.
Database	Instance size	small	Unchanged. It now sees only the pooler.

Research this Design needs: Load balancing, Connection pooling, PHP-FPM.

Both pictures run the same servers. In the first, each PHP-FPM worker holds its own connection, so two servers ask for more than the Database allows and requests are refused. In the second, the servers connect to the pooler, which holds a few connections to the Database.

Why it works#

Postgres forks a process per connection, so its limit arrives early. The pooler turns many short client connections into a few long server ones. The Database card in the second picture shows a small, steady connection count, and the refused requests are gone.

What it costs#

The pooler adds a small hop and one more Node that can fail. Transaction mode breaks code that keeps session state, such as prepared statements or session-level settings, so a share of requests crash until your Team finishes the Drop session state task. Statement mode cannot run a transaction that spans statements, so checkouts fail. Session mode breaks nothing and helps little.

In Quorum#

Research Connection pooling to unlock the Connection Pooler. The Connection Limit Sandbox shows the failure, and Connection exhaustion is the incident. Connection pooling covers the pattern. The next rung is DNS in front.