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.