Threadpool in 5.5

You are viewing an old version of this article. View the current version here.

What is the problem thread pools solve

The task of a scalable server software (and DBMS is an example of such software) is to maintain top performance with increasing number of clients. MySQL traditionally assigned a thread for every client connection, and as the number of concurrent users grows this model would show performance drops. Many active threads appear to be a performance killer, because increasing number of threads leads to extensive context switching and bad locality for CPU caches, and increased contention for hot locks. So the ideal solution that would help to reduce context switching is maintaining lower number of threads than number of client, but this number should not be too low either, since we also want to utilize CPUs to their fullest - ideally, there is a single active thread for each CPU on the machine.

What is new in MariaDB threadpool in 5.5

MariaDB has had option to run server threads in a pool since version 5.2. The main drawback of the previous solution was that this pool was static, i.e it had a fixed number of threads. Static thread pools can have their merits, for a limited cases where callbacks executed by pool threads never block and do not depend on each other (imagine something like an echo server). But DBMS clients are more complicated, they depend on each other’s completion, they block on different locks and IO, thus it is very hard to impossible to predict how many threads would be ideal of even sufficient to prevent deadlocks. 5.5 implements a dynamic/adaptive pool that itself takes care of creating new threads in the times of high demand and retiring threads if they have nothing to do. This is a complete reimplementation of the legacy pool-of-threads scheduler, with following goals:

  • Make the pool dynamic (grow and shrink whenever required)
  • Minimize the overhead required to maintain threadpool itself
  • Make best use of underlying OS capabilities (use native OS threadpool if provided, use best IO multiplexing method otherwise)

There are currently two different low-level implementation depending on OS – one for Windows which utilizes native threadpool, and one generic for *nix systems. Therefore some tuning parameters will differ from Windows to *nix.

Using threadpool scheduler

On *nix, add thread_handling=pool-of-threads to the my.cnf config file.

On Windows, the default for thread_handling is already preset to pool-of-threads, so nothing needs to be done here. NOTE (on older versions of Windows, such as XP and 2003, pool-of-threads is not implemented, and server will silently switch to using legacy thread-per-connection method)

Legacy scheduler is set with thread_handling= thread-per-connection in the config file.

Threadpool server variables

Generally there should be no need to tweak the parameters, since the goal was to provide good performance out-of-the box. We encourage submitting bug reports, if defaults do not appear to work well in your specific situation. Still, the parameters are not hardcoded and we expose as much as underlying implementation allows, so you can tweak them as you see fit at your own risk

Optimizing server variables on Windows

The native threadpool implementation allows setting minimum and maximum number of threads in the pool. Thus following variables are exposed:

  • thread_pool_min_threads - minimum number of threads in the pool. Default is 1. This applicable in a special case of very “bursty” workloads. Imagine having longer periods of inactivity after periods of high activity. While threadpool is idle, Windows would decide to retire pool threads (based on experimentation, this seems to happen after thread had been idle for 1 minute). Next time high load comes, it could take some milliseconds or seconds until threadpool size stabilizes again to optimal value. To avoid thread retirement, one could set the parameter to a higher value.
  • thread_pool_max_threads – maximum number of threads in the pool. Threads are not created when this value is reached. Default is 500. This parameter is can be used to prevent creation of new threads if the pool can have short periods where many or all clients are blocked (for example, with “FLUSH TABLES WITH READ LOCK”, high contention on row locks or similar). Pool does create new threads if blocking situation occur (after some throttling interval), but sometimes you’ll want to cap the number of threads, if you’re familiar with the application, e.g to save memory. If your application constantly pegs at 500 threads, it might be a strong indicator for high contention in the application, and threadpool does not help much.

Optimizing server variables on *nix:

Following variables are exposed by underlying implementation

  • thread_pool_size – number of thread groups. Default is number of processors. This is the parameter with the most visible performance effect. It is roughly equivalent to number of threads that can run at the same time (where run means use CPU, rather than sleep or wait). The implementation partitions all clients into groups, with the goal of having one runnable thread per group. One reason to change to a lower value it could be running the server on dedicated processors (e.g. with taskset utility on Linux) Increase the value if you find that despite your workload is CPU-bound, CPUs are still underutilized. (This should not happen in ideal world, but in this world it does. Pool does not currently have a full info on waits in the worker threads - some waits like page fault or miss in OS buffer cache cannot be detected, some things like detection of TCP related waits is currently not implemented)
  • thread_pool_stall_limit – number of milliseconds before a running thread is considered stalled. Default is 500. Threadpool will wake up or create another thread if this limit is reached. This is the preemption mechanism that prevents long-running query from monopolizing the pool, and temporarily allowing several queries to run in parallel.
  • thread_pool_idle_timeout- number of seconds before an idle worker thread exits. Default is 60 If there is currently no work to do, how long should an idle thread wait before exiting?
  • thread_pool_oversubscribe – internal parameter. Change at your own risk. Default is 3.

If this parameter is set higher, sometimes more threads can be allowed to run in parallel. There is a tradeoff Threadpool will wake up or create another thread if this limit is reached. between letting more than 1 runnable thread per CPU versus putting thread into sleep and awake almost instantly after it. With higher oversubscribe more threads could run at the same time. Lower oversubscribe value can lead to more sleeps and wakes.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.