MariaDB starting with 5.5
MariaDB 5.5 introduced an improved thread pool.
- Problem that thread pools solve
- MariaDB threadpool features
- When to use the thread pool
- When the thread pool is less efficient
- Using thread pool scheduler
- Thread pool server variables
- Priority scheduling
- Optimizing server variables on Windows
- Optimizing server variables on Unix
- Monitoring pool activity
- Troubleshooting blocking situations
- MariaDB thread pool vs Oracle MySQL Enterprise thread pool
- MariaDB thread pool vs Percona thread pool
- Thread pool internals
- Running benchmarks
- Best of both worlds (running with pool-of-threads AND with one-thread-per-connection)
- See also
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.
MariaDB threadpool features
MariaDB threadpool, in its current form, was implemented in version 5.5 and replaced the legacy threadpool that existed in MariaDB 5.1-5.3 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, primarily for use in Unix-like systems. Because the implementations are different, some tuning parameters differ between Windows and Unix. For example, the thread_pool_size variable does not exist on Windows, and thread_pool_min_threads is not available on Unix.
When to use the thread pool
Thread pools 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 the thread pool is less efficient
There are special, rare cases where the thread pool 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 the thread pool. 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/pre-emption mechanism (s. description thread_pool_stall_limit). You can still connect through 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 thread pool, queries might be queued for later execution, such that even a simple SELECT 1, might take more time than with thread-per-connection.
Using thread pool 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.
On older versions of Windows, such as XP and 2003, pool-of-threads is not implemented, and the server will silently switch to using the legacy one-thread-per-connection method.
Thread pool 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.
Starting with 10.2.2, it is possible to control connection prioritization. The priority behavior is controlled by the thread_pool_priority variable.
By default (thread_pool_priority=auto), queries would be given a higher priority, in case the current connection is inside a transaction. This allows the running transaction to finish faster, and has the effect of lowering the number of transactions running in parallel. The default setting will generally improve throughput for transactional workloads. But it is also possible to explicitly set the priority for the current connection to either 'high' or 'low'.
There is also a mechanism in place to ensure that higher priority connections are not monopolizing the worker threads in the pool (which would cause indefinite delays for low priority connections). On Unix, low priority connections are put into the high priority queue after the timeout specified by the thread_pool_kickup_timer parameter.
Optimizing server variables on Windows
See Thread Pool System and Status Variables for full details of each.
The native thread pool 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 the thread pool 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 the thread pool 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 from MariaDB 5.5 to 10.0 is 500 (this has been increased to 1000 in MariaDB 10.1). 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 the thread pool does not help much.
Optimizing server variables on Unix
See Thread Pool System and Status Variables for full details of each.
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.
CPU underutilization should not happen in an ideal world, but in the real world it does. The thread pool is usually informed when a thread is going to wait, however some waits, like a page fault or a miss in the OS buffer cache, cannot be detected. Detection of network I/O related waits is only available for MariaDB 10.0.2 and later (not implemented in 5.5 due to differences in networking code)
- thread_pool_stall_limit – The number of milliseconds before a running thread is considered stalled. The default is 500. The thread pool will wake up or create another thread if this limit is reached. This is the pre-emption 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 from MariaDB 5.5 to 10.0 is 500 (this has been increased to 1000 in MariaDB 10.1). 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.
|threadpool_threads||Number of threads in the pool|
|threadpool_idle_threads||Number of inactive threads in the pool. Only meaningful on Unixes, not tracked on Windows. Threads become idle either by waiting for new work, or if it's blocked due to disk io, row or table lock etc.|
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 any more. 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 work around 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 thread pool vs Oracle MySQL Enterprise thread pool
Commercial editions of MySQL since 5.5 include an Oracle MySQL Enterprise thread pool 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.
- 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).
- 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 one is builtin, not a plugin.
MariaDB thread pool vs Percona thread pool
Percona's implementation is a port of the MariaDB's threadpool with some added features. In particular, Percona added priority scheduling to its 5.5-5.7 releases. MariaDB 10.2 and Percona priority scheduling work in a similar fashion, but there are some differences in details.
- MariaDB's 10.2 thread_pool_priority=auto,high, low correspond to Percona's thread_pool_high_prio_mode=transactions,statements,none
- Percona has a thread_pool_high_prio_tickets connection variable to allow every nth low priority query to be put into the high priority queue. MariaDB does not have corresponding settings.
- MariaDB has a thread_pool_prio_kickup_timer setting, which Percona does not have.
Thread pool internals
Low-level implementation details are documented in the WL#246
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 pre-empt 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 not desired for some reason) would be to increase thread_pool_size to make the server threads more "competitive" against the client threads.
When running sysbench, a good rule of thumb could be to give 1/4 of all CPUs to the sysbench, and 3/4 of CPUs to mysqld. It is also good idea to run sysbench and mysqld on different NUMA nodes, if possible.
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.