71. How would you investigate database connection exhaustion in PHP-FPM?
Explain how PHP-FPM worker counts, persistent connections, connection pools or proxies, long transactions, leaked work, timeouts, and database limits interact, and define a measurement-led remediation plan.
I would correlate PHP-FPM workers and queues with database session states, transaction age, query latency, locks, connection churn, and configured limits. Then I would fix slow or long-held work, persistent-connection misuse, retries, and capacity mismatches before changing limits or introducing a connection proxy.
This question asks how I would find why a PHP service has used all the available paths to its data store. When that happens, new requests may wait, fail, or become very slow. I need to compare how many PHP tasks can run at once with how many data-store sessions are allowed. I must also find tasks that keep a session too long, fail to finish work, or repeatedly open new sessions. The goal is to use measurements to locate the real cause, make a safe correction, and prevent the same failure from returning.
Useful clarifying questions:
- Which database engine and version are in use?
- Is PHP-FPM running on one host or across multiple replicas?
- Which PHP-FPM process-management mode is configured, and what are the worker limits?
- Are PDO persistent connections enabled?
- Is there a database proxy or pooler between PHP and the database?
- Which exact errors occur: too many connections, connection timeout, refused connection, or PHP-FPM queue growth?
- Which background workers, scheduled jobs, administration tools, or other services share the database?
I would start with a practical rule: I would not immediately raise the database connection limit. First, I would determine which clients own the connections, what those connections are doing, and why they remain occupied.
I would collect the exact application exception, SQLSTATE or driver error, proxy error, and database log entry. A request can fail to obtain a connection because the database limit is reached, a proxy pool is full, the connection-acquisition timeout expires, networking is unhealthy, authentication fails, or the database is unavailable. These cases need different remedies.
I would place all PHP-FPM, application, proxy, and database events on the same timeline. This prevents me from treating a slow-query incident or network failure as a connection-limit problem.
PHP-FPM serves requests through worker processes. One worker handles one request at a time. A request may use no database connection, one connection, or more than one connection if the application talks to multiple databases or creates duplicate handles.
I would inventory:
- Every PHP-FPM pool and application replica.
- The configured and observed worker counts.
- Queue consumers and long-running CLI processes.
- Scheduled jobs, migrations, reporting tools, monitoring, and administration clients.
- Read and write database endpoints if they are separate.
- Connection-proxy client limits and backend-pool limits.
- The database connection limit and any connections reserved for administrators or system processes.
A useful planning model is:
safe application budget = database connection limit - reserved operational capacity - other clients - safety margin
The PHP application must be designed against the safe application budget, not the database's full advertised maximum. I would also reserve enough access for diagnosis and recovery during an incident.
On PHP-FPM, I would measure:
- Active, idle, and total workers.
- Whether the pool repeatedly reaches pm.max_children.
- Listen-queue length and rejected or delayed requests.
- Request throughput, duration, and error rate.
- Slow-request traces.
- Worker restarts and worker memory usage.
- The number of application replicas over time.
On the database or proxy, I would measure:
- Current and peak client connections.
- Current and peak backend database sessions.
- Connection acquisition time and acquisition failures.
- New connections and disconnections per second.
- Sessions grouped by application name, user, host, database, and state.
- Active query age and transaction age.
- Idle sessions and sessions idle while a transaction remains open.
- Lock waits, blocking sessions, slow queries, CPU, memory, and storage latency.
The categories matter. A high number of active sessions may represent real concurrency, blocked work, or slow SQL. A high number of ordinary idle sessions may be expected when persistent connections or a proxy pool are used. An idle session with an open transaction is more serious because it can retain locks, snapshots, old row versions, or other transactional resources depending on the database.
PHP-FPM workers normally survive across many requests. A non-persistent PDO connection is associated with its PDO object and is normally closed when PHP destroys the object or completes request cleanup, provided no remaining reference keeps it alive.
A PDO persistent connection can survive request completion and be reused by a later request in the same PHP-FPM worker process. It is not one shared PHP-level pool used by every worker. Therefore, many workers across many replicas can retain many separate persistent database sessions.
Persistent connections may reduce repeated connection setup cost, especially when connection establishment is expensive. However, they can also:
- Keep a large number of idle sessions open.
- Make retained session count scale with the number of worker processes.
- Preserve connection-level state unless the driver and application reset it correctly.
- Interact badly with session variables, temporary objects, advisory locks, or unfinished transactions.
- Provide little benefit when a database proxy already manages backend connections efficiently.
I would inspect the actual PDO options, framework configuration, and driver behavior rather than assume persistence is enabled or safe.
Connection exhaustion does not require a permanent leak. If each request holds a connection for longer, the same traffic creates more simultaneous connection demand.
I would trace requests that:
- Connect much earlier than the first database operation.
- Keep a connection while calling an external API.
- Perform file, network, sleep, or heavy computation inside a transaction.
- Stream or iterate through a large result while doing unrelated work.
- Wait on a database lock.
- Execute slow or badly planned queries.
- Produce an N+1 query pattern.
- Retry immediately and repeatedly after transient failures.
- Open multiple handles to the same database within one request.
- Wait for user interaction or another service while a transaction remains open.
The safest transaction scope is usually narrow: acquire the connection when needed, start the transaction immediately before the related database work, execute only the required statements, commit promptly, and roll back on every failure path.
In request-based PHP-FPM code, request cleanup normally releases non-persistent PDO objects. Therefore, a rising connection count is often caused by long requests, long transactions, blocked queries, persistent connections, additional replicas, or high connection churn rather than a classic permanent memory leak.
I would still inspect for incorrect lifecycle management, including:
- Static or global registries that create and retain multiple connection objects.
- Dependency-injection configuration that constructs duplicate connection services.
- Reconnection logic that creates a replacement without releasing the prior handle.
- Exception paths that leave a transaction open until request shutdown.
- Long-running CLI consumers that never close, refresh, or validate stale connections.
- ORM or framework workers that retain database state between jobs.
- Separate read and write connections created even when only one is needed.
Setting pm.max_requests can recycle PHP-FPM workers and limit the lifetime of per-process state or gradual memory growth. It is a containment measure, not proof that the connection-lifecycle defect has been fixed.
A slow query increases the time a request occupies its connection. A blocked query can hold both a worker and a connection while making no progress. A long transaction may also hold locks or prevent cleanup of old row versions.
I would identify:
- The oldest active transactions.
- Sessions idle inside a transaction.
- Blocking and blocked sessions.
- Queries with increased execution time or rows examined.
- Query-plan changes after a deployment or data-growth event.
- Missing, unused, or inappropriate indexes.
- Large scans, sorts, temporary results, and lock-heavy updates.
- Application changes that widened transaction boundaries.
I would use the database's native activity, lock, slow-query, and query-plan tools. Exact commands depend on the database engine, so I would not present MySQL, PostgreSQL, or another engine's session-state behavior as universal.
Improving a query or shortening a transaction can reduce occupied-connection time and therefore reduce required concurrency without increasing connection capacity.
Retries can turn a partial slowdown into connection exhaustion. If failed requests retry immediately, each layer may create more demand while the database is already unhealthy.
I would verify that retries are:
- Limited to genuinely transient and safe-to-retry failures.
- Bounded by a small maximum attempt count.
- Delayed with backoff and jitter.
- Constrained by an overall request deadline.
- Safe for the transaction and operation's idempotency rules.
I would review timeouts at every layer:
- Database connection or proxy-acquisition timeout.
- Statement or query timeout where supported.
- Lock-wait timeout.
- Idle-transaction timeout where supported.
- PHP execution and application request deadlines.
- PHP-FPM request termination settings.
- Web-server, load-balancer, and reverse-proxy timeouts.
- Database-proxy client, queue, idle, and backend timeouts.
The timeouts must be coordinated. A client-facing request should not time out while PHP continues holding a database connection for substantially longer. However, timeouts that are too aggressive can cancel valid work and cause retry storms, so I would choose them from observed latency and service objectives.
pm.max_children limits simultaneous PHP-FPM requests in a pool. Memory capacity is a major input because each worker is a separate process with its own memory footprint. Database capacity is another input because active workers may create database demand.
I would not assume one worker always equals one database connection. Instead, I would measure:
- The percentage of requests that use the database.
- Connections used per database-using request.
- Average and high-percentile connection hold time.
- Peak active workers and peak database sessions.
- The effect of background jobs and autoscaling.
I would set or cap total concurrency across all replicas so expected demand remains below the safe connection budget. Configuring each replica safely in isolation is insufficient if autoscaling can multiply the total worker count beyond database capacity.
Lowering worker concurrency can protect the database, but it may increase the PHP-FPM request queue and user latency. The correct value balances worker memory, request throughput, database throughput, and acceptable queueing.
I would keep PDO persistent connections only when measurements show that connection-establishment overhead is material and the retained-session count and connection-state behavior are controlled.
I would disable or avoid them when:
- Worker count makes the retained connection total unsafe.
- Session state cannot be reliably reset.
- A proxy already provides effective pooling.
- Connection setup is not an important part of latency.
- Persistent idle sessions consume scarce database capacity.
Disabling persistence may increase connection creation rate, authentication work, TLS setup, or network overhead. I would therefore compare connection latency, database load, and total session count before and after the change rather than assuming either mode is universally better.
A connection proxy can accept many application-side connections while maintaining a controlled backend pool, depending on the database, proxy, and pooling mode. It can be useful when many PHP-FPM workers or replicas create excessive connection churn or when the database handles a smaller number of backend sessions more efficiently.
I would verify:
- Client connection limits and backend pool limits.
- Queue size and connection-acquisition timeout.
- Transaction pooling versus session pooling.
- Transaction pinning and backend-session reuse rules.
- Compatibility with session variables, temporary tables, advisory locks, prepared statements, and other connection-local state.
- Failure handling, observability, and high availability.
A proxy does not repair slow SQL, wide transactions, lock contention, or retry storms. An unlimited proxy queue can merely replace fast connection errors with extreme latency and memory pressure. Its limits must therefore be explicit and observable.
My remediation plan would be:
- Confirm the failure type and preserve evidence from all layers.
- Stop or limit runaway traffic, retry storms, or unhealthy consumers if the database is at immediate risk.
- Preserve reserved administrative access for diagnosis and recovery.
- Identify and handle clearly abandoned, blocked, or harmful sessions using an approved operational procedure.
- Fix slow queries, missing or ineffective indexes, lock chains, and unnecessarily wide transactions.
- Move network calls, file work, and unrelated computation outside transactions.
- Ensure every transaction commits or rolls back on all code paths.
- Remove duplicate connections and correct persistent-connection misuse.
- Add bounded acquisition, query, lock, transaction, request, and retry deadlines.
- Right-size PHP-FPM and background-worker concurrency across all replicas.
- Introduce or tune a proxy when measured connection churn or backend-session pressure justifies it.
- Increase the database connection limit only after confirming the database has enough per-connection memory, process or thread capacity, CPU, storage throughput, and lock-management capacity.
Increasing the limit alone can worsen performance. More simultaneous queries may increase context switching, memory use, cache pressure, lock contention, and storage contention even though connection-refusal errors temporarily disappear.
I would test with representative traffic, including normal peaks, background jobs, autoscaling, slow dependencies, and partial database degradation.
I would verify:
- Peak client connections and backend sessions stay below their budgets.
- Connection acquisition latency remains stable.
- PHP-FPM queue length and worker saturation remain acceptable.
- Query latency, lock waits, and database resource use do not regress.
- Long transactions and idle-in-transaction sessions are absent or within an approved threshold.
- Retry volume remains bounded during failures.
- Administrative headroom is preserved.
- Scaling an application replica does not unexpectedly exceed the shared budget.
I would add alerts for connection-budget utilization, acquisition failures, acquisition latency, oldest transaction age, idle-in-transaction sessions, connection churn, PHP-FPM queue growth, active-worker saturation, and abnormal retry rates. This turns the fix into a measurable capacity policy rather than a one-time configuration change.
- Capture the exact driver, proxy, and database errors for the incident window.
- Inventory every PHP-FPM pool, replica, background process, scheduled job, proxy, and other database client.
- Calculate a safe application connection budget with operational headroom.
- Correlate PHP-FPM workers, queues, request duration, and retries with database client connections, backend sessions, query age, transaction age, and locks.
- Group sessions by client identity, user, host, database, state, query age, and transaction age.
- Classify the cause as excessive concurrency, persistent idle sessions, connection churn, slow SQL, blocked work, long transactions, retries, or duplicate connection creation.
- Trace the responsible requests and inspect their connection and transaction boundaries.
- Fix slow, blocked, duplicate, or long-held work before increasing limits.
- Add reliable rollback, cleanup, bounded retries, and coordinated timeouts.
- Right-size total PHP-FPM and background-worker concurrency across all replicas.
- Evaluate a proxy only when measured pooling or connection-churn needs justify it.
- Validate under representative and degraded load, then alert on budget utilization, acquisition time, transaction age, queues, and retry volume.
This is mainly an operational capacity problem rather than an algorithm with Big O complexity. Collecting connection counts and worker metrics is usually inexpensive, but detailed tracing, slow-query logging, and high-cardinality labels can consume CPU, storage, memory, and network capacity. Each PHP-FPM worker uses process memory even while waiting, and each database connection may use database-side memory and process or thread resources. Lowering worker counts can protect the database but increase queueing. Raising connection limits can increase memory use, context switching, lock contention, and storage pressure. Persistent connections reduce setup work but may reserve idle sessions. A proxy can reduce backend connections but adds queueing, memory use, operational complexity, and another failure point.
Interviewers ask this question to test whether the candidate understands how PHP-FPM process concurrency interacts with finite database capacity. A strong answer distinguishes connection exhaustion from database CPU or query saturation and separates active queries, idle sessions, idle sessions with open transactions, persistent PDO connections, connection churn, slow requests, blocked work, and genuine connection-retention defects. It also demonstrates production judgment by measuring before changing limits, preserving administrative access, controlling retries and timeouts, considering all application replicas and background clients, and validating that a proposed fix improves both reliability and latency.
Common mistakes include raising the database connection limit before proving the cause; treating every connection failure as a max-connections error; calculating capacity from only one PHP-FPM host; ignoring queue consumers, jobs, and other services; assuming every worker uses exactly one connection; assuming PDO persistent connections form one shared pool; calling every idle session a leak; ignoring sessions idle inside a transaction; performing network or file work inside transactions; failing to roll back after exceptions; retrying immediately without limits or jitter; using pm.max_requests as the permanent fix; setting contradictory timeouts across layers; adding a proxy without checking session-state compatibility; allowing an unlimited proxy queue; and declaring success after connection errors disappear while latency, memory use, lock contention, or database saturation becomes worse.
Present the answer as a measurement-led sequence: confirm the failure, establish the shared connection budget, correlate PHP-FPM and database metrics, classify session states, inspect transaction and connection lifecycles, correct the cause, and validate under load. Emphasize that increasing limits or adding a proxy is a capacity decision made after measurement, not the default first response.










