Database · Pool

Connection pool

Application code never sees the pool: a repository takes a connection and gives it back on its own. This page is about what happens underneath, and about the three decisions a person still makes: how many connections, what to do on failure, and how to tell when the pool is full.

Package flytachi/winter-ppaDefault 5 connections per workerModelled on HikariCP

What a connection pool is and why

The problem. A database connection is an expensive thing: a TCP handshake, encryption, authentication, and on PostgreSQL a separate process on the server side as well. Classic PHP paid for that on every request, because the process was going to die right after anyway.

A resident worker lives for weeks — and that brings the opposite trouble. A connection opened once does not stay healthy by itself: the database restarted, a firewall dropped an idle socket, the server closed it on its own timeout. The worker learns none of this and goes on treating the socket as working — until the first request that fails.

And a third thing: if a connection is opened per incoming request, their number is bounded by nothing. A thousand simultaneous requests means a thousand connection attempts, and the answer is too many connections, which takes down the whole application rather than just the spike.

The solution. A set of connections opened ahead of time, handed to coroutines for the duration of their work and returned by themselves. The pool checks them before handing them over, replaces dead ones, rotates aged ones and never opens more than allowed. Application code does not change at all: the repository is what takes the connection.

How it works

The main thing PPA gives you. It is modelled on HikariCP from the Java world: the point is not reuse as such, but that connections are kept working.

text
worker
└── pool (per configuration class)
    ├── connection 1  ← coroutine A took it for the request
    ├── connection 2  ← coroutine B
    └── connection 3    free

A coroutine takes a connection on its first database call and returns it automatically when it finishes. There is nowhere you have to release it by hand — the return is attached with defer.

Rules applied on every hand-out

Only idle connections are checked. A connection that lay unused for more than 500 ms is checked before being handed over; a dead one is discarded and replaced with a fresh one. A connection that was just in use is not checked at all.

This is a deliberate trade-off: a SELECT 1 before every query would cost an extra round trip to the server every time. Only what really was idle gets checked.

Rotation by age. A connection older than 30 minutes is replaced in advance, before the server or a firewall closes it. The moment of replacement is jittered slightly so that the whole pool does not renew at once.

Bounded waiting. poolWaitTimeout is the budget for the whole borrow, not for a single wait: waiting for a free connection, discarding dead ones and opening their replacements all come out of it. When it runs out the request fails with a PpaPoolException. A fast, comprehensible refusal is better than a request that hangs forever.

So the first request after a long pause does not fail even when the server closed every socket meanwhile: the borrow discards them one by one, opens a fresh connection and carries on — at the cost of a single reconnect.

What the pool does on return

A connection outlives the request, and a transaction lives on the connection. If a request opened a transaction and reached neither commit() nor rollBack() — an exception that went past finally, an early return, a request timeout — the connection would go back to the pool mid-transaction. The next request to receive it would write inside someone else’s transaction: its writes would be lost along with it, even though the client got a 200, and its own beginTransaction() would fail with “There is already an active transaction”.

So on return the pool checks the connection and, if a transaction is open, rolls it back. The uncommitted work is discarded, the connection comes back clean and stays in service. If the rollback fails, the connection is evicted.

The usual case costs nothing: the driver itself knows whether a transaction is open, and nobody goes to the server to ask.

Every such rollback is logged at ERROR — with the config class and the coroutine id:

text
App\Config\MainDb: connection returned to the pool with an open transaction (cid=42) — rolled back; its uncommitted work is discarded. Close every beginTransaction() with commit()/rollBack(), or use transaction().

The rollback is a safety net, not a way of working

The pool saves the next request, not the one that left the transaction behind: its data is lost. A line in the log means there is a path somewhere in the code on which the transaction is not closed. Close it on every path — try/catch with rollBack(), or transaction(), as shown in Transactions.

The safety net works only where the connection goes back to the pool. A process and a daemon hold one connection for their whole life — what happens there to an unclosed transaction is described in Transactions.

Why a rollback rather than commit(): the request did not finish its work, and publishing it on the request’s behalf means writing to the database what it never meant to write.

What happens on a failure

The pool distinguishes two fundamentally different causes of an error:

Cause How it is detected What the pool does
The connection died SQLSTATE class 08, PostgreSQL codes 57P01/02/03, MySQL 2006/2013/2055 Discards the connection — the next query gets a new one
The query was rejected A constraint violation (23xxx), syntax (42xxx), a deadlock Nothing: the server is healthy and the connection is fine

PostgreSQL took some work: PDO does not report a lost connection with code 08006. When the socket is already gone there is nowhere to take a SQLSTATE from, and the error arrives as HY000 with the generic libpq code 7 — the same one an ordinary syntax error carries. When the driver’s verdict is that uninformative, the pool checks the connection and decides by the answer.

A failed query is not retried

The pool discards the connection but does not try to run the query again — and it will not. It does not know what managed to happen: the break could have occurred after the server applied the write, and a retry would duplicate it. Retrying one statement out of an aborted transaction is meaningless anyway.

One query ends in an error and the connection is scrapped. The decision to retry is yours, at the business-logic level.

Tuning

Every database configuration has a pool — for 5 connections by default. To set your own values, implement PpaPoolConfigInterface through PpaPoolTrait:

main/MainDbConfig.php
use Flytachi\Winter\Cdo\Config\PgDbConfig;
use Flytachi\Winter\Ppa\Pool\{PpaPoolConfigInterface, PpaPoolTrait};

class MainDbConfig extends PgDbConfig implements PpaPoolConfigInterface
{
  use PpaPoolTrait;

  public int   $poolMaxConnections = 10;
  public float $poolWaitTimeout    = 5.0;

  public function setUp(): void
  {
      $this->host     = env('DB_HOST', 'localhost');
      $this->database = env('DB_NAME', 'app');
      $this->username = env('DB_USER', 'postgres');
      $this->password = env('DB_PASS', '');
  }
}
Property Default What it sets
$poolMaxConnections 5 The connection ceiling per configuration
$poolWaitTimeout 3.0 Deadline for a whole borrow: waiting, discarding dead ones, reopening
$keepaliveTime 120.0 Background checking of idle connections (0 — off)
$idleTimeout 600.0 Close connections idle for longer than N seconds (0 — never)
$minimumIdle 0 (lazy) How many connections to keep ready

The trait supplies a default for every parameter, so a configuration declares only what it changes and does not break when a new one is added to the interface.

The last three work under Swoole only and drive a background maintenance timer; at zero it is not started at all. It is on by default, and not by accident: a pool lives in one worker’s memory and is only ever examined when someone borrows from it. An application between bursts would hold its sockets indefinitely and never notice the server or a firewall dropping them — and the first request back would pay for that discovery, burying dead connections and reopening. Instead, idle connections are pinged every two minutes and handed back to the database after ten idle minutes.

The numbers follow HikariCP’s ordering rule — keepaliveTime < idleTimeout < maxLifetime — and the pool drops a keepaliveTime the connection will not live to receive. minimumIdle deliberately stays lazy: a warm floor multiplies by worker count here, unlike a pool that lives once per JVM. Details on the pool policy page.

Count the ceiling together with the workers

The limit is per worker, not per application. The server has to withstand worker count × poolMaxConnections × number of configurations.

Eight workers with a pool of 10 is 80 connections to the database from a single container. With max_connections = 100 on PostgreSQL, a second such container will not come up.

Seeing what is going on

bash
php call db pool
text
Connection pools — web workers
Main\MainDbConfig
  active 12 · idle 3 · total 15 · maximum 20 · workers 2
  saturated  1 of 2 workers                  [SATURATED]
Connection pools — processes
Main\MainDbConfig
  active 4 · idle 2 · total 6 · maximum 10 · processes 2   [OK]
open connections per config
Main\MainDbConfig      21  (web 15 + processes 6)
per source
worker#0                           Main\MainDbConfig  active=2  idle=3 total=5  max=10  pid=4101  age=0s
worker#1                           Main\MainDbConfig  active=10 idle=0 total=10 max=10  pid=4102  age=3s
process Main\Process\ReportProcess  Main\MainDbConfig  active=1  idle=1 total=2  max=5   pid=4120  age=1s
daemon Main\Daemon\Emails.0         Main\MainDbConfig  active=3  idle=1 total=4  max=5   pid=4131  age=2s

A pool lives in the memory of one OS process, so every web worker, every process and every daemon worker has its own — even when they all use one config. The output is split into “web workers” and “processes”; the “open connections per config” line shows how many connections the application holds per config (hold it against the database’s max_connections), and “per source” shows each source on its own.

What to look at is the source lines, not the overall total: a request queues for its own process’s pool, so one saturated worker means real latency even when there is plenty of room in aggregate.

The console is a separate process and cannot peer into the running server’s memory, so the workers and processes publish their statistics on a timer themselves. The interval is set by the PPA_POOL_TELEMETRY variable (seconds, 5 by default, 0 disables it). The /actuator/pools endpoint serves the same fields, but only for the worker that served the request — when the actuator is enabled and pools is in expose.

Pool load is deliberately not part of /actuator/health: database availability and pool saturation are different questions. A busy but correctly working service should not answer degraded to the check that decides whether to send it traffic.

An application without a database pays nothing

Publishing starts not when the worker starts but when the first pool is created. An application that never touches a database starts no timer, writes no records and creates no storage directory.

Without Swoole

A process serves one unit of work at a time and there is nothing to distribute: each config gets one self-maintaining connection for the life of the process. Its liveness checks and lifetime are the same, so a long-running CLI worker does not wake up holding a socket the server closed hours ago. Application code is identical either way.


PpaConnectionPool reference

PpaConnectionPool is a static facade over every pool in the process. Application code rarely needs it: the repository takes the connection, and the kernel handles closing and resetting. The methods are described here because diagnostics go through them, and because two of them do almost the same thing — and mixing those two up is expensive.

Access is static, with no dependency injection: the pool holds process-wide state, and there cannot be a second instance of it.

db()

Returns the connection of the current unit of work.

Under Swoole it borrows a connection from the pool on the first call within a coroutine, caches it in the coroutine’s context and registers a defer that returns it to the pool when the coroutine ends. Every query of one coroutine therefore runs over one connection — which is what transactions rest on.

Without Swoole there is no pool at all: the method hands out the process’s single connection.

Syntax

php
public static function db(string $configClass): CDO

Parameters

$configClass — the database configuration class, MainDbConfig::class.

Returns

A CDO already bound to the current coroutine. There is no need to release it by hand.

Errors

PpaPoolException — if the borrow never reached a live connection within poolWaitTimeout. The reason differs and the message is worth reading: every connection busy; a connection that could not be opened (the driver’s error is inside); or a window spent on dead connections — the server is reachable but not serving.

getConfigDb()

Returns the registration instance of a configuration.

For reading settings without opening a connection: the driver, the schema, the address. The instance is created once per class and reused — it is the one setUp() was called on.

Syntax

php
public static function getConfigDb(string $configClass): DbConfigInterface

Parameters

$configClass — the configuration class.

Returns

The configuration instance. No connection is opened.

showDbConfigs()

Returns every configuration registered in the process.

It is a diagnostic tool: the list shows the databases this process has already used, and each one can be polled with pingDetail(). A configuration the process has not used yet is not in it. The built-in /actuator/health does not rely on this list: it finds every configuration of the project by scanning and checks fresh instances.

Syntax

php
public static function showDbConfigs(): array

Returns

An array of configuration instances. Only the ones already used appear in it: registration is lazy.

stats()

Returns the utilisation of every pool.

The same numbers call db pool prints and /actuator/pools serves. They are counted for the current worker: each worker has its own pool, and a request sees the one that served it.

Syntax

php
public static function stats(): array

Returns

An array keyed by configuration class, each value four numbers: total (open in all), idle (free), active (handed out) and maximum (the ceiling).

Without Swoole the array is empty: there are no pools there.

reportFailure()

Reports to the pool a failure that happened on a borrowed connection.

The method decides whether the connection is to blame. A lost connection — the connection is evicted, and the next request gets a new one. A constraint violation or a syntax error — the connection is healthy and nobody touches it.

Repositories call it; you rarely have to write it in application code.

Syntax

php
public static function reportFailure(string $configClass, Throwable $error): bool

Parameters

$configClass — the configuration whose connection failed.

$error — the exception exactly as CDO or PDO threw it.

Returns

true if the error was classified as a connection loss and the connection was evicted; false if the server is healthy and the statement itself was at fault.

shutdown()

Closes every pool and connection the process owns.

Called when a worker stops. Besides the sockets it releases the housekeeping timer: a live Timer::tick holds a reference to its pool and keeps the worker’s reactor from draining.

Syntax

php
public static function shutdown(): void

closeBeforeFork()

Closes the process’s connections right before fork(), so the child has nothing to inherit.

Called in the parent. A fork copies a PDO object together with its socket, and the child cannot let an inherited object go quietly: as soon as it drops it — in reset(), or simply by exiting — the driver tells the server to close the session (Terminate on PostgreSQL, COM_QUIT on MySQL) over the socket it shares with the parent. The parent’s next query then fails with “server closed the connection unexpectedly” or “MySQL server has gone away”, even when the child never touched the database. Closing the connections first leaves the child nothing to share; both reconnect on their next query.

It covers connections outside coroutines — the only kind a forking process has: under Swoole spawn() creates a coroutine, not a fork. A connection that no longer answers is just closed, even though pdo_pgsql reports a dead connection as being in a transaction.

Syntax

php
public static function closeBeforeFork(): void

Errors

PpaPoolException when one of the connections is inside a transaction. Closing it would roll its owner’s work back, and not closing it would hand its session to the child; neither is acceptable, so the fork is refused and the transaction is left untouched.

The kernel calls this itself

Before every fork() the framework makes — spawn() without Swoole, starting a daemon worker, the application (call run) starting each of its components, and its fork of winter-sentinel — closeBeforeFork() is called automatically. You only need it by hand if you fork a process yourself.

reset()

Forgets every pool and connection without closing them.

Called in a child process straight after fork(). The difference from shutdown() is fundamental: a fork copies file descriptors, so a socket inherited from the parent is physically the same one — close it in the child and you tear down the parent’s connection. The child must simply forget them and open its own.

But even “forgetting” is safe only because there is nothing to forget: a dropped PDO object closes the session on the server in its destructor. The parent calls closeBeforeFork() first, and the child inherits no connection at all.

Syntax

php
public static function reset(): void

`shutdown()` and `reset()` are not interchangeable

Mixing them up means either tearing down the parent process’s connections (shutdown() in a child) or leaving a worker that cannot exit because its timer is still alive (reset() at shutdown).

In a Winter application there is no need to call either by hand: the kernel wires both up itself.

The kernel does this for you

The fork reset, the shutdown at worker exit, the pool’s logger, the timezone provider and the telemetry storage are all installed at boot — in Kernel::init() and on the worker events. The package fetches none of it itself: it never reaches for the framework’s globals, which is exactly what lets it be used (and tested) without the kernel.

Next