MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0 Thread Pool Operation

The thread pool consists of a number of thread groups, each of which manages a set of client connections. As connections are established, the thread pool assigns them to thread groups in round-robin fashion.

The thread pool exposes system variables that may be used to configure its operation:

To configure the number of thread groups, use the thread_pool_size system variable. The default number of groups is 16. For guidelines on setting this variable, see Section, “Thread Pool Tuning”.

The maximum number of threads per group is 4096 (or 4095 on some systems where one thread is used internally).

The thread pool separates connections and threads, so there is no fixed relationship between connections and the threads that execute statements received from those connections. This differs from the default thread-handling model that associates one thread with one connection such that a given thread executes all statements from its connection.

By default, the thread pool tries to ensure a maximum of one thread executing in each group at any time, but sometimes permits more threads to execute temporarily for best performance:

Here are examples of conditions under which a thread group might have multiple threads started to execute statements:

The thread pool is designed to be scalable across an increasing number of connections. It is also designed to avoid deadlocks that can arise from limiting the number of actively executing statements. It is important that threads that do not report back to the thread pool do not prevent other statements from executing and thus cause the thread pool to become deadlocked. Examples of such statements follow:

In each case, to prevent deadlock, the statement is moved to the stalled category when it does not complete quickly, so that the thread group can permit another statement to begin executing. With this design, when a thread executes or becomes blocked for an extended time, the thread pool moves the thread to the stalled category and for the rest of the statement's execution, it does not prevent other statements from executing.

The maximum number of threads that can occur is the sum of max_connections and thread_pool_size. This can happen in a situation where all connections are in execution mode and an extra thread is created per group to listen for more statements. This is not necessarily a state that happens often, but it is theoretically possible.

Privileged Connections

When the limit defined by thread_pool_max_transactions_limit has been reached, new connections appear to hang until one or more existing transactions are completed. The same occurs when attempting to start a new transaction on an existing connection. If existing connections are blocked or long-running, the only way to access the server is using a privileged connection.

To establish a privileged connection, the user initiating the connection must have the TP_CONNECTION_ADMIN privilege. A privileged connection ignores the limit defined by thread_pool_max_transactions_limit and permits connecting to the server to increase the limit, remove the limit, or kill running transactions. TP_CONNECTION_ADMIN privilege must be granted explicitly. It is not granted to any user by default.

A privileged connection can execute statements and start transactions, and is assigned to a thread group designated as the Admin thread group.

When querying the performance_schema.tp_thread_group_stats table, which reports statistics per thread group, Admin thread group statistics are reported in the last row of the result set. For example, if SELECT * FROM performance_schema.tp_thread_group_stats\G returns 17 rows (one row per thread group), the Admin thread group statistics are reported in the 17th row.