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

Production alerts indicating an unresponsive database are every sysadmin's nightmare. Recently, I investigated a classic connection exhaustion incident in MySQL/MariaDB that perfectly illustrates how legacy software without query optimization can bring down an entire database instance.

Below is the terminal diagnosis, the one-liner command to batch-kill hung threads, and structural mitigations to prevent recurrence.

Initial symptom: max_connections limit exceeded#

The monitoring alert triggered indicating that MySQL stopped accepting incoming handshakes. Checking the error logs revealed:

ERROR 1040 (08004): Too many connections

When active connection counts reach the global database threshold (@@max_connections), all subsequent connection requests (originating from PHP-FPM pools or other websites) are immediately rejected by the socket listener.

Terminal investigation: pinpointing the offending query#

I queried the running threads via processlist:

| ID       | User            | Host      | db              | Command | Time | State        | Info                                                                                                   |
| 55764331 | cliente_liveusr | localhost | cliente_livechat| Query   | 5054 | Sending data | SELECT COUNT(*) AS `total`,(SELECT COUNT(*) FROM (SELECT `lz_chat_archive`.`chat_id` FROM `lz_chat_a |
| 55767542 | cliente_liveusr | localhost | cliente_livechat| Query   | 5024 | Sending data | SELECT COUNT(*) AS `total`,(SELECT COUNT(*) FROM (SELECT `lz_chat_archive`.`chat_id` FROM `lz_chat_a |
| 55769351 | cliente_liveusr | localhost | cliente_livechat| Query   | 5025 | Sending data | SELECT COUNT(*) AS `total`,(SELECT COUNT(*) FROM (SELECT `lz_chat_archive`.`chat_id` FROM `lz_chat_a |

Key findings from the processlist:#

• Execution duration: Dozens of identical queries were stuck for over 5,000 seconds (nearly an hour and a half each).
• Thread state (Sending data): MySQL was actively reading massive data pages from disk and allocating temporary on-disk tables.
• Target application: The database belonged to LiveZilla (a customer support chat platform).
• The query structure: Nested subqueries executing SELECT COUNT(*) iterations across the lz_chat_archive table.

Root cause mechanism#

LiveZilla was attempting to calculate real-time chat statistics on the dashboard. Because the lz_chat_archive table contained hundreds of thousands of rows and lacked covering indexes for that specific query, the MySQL optimizer created temporary disk tables on every dashboard hit.

With every browser refresh, PHP spawned a new connection executing the same query. Because earlier queries never finished due to disk I/O bottlenecks, incoming connections piled up until exhausting available connection slots.

Emergency action: batch-killing hung threads#

Manually running KILL [ID] across dozens of threads via CLI is slow during an active incident.

To terminate all hung connections from the specific database user cleanly:

mysql -e "SELECT concat('KILL ', id, ';') FROM information_schema.processlist WHERE user = 'cliente_liveusr' AND time > 100" | grep -v "concat" | mysql

How this command works:#

1. Queries information_schema.processlist filtering for threads owned by that user running longer than 100 seconds.
2. Generates individual KILL [ID]; statements dynamically.
3. Pipes the output through grep -v "concat" to strip MySQL table headers.
4. Pipes the SQL script directly back into the mysql client, executing all thread terminations in a single batch.

Complementary step: If PHP-FPM continues flooding MySQL immediately after the kill, reset the user's PHP workers to drop active sockets (apply this only if the user account is dedicated to LiveZilla; otherwise restart only the specific FPM pool to avoid terminating unrelated sites under the same user):

killall -u cliente_liveusr php-fpm

Conclusion: long-term recommendations and best practices#

1. Data purging and archiving: Archive chat logs older than 12 months out of the primary transactional table.
2. Index optimization: Create explicit composite indexes on chat_id and timestamp columns used in analytics.
3. Disable real-time dashboard stats: Configure the chat backend to cache report counters or poll on longer intervals.
4. Why simply increasing max_connections is dangerous: Blindly raising the connection limit without fixing the underlying query causes hundreds of slow queries to run in parallel, exhausting physical RAM and triggering the Linux kernel OOM Killer.

See also

Databases Surviving MariaDB connection exhaustion on CloudLinux with zero downtime

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

Infrastructure Anatomy of a silent deadlock in CloudLinux and cPanel: phantom processes, session locks, and TCP queues

This post is licensed under CC BY-NC.