Connection manager threads handle client connection requests on the network interfaces that the server listens to. On all platforms, one manager thread handles TCP/IP connection requests. On Unix, this manager thread also handles Unix socket file connection requests. On Windows, a manager thread handles shared-memory connection requests, and another handles named-pipe connection requests. The server does not create threads to handle interfaces that it does not listen to. For example, a Windows server that does not have support for named-pipe connections enabled does not create a thread to handle them.

The original thread model for handing client connections is that connection manager threads associate each client connection with a thread dedicated to it that handles authentication and request processing for that connection. Manager threads create a new thread when necessary but try to avoid doing so by consulting the thread cache first to see whether it contains a thread that can be used for the connection. When a connection ends, its thread is returned to the thread cache if the cache is not full.

In the original connection thread model, there are as many threads as there are clients currently connected, which has some disadvantages when server workload must scale to handle large numbers of connections. For example, thread creation and disposal becomes expensive. Also, each thread requires server and kernel resources, such as stack space. To accommodate a large number of simultaneous connections, the stack size per thread must be kept small, leading to a situation where it is either too small or the server consumes large amounts of memory. Exhaustion of other resources can occur as well, and scheduling overhead can become significant.

As of MySQL 6.0.4, an alternative thread model is available for dealing with the preceding issues that occur when scaling to large numbers of simultaneous connections. This model uses thread pooling:

Thread pooling works best if the normal workload consists mostly of lightweight queries, with no more than a few simultaneous complex long-running statements.

Thread pooling is less suitable when you have N threads in the pool but it is possible that all or most of the threads will be tied up running complex queries simultaneously. If this happens, the workload from other clients will be stalled. Configuring the server to use a pool larger than the maximum number of simultaneous complex queries will help in this case.

Thread pooling is based on the libevent library. To build a server that includes the thread-pool capability, configure MySQL using the --with-libevent option. The --without-libevent option excludes the libevent code. See Section 2.9.2, “Typical configure Options”.

To control which thread model the server uses to manage threads that handle client connections and to monitor thread use, several system and status variables are relevant. (See Section 5.1.3, “Server System Variables”, and Section 5.1.6, “Server Status Variables”.)

At server startup, the thread_handling system variable controls which thread model is used. --thread_handling=one-thread-per-connection is the default and selects the model that allocates one thread per connected client. To use thread pooling, start the server with --thread_handling=pool-of-threads.

When the server uses one thread per client connection, it uses the thread cache, which has a size determined by the thread_cache_size system variable. The default value is 0 (no caching), which causes a thread to be set up for each new connection and disposed of when the connection terminates. Set thread_cache_size to N to allow N inactive connection threads to be cached. thread_cache_size can be set at server startup or changed while the server runs. A connection thread becomes inactive when the client connection with which it was associated terminates.

To monitor the number of threads in the cache and how many threads have been created because a thread could not be taken from the cache, monitor the Threads_cached and Threads_created status variables. (See Section 5.1.6, “Server Status Variables”.)

When the server uses thread pooling, thread_pool_size controls the maximum number of requests that the server should be allowed to process simultaneously. The pool has a fixed size over the lifetime of server execution, but the size can be specified at startup. The default pool size is 20 threads. Starting the server with --thread_pool_size=N sets the size to N threads, up to a maximum of 16,384.

With thread pooling enabled, InnoDB concurrency control becomes unnecessary, so you can disable InnoDB thread concurrency by setting innodb_thread_concurrency to 0.

Regardless of thread model, you can set max_connections at server startup or at runtime to control the maximum number of clients that can connect simultaneously.

When the thread stack is too small, this limits the complexity of the SQL statements which the server can handle, the recursion depth of stored procedures, and other memory-consuming actions. To set a stack size of N bytes for each thread, start the server with --thread_stack=N.