Connections, Replicas, and Pooling

Connections, Replicas, and Pooling#

Introduction#

This guide covers read/write split behavior, replica selection strategies, and pooled connection lifecycle.

Connection Access#

$conn = DB::connection();       // default
$fresh = DB::freshConnection(); // uncached

Use freshConnection() when you explicitly need a new underlying PDO instance and do not want shared connection reuse.

Connection Runtime Methods#

Runtime controls are available on Connection instances:

  • lifecycle hooks: beforeConnect(), afterConnect(), beforeReconnect(), afterReconnect(), onConnectionFailure()

  • query comment context: setQueryCommentContext(), mergeQueryCommentContext(), clearQueryCommentContext(), getQueryCommentContext()

  • execution helpers: setFetchMode(), withoutQueryEvents(), stream(), yieldRows(), readOnlyTransaction()

Read/Write Split#

DB::addConnection([
    'driver' => 'mysql',
    'database' => 'app_db',
    'username' => 'app_user',
    'password' => 'secret',
    'sticky' => true,
    'read_strategy' => 'round_robin',
    'read_latency_ttl' => 15,
    'read_probe_sample_size' => 0,
    'read_session_read_only' => false,
    'read' => [
        ['host' => 'replica1.internal', 'weight' => 1],
        ['host' => 'replica2.internal', 'weight' => 3],
    ],
    'write' => [
        ['host' => 'primary.internal'],
    ],
], 'main');

In this setup:

  • Writes use the write channel.

  • Reads use replicas when available.

  • With sticky=true, reads switch to write PDO after a write on that connection to reduce read-after-write inconsistency windows.

  • For SQLite read replicas, DBLayer applies PRAGMA query_only = ON on read handles to prevent accidental writes through read PDO.

Note

Sticky read-after-write behavior is request-scope consistency behavior. It is useful for immediate read-back of writes, but increases read load on the primary/write connection.

Read Strategies#

  • random

  • round_robin

  • least_latency

  • weighted

Replica telemetry:

$info = DB::connection('main')->getReadReplicaInfo();

Strategy Behavior Summary#

  • random: random healthy replica selection.

  • round_robin: deterministic rotation.

  • least_latency: probe healthy replicas and choose fastest response. Winner is cached for read_latency_ttl seconds. read_probe_sample_size can bound first-pass probes for large pools.

  • weighted: weighted random using per-replica weight.

When a replica fails, DBLayer applies cooldown-based suppression before retry.

Using Multiple Database Connections#

Use named connections for operational separation:

DB::addConnection([...], 'primary');
DB::addConnection([...], 'reporting');

$users = DB::table('users', 'primary')->get();
$events = DB::table('user_events', 'reporting')->get();

Pooling#

DB::poolManager([
    'max_connections' => 10,
    'idle_timeout' => 60,
    'max_lifetime' => 3600,
    'health_check_interval' => 30,
]);

DB::withPooledConnection(function ($pooled) {
    return $pooled->select('select 1');
}, 'main');

Operational Notes#

  • max_connections bounds total open pooled connections.

  • idle_timeout evicts idle connections.

  • max_lifetime rotates old connections.

  • health_check_interval controls probe cadence.

Use pool stats to tune these settings under real workload:

$stats = DB::pool()->getStats();

Warning

Oversizing max_connections can overwhelm downstream databases. Tune against real connection limits and query concurrency.

Pool Limitations and Fit#

  • PHP-FPM request lifecycles usually do not benefit much from pooling.

  • Long-running workers and CLI daemons benefit more from pooled reuse.

  • Pool health checks are interval-based and batched, not full scans on every acquire path.