Thread pool in MariaDB 5.5

Problem that thread pools solve

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

What is new in MariaDB threadpool in 5.5

MariaDB has had the option of running server threads in a pool since version 5.1. The main drawback of the previous solution was that this pool was static, it had a fixed number of threads. Static thread pools can have their merits, for some limited use cases, like 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. For example, they each depend on the others’ completion, and they block on different locks and IO. Thus it is very hard, and sometimes impossible, to predict how many threads would be ideal or even sufficient to prevent deadlocks in every situation. MariaDB 5.5 implements a dynamic/adaptive pool that itself takes care of creating new threads in 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 the following goals:

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

There are currently two different low-level implementations – depending on OS – one for Windows which utilizes Windows-native threadpool, and one generic implementation for Unix-like systems. Because the implementations are different, some tuning parameters differ between Windows and Unix. For example, thread_pool_size variable does not exist on Windows, and threadpool_min_threads is not available on Unix.

When to use threadpool

Threadpools are most efficient in situations where queries are relatively short and the load is CPU bound (OLTP workloads). If the workload is not CPU bound, you might still want to limit the number of threads to save memory for the database memory buffers.

When threadpool is less efficient

There are special cases where threadpool is likely to be less efficient.

• very bursty workload (long periods of inactivity mixed with short periods of high activity by many users), and also you cannot tolerate delays coming from thread creation throttling. Even in this situation, performance can be improved by tweaking the way threads are retired, e.g. thread_pool_idle_timeout on Unix, or thread_pool_min_threads on Windows.

• many concurrent, long, non-yielding queries. Non-yielding here means that a thread never waits while executing a query, or does not indicate waits to threadpool. Such workloads are mostly used in data warehouse scenarios. In this case long running queries will delay execution of other queries, and the delay is fixed only with stall detection/preemption mechanism (s. description thread_pool_stall_limit). You can still connect trough the extra-port TCP/IP port.

• you rely on the fact that simple queries always finish quickly, no matter how loaded you database server is. With loaded threadpool, queries might be queued for later execution, such that even a simple SELECT 1, might take more time than with thread-per-connection.

Using threadpool scheduler

On Unix, 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. If you want to use one thread per connection add thread_handling=one-thread-per-connection to the my.cnf config file.

Threadpool server variables

Generally there is no need to tweak the parameters, since the goal was to provide good performance out-of-the box. We encourage submitting bug reports if the defaults do not appear to work well in your specific situation. Still, the parameters are not hardcoded and we expose as much as the underlying implementation allows, so you can tweak them as you see fit. All of the server variables documented below are dynamic, meaning they can be changed on a running server.

Optimizing server variables on Windows

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

• thread_pool_min_threads – The 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 – The maximum number of threads in the pool. Threads are not created when this value is reached. The default is 500. This parameter can be used to prevent the 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). New threads are created if a blocking situation occurs (such as after a throttling interval), but sometimes you want to cap the number of threads, if you’re familiar with the application and need to, for example, 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 Unix

The following variables are exposed by the underlying implementation

• thread_pool_size – The number of thread groups. The default is the number of processors. This is the parameter with the most visible performance effect. It is roughly equivalent to the 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 it to a lower value could be running the server on dedicated processors (e.g. with the taskset utility on Linux).

Increase the value if you find that, despite your workload being CPU-bound, CPUs are still underutilized.

• thread_pool_stall_limit – The number of milliseconds before a running thread is considered stalled. The default is 500. Threadpool will wake up or create another thread if this limit is reached. This is the preemption mechanism that prevents a long-running query from monopolizing the pool, and temporarily allowing several queries to run in parallel. Note that once the number of threads reaches thread_pool_max_threads, no further threads are created, not even in cases of stalls longer than thread_pool_stall_limit.

• thread_pool_max_threads – The maximum number of threads in the pool. New threads are not created when this value is reached. The default is 500. This variable has the same meaning as on Windows.

• thread_pool_idle_timeout – The number of seconds before an idle worker thread exits. The default is 60. If there is currently no work to do, how long should an idle thread wait before exiting?

• thread_pool_oversubscribe – This is an internal parameter. Change at your own risk. The default is 3. Here is a approximate description of the behavior of this parameter: there is a tradeoff between letting more than 1 runnable thread per CPU versus putting a thread into sleep and awake almost instantly after it. The higher the value for this parameter, the more threads that could run at the same time. A lower value can lead to more sleeps and wakeups.

Monitoring pool activity

Currently there are two status variables exposed to monitor pool activity.

Troubleshooting blocking situations

Even with thread_pool_max_threads set high (recall the default for it is 500), if global locks are involved it is possible to block the entire pool. Imagine a situation where one client performs a FLUSH TABLES WITH READ LOCK and then pauses. If 500 other clients start a write operation, the maximum number of threads allowed in the pool is reached and then it is impossible to UNLOCK TABLES anymore. One way to solve the problem would be to set thread_pool_max_threads to a very high value, but this might be less than ideal, and negatively affect performance.

To workaround such a situation, MariaDB permits you to use a dedicated admin connection. To allow a dedicated admin connection, set the extra_port server variable to the TCP port for this connection (this must be different from the usual port), and connect using this port as a superuser. Once connected, you can either increase thread_pool_max_threads or kill the offending connection – in the example above that would be the connection that holds the global lock (such a connection would be in the 'sleep' state).

MariaDB threadpool vs Oracle MySQL Enterprise Threadpool

Commercial editions of MySQL since 5.5 include an Oracle MySQL Enterprise threadpool implemented as a plugin, which delivers similar functionality. Official documentation of the feature can be found in the MySQL Reference Manual and a detailed discussion about the design of the feature is at Mikael Ronstrom's blog. Here is the summary of similarities and differences, based on the above materials.

Similarities

• On Unix, both MariaDB and Oracle MySQL Enterprise Threadpool will partition client connections into groups. The thread_pool_size parameter thus has the same meaning for both MySQL and MariaDB.

• Both implementations use similar schema checking for thread stalls, and both have the same parameter name for thread_pool_stall_limit (though in MariaDB it is measured using millisecond units, not 10ms units like in Oracle MySQL).

Differences

• The Windows implementation is completely different – MariaDB's uses native Windows threadpooling, while Oracle's implementation includes a convenience function WSAPoll() (provided for convenience to port Unix applications). As a consequence of relying on WSAPoll(), Oracle's implementation will not work with named pipes and shared memory connections.

• MariaDB uses the most efficient I/O multiplexing facilities for each operating system: Windows (the I/O completion port is used internally by the native threadpool), Linux (epoll), Solaris (event ports), FreeBSD and OSX (kevent). Oracle uses optimized I/O multiplexing only on Linux, with epoll, and uses poll() otherwise.

• Unlike Oracle MySQL Enterprise Threadpool, MariaDB's threadpool does not try to minimize the number of concurrent transactions.

• Unlike Oracle MySQL Enterprise Threadpool, MariaDB's one is builtin, not a plugin.

Threadpool internals

Low-level implementation details are documented in the WL#246

Running benchmarks

When running sysbench and maybe other benchmarks, that create many threads on the same machine as the server, it is advisable to run benchmark driver and server on different CPUs to get the realistic results. Running lots of driver threads and only several server threads on the same CPUs will have the effect that OS scheduler will schedule benchmark driver threads to run with much higher probability than the server threads, that is driver will preempt the server. Use "taskset –c" on Linuxes, and "set /affinity" on Windows to separate benchmark driver and server CPUs, as the preferred method to fix this situation.

A possible alternative on Unix (if taskset or a separate machine running the benchmark is desired for some reason) would be to increase thread_pool_size to make the server threads more "competitive" against the client threads.

Best of both worlds (running with pool-of-threads AND with one-thread-per-connection)

One issue with pool-of-threads is that if all worker threads are doing work (like running long queries) or are locked by a row/table lock no new connections can be established and you can't login and find out what's wrong or login and kill queries.

To help this, we have introduced two new options for mysqld:

--extra-port=#             (Default 0)
--extra-max-connections=#  (Default 1)

If extra-port is <> 0 then you can connect max_connections number of normal threads + 1 extra SUPER user through the 'extra-port' TCP/IP port. These connections use the old one-thread-per-connection method.

To connect with through the extra port, use:

mysql --port='number-of-extra-port' --protocol=tcp

This allows you to freely use, on connection bases, the optimal connection/thread model.

Notes

The thread_cache_size variable is not used when the thread pool is used and the Threads_cached status will have a value of 0.