MySQL - Why Do We Need a Thread Pool?

MySQL - Why Do We Need a Thread Pool? - mysql8.0

本文主要由于上次写的感觉又长又臭，感觉学习方法有问题，我们这次直接找来了 thread pool 的原文，一起来看看官方的开发者给出的blog – 感觉是个大神但是好像不是最官方的，所以我们还是带着批判性的思维去看；即使是最官方的也一样哈

and 多逼逼一句，学一个技术最好去官网

参考博客：

https://dev.mysql.com/blog-archive/the-new-mysql-thread-pool/

part 1 对原文的总结和梳理

The New MySQL Thread Pool

Posted on Jun 7, 2023 by [Daniel Blanchard](https://dev.mysql.com/blog-archive/?author=Daniel Blanchard)
Category: [Thread Pool](https://dev.mysql.com/blog-archive/?cat=Thread Pool)
Tags: [thread pool](https://dev.mysql.com/blog-archive/?tag=thread pool)

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.

关键点总结：

叫mysql thread pool - 企业级的
版本 - 8 和特性 - 最大事务限制
配置信息和性能对比

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.

关键点总结：

传统的是什么 - 一客户端一线程创造
一个线程一生需要等待三件事情（高考 - 结婚- 生孩子。。。。 Just Kiding）
总之，线程太累了，要罢工；how to use 牛马 properly ?
算法和锁结构的变化， 5.6 那个过时了，不过没说为啥，看后面

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.

关键点总结：

线程组的概念 - 16个
轮训算法 - receiver
线程组对于客户端连接的管理- subject 的概念
结构：1个线程池 - 16 个线程组 - 默认 1 线程（一般为了性能会分配更多线程）
THD 这里有点不好理解，单独说下 - 是一个用于存储与每个用户连接相关的信息的结构。每当一个用户连接到MySQL数据库时，系统会为该连接创建一个THD结构体，包含该连接的上下文数据，如连接的状态、执行的查询、事务信息等。THD的作用是将每个用户的连接独立开来，使得每个线程可以处理不同用户的请求，而不会相互干扰。每个连接的查询在其独立的上下文中执行，从而支持高并发的操作。
独特之处在于限制了事务

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.

在对于时间要求极高的坏境中，分开处理链接和线程， mysql服务器可能会面对处理高度集中的事务并发的挑战，每一个事务典型地锁着表和行直到事务被提交或者回滚

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.

当系统尝试处理比有限管理更多的并发线程，系统将会停止响应，因为事务等待过长的时间 for these locks ; 最后，总体的服务器的tps将会下降

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

最大事务限制特性有效地控制了并发线程执行数量，它由非零的系统参数 - thread_pool_max_transactions_limit 而启动；一个非零参数N 代表着线程池将不会拥有更多的事务并行在接下的时间；当最大事务特性启动的时候，最大事务限制将会动态改变，而不需要服务器重启（改变最大事务限制的值，从0 改到到另一个非零值，需要mysql服务器重启）

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.

当这个功能被启动时，每一个线程组都可以执行一个最大的（全局最大事务限制除以线程组数量- 线程池的size）并发事务数；执行完后， query worker 线程从同样的事务中选择出下一个statement ，（而不是从不同的连接请求中选择）去加速事务执行直到该事务被提交或者回滚

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.

值得注意的是，最大事务限制数倍线程组均分，因此最大事务限制数应该是线程组的整数倍，如果不是的话，每个线程组得到一个非整数会执行向上取整 - be rounded up

关键点总结：

Max-transaction-limit 动态调整（只有0 - 非零需要重启服务器）

Recommended Configuration for Thread Pool in MySQL 8.0

The following is the recommended initial configuration for the thread pool

thread_pool_size	#physical_cores, max 512
thread_pool_max_transaction_limit	#physical_cores * 32, max 512
thread_pool_algorithm	1 (high concurrency algorithm)
thread_pool_query_worker_threads_per_group	2

总结表格信息：

物理核心 * 32 = 最大事务控制限制
算法 - 模糊的不清晰的
每个线程组负责2个 worker-threads

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.

补充细节：

Spcc-100w :standard for evaluating the performance of database management systems.

100W in TPCC-100W

• The 100W means 100 warehouses, where each warehouse represents a specific amount of data.

• Each warehouse in the TPC-C benchmark is typically around 100 MB of data.

• For TPCC-100W, this translates to 10 GB of total data (100 warehouses × 100 MB per warehouse).

• Metrics include throughput (transactions per minute) and latency.

System Configuration:		Thread Pool Configuration:
Processor	48cores-HT Intel® Xeon® Platinum 8268 CPU @ 2.90GHz.	thread_pool_size（thread-group）	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.

当我们使用mysql线程池的时候，与常规的连接处理方法相比，即使有更高的用户连接数，但是表现率降低的更低。默认的连接处理方法表现出每秒事务处理数量的降低，当用户连接数到达512个的时候，当超过4096个用户连接的时候，仅仅能处理大约2000个事务/second 。然而，使用线程池后，较好的表现能力会被维持即使在更高的用户连接数量，允许处理大约17000个事务每秒，超过4096个事务的连接。这些结果强调了mysql 线程池的重要性，在被压测后，维持更高的表现能力上面

关键点：

4096 transactions
512 connections of users

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.

推荐的配置是一个合理的起点。针对特定工作负载产生最佳性能的配置将取决于工作负载本身。幸运的是，最大事务限制（Max Transaction Limit）可以动态调整，无需重启服务器。在运行类似于生产环境中预期遇到的工作负载时，可以从这个起始值开始增大或减小最大事务限制，并比较系统在不同最大事务限制值下的吞吐量。

在测试期间，一个合理的最大事务限制上限是您期望系统需要处理的最大并发连接数（您可能会发现参考状态变量 Max_used_connections 很有用，因为该变量记录了自服务器启动以来同时使用的最大连接数）。在调优时，您可能会发现从这个上限值开始设置最大事务限制是有用的，然后逐步降低该值，同时观察测试工作负载的吞吐量变化。

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.

thread_pool_size 和 thread_pool_query_worker_threads_per_group 的乘积（大致）是可用于处理查询的线程数。通常，需要在 较低的 thread_pool_size 和较高的 thread_pool_query_worker_threads_per_group 与 较高的 thread_pool_size 和较低的 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.

较高的 thread_pool_query_worker_threads_per_group 值的优点是：当工作负载包含长时间运行和短时间运行的查询混合时，不太可能所有线程组中的线程同时执行长时间运行的查询，从而阻塞短查询的执行。然而，当使用较小的 thread_pool_size 和较高的 thread_pool_query_worker_threads_per_group 值时，每个线程组的连接轮询操作开销会增加。需要注意的是，将 thread_pool_query_worker_threads_per_group 设置为小于 2 的值很可能无法改善性能。

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.

Sysbench OLTP_RW 性能调优示例：

显示的 Sysbench OLTP_RW 性能数据提供了调优 Max Transaction Limit 值的一个例子。在此例中，默认线程处理模型的性能在 64 个并发用户 时达到峰值，因此对于该负载选择了 64 的 Max Transaction Limit。

关于对比图：

以下图表比较了使用默认线程处理模型的服务器与使用线程池和 Max Transaction Limit 特性的服务器在运行 Sysbench OLTP_RW 测试时的结果。测试数据为 1 张表包含 8000 万行（20 GB 数据），访问模式为 Pareto 分布。

需要注意的是，使用 Pareto 访问模式 会导致频繁访问一小部分行数据，随着并发用户数量的增加，这种访问模式会导致数据锁的争用不断增加。

System Configuration:		Thread Pool Configuration:
Processor	48cores-HT Intel® Xeon® 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.

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

最大事务限制功能（Max Transaction Limit） 设置了可执行的最大并发事务/线程的硬性上限。虽然该功能提供了更好的性能，但也存在一些注意事项：

1. 当最大事务数量同时执行时，新的连接和事务/查询必须等待现有事务完成。不会创建新线程来处理新请求。如果所有并发事务都由长时间运行的查询组成，MySQL 系统可能看起来像是停滞状态。为缓解此问题，可以使用具有 “TP_ADMIN” 权限的用户连接绕过 “Max Transaction Limit”。这种特权连接可用于动态调整 “Max Transaction Limit” 或终止一个或多个阻塞查询，从而恢复正常流量。

2. 在 MySQL 线程池中，每个线程组最多可同时执行 thread_pool_max_transactions_limit 除以线程组数量 的事务。当某个线程组由于长时间运行的查询达到该限制时，新的连接和事务/查询将被搁置，直到现有事务完成。需要注意的是，即使运行中的事务总数低于 “Max Transaction Limit”，这些线程组中的新连接和事务仍可能无法得到处理。

3. 当系统包含多个独立数据库时，当达到最大并发事务数时，一个数据库的查询可能会因另一个数据库的查询而变慢。对许多系统来说，这种情况不成问题，但了解这种行为仍然很有帮助。遇到这种情况时，可以通过（可能是临时的）增加 “Max Transaction Limit” 的值来解决（无需重启服务器即可完成调整）。将 “Max Transaction Limit” 设置为 max_connections 的值，可以消除最大事务限制功能的影响，但仍然保留该功能，使其可以在无需重启服务器的情况下进行后续调整。

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.

总体而言，新的 MySQL 线程池为处理高并发事务的 MySQL 服务器提供了显著的性能退化保护。通过高效管理操作系统线程和事务，结合使用 Max Transaction Limit 功能，MySQL 线程池在连接数增加时能够保持性能稳定。它减少了资源争用，最小化了上下文切换，并优化了 CPU 缓存利用率。这些优化在高负载情况下提升了整体性能并加快了响应时间。

对于运行高并发连接和高负载系统的用户，可能会遇到随着负载增加每秒事务数开始下降的拐点：这种情况下，线程池及其 Max Transaction Limit 功能可有效防止性能下降。

以下是一些客户场景示例，展示了新的 MySQL 线程池如何在以前的性能水平上带来改进：

• 一位客户的生产环境中通常有超过 8000 个持久用户连接。由于通常只有少部分用户连接处于活跃状态，服务器的 CPU 使用率通常保持在 50% 以下。然而，当所有（或过多的）用户连接同时变为活跃时，会导致活动量激增，显著减慢整个系统的运行速度，几乎导致系统崩溃。使用 MySQL 线程池并设置合适的 Max Transaction Limit，可在此类活动峰值期间保护客户系统免受过载影响，并在这些条件下仍能实现最高效的处理速度。

• 另一位客户的在线商店经常遇到周期性的过载问题。在对特定商品进行促销时，许多并发更新作用于同一小数据集，导致查询排起长队争夺各种锁，占用 CPU 周期用于锁自旋，并造成额外的系统过载。这种过载还可能阻塞或减慢其他查询的执行。通过使用 MySQL 线程池并设置合适的 Max Transaction Limit，可以显著降低系统整体过载，同时使系统整体性能达到可能的最高效率水平。