The idea: a thread for each connection, or a shared pool

MariaDB is one process, mysqld, with many threads. Every client connection needs a thread to read its commands and run them. The variable thread_handling decides how. With one-thread-per-connection, the default, each connection owns an OS thread for its whole life, even while it sits idle. With pool-of-threads, MariaDB's built-in thread pool, connections share a small set of threads, and an idle connection holds no thread at all.

Left: one-thread-per-connection, six connections each tied to its own thread, plus a thread cache with parked threads. Right: pool-of-threads, the same six connections split into two thread groups, each with one listener and one worker, and idle sockets waiting in epoll.
Six connections: six threads in the default mode, four threads in the pool (a listener and a worker per group).

One thread per connection and the thread cache

The main thread waits on port 3306. When a client connects it accept()s the socket, checks the connection limit, and gives the socket to a thread. That thread sends the greeting, checks the password, and then loops: block in read() until a command arrives, run it, send the result. Since every connection has its own thread, a slow query only slows its own client (Demo: one thread per connection).

Creating a thread costs a little time. So when a connection ends, its thread can park in the thread cache instead of exiting, if fewer than thread_cache_size threads are already parked. The next new connection takes a parked thread. Watch Threads_created: it stops growing when the cache works, and Threads_cached shows the parked threads (Demo: thread cache reuses threads). MariaDB sizes thread_cache_size automatically (up to 256).

max_connections

max_connections (151 by default) limits connections, not threads. When Threads_connected has reached it, the main thread answers ERROR 1040 (HY000): Too many connections and closes the socket, and Connection_errors_max_connections grows (Demo: max_connections, ERROR 1040). MariaDB keeps one extra connection for a user with the SUPER (or CONNECTION ADMIN) privilege, so an administrator can still get in. The limit applies with the thread pool too; the pool only makes many connections cheap.

status variablemeaning
Connectionsconnection attempts, refused ones included
Threads_connectedopen connections
Threads_runningconnections running a statement now
Threads_created, Threads_cachedthreads made for connections; threads parked in the cache
Threadpool_threads, Threadpool_idle_threadspool threads; those not running (idle, or waiting on a lock, I/O or sleep)

pool-of-threads: groups, listener, queue, workers

With thread_handling=pool-of-threads (set at startup; it cannot change while the server runs) the pool has thread_pool_size thread groups, by default one per CPU. A connection belongs to group connection id % thread_pool_size. Each group has:

  • a listener that waits in epoll_wait() on the sockets of all the group's idle connections;
  • a queue of ready events (a login, a query that arrived);
  • workers that take events from the queue and run them.

The group tries to keep exactly one worker active, like one thread per CPU. A new event wakes or creates a worker only if no worker of the group is active. A worker that finishes takes the next event itself, as long as fewer than 1 + thread_pool_oversubscribe (1 + 3) workers are active. Six connections can run on four threads, two listeners and two workers (Demo: pool, 6 connections on few threads).

Stalls and waits

One active worker per group has a weak spot: a long, CPU-heavy query keeps the worker active, and every other event of the group waits behind it. A separate timer thread checks each group every thread_pool_stall_limit (500 ms by default). If a group's queue has events but nothing was taken from it since the last check, the group is stalled, and the timer wakes or creates one more worker. Because the check looks at a whole interval, a stall is noticed between one and two stall limits after it starts: in the demo the 2 s report starts at 0 s, the check at 0.5 s still sees the logins taken from the queue, and the check at 1.0 s adds a worker for C2 (Demo: pool, a CPU query stalls the group).

A query that waits is different. Before a thread sleeps on a row lock, a metadata lock, disk I/O or SELECT SLEEP(), MariaDB calls thd_wait_begin(). The worker stops counting as active, and if events are queued the group starts another worker at once, with no stall (Demo: pool, SLEEP lets another worker run).

Two timelines in one thread group. Left: C1 runs a CPU report; C2's SELECT waits in the queue; the check at 0.5 s still sees progress, and the check at 1.0 s sees a stall and adds a worker. Right: C1 runs SELECT SLEEP(1), which calls thd_wait_begin, so C2's SELECT gets a second worker at once at 0 s.
A CPU-bound query makes the queue wait for the stall check; a waiting query hands over at once.

Lower thread_pool_stall_limit if short queries suffer behind long ones; thread_pool_max_threads caps the total number of pool threads, and idle workers exit after thread_pool_idle_timeout (60 s).

Background threads

Not every thread serves a connection. mysqld also runs a signal handler thread, the pool timer (with the pool), and InnoDB's own workers: the page cleaner buf_flush_page_cleaner that writes dirty pages, and InnoDB's internal task pool that runs purge (innodb_purge_threads), the completion of asynchronous reads and writes (innodb_read_io_threads, innodb_write_io_threads) and timer tasks such as the master callback and statistics updates. They exist in both thread_handling modes, are not counted in Threads_connected, and appear in performance_schema.threads with type BACKGROUND. See the InnoDB buffer pool for what the page cleaner does.

Which one to use

one-thread-per-connectionpool-of-threads
threadsone per connection (plus the cache)about one active per group, more on stalls and waits
idle connection costsa thread and its stacka socket in epoll
good forfew, busy connections; long queriesthousands of mostly idle or short-query connections
riskthousands of threads thrash the CPUlong CPU queries wait for the stall check

With the pool on, an administrator can still reach a busy server through extra_port, whose connections (up to extra_max_connections) always get a thread of their own. See also Thread Pool for the general pattern, Connection Pool for the pool on the application side, Prefork vs Threads vs Events for the same choice in web servers, and How MySQL Runs a Query for what the thread does with the query.

What the page leaves out

In MariaDB a pool thread's role is not fixed: an idle worker becomes the listener when a group has none, and the listener runs an event itself when no worker is active. The page draws one fixed listener per group, starts it with the group's first connection, and draws at most four workers per group. It leaves out the high-priority queue (thread_pool_priority, used for connections inside a transaction), thread_pool_dedicated_listener, the throttling of new workers, the Windows thread pool, thread_stack and per-thread buffers, TLS, the max_user_connections and per-account limits, the event scheduler and replication threads, and the real timing of queries.