Preface

This blog begins by introducing an alternative and optional thread handling mechanism called MySQL Thread Pool, available in the MySQL Enterprise Edition. It then delves into the "Max Transaction Limit" feature that was added to MySQL Thread Pool in MySQL 8.0. The blog shows the recommended MySQL Thread pool configuration and proceeds to compare the performance of MySQL Thread Pool using the Max Transaction Limit feature against the default thread handling mechanism in MySQL using the recommended configuration. Some advice on tuning the configuration is then given, followed by some of the caveats associated with the Max Transaction Limit feature.

Introduction

The MySQL Thread Pool is an alternative and optional thread handling mechanism that is available in the MySQL Enterprise Edition. It is implemented as a server plugin but is not enabled by default. By default, the MySQL server utilizes the default connection handling mechanism.

MySQL has a default connection handling model called "one-thread-per-connection." In this model, when a user connects to the server, a dedicated OS thread is created for the connection. This thread executes all the queries from the user and sends back results until the user disconnects. However, as more users connect to the MySQL server, more OS threads are created and executed in parallel. Eventually, a limit is reached where adding more threads becomes inefficient. And in a highly concurrent environment, as the number of connections increases, the overall performance of MySQL degrades. A thread executes instructions until it needs to wait for something or until it has used up its time-slice provided by the OS scheduler. There are three things a thread might need to wait for: mutexes, database object locks, and IO. As the number of threads increase, even the CPU cache can become overwhelmed.

The MySQL Thread Pool serves as a solution to address the limitations of the default connection handling mechanism. The original design of the thread pool addressed the needs of MySQL 5.6, but as the MySQL server locking structures and algorithms have been improved over time the additional benefits provided by the original thread pool design have been reduced. The Max Transaction Limit feature in the MySQL 8.0 thread pool revitalizes the thread pool as a customer tool.

MySQL Thread Pool

The MySQL Thread Pool introduces a separation between user connections and threads. Unlike the previous model, each user connection no longer has its own dedicated OS thread to execute statements. Instead, the Thread Pool is composed of Thread Groups, with a default of 16 Thread Groups. User connection is assigned to Thread Group in a round-robin fashion. Each Thread Group manages a subset of user connections. Within each Thread Group, there are one or more threads responsible for executing queries received from the user connection assigned to that Thread Group.

By default, the thread pool aims to ensure that only the configured number of threads (by default only one) are executing within a Thread Group. However, in order to achieve optimal performance, the thread pool may optionally permit more threads to execute within a Thread Group.

The following figure illustrates MySQL client connection to a MySQL server with "Thread Pool" model

• Clients: A MySQL client is a command line tool or application API that communicates with MySQL server over the MySQL client-server protocol.

• Connection Request: A connect request from a client sent to the MySQL server.

• Receiver Thread: Incoming connection requests are queued and processed by the receiver thread one by one. The receiver thread assigns a Thread Group to a connection in a round-robin fashion.

• Query Worker Threads: Threads in a Thread Group that executes user queries.

• THD: A thread context data structure created for each user connection.

The thread pool is specifically designed to prevent performance degradation as the number of user connections grows.  The introduction of the "Max Transaction Limit" feature to the Thread Pool in MySQL 8.0 provides a way of limiting the number of transactions that are allowed to execute concurrently. On a heavily loaded system, limiting the number of concurrent transactions improves the overall throughput of the server by limiting the number of concurrent data locks and reducing the occurrence of deadlocks.

Max Transaction Limit

In a highly concurrent environment, apart from having to deal with excessive connections and threads, the MySQL server may also face the challenge of handling an excessive number of concurrent transactions. Each transaction typically holds locks on tables and rows until it is committed or rolled back.

When the system tries to handle more concurrent transactions than it can effectively manage, the system can appear to stop responding as transactions wait longer and longer for these locks. Consequently, the overall throughput (TPS) of the server decreases.

The Max Transaction Limit feature efficiently controls the number of concurrently executing transactions. It is enabled by setting a non-zero value to the system variable, thread_pool_max_transactions_limit. A non-zero value N indicates that the thread pool will not have more than N transactions concurrently executing at any given time.  When the Max Transaction Feature is enabled, The Max Transaction Limit can be changed dynamically without needing to restart the MySQL server. (Changing the Max Transaction Limit from a zero value to a non-zero value does, however, require the MySQL server to be restarted.)

When this feature is enabled, each thread group can execute a maximum of "thread_pool_max_transactions_limit divided by number of thread groups (thread_pool_size) " transactions concurrently. After executing a statement, the query worker thread selects the next statement from the same transaction (rather than picking a statement from a different connection's queue) to expedite transaction execution until the transaction is either committed or rolled back.

Note that as the Max Transaction Limit is divided equally amongst the thread groups, the Max Transaction Limit should be a multiple of the thread pool size. If the configured value for Max Transaction Limit is not a multiple of the thread pool size, i.e. the transaction limit per thread group thread_pool_max_transactions_limit divided by number of thread groups (thread_pool_size) is not an integer, this value is rounded up to the nearest integer.

Recommended Configuration for Thread Pool in MySQL 8.0

The following is the recommended initial configuration for the thread pool

This configuration can be further tuned according to the specific load requirements to achieve even better performance.

Let's compare the performance results of testing the thread pool using the Max Transaction Limit against the default thread handling mechanism. The MySQL thread pool configuration should be tuned depending on load to achieve the best performance for a given load, but the following results are obtained with the generic recommended configuration.

• The following graph compares the results of TPCC-100W (10 GB data) on a server using the default thread handling model with results on a server utilizing the Thread Pool and Max Transaction Limit feature.

  System Configuration:			Thread Pool Configuration:
  Processor	48cores-HT Intel(R) Xeon(R) Platinum 8268 CPU @ 2.90GHz.		thread_pool_size	48
  RAM	192GB		thread_pool_max_transaction_limit	512
  Storage	NVMe Optane 2 x 375 GB		thread_pool_algorithm	1 (high concurrency algorithm)
  OS	OL7.9 UEK6		thread_pool_query_worker_threads_per_group	2
  MySQL Version	8.0.34
  OpenSSL Version	1.1.1

 When utilizing MySQL thread pool, the rate of performance decline is much lower (better!) with a higher number of user connections compared to the rate of performance decline observed with the default connection handling. The default connection handling mechanism shows a decline in transactions per second after reaching 512 user connections, processing approximately 2000 transactions per second only above 4096 user connections. However, with the MySQL thread pool, good performance is maintained even with a higher number of user connections, allowing for the processing of around 17000 transactions per second above 4096 user connections. These results highlight the role of the MySQL thread pool in maintaining a higher level of performance when handling a larger number of concurrent connections.

Tuning advice

The recommended configuration is a reasonable place to start. The configuration that produces the best performance for a specific workload will depend upon the workload itself. Fortunately, the Max Transaction Limit can be varied dynamically, without having to restart the server.  Increase or decrease the value from this starting point whilst running a similar workload to that which you expect to encounter in production and compare that system throughput for different values of Max Transaction Limit. A reasonable upper bound for the Max Transaction Limit whilst testing is the maximum number of concurrent connections that you expect your system to have to handle (you may find it useful to consult the status variable Max_used_connections, as this variable records the maximum number of connections that have been in use simultaneously since the server started). Whilst tuning, you may find it useful to start the Max Transaction limit at this upper bound value and then adjust it downwards from this value whilst observing the effect on the throughput of your test workload.

The product of thread_pool_size and thread_pool_query_worker_threads_per_group is (roughly) the number of threads that will be available to process queries, and generally you will be compromising between having a low thread_pool_size with higher thread_pool_query_worker_threads_per_group or a higher thread_pool_size with a lower thread_pool_query_worker_threads_per_group.

The advantage of a higher thread_pool_query_worker_threads_per_group value is that it is less likely that all the threads in the thread group are simultaneously executing long running queries whilst blocking a shorter query (when your workload involves a mix of long running and short running queries).  However, the overhead of the connection polling operation for each thread group increases when using a smaller thread_pool_size with a higher thread_pool_query_worker_threads_per_group  value. Note that setting thread_pool_query_worker_threads_per_group less than 2 is very unlikely to improve performance.

The sysbench OLTP_RW  performance data shown provides an example of tuning the Max Transaction Limit value. In this example, the default thread handling model performance peaks at 64 concurrent users, so a Max Transaction Limit of 64 is chosen for this load.

• The following graph compares the results of Sysbench OLTP_RW 80 million rows in 1 table with a pareto access pattern (20 GB data) on a server using the default thread handling model with results on a server utilizing the Thread Pool and Max Transaction Limit feature. Note that using the pareto access pattern results in frequent access of a limited set of rows, leading to an increasing contention for data locks as the number of concurrent users increases.
   

  System Configuration:			Thread Pool Configuration:
  Processor	48cores-HT Intel(R) Xeon(R) Platinum 8268 CPU @ 2.90GHz.		thread_pool_size	32
  RAM	192GB		thread_pool_max_transaction_limit	64
  Storage	NVMe Optane 2 x 375 GB		thread_pool_algorithm	1 (high concurrency algorithm)
  OS	OL7.9 UEK6		thread_pool_query_worker_threads_per_group	2
  MySQL Version	8.0.34
  OpenSSL Version	1.1.1

When utilizing MySQL thread pool, performance is maintained with a higher number of user connections. In contrast, the default connection handling mechanism shows a decline in transactions per second after reaching 64 user connections, processing approximately only 900 transactions per second from 4096 user connections onwards. However, with the MySQL thread pool, performance is maintained even with a higher number of user connections, allowing for the processing of around 7000 transactions per second above 4096 user connections. These results demonstrate performance being maintained by the MySQL thread pool when handling a larger number of concurrent connections.

Max Transaction Limit Caveats

The Max Transaction Limit feature sets a hard limit on the maximum number of concurrent transactions/threads that can be executed. While it provides better performance, it also comes with certain caveats.

1. When the maximum number of transactions are executing concurrently, new connections and transactions/queries must wait until existing transactions are completed. No new threads are created to handle new requests. If all concurrent transactions consist of long-running queries, it may appear as if the MySQL system is stalled. To mitigate this issue, bypass the "Max Transaction Limit" using user connections with the "TP_ADMIN" privilege. Such privileged connections can be used to dynamically adjust the "Max Transaction Limit" or terminate one or more blocking queries to allow normal traffic to resume.
2. In the MySQL Thread Pool, each thread group can execute a maximum of thread_pool_max_transactions_limit divided by number of thread groups transactions concurrently. When this limit is reached due to long-running queries within a thread group, new connections and transactions/queries are put on hold until existing transactions are completed. It's important to note that new connections and transactions in such Thread Groups are not handled, even if the number of running transactions is lower than the Max Transaction Limit.

Note that when the Max Transaction Limit is in use on a system with multiple independent databases, queries against one database can be slowed down by queries against another database when the maximum number of concurrent transactions is reached. For many systems this scenario is not a concern, but it can be useful to be aware of this behaviour. On encountering this situation, a workaround is to (possibly temporarily) increase the Max Transaction Limit value (which can be done without restarting the server). Setting the Maximum Transaction Limit equal to the max_connections value removes the effect of the Maximum Transaction Limit feature but leaves it enabled so that its value can be modified subsequently without needing to restart the server.

Conclusion

Overall, the new MySQL Thread Pool provides substantial protection against performance degradation for MySQL servers handling highly concurrent systems with many concurrent transactions. By efficiently managing OS threads and transactions, utilizing the Max Transaction Limit feature, the MySQL Thread Pool preserves performance as the number of connections increases. It reduces resource contention, minimizes context switching, and optimizes CPU cache utilization. These optimizations result in improved overall performance and faster response times when under heavy load.
Customers running systems with many concurrent connections and high loads might encounter tipping points where the number transactions per second begins to drop as the load increases: this situation is where the thread pool and its Max Transaction Limit feature comes in handy to prevent the performance drop.

The following are some examples of scenarios encountered by customers where the new MySQL Thread Pool can be expected to improve upon previous levels of performance:

A customer currently in production typically has over 8000 persistent user connections. The server CPU usage generally remains under 50% since usually only a minority of the user connections are active. Problems arise when all (or just too many) user connections become active at once, creating a huge activity spike which dramatically slows the whole system, nearly bringing it down. Using the MySQL Thread Pool with a suitable Max Transaction Limit will protect the customer system from overload during such activity spikes and still allows the system to achieve the most efficient processing rate possible under these conditions.

Another customer with an online store encounters general periodic overload problems. The customer also encounters slow downs when running sales promotions on particular items as many concurrent updates are applied to the same small data set, resulting in long queues of queries fighting for various locks, wasting CPU cycles on lock spinning and creating additional system overload. This overload can also block or slow down other queries execution. Using the MySQL Thread Pool with a suitable Max Transaction Limit setting will be able to significantly lower system overload generally, and also brings overall system performance to the most efficient level possible.

References

1. MySQL Thread Pool documentation: MySQL documentation provides detailed information on how to configure and use the Thread Pool plugin.