Surviving MariaDB connection exhaustion on CloudLinux with zero downtime

On shared servers hosting dozens or hundreds of accounts, a single misbehaving website can degrade stability for everyone. A frequent pattern occurs when a live chat system (such as LiveZilla) or an unoptimized plugin opens dozens of concurrent database connections.

During a recent incident, a single account (user_cliente) queued 88 simultaneous connections on MariaDB 10.11, pushing the database daemon to its global max_connections ceiling and locking out queries from all other tenants on the host.

What made this case interesting was that CloudLinux LVE Manager was already active with strict quotas for that specific user:

• SPEED: 200% (2 CPU cores)
• SPEED MYSQL: 400%
• EP (Entry Processes): 20
• NPROC: 50

Despite these thresholds, mysqladmin proc stat continued reporting 88 active threads originating from the same account.

This article examines why LVE panel limits failed to prevent the connection pile-up on their own, how kernel scheduling behaves under this condition, and how to stabilize the database service on the fly without downtime during business hours.

1. Why LVE limits appeared ineffective#

To understand what happened, we must examine how the Linux kernel schedules processes and coordinates communication between web workers and the database daemon.

LVE isolates and throttles processes running directly under the tenant's UID (user_cliente), such as PHP-FPM workers or CGI scripts. However, when a PHP script calls mysqli_connect() or creates a PDO instance, it opens a network socket or UNIX domain file descriptor to the MariaDB daemon (mysqld).

Once the handshake completes, query execution takes place within the mysqld process, running under the system mysql UID. In other words, database queries execute outside the tenant's LVE cage, unless MySQL Governor is actively hooked into the database binary.

The CPU throttling feedback loop#

In this environment, restricting the tenant's CPU quota to 200% produced an unintended consequence:

1. With constrained CPU power, the chat application's PHP scripts took significantly longer to process each web request.
2. Because scripts slowed down or hit execution timeouts, they never invoked the standard connection teardown (mysql_close) to send the QUIT packet.
3. On the MariaDB side, database connections lingered in Sleep state or remained stuck processing unindexed queries, accumulating zombie threads until the global server limit was exhausted.

Throttling tenant CPU inside LVE actually accelerated connection accumulation on the database engine.

2. Command-line diagnostics and ruling out edge cases#

Before taking corrective action, inspecting the active MariaDB process list reveals thread execution times:

mysqladmin proc v | grep user_cliente

The output showed dozens of queries running for between 380 and 2,377 seconds (nearly 40 minutes continuous runtime).

To identify the root cause, standard edge cases should be evaluated:

1. Table-level locking in legacy engines (MyISAM): a long-running INSERT locks the entire table for incoming reads.

• Check: mysql -e "SHOW OPEN TABLES WHERE In_use > 0;".

1. InnoDB deadlocks: concurrent transactions competing for identical row locks.

• Check: mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 20 "TRANSACTIONS".

1. TCP port exhaustion (TIME_WAIT): network sockets lingering in cleanup states.

• Check: netstat -nat | awk '/:3306/ {print $6}' | sort | uniq -c.

1. OOM Killer invocations: whether resident memory demands from active threads exceeded total physical RAM.

• Check: dmesg -T | grep -i oom-killer.

The inspection confirmed that the threads were either executing large temporary table operations or stalled waiting on unresponsive PHP processes.

To monitor how MariaDB accepts incoming connections at the system call level, you can trace system calls with strace:

strace -fp $(pidof mysqld) -e trace=accept,accept4

3. Auditing MySQL governor status#

A common first reaction is to use dbctl to assign connection limits:

dbctl set user_cliente --max_user_connections 30

However, checking running processes on the host revealed:

ps faux | grep dbgovernor

Only the auxiliary metric collector sentry_daemon.py was active. The primary dbgovernor daemon was not running.

Checking package installation:

yum install governor-mysql -y

The base package was present, but the running MariaDB binary was the standard distribution build rather than CloudLinux's custom build (cl-mariadb1011):

mysql -V

For CloudLinux to intercept internal database queries and throttle abusive threads automatically, standard packages must be replaced with CloudLinux-wrapped versions:

/usr/share/lve/dbgovernor/mysqlgovernor.py --set-mysql-version=mariadb1011
/usr/share/lve/dbgovernor/mysqlgovernor.py --install

Running --install replaces vendor MariaDB packages with the CloudLinux build. However, doing this during peak hours forces a database restart and brief downtime, which is unacceptable in production environments.

4. Zero-downtime hot mitigation strategy#

To restore database responsiveness immediately without interrupting other hosted tenants, we applied three runtime mitigations.

Step 1: Enforcing per-user connection limits in SQL#

Rather than relying on external daemons, MariaDB's authentication engine was instructed to reject connections once the user reached 20 concurrent sessions:

ALTER USER 'user_cliente'@'localhost' WITH MAX_USER_CONNECTIONS 20;
ALTER USER 'user_cliente'@'127.0.0.1' WITH MAX_USER_CONNECTIONS 20;
FLUSH PRIVILEGES;

With this rule active in memory, any attempt to establish a 21st connection fails with error 1226 (User has exceeded the 'max_user_connections' resource). The application's overload remains contained within its own account.

Step 2: Reducing idle connection timeouts dynamically#

To clean up abandoned connections left behind by stalled PHP workers, timeout thresholds were reduced globally without restarting the daemon:

SET GLOBAL wait_timeout = 60;
SET GLOBAL interactive_timeout = 60;

To ensure the settings persist across future restarts, add them to /etc/my.cnf:

[mysqld]
wait_timeout = 60
interactive_timeout = 60
max_statement_time = 120

The max_statement_time = 120 parameter enforces a hard execution ceiling in seconds on SELECT queries, terminating queries that exceed two minutes.

Step 3: Automated watchdog script via crontab#

To actively terminate long-running queries, we deployed a small maintenance script:

Create /usr/local/bin/mysql_limit_user.sh:

#!/bin/bash
# Find queries belonging to the user running longer than 120 seconds
THREADS=$(mysql -Ne "SELECT id FROM information_schema.processlist WHERE user='user_cliente' AND time > 120")

if [ -n "$THREADS" ]; then
    for ID in $THREADS; do
        mysql -e "KILL $ID"
        logger -t MYSQL_WATCHDOG "Killed thread $ID for user user_cliente exceeding execution threshold"
    done
fi

Make the script executable:

chmod +x /usr/local/bin/mysql_limit_user.sh

Add the script to the root crontab to run every two minutes:

(crontab -l 2>/dev/null; echo "*/2 * * * * /usr/local/bin/mysql_limit_user.sh >/dev/null 2>&1") | crontab -

5. Best practices for ongoing database stability#

Once the immediate threat is contained without downtime, permanent configuration changes ensure long-term reliability:

1. Schedule CloudLinux Governor integration during maintenance windows: during off-peak hours, switch packages to cl-mariadb1011 and activate the dbgovernor service so kernel-level throttling functions as intended.
2. Review application query structures: in chat and forum applications, unindexed SELECT COUNT(*) queries on historical archives frequently cause heavy disk I/O. Adding indexes and archiving older records relieves storage contention.
3. Maintain MAX_USER_CONNECTIONS on resource-heavy accounts: configuring limits between 20 and 30 connections for active chat or e-commerce applications prevents sudden traffic bursts from consuming the entire shared database connection pool.

See also

Infrastructure MySQL handshake desynchronization in multi-tenant environments: cPanel, CloudLinux, and CageFS

Databases How a SELECT COUNT query in LiveZilla crashed MySQL with max connections (and how i fixed it)

Infrastructure Complete Guide to Exim Spool: Frozen Queues, Kernel Locks, and CloudLinux Troubleshooting

This post is licensed under CC BY-NC.