Troubleshooting database connection leaks in long-running Laravel queue daemon processes on RHEL 9

Identifying the Symptoms: The Subtle Signs of Leaking Connections

Long-running processes, particularly those responsible for consuming Laravel queues, are prime candidates for database connection leaks. The symptoms often manifest subtly at first: gradual performance degradation, increased database server load, and eventually, outright connection exhaustion errors. You might observe a steady, upward trend in the number of active database connections on your RHEL 9 server, even when queue activity appears to be within expected parameters. This isn’t a sudden failure but a creeping problem that can cripple your application’s responsiveness.

Key indicators to monitor include:

• Max_used_connections in MySQL’s SHOW GLOBAL STATUS; steadily increasing over time without commensurate job processing.
• Application errors like SQLSTATE[HY000] [2002] Connection refused or Too many connections originating from your Laravel application.
• Increased latency in database queries reported by your application’s performance monitoring tools.
• The number of idle database connections on the server remaining persistently high.

The Root Cause: Laravel’s Queue Worker and Database Pooling

Laravel’s queue workers, by default, are designed to be long-running processes. Each time a job is processed, the worker establishes a database connection to interact with Eloquent models, the database queue driver, or other database-dependent services. The core of the problem lies in how these connections are managed (or mismanaged) within the persistent worker process. Unlike short-lived web requests where connections are typically opened and closed for each request, a queue worker might hold onto a connection for an extended period, especially if jobs are processed sequentially without explicit connection closure.

Consider a typical scenario:

// Inside a Laravel Job's handle() method
public function handle()
{
    $user = User::find($this->userId);
    // ... perform operations using $user ...
    $post = new Post(['title' => 'New Post']);
    $post->save(); // This implicitly uses a database connection
    // ... more operations ...
}

In a long-running worker, if the connection isn’t properly released or reset between jobs, or if an exception occurs mid-job without proper cleanup, the connection can remain open in an idle state. Over thousands of jobs, this accumulation leads to the leak.

Leveraging `DB::reconnect()` and Connection Pooling

The most direct approach to mitigate connection leaks in long-running PHP processes is to explicitly manage the database connection lifecycle. Laravel’s database manager provides the reconnect() method, which can be used to close the existing connection and establish a new one. This effectively “resets” the connection, preventing stale or leaked resources from accumulating.

A common strategy is to reconnect after a certain number of jobs have been processed. This can be implemented within the queue worker itself, or more elegantly, by using a supervisor like Supervisor to restart the worker process periodically.

Implementing `DB::reconnect()` in a Custom Queue Worker

While Laravel’s default artisan queue:work command doesn’t offer a direct “reconnect every N jobs” option, you can achieve this by creating a custom worker script. This script would loop, process jobs, and periodically call DB::reconnect().

<?php

require __DIR__.'/vendor/autoload.php';

$app = require_once __DIR__.'/bootstrap/app.php';

$console = $app->make(Illuminate\Contracts\Console\Kernel::class);

// Define the maximum number of jobs before reconnecting
define('MAX_JOBS_BEFORE_RECONNECT', 100);

$jobCount = 0;

// Get the default queue connection name
$connection = config('queue.default');

// Simulate the queue:work loop
while (true) {
    // Process one job
    $exitCode = $console->call('queue:work', [
        '--once' => true, // Process only one job at a time
        '--queue' => $connection,
        '--no-interaction' => true,
        '--env' => 'production', // Or your desired environment
    ]);

    if ($exitCode === 0) {
        $jobCount++;
        echo "Processed job. Total jobs since last reconnect: {$jobCount}\n";

        if ($jobCount >= MAX_JOBS_BEFORE_RECONNECT) {
            echo "Reached job limit. Reconnecting to database...\n";
            // Explicitly reconnect to the database
            DB::reconnect($connection);
            echo "Database reconnected.\n";
            $jobCount = 0; // Reset the counter
        }
    } else {
        // Handle potential errors or non-zero exit codes from queue:work
        echo "queue:work exited with code: {$exitCode}\n";
        // Consider adding more robust error handling or a sleep here
        sleep(5);
    }

    // Small delay to prevent tight loop if no jobs are available
    if ($exitCode !== 0) {
        sleep(1);
    }
}

To run this custom worker, you would typically use Supervisor. For example, in your Supervisor configuration file (e.g., /etc/supervisord.d/laravel-worker.conf):

[program:laravel-queue-worker]
process_name=%(program_name)s_%(process_num)02d
command=/usr/bin/php /var/www/your-app/artisan custom:queue:work --queue=database --env=production
autostart=true
autorestart=true
user=www-data
numprocs=4
redirect_stderr=true
stdout_logfile=/var/log/supervisor/laravel-queue-worker.log

Note: Replace /usr/bin/php and /var/www/your-app/ with your actual paths. The custom:queue:work command would point to your custom script. You’d need to register this script in your app/Console/Kernel.php using protected $commands = [ ... ]; or by placing it in the app/Console/Commands directory and registering it there.

Rethinking Connection Management with Persistent Connections

If you are using persistent database connections (e.g., via PDO::ATTR_PERSISTENT => true in your config/database.php), this can exacerbate connection leaks. Persistent connections are designed to be reused across multiple requests, which is generally beneficial for web requests but can be problematic for long-running workers if not managed carefully. When a persistent connection “leaks” in a worker, it’s not just an idle connection; it’s a connection that might hold onto application state or be in an unexpected transaction state.

For queue workers, it’s often safer to disable persistent connections in your config/database.php for the relevant environment (e.g., production):

// config/database.php

'connections' => [
    'mysql' => [
        // ... other settings
        'options' => [
            // Disable persistent connections for queue workers
            PDO::ATTR_PERSISTENT => false,
        ],
        // ...
    ],
],

By disabling persistent connections, each job will acquire a fresh, clean connection, and when DB::reconnect() is called (or when the worker process is restarted), the underlying OS resources are more reliably released.

System-Level Diagnostics and Configuration on RHEL 9

Beyond application-level fixes, understanding and configuring your RHEL 9 system’s network and database server settings is crucial for robust connection management.

Monitoring Database Connections

On the RHEL 9 server hosting your MySQL/MariaDB instance, you can monitor active connections using:

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW PROCESSLIST;

To continuously monitor connection count, you can use a simple shell script:

#!/bin/bash
MYSQL_USER="your_db_user"
MYSQL_PASSWORD="your_db_password"
MYSQL_HOST="localhost"

while true; do
    CONN_COUNT=$(mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW GLOBAL STATUS LIKE 'Threads_connected';" | grep Threads_connected | awk '{print $2}')
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Active DB Connections: $CONN_COUNT"
    sleep 60
done

Save this script (e.g., as monitor_db_connections.sh), make it executable (chmod +x monitor_db_connections.sh), and run it in the background (e.g., using nohup ./monitor_db_connections.sh &).

Tuning MySQL/MariaDB `wait_timeout` and `interactive_timeout`

The wait_timeout and interactive_timeout variables in MySQL control how long the server waits for activity on a connection before closing it. For long-running queue workers, setting these too low can cause legitimate connections to be dropped prematurely, leading to errors. Conversely, setting them too high can exacerbate leaks if connections are not properly closed by the application.

A common recommendation for applications with persistent connections or long-running processes is to increase these values, but this should be done cautiously. For queue workers that are *not* using persistent connections and are managed with explicit reconnects or worker restarts, a moderate value is often sufficient. A value of 28800 (8 hours) is often seen, but for queue workers, you might consider a value that aligns with your worker restart policy or a reasonable idle timeout.

To check current values:

SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

To set them dynamically (until next server restart):

SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

To make these changes permanent, edit your MySQL configuration file (e.g., /etc/my.cnf or a file in /etc/my.cnf.d/) and add/modify the following under the [mysqld] section:

[mysqld]
wait_timeout = 28800
interactive_timeout = 28800

After modifying the configuration file, restart the MySQL/MariaDB service:

sudo systemctl restart mariadb
# or
sudo systemctl restart mysqld

RHEL 9 Network Tuning (Less Common, but Possible)

While less frequently the direct cause of application-level connection leaks, RHEL’s network stack parameters (e.g., TCP keepalive settings) can influence how quickly stale connections are detected and cleaned up by the operating system. For most standard Laravel applications, default RHEL 9 network settings are usually adequate. However, in highly saturated network environments or with very aggressive firewall rules, tuning net.ipv4.tcp_keepalive_time, net.ipv4.tcp_keepalive_intvl, and net.ipv4.tcp_keepalive_probes might be considered as a last resort, but this is advanced and typically not the primary solution for application-level leaks.

Best Practices for Production Queue Workers

To prevent database connection leaks and ensure stable queue processing in production:

• Use Supervisor: Always manage your queue workers with a process manager like Supervisor. Configure it to restart workers periodically (e.g., every few hours) as a failsafe against unforeseen leaks.
• Disable Persistent Connections: For queue workers, disable PDO persistent connections in your database configuration.
• Implement `DB::reconnect()`: If not relying solely on worker restarts, implement a strategy to periodically call DB::reconnect() within your worker logic after a set number of jobs.
• Monitor Actively: Set up monitoring for both application-level errors (connection refused, timeouts) and system-level metrics (database connection count, CPU, memory).
• Tune `wait_timeout` Prudently: Adjust MySQL’s wait_timeout and interactive_timeout based on your application’s behavior and worker management strategy, but avoid excessively high values that mask leaks.
• Error Handling: Ensure your jobs have robust error handling. Uncaught exceptions can leave database connections in an inconsistent state. Consider using Laravel’s failed() job method for cleanup.

Conclusion

Database connection leaks in long-running Laravel queue workers are a common yet insidious problem. By understanding the interplay between Laravel’s connection management, your application’s code, and system-level configurations on RHEL 9, you can implement effective strategies. Employing explicit connection management with DB::reconnect(), leveraging process managers like Supervisor for periodic restarts, and carefully monitoring your database server are key to maintaining a stable and performant application.