About Pool of Threads

This is an extended version of the pool-of-threads code from MySQL 6.0. This allows you to use a limited set of threads to handle all queries, instead of the old 'one-thread-per-connection' style. In recent times, its also been referred to as "thread pool" or "thread pooling" as this feature (in a different implementation) is available in Enterprise editions of MySQL (not in the Community edition).

This can be a very big win if most of your queries are short running queries and there are few table/row locks in your system.

Instructions

To enable pool-of-threads you must first run configure with the--with-libevent option. (This is automatically done if you use any 'max' scripts in the BUILD directory):

When starting mysqld with the pool of threads code you should use:

Default values are:

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; and :

If 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:

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

See also

_{This page is licensed: CC BY-SA / Gnu FDL}

Problems 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.

This article does not apply to the thread pool implementation on Windows. On Windows, MariaDB uses a native thread pool created with the CreateThreadpool APl, which has its own methods to distribute threads between CPUs.

On Unix, the thread pool implementation uses objects called thread groups to divide up client connections into many independent sets of threads. The thread_pool_size system variable defines the number of thread groups on a system. Generally speaking, the goal of the thread group implementation is to have one running thread on each CPU on the system at a time. Therefore, the default value of the thread_pool_size system variable is auto-sized to the number of CPUs on the system.

When setting the thread_pool_size system variable's value at system startup, the max value is 100000. However, it is not a good idea to set it that high. When setting its value dynamically, the max value is either 128 or the value that was set at system startup--whichever value is higher. It can be changed dynamically with SET GLOBAL. For example:

It can also be set in a server option group in an option file prior to starting up the server. For example:

If you do not want MariaDB to use all CPUs on the system for some reason, then you can set it to a lower value than the number of CPUs. For example, this would make sense if the MariaDB Server process is limited to certain CPUs with the utility on Linux.

If you set the value to the number of CPUs and if you find that the CPUs are still underutilized, then try increasing the value.

The system variable tends to have the most visible performance effect. It is roughly equivalent to the number of threads that can run at the same time. In this case, run means use CPU, rather than sleep or wait. If a client connection needs to sleep or wait for some reason, then it wakes up another client connection in the thread group before it does so.

One reason that CPU underutilization may occur in rare cases is that the thread pool is not always informed when a thread is going to wait. For example, some waits, such as a page fault or a miss in the OS buffer cache, cannot be detected by MariaDB.

Distributing Client Connections Between Thread Groups

When a new client connection is created, its thread group is determined using the following calculation:

The connection_id value in the above calculation is the same monotonically increasing number that you can use to identify connections in output or the table.

This calculation should assign client connections to each thread group in a round-robin manner. In general, this should result in an even distribution of client connections among thread groups.

Types of Threads

Thread Group Threads

Thread groups have two different kinds of threads: a listener thread and worker threads.

• A thread group's worker threads actually perform work on behalf of client connections. A thread group can have many worker threads, but usually, only one will be actively running at a time. This is not always the case. For example, the thread group can become oversubscribed if the thread pool's timer thread detects that the thread group is stalled. This is explained more in the sections below.

• A thread group's listener thread listens for I/O events and distributes work to the worker threads. If it detects that there is a request that needs to be worked on, then it can wake up a sleeping worker thread in the thread group, if any exist. If the listener thread is the only thread in the thread group, then it can also create a new worker thread. If there is only one request to handle, and if the system variable is not enabled, then the listener thread can also become a worker thread and handle the request itself. This helps decrease the overhead that may be introduced by excessively waking up sleeping worker threads and excessively creating new worker threads.

Global Threads

The thread pool has one global thread: a timer thread. The timer thread performs tasks, such as:

• Checks each thread group for stalls.

• Ensures that each thread group has a listener thread.

Thread Creation

A new thread is created in a thread group in the scenarios listed below.

In all of the scenarios below, the thread pool implementation prefers to wake up a sleeping worker thread that already exists in the thread group, rather than to create a new thread.

Worker Thread Creation by Listener Thread

A thread group's listener thread can create a new worker thread when it has more client connection requests to distribute, but no pre-existing worker threads are available to work on the requests. This can help to ensure that the thread group always has enough threads to keep one worker thread active at a time.

A thread group's listener thread creates a new worker thread if all of the following conditions are met:

• The listener thread receives a client connection request that needs to be worked on.

• There are more client connection requests in the thread group's work queue that the listener thread still needs to distribute to worker threads, so the listener thread should not become a worker thread.

• There are no active worker threads in the thread group.

• There are no sleeping worker threads in the thread group that the listener thread can wake up.

Thread Creation by Worker Threads During Waits

A thread group's worker thread can create a new worker thread when the thread has to wait on something, and the thread group has more client connection requests queued, but no pre-existing worker threads are available to work on them. This can help to ensure that the thread group always has enough threads to keep one worker thread active at a time. For most workloads, this tends to be the primary mechanism that creates new worker threads.

A thread group's worker thread creates a new thread if all of the following conditions are met:

• The worker thread has to wait on some request. For example, it might be waiting on disk I/O, or it might be waiting on a lock, or it might just be waiting for a query that called the function to finish.

• There are no active worker threads in the thread group.

• There are no sleeping worker threads in the thread group that the worker thread can wake up.

• And one of the following conditions is also met:

Listener Thread Creation by Timer Thread

The thread pool's timer thread can create a new listener thread for a thread group when the thread group has more client connection requests that need to be distributed, but the thread group does not currently have a listener thread to distribute them. This can help to ensure that the thread group does not miss client connection requests because it has no listener thread.

The thread pool's timer thread creates a new listener thread for a thread group if all of the following conditions are met:

• The thread group has not handled any I/O events since the last check by the timer thread.

• There is currently no listener thread in the thread group. For example, if the system variable is not enabled, then the thread group's listener thread can became a worker thread, so that it could handle some client connection request. In this case, the new thread can become the thread group's listener thread.

• There are no sleeping worker threads in the thread group that the timer thread can wake up.

• And one of the following conditions is also met:

Worker Thread Creation by Timer Thread during Stalls

The thread pool's timer thread can create a new worker thread for a thread group when the thread group is stalled. This can help to ensure that a long query can't monopole its thread group.

The thread pool's timer thread creates a new worker thread for a thread group if all of the following conditions are met:

• The timer thread thinks that the thread group is stalled. This means that the following conditions have been met:

  • There are more client connection requests in the thread group's work queue that the listener thread still needs to distribute to worker threads.

  • No client connection requests have been allowed to be dequeued to run since the last stall check by the timer thread.

• There are no sleeping worker threads in the thread group that the timer thread can wake up.

Thread Creation Throttling

In some of the scenarios listed above, a thread is only created within a thread group if no new threads have been created for the thread group within the throttling interval. The throttling interval depends on the number of threads that are already in the thread group.

In and later, thread creation is not throttled until a thread group has more than 1 + threads:

The throttling factor is calculated like this (see for more information):

Thread Group Stalls

The thread pool has a feature that allows it to detect if a client connection is executing a long-running query that may be monopolizing its thread group. If a client connection were to monopolize its thread group, then that could prevent other client connections in the thread group from running their queries. In other words, the thread group would appear to be stalled.

This stall detection feature is implemented by creating a timer thread that periodically checks if any of the thread groups are stalled. There is only a single timer thread for the entire thread pool. The system variable defines the number of milliseconds between each stall check performed by the timer thread. The default value is 500. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

The timer thread considers a thread group to be stalled if the following is true:

• There are more client connection requests in the thread group's work queue that the listener thread still needs to distribute to worker threads.

• No client connection requests have been allowed to be dequeued to run since the last stall check by the timer thread.

This indicates that the one or more client connections currently using the active worker threads may be monopolizing the thread group, and preventing the queued client connections from performing work. When the timer thread detects that a thread group is stalled, it wakes up a sleeping worker thread in the thread group, if one is available. If there isn't one, then it creates a new worker thread in the thread group. This temporarily allows several client connections in the thread group to run in parallel.

The system variable essentially defines the limit for what a "fast query" is. If a query takes longer than , then the thread pool is likely to think that it is too slow, and it will either wake up a sleeping worker thread or create a new worker thread to let another client connection in the thread group run a query in parallel.

In general, changing the value of the system variable has the following effect:

• Setting it to higher values can help avoid starting too many parallel threads if you expect a lot of client connections to execute long-running queries.

• Setting it to lower values can help prevent deadlocks.

Thread Group Oversubscription

If the timer thread were to detect a stall in a thread group, then it would either wake up a sleeping worker thread or create a new worker thread in that thread group. At that point, the thread group would have multiple active worker threads. In other words, the thread group would be oversubscribed.

You might expect that the thread pool would shutdown one of the worker threads when the stalled client connection finished what it was doing, so that the thread group would only have one active worker thread again. However, this does not always happen. Once a thread group is oversubscribed, the system variable defines the upper limit for when worker threads start shutting down after they finish work for client connections. The default value is 3. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

To clarify, the system variable does not play any part in the creation of new worker threads. The system variable is only used to determine how many worker threads should remain active in a thread group, once a thread group is already oversubscribed due to stalls.

In general, the default value of 3 should be adequate for most users. Most users should not need to change the value of the system variable.

_{This page is licensed: CC BY-SA / Gnu FDL}

This article describes the system and status variables used by the MariaDB thread pool. For a full description, see Thread Pool in MariaDB.

System variables

extra_max_connections

• Description: The number of connections on the .

  • See for more information.

• Command line: --extra-max-connections=#

• Scope: Global

extra_port

• Description: Extra port number to use for TCP connections in a one-thread-per-connection manner. If set to 0, then no extra port is used.

  • See for more information.

• Command line: --extra-port=#

thread_handling

• Description: Determines how the server handles threads for client connections. In addition to threads for client connections, this also applies to certain internal server threads, such as . On Windows, if you would like to use the thread pool, then you do not need to do anything, because the default for the thread_handling system variable is already preset to pool-of-threads.

  • When the default one-thread-per-connection mode is enabled, the server uses one thread to handle each client connection.

  • When the pool-of-threads

thread_pool_dedicated_listener

• Description: If set to 1, then each group will have its own dedicated listener, and the listener thread will not pick up work items. As a result, the queueing time in the and the actual queue size in the table will be more exact, since IO requests are immediately dequeued from poll, without delay.

  • This system variable is only meaningful on Unix.

• Command line: thread-pool-dedicated-listener={0|1}

thread_pool_exact_stats

• Description: If set to 1, provides better queueing time statistics by using a high precision timestamp, at a small performance cost, for the time when the connection was added to the queue. This timestamp helps calculate the queuing time shown in the table.

  • This system variable is only meaningful on Unix.

• Command line: thread-pool-exact-stats={0|1}

thread_pool_idle_timeout

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

  • This system variable is only meaningful on Unix.

  • The system variable is comparable for Windows.

thread_pool_max_threads

• Description: The maximum number of threads in the . Once this limit is reached, no new threads will be created in most cases.

  • On Unix, in rare cases, the actual number of threads can slightly exceed this, because each needs at least two threads (i.e. at least one worker thread and at least one listener thread) to prevent deadlocks.

• Scope:

• Command line:

thread_pool_min_threads

• Description: Minimum number of threads in the . In bursty environments, after a period of inactivity, threads would normally be retired. When the next burst arrives, it would take time to reach the optimal level. Setting this value higher than the default would prevent thread retirement even if inactive.

  • This system variable is only meaningful on Windows.

  • The system variable is comparable for Unix.

thread_pool_oversubscribe

• Description: Determines how many worker threads in a thread group can remain active at the same time once a thread group is oversubscribed due to stalls. The default value is 3. Usually, a thread group only has one active worker thread at a time. However, the timer thread can add more active worker threads to a thread group if it detects a stall. There are trade-offs to consider when deciding whether to allow only one thread per CPU to run at a time, or whether to allow more than one thread per CPU to run at a time. Allowing only one thread per CPU means that the thread can have unrestricted access to the CPU while its running, but it also means that there is additional overhead from putting threads to sleep or waking them up more frequently. Allowing more than one thread per CPU means that the threads have to share the CPU, but it also means that there is less overhead from putting threads to sleep or waking them up.

  • See for more information.

thread_pool_prio_kickup_timer

• Description: Time in milliseconds before a dequeued low-priority statement is moved to the high-priority queue.

  • This system variable is only meaningful on Unix.

• Command line: thread-pool-kickup-timer=#

• Scope: Global

thread_pool_priority

• Description: priority. High-priority connections usually start executing earlier than low-priority. If set to 'auto' (the default), the actual priority (low or high) is determined by whether or not the connection is inside a transaction.

• Command line: --thread-pool-priority=#

• Scope: Global,Connection

• Data Type: enum

thread_pool_size

• Description: The number of in the , which determines how many statements can execute simultaneously. The default value is the number of CPUs on the system. When setting this system variable's value at system startup, the max value is 100000. However, it is not a good idea to set it that high. When setting this system variable's value dynamically, the max value is either 128 or the value that was set at system startup--whichever value is higher.

  • See for more information.

  • This system variable is only meaningful on Unix.

thread_pool_stall_limit

• Description: The number of milliseconds between each stall check performed by the timer thread. The default value is 500. Stall detection is used to prevent a single client connection from monopolizing a thread group. When the timer thread detects that a thread group is stalled, it wakes up a sleeping worker thread in the thread group, if one is available. If there isn't one, then it creates a new worker thread in the thread group. This temporarily allows several client connections in the thread group to run in parallel. However, note that the timer thread will not create a new worker thread if the number of threads in the thread pool is already greater than or equal to the maximum defined by the variable, unless the thread group does not already have a listener thread.

  • See for more information.

Status variables

Threadpool_idle_threads

• Description: Number of inactive threads in the . Threads become inactive for various reasons, such as by waiting for new work. However, an inactive thread is not necessarily one that has not been assigned work. Threads are also considered inactive if they are being blocked while waiting on disk I/O, or while waiting on a lock, etc.

  • This status variable is only meaningful on Unix.

• Scope: Global, Session

• Data Type:

Threadpool_threads

• Description: Number of threads in the . In rare cases, this can be slightly higher than , because each thread group needs at least two threads (i.e. at least one worker thread and at least one listener thread) to prevent deadlocks.

• Scope: Global, Session

• Data Type: numeric

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}