Tuning max_connections and Thread Handling in MySQL

Every time I get paged for ERROR 1040 (HY000): Too many connections, someone has already bumped max_connections to a bigger number before I log in. Sometimes that's right. More often the limit is doing its job and stopping a connection storm from turning a slow database into a dead one.

Here's how I look at connections and threads, size the limit from real numbers, and tell a leak from a genuinely busy server. It applies to MySQL 5.7 and 8.0 and MariaDB 10.2 and later, and I flag where they differ in the thread pool section.

Measure first

SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Threads_connected', 'Threads_running', 'Max_used_connections',
   'Connections', 'Aborted_connects', 'Threads_created', 'Threads_cached');
SHOW GLOBAL VARIABLES LIKE 'max_connections';

On MySQL 5.7 and later, Max_used_connections_time tells you when the peak happened, which is often the key clue (backup window, cron burst, deploy).

The two numbers to hold in your head are Threads_connected (open sessions, mostly idle) and Threads_running (sessions executing right now). A server with 400 connected and 6 running is healthy. A server with 400 connected and 380 running is saturated, and raising max_connections will make it worse.

Setting max_connections

Each connection is a thread with its own stack (thread_stack, 256 KB default) plus per-session buffers allocated as needed: sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size, tmp_table_size. The worst case for a session that sorts and joins is several megabytes; 1000 such sessions at once would consume gigabytes on top of the buffer pool.

I don't trust formulas here. I size it to the workload: take Max_used_connections over a month, add headroom for a deploy or failover doubling the pool briefly, and stop there. For a typical application tier with a connection pool of 50 per app server and 6 app servers, 300 connections is the real maximum, and 400 leaves room.

In /etc/my.cnf on CentOS or /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu:

[mysqld]
max_connections      = 400
thread_cache_size    = 64
wait_timeout         = 300
interactive_timeout  = 300
max_connect_errors   = 100000

The limit is dynamic, so you can also change it live while you edit the file:

SET GLOBAL max_connections = 400;

Note that MySQL reserves one extra connection above the limit for a user with the SUPER privilege (or CONNECTION_ADMIN on 8.0), which is how you get in to fix things during a storm. On 8.0.14 and later there is also admin_address and admin_port for a dedicated administrative interface.

Thread cache

By default MySQL creates a new OS thread for each connection and destroys it at disconnect. thread_cache_size keeps finished threads around for reuse. Check the hit rate:

SHOW GLOBAL STATUS LIKE 'Threads_created';
SHOW GLOBAL STATUS LIKE 'Connections';

If Threads_created grows at anything like the rate of Connections, the cache is too small. Setting it to something near your normal Threads_connected makes Threads_created almost flat. Applications that open a connection per request (PHP without persistent connections is the classic case) benefit most.

Idle connections and leaks

Connections that sit in Sleep state for hours are usually leaked by an application that forgot to close them, or a pool with a high minimum. Find them:

SELECT id, user, host, db, command, time
FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 600
ORDER BY time DESC;

wait_timeout closes non-interactive connections idle longer than the given seconds. The default 28800 (8 hours) is far too generous; 300 to 600 seconds is reasonable when the application has a pool that handles reconnects. Setting it too low on an application without reconnect logic produces MySQL server has gone away errors, so talk to the developers first. I lowered it once without asking and got a very unhappy phone call.

Per-user limits stop one application from consuming the whole server:

ALTER USER 'reporting'@'10.0.0.%' WITH MAX_USER_CONNECTIONS 20;

A thread pool, if concurrency is really high

The one-thread-per-connection model degrades when Threads_running is far above the CPU core count, because the threads fight over mutexes. A thread pool caps the number of concurrently executing threads and queues the rest.

MariaDB includes one in the free build:

[mysqld]
thread_handling      = pool-of-threads
thread_pool_size     = 16
thread_pool_max_threads = 1000

thread_pool_size should be roughly the number of CPU cores. MySQL Community Edition does not ship a thread pool; it is an Enterprise plugin. Percona Server includes one with the same thread_handling = pool-of-threads option. If you run Oracle's community build and need this, the practical alternative is to put ProxySQL in front and use its multiplexing to reduce back-end connections.

Watch it for a day

SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Threads_connected', 'Threads_running', 'Threads_created',
   'Max_used_connections', 'Connection_errors_max_connections', 'Aborted_clients');

Connection_errors_max_connections should stay at zero. Aborted_clients climbing after a wait_timeout reduction means the application is not handling the disconnects; raise it back and talk to the developers. Threads_created should be nearly flat.

Check memory too:

free -m
ps -o rss= -p $(pidof mysqld)

If RSS grows steadily with connection count and never comes back, per-session buffers are too large for the connection limit you set.

Symptoms and what they usually mean

ERROR 1040 (HY000): Too many connections during a deploy but not otherwise. The pool minimums across new and old application instances overlap briefly. Either increase max_connections by the pool size of one deploy wave, or configure the deploy to drain before starting new instances.

ERROR 1129 (HY000): Host '10.0.0.5' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'. A host exceeded max_connect_errors with failed handshakes, often a health check hitting the port without logging in. Run mysqladmin flush-hosts and raise max_connect_errors as shown above.

ERROR 1226 (42000): User 'reporting' has exceeded the 'max_user_connections' resource. The per-user cap from earlier is working; decide whether the user or the cap is wrong.

Server accepts connections but every query is slow, with Threads_running in the hundreds. The limit is too high for the hardware, not too low. Lower it, or add a thread pool, and fix the slow queries that are piling up.

Look at Threads_running, not just Threads_connected. A rising Threads_running is a query problem, not a connection limit problem.