Batch API chunk size impact on connection pool retention

Question

Investigate how Drupal Batch API chunk size influences database connection pool retention during processing of large entity sets.

The Batch API processes datasets in configurable chunks; larger chunk sizes generate pending queries that may approach the configured connection pool limit. Entity result caching can retain database references between iterations, potentially preventing connection release. The threshold at which chunk size triggers persistent pool exhaustion remains undocumented for standard Drupal 10 installations.

What chunk size threshold triggers persistent connection pool exhaustion in a typical Drupal 10 setup? Does EntityFieldQuery result caching retain database connections across batch iterations? How does the $db->resetActiveConnection() method affect pending query accumulation during batch processing?

Siddharth · Answer

Connection Pool Retention in Batch API
There is no fixed chunk size threshold that triggers connection pool exhaustion in Drupal 10 because the Batch API operates across separate HTTP requests. In a standard web server environment, each batch iteration is a fresh request that opens and closes its own database connection. Consequently, connection pool exhaustion typically occurs within a single chunk's execution rather than accumulating across iterations.

Likely Explanations and Behavioral Analysis
While connections do not persist between iterations, large chunk sizes can still lead to exhaustion or failures due to the following factors:

Concurrent Requests: If the batch process triggers multiple concurrent AJAX requests (depending on the browser and Drupal configuration), the number of active connections will scale with the number of concurrent requests, not the chunk size.
Statement Accumulation: Very large chunks increase the number of active statements and memory overhead per request. If the database server has a low max_connections limit or strict resource quotas per session, a single massive chunk can hit these limits.
Entity Caching: Entity result caching stores data in static memory or persistent cache bins (like Redis or the database). It does not retain open database connection handles across the HTTP requests that separate batch iterations.

Managing Connection State
The $db->resetActiveConnection() method (or similar connection management calls) forces the release of the current database handle for the current request. This is useful for:

Clearing pending query state and freeing server-side resources before the request terminates.
Preventing "Too many connections" errors in environments where PHP-FPM processes are reused aggressively and connections are not closed promptly.

Verification Steps
To determine the actual limits of your specific environment, use the following scoped approach:

Monitor Process Lists: Run SHOW PROCESSLIST; (MySQL) or SELECT * FROM pg_stat_activity; (PostgreSQL) while varying chunk sizes (e.g., 10, 100, 1000).
Profile Memory: Use a tool like XHProf or Tideways to ensure that "exhaustion" is not actually a PHP memory limit being hit due to entity loading in large chunks.
Test Reset Logic: Implement a connection reset at the end of your batch operation callback to see if it reduces the peak connection count on the database server.

Missing Diagnostic Detail: To provide a more precise recommendation, please specify if you are using a persistent connection proxy (e.g., PgBouncer for PostgreSQL) or a specific PHP-FPM configuration that enables persistent database links (pdo_mysql.persistent).

Batch API chunk size impact on connection pool retention

1 answer

Connection Pool Retention in Batch API

Likely Explanations and Behavioral Analysis

Managing Connection State

Verification Steps

Your perspective belongs here.

1 question comment