The worst connection storm I ever watched was self-inflicted. A launch-day traffic spike triggered autoscaling in the application tier, the platform obligingly grew from twenty app instances to two hundred, and each instance arrived with its own connection pool of twenty. Four thousand connections landed on a MariaDB server running the default thread handling — one thread per connection — inside of a minute. Load average climbed into the hundreds, CPU time went almost entirely to system rather than user, and query latency fell apart across the board. The queries were fine. The indexes were fine. The server was simply spending its life context-switching between thousands of runnable threads instead of executing any of them.
The fix was one line: thread_handling=pool-of-threads. MariaDB ships a genuine thread pool in the community server — a feature MySQL reserves for its Enterprise edition — and understanding how it behaves is the difference between a server that degrades gracefully under a storm and one that falls off a cliff. This post covers how the pool works, when it helps, when it quietly serializes you, and which counters tell the truth.
The default: one thread per connection, and where its cliff is
With the default thread_handling=one-thread-per-connection, every connection gets a dedicated OS thread for its lifetime. The thread cache (thread_cache_size) softens the connect/disconnect churn by parking threads for reuse, and for a few hundred concurrently active connections the model works well — each thread runs independently, there is no queueing layer, and latency is predictable.
The model has a cliff, not a slope. Beyond a few hundred active threads, three things compound: the scheduler burns CPU context-switching; each thread carries a few hundred kilobytes of stack, so thousands of connections mean gigabytes of memory that does nothing but exist; and contention on the server's internal hot spots gets worse as more threads pile onto the same mutexes. The worst part is that idle connections are nearly free in this model — it is the concurrently running queries multiplied by the number of connections that decides when you fall off the cliff, which is why a storm and a slow-query event together are so much deadlier than either alone.
What the pool actually does
With thread_handling=pool-of-threads, MariaDB keeps a fixed number of thread groups — thread_pool_size, which defaults to the server's CPU core count — each with its own listener and work queue. Connections are distributed across the groups, and only a small number of threads per group actually run at any moment; everything else waits in the queue. A sleeping connection holds no worker at all, which is the entire trick: thousands of mostly-idle connections cost almost nothing, because the pool sizes itself to the work, not to the connection count.
The pool stays responsive through stall detection. If a thread group makes no progress for thread_pool_stall_limit milliseconds — the default is 500 — the group is considered stalled and may wake an idle thread or create a new one, up to thread_pool_max_threads (which defaults to an effectively unlimited 65536, so set a real cap). thread_pool_oversubscribe, default 3, controls how many extra active threads a group tolerates before it starts queueing. The configuration I actually run looks like this:
-- In my.cnf, under [mysqld]:
-- thread_handling = pool-of-threads
-- thread_pool_size = 16 -- defaults to CPU core count
-- thread_pool_stall_limit = 500 -- ms before a group is treated as stalled
-- thread_pool_max_threads = 2000 -- hard cap; the default is far too high
-- At runtime, the vital signs:
SHOW GLOBAL VARIABLES LIKE 'thread_pool%';
SHOW GLOBAL STATUS LIKE 'Threadpool_threads';
SHOW GLOBAL STATUS LIKE 'Threadpool_idle_threads';One platform note that matters in mixed estates: the Unix build has its own pool implementation, while on Windows MariaDB builds the pool over the operating system's native thread pool — and there pool-of-threads is the default thread_handling, not an option you switch on. The platform defaults differ, so read SHOW VARIABLES on a Windows box instead of assuming your Unix config carried over.
When the pool earns its keep
- Connection storms. The launch-day scenario: connection count triples in a minute, but the pool keeps runnable threads near the core count, so the server keeps executing queries instead of scheduling threads. Degradation becomes a queue, not a collapse.
- Many mostly-idle connections. Applications with generous pools, persistent connections, and low duty cycles — the common case behind a web platform — map beautifully onto a thread pool.
- Short OLTP queries at high concurrency. Keeping the working set of threads near the core count improves CPU cache locality and reduces pile-ups on internal mutexes. Throughput under contention is the benchmark the pool was built for.
When the pool serializes you
The pool's weakness is head-of-line blocking inside a group. If every worker in a group is occupied by long-running queries, the short queries behind them wait — the stall limit eventually spawns relief threads, but each spike costs real latency and the queue can be deep by then. The classic self-inflicted case is mixing reporting queries with OLTP on the same instance: a handful of thirty-second scans will monopolize enough workers to make one-millisecond lookups queue behind them. The fix is architectural, not tunable: heavy reads go to a replica.
Two more honest caveats. First, anything that parks a worker without doing work — long transactions holding locks, user-level GET_LOCK waits, deliberate sleeps in stored procedures — occupies pool capacity exactly like a slow query does, and under the pool that capacity is finite and shared. Second, thread_pool_max_threads cuts both ways: too low and you queue behind work that could have run in parallel; too high and you have rebuilt one-thread-per-connection with extra steps. Size it against the worst legitimate burst, and let the queue absorb the rest.
And one operational feature worth setting before you need it: extra_port with extra_max_connections gives you an administrative backdoor that still accepts connections when the main port and the pool are saturated. During a storm, the ability to get a root session is the difference between observing the queue and power-cycling the box.
Reading the pool's vital signs
Two status variables do most of the work. Threadpool_threads is the total number of threads in the pool; Threadpool_idle_threads is how many are waiting for work. The gap between them is your busy-worker count — worth trending, but do not benchmark it against thread_pool_size. That variable counts thread groups, not workers, and a stalled group legitimately runs extra threads (thread_pool_oversubscribe plus stall-relief spares), so the busy count can sit well above the group count with nothing queued at all. Saturation shows up as work that stays queued, and that is exactly what the per-group tables report — MariaDB exposes the pool through information_schema:
SELECT * FROM information_schema.THREAD_POOL_GROUPS;
SELECT * FROM information_schema.THREAD_POOL_QUEUES;
SELECT * FROM information_schema.THREAD_POOL_STATS;
-- Correlate with the classic pair:
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';THREAD_POOL_STATS shows you connections and queues per group, which is how you spot the unlucky-group effect: one group drowning in slow queries while the others idle. A word on measurement hygiene during storms — per-minute averages hide the shape of these events entirely. Sample per second during the incident, or accept that you are guessing.
The two counters nobody trends
Set the trend up tonight: Threadpool_threads minus Threadpool_idle_threads for busy workers, plus a per-second sample of the queue tables during your next storm. It costs nothing, and it turns "the database is slow" into a queue depth with a timestamp on it. MonPG monitors PostgreSQL today, and its MariaDB support is coming soon — in active development now; pool busy ratios and per-group queue depth are exactly the signals it is being built to graph as first-class series rather than leave in SHOW STATUS. PostgreSQL fleets already get the connection-storm counterpart — pg_stat_activity state ratios and connection counts per backend type — described on the PostgreSQL overview, with more on the blog and the engine comparison.