Backlog and Listen Queue Tuning

Configure kernel-level networking buffers to handle spikes in incoming requests.

In this lesson, you will learn how to configure your operating system’s networking buffers to prevent connection drops during traffic spikes by tuning the TCP listen backlog.

The Problem: Connection Spikes

Even if you have perfectly tuned your MySQL max_connections, your database might still reject incoming requests during a sudden burst of traffic. This happens because the operating system manages a "waiting room" for pending connection requests—known as the listen queue—before the database server is ready to process them. If this queue fills up faster than MySQL can "accept" the connections, the OS will start dropping new incoming packets, leading to "Connection refused" errors for your users.

The Solution: Tuning the Backlog

The size of this waiting room is determined by the kernel parameter net.core.somaxconn (at the system level) and the back_log variable (at the MySQL level).

When a spike occurs, you need to ensure the system can hold enough requests in the queue while the database threads are busy picking up current connections.

Increase System Limit: First, adjust the Linux kernel limit to allow a larger backlog. This is done by editing /etc/sysctl.conf:
```
# Allow a larger queue for incoming TCP connections
net.core.somaxconn = 1024
```
Apply this with sysctl -p.
Adjust MySQL back_log: Next, tell MySQL how many outstanding connection requests it should allow in its own queue. This value should match or be slightly lower than the system’s somaxconn. You can set this in your my.cnf file:
```
[mysqld]
back_log = 1024
```

Why This Matters

If back_log is too low, the OS silently discards connection attempts when the server is momentarily overwhelmed. By increasing these values, you provide a larger buffer, allowing the database a brief window to process the backlog without forcing clients to retry or failing their connection attempts entirely. This is an essential safety net for high-concurrency environments where traffic is unpredictable.

Key Takeaway: Tuning back_log and net.core.somaxconn expands the server's "waiting room," preventing connection drops during transient traffic spikes by providing the kernel more capacity to queue pending requests.