thread_handling

thread_handling is a MySQL system variable that determines how the server manages client connection threads. It specifies the thread-handling model that the server uses to process multiple client connections concurrently.

Releem automatically tunes thread_handling and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding of this variable by reading our documentation.

In computing, a thread is the smallest unit of CPU execution, allowing programs to perform multiple tasks simultaneously. In MySQL, threads manage individual operations, enabling efficient multitasking. Client connections refer to the links between the MySQL server and its clients, be it applications, users, or other databases. Each connection corresponds to a separate thread on the server.

The thread_handling variable in MySQL dictates how these threads are managed. Each client connection spawns a new thread, so in high-traffic environments, a server might handle thousands of threads concurrently.

There are different thread-handling strategies available. These are all the possible values for thread handling:

• one-thread-per-connection – Each client connection is managed by its dedicated thread. Best suited for environments with moderate traffic where individual threads for each connection won't hinder performance.
• no-threads – The server does not create any threads. This is mainly used for debugging purposes. Primarily used for debugging purposes to isolate issues without the interference of multiple threads.
• loaded-dynamically (MySQL only) – Threads are dynamically loaded based on the server's requirements. Ideal for high-traffic environments or situations with variable traffic patterns, allowing the server to adjust the number of threads based on demand.
• pool-of-threads (MariaDB and Percona)

MariaDB and Percona do not support the loaded-dynamically thread handling strategy. Given the complexity and the potential for deadlocks and other issues when threads depend on each other or block each other via locks and I/O operations, dynamically loading or unloading the thread pool at runtime could lead to instability and unpredictable behavior.

Instead, MariaDB (starting from version 5.5) and Percona opted for a reimplementation of the legacy pool-of-threads scheduler with several key goals:

• Dynamic Sizing – The thread pool automatically grows and shrinks based on demand, adapting to the workload.
• Minimized Overhead – The implementation aims to minimize the overhead of maintaining the thread pool itself.
• Optimal Use of OS Capabilities – It leverages native OS capabilities where available, utilizing the best available methods for I/O multiplexing.
• Resource Limitation – The implementation aims to limit the resources used by threads, ensuring efficient resource utilization.

Command Line Configuration:
mysqld> set global thread_handling = XX

Replace XX with the thread-handling strategy that suits your database needs. Must restart MySQL server to see changes applied.

To verify that the variable has been changed:

mysqld> show global variables like ‘thread_handling’

Configuration File:
[mysqld]
thread_handling variable = XX

Replace XX with the thread-handling strategy that suits your database needs. Must restart MySQL server to see changes applied.

When considering the usage and adjustment of the thread_handling variable, it's important to understand the potential impact on system performance. Here are a few key considerations to keep in mind:

• Server Load – If your server typically handles a large number of simultaneous connections, the one-thread-per-connection strategy might lead to excessive context switching, affecting performance. In such cases, consider other thread-handling strategies.

• Hardware – The optimal thread-handling strategy might vary based on the server's underlying hardware, especially the number of CPU cores.

• Compatibility – Ensure that the chosen thread-handling strategy is compatible with other server configurations and any third-party tools or plugins you might be using.