MySQL8 中文参考（二十一）（4）

MySQL8 中文参考（二十一）（3）https://developer.aliyun.com/article/1566172

7.6.3 MySQL 企业线程池

7.6.3.1 线程池元素

7.6.3.2 线程池安装

7.6.3.3 线程池操作

7.6.3.4 线程池调优

注意

MySQL 企业线程池是 MySQL 企业版中包含的一个扩展，是一款商业产品。要了解更多关于商业产品的信息。

MySQL 企业版包括 MySQL 企业线程池，使用服务器插件实现。MySQL  服务器中的默认线程处理模型使用每个客户端连接一个线程执行语句。随着更多客户端连接到服务器并执行语句，整体性能会下降。线程池插件提供了一个旨在减少开销并提高性能的替代线程处理模型。该插件实现了一个线程池，通过有效管理大量客户端连接的语句执行线程来提高服务器性能。

线程池解决了使用每个连接一个线程的模型存在的几个问题：

• 太多线程堆栈使 CPU 缓存在高度并行执行工作负载中几乎无用。线程池促进线程堆栈重用，以最小化 CPU 缓存占用。
  
• 太多线程并行执行时，上下文切换开销很高。这也对操作系统调度程序构成挑战。线程池控制活动线程的数量，以保持 MySQL 服务器内部的并行性在一个服务器主机上可以处理的水平，并且适合 MySQL 正在执行的服务器主机。
  
• 太多并行执行的事务会增加资源争用。在InnoDB中，这会增加持有中央互斥锁的时间。线程池控制事务何时开始以确保不会有太多并行执行。
  

其他资源

第 A.15 节，“MySQL 8.0 FAQ：MySQL 企业线程池”

7.6.3.1 线程池元素

MySQL 企业线程池包括以下元素：

• 一个插件库文件实现了线程池代码的插件以及几个相关的监控表，提供有关线程池操作的信息：

• 截至 MySQL 8.0.14，监控表是性能模式表；请参见第 29.12.16 节，“性能模式线程池表”。
  
• 在 MySQL 8.0.14 之前，监控表是INFORMATION_SCHEMA表；请参见第 28.5 节，“INFORMATION_SCHEMA 线程池表”。
  INFORMATION_SCHEMA表现在已被弃用；预计它们将在将来的 MySQL 版本中被移除。应用程序应该从INFORMATION_SCHEMA表过渡到性能模式表。例如，如果一个应用程序使用此查询：

SELECT * FROM INFORMATION_SCHEMA.TP_THREAD_STATE;

• 应用程序应该改用此查询：

SELECT * FROM performance_schema.tp_thread_state;

• 注意
  如果您没有加载所有监控表，一些或所有 MySQL 企业监控器线程池图可能为空。
  要详细了解线程池的工作原理，请参见第 7.6.3.3 节，“线程池操作”。
  
• 有几个系统变量与线程池相关。当服务器成功加载线程池插件时，thread_handling系统变量的值为loaded-dynamically。
  其他相关的系统变量由线程池插件实现，并且除非启用，否则不可用。有关使用这些变量的信息，请参见第 7.6.3.3 节，“线程池操作”和第 7.6.3.4 节，“线程池调整”。
  
• 性能模式具有暴露有关线程池信息的工具，可用于调查操作性能。要识别它们，请使用此查询：

SELECT * FROM performance_schema.setup_instruments
WHERE NAME LIKE '%thread_pool%';

• 有关更多信息，请参见第二十九章，“MySQL 性能模式”。

7.6.3.2 线程池安装

本节描述了如何安装 MySQL Enterprise Thread Pool。有关安装插件的一般信息，请参见第 7.6.1 节，“安装和卸载插件”。

要被服务器使用，插件库文件必须位于 MySQL 插件目录中（由plugin_dir系统变量命名的目录）。如有必要，在服务器启动时通过设置plugin_dir的值来��置插件目录位置。

插件库文件基本名称为thread_pool。文件名后缀因平台而异（例如，对于 Unix 和类 Unix 系统，为.so，对于 Windows 为.dll）。

• MySQL 8.0.14 线程池安装
  
• MySQL 8.0.14 之前的线程池安装
  

MySQL 8.0.14 线程池安装

在 MySQL 8.0.14 及更高版本中，线程池监控表是性能模式表，随着线程池插件一起加载和卸载。INFORMATION_SCHEMA 版本的表已被弃用，但仍可用；它们按照 MySQL 8.0.14 之前的线程池安装说明中的说明安装。

要启用线程池功能，请通过使用--plugin-load-add选项启动服务器加载插件。为此，请将以下行放入服务器的my.cnf文件中，并根据需要调整.so后缀以适应您的平台：

[mysqld]
plugin-load-add=thread_pool.so

要验证插件安装，请检查信息模式PLUGINS表或使用SHOW PLUGINS语句（参见第 7.6.2 节，“获取服务器插件信息”）。例如：

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE 'thread%';
+-----------------------+---------------+
| PLUGIN_NAME           | PLUGIN_STATUS |
+-----------------------+---------------+
| thread_pool           | ACTIVE        |
+-----------------------+---------------+

要验证性能模式监控表是否可用，请检查信息模式TABLES表或使用SHOW TABLES语句。例如：

mysql> SELECT TABLE_NAME
       FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_SCHEMA = 'performance_schema'
       AND TABLE_NAME LIKE 'tp%';
+-----------------------+
| TABLE_NAME            |
+-----------------------+
| tp_thread_group_state |
| tp_thread_group_stats |
| tp_thread_state       |
+-----------------------+

如果服务器成功加载线程池插件，则将thread_handling系统变量设置为loaded-dynamically。

如果插件初始化失败，请检查服务器错误日志以获取诊断消息。

MySQL 8.0.14 之前的线程池安装

在 MySQL 8.0.14 之前，线程池监视表是与线程池插件分开的插件，可以单独安装。

要启用线程池功能，请通过使用--plugin-load-add选项启动服务器加载要使用的插件。例如，如果只命名插件库文件，则服务器将加载其中包含的所有插件（即线程池插件和所有INFORMATION_SCHEMA表）。为此，请将以下行放入服务器的my.cnf文件中，并根据需要调整平台的.so后缀：

[mysqld]
plugin-load-add=thread_pool.so

这相当于通过逐个命名加载所有线程池插件：

[mysqld]
plugin-load-add=thread_pool=thread_pool.so
plugin-load-add=tp_thread_state=thread_pool.so
plugin-load-add=tp_thread_group_state=thread_pool.so
plugin-load-add=tp_thread_group_stats=thread_pool.so

如果需要，可以从库文件中加载单独的插件。要加载线程池插件但不加载INFORMATION_SCHEMA表，请使用以下选项：

[mysqld]
plugin-load-add=thread_pool=thread_pool.so

要加载线程池插件和仅TP_THREAD_STATE INFORMATION_SCHEMA表，请使用以下选项：

[mysqld]
plugin-load-add=thread_pool=thread_pool.so
plugin-load-add=tp_thread_state=thread_pool.so

要验证插件安装，请检查信息模式PLUGINS表，或使用SHOW PLUGINS语句（参见第 7.6.2 节，“获取服务器插件信息”）。例如：

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE 'thread%' OR PLUGIN_NAME LIKE 'tp%';
+-----------------------+---------------+
| PLUGIN_NAME           | PLUGIN_STATUS |
+-----------------------+---------------+
| thread_pool           | ACTIVE        |
| TP_THREAD_STATE       | ACTIVE        |
| TP_THREAD_GROUP_STATE | ACTIVE        |
| TP_THREAD_GROUP_STATS | ACTIVE        |
+-----------------------+---------------+

如果服务器成功加载线程池插件，则将thread_handling系统变量设置为loaded-dynamically。

如果插件初始化失败，请检查服务器错误日志以获取诊断消息。

7.6.3.3 线程池操作

线程池由多个线程组组成，每个组管理一组客户端连接。随着连接的建立，线程池以轮询方式将它们分配给线程组。

线程池公开了可用于配置其操作的系统变量：

• thread_pool_algorithm: 用于调度的并发算法。
  
• thread_pool_dedicated_listeners: 在每个线程组中专门指定一个监听线程，以监听分配给该组的连接的传入语句。
  
• thread_pool_high_priority_connection: 如何为会话安排语句执行。
  
• thread_pool_max_active_query_threads: 每组允许的活动线程数。
  
• thread_pool_max_transactions_limit: 线程池插件允许的最大事务数。
  
• thread_pool_max_unused_threads: 允许的休眠线程数。
  
• thread_pool_prio_kickup_timer: 在低优先级队列中等待执行的语句移动到高优先级队列之前的时间。
  
• thread_pool_query_threads_per_group: 在线程组中允许的查询线程数（默认为单个查询线程）。如果由于长时间运行的事务导致响应时间变慢，考虑增加该值。
  
• thread_pool_size: 线程池中线程组的数量。这是控制线程池性能最重要的参数。
  
• thread_pool_stall_limit: 执行语句被视为停滞之前的时间。
  
• thread_pool_transaction_delay: 开始新事务之前的延迟时间。
  

要配置线程组的数量，请使用thread_pool_size系统变量。默认组数为 16。有关设置此变量的指导，请参见第 7.6.3.4 节“线程池调优”。

每个组的最大线程数为 4096（在某些系统上为 4095，其中一个线程在内部使用）。

线程池将连接和线程分开，因此连接和执行来自这些连接的语句的线程之间没有固定关系。这与默认的线程处理模型不同，后者将一个线程与一个连接关联起来，以便给定线程执行来自其连接的所有语句。

默认情况下，线程池尝试确保每个组中最多有一个线程在任何时候执行，但有时允许更多线程暂时执行以获得最佳性能：

• 每个线程组都有一个监听线程，用于监听分配给该组的连接的传入语句。当语句到达时，线程组要么立即开始执行它，要么将其排队以供稍后执行：

• 如果接收到的语句是唯一的，并且没有排队或当前正在执行的语句，则会立即执行。
  从 MySQL 8.0.31 开始，可以通过配置thread_pool_transaction_delay来延迟立即执行，这对事务有限制作用。有关更多信息，请参考后续讨论中对该变量的描述。
  
• 如果语句由于同时排队或执行的语句而无法立即开始执行，则会发生排队。
  

• thread_pool_transaction_delay 变量指定了以毫秒为单位的事务延迟。工作线程在执行新事务之前会休眠指定的时间段。
  在并行事务影响其他操作的性能的情况下，可以使用事务延迟。例如，如果并行事务影响索引创建或在线缓冲池调整操作，可以配置事务延迟以减少资源争用。延迟对事务有限制作用。
  thread_pool_transaction_delay 设置不影响从特权连接（分配给Admin线程组的连接）发出的查询。这些查询不受配置的事务延迟影响。
  
• 如果立即执行发生，则监听线程执行它。（这意味着暂时没有线程在组中监听。）如果语句很快完成，执行线程将返回到监听语句。否则，线程池会认为语句被阻塞，并启动另一个线程作为监听线程（必要时创建）。为了确保没有线程组被阻塞在被阻塞的语句上，线程池有一个定期监视线程组状态的后台线程。
  通过使用监听线程执行可以立即开始的语句，如果语句很快完成，则无需创建额外的线程。这确保在并发线程数量较低的情况下实现最有效的执行。
  当线程池插件启动时，它会为每个组创建一个线程（监听线程），以及后台线程。根据需要创建额外的线程来执行语句。
  
• thread_pool_stall_limit系统变量的值确定了前一项中“快速完成”的含义。在被视为停顿之前的默认时间为  60ms，但可以设置为最大 6  秒。此参数可配置，以便您能够在服务器工作负载中找到适当的平衡。较短的等待值允许线程更快地启动。较短的值也更有利于避免死锁情况。较长的等待值对包含长期运行语句的工作负载很有用，以避免在当前语句执行时启动过多的新语句。
  
• 如果thread_pool_max_active_query_threads为 0，则默认算法适用于确定每个组的活动线程的最大数量，就像前面描述的那样。默认算法考虑了停顿的线程，并可能暂时允许更多的活动线程。如果thread_pool_max_active_query_threads大于 0，则它会限制每个组的活动线程数量。
  
• 线程池专注于限制并发短期运行的语句数量。在执行语句达到停顿时间之前，它会阻止其他语句开始执行。如果语句执行超过停顿时间，它将被允许继续执行，但不再阻止其他语句开始。通过这种方式，线程池试图确保在每个线程组中永远不会有多个短期运行的语句，尽管可能存在多个长期运行的语句。让长期运行的语句阻止其他语句执行是不可取的，因为可能需要等待的时间没有限制。例如，在复制源服务器上，负责向副本发送二进制日志事件的线程实际上会永远运行。
  
• 如果语句遇到磁盘 I/O 操作或用户级锁（行锁或表锁），则该语句将被阻塞。阻塞将导致线程组变为未使用状态，因此会有回调到线程池，以确保线程池可以立即在该组中启动一个新线程来执行另一个语句。当阻塞的线程返回时，线程池允许其立即重新启动。
  
• 有两个队列，一个高优先级队列和一个低优先级队列。事务中的第一条语句进入低优先级队列。如果事务正在进行中（其语句已开始执行），则事务的任何后续语句进入高优先级队列，否则进入低优先级队列。通过启用thread_pool_high_priority_connection系统变量，可以影响队列分配，导致会话的所有排队语句进入高优先级队列。
  针对非事务性存储引擎的语句，或者如果启用了autocommit的事务性引擎，被视为低优先级语句，因为在这种情况下每条语句都是一个事务。因此，对于InnoDB和MyISAM表的语句混合，线程池优先处理InnoDB表的语句而不是MyISAM表的语句，除非启用了autocommit。启用autocommit后，所有语句都具有低优先级。
  
• 当线程组选择一个排队的语句进行执行时，首先查看高优先级队列，然后查看低优先级队列。如果找到一条语句，则将其从队列中移除并开始执行。
  
• 如果一条语句在低优先级队列中停留时间过长，线程池将移动到高优先级队列。thread_pool_prio_kickup_timer系统变量的值控制移动前的时间。对于每个线程组，每 10ms（每秒 100 次）最多将一条语句从低优先级队列移动到高优先级队列。
  
• 线程池重复使用最活跃的线程以更好地利用 CPU 缓存。这是一个对性能有很大影响的小调整。
  
• 当一个线程执行来自用户连接的语句时，性能模式仪表化将线程活动归因于用户连接。否则，性能模式将活动归因于线程池。
  

以下是线程组可能启动多个线程执行语句的条件示例：

• 一个线程开始执行一条语句，但运行时间足够长以被视为停滞。线程组允许另一个线程开始执行另一条语句，即使第一个线程仍在执行。
  
• 一个线程开始执行一条语句，然后变为阻塞状态并将此情况报告给线程池。线程组允许另一个线程开始执行另一条语句。
  
• 一个线程开始执行一个语句，变得阻塞，但没有报告它被阻塞，因为阻塞不发生在已经使用线程池回调进行仪器化的代码中。在这种情况下，该线程对线程组来说仍在运行。如果阻塞持续时间足够长，使语句被视为停滞不前，组允许另一个线程开始执行另一个语句。
  

线程池设计为可扩展到越来越多的连接。它还设计为避免由于限制活动执行语句的数量而可能产生的死锁。重要的是，不向线程池报告的线程不应该阻止其他语句的执行，从而导致线程池陷入死锁。以下是此类语句的示例：

• 长时间运行的语句。这些将导致仅有少数语句使用所有资源，它们可能阻止其他所有人访问服务器。
  
• 读取二进制日志并将其发送到副本的二进制日志转储线程。这是一种长时间运行的“语句”，运行时间很长，不应该阻止其他语句的执行。
  
• 在行锁、表锁、休眠或任何其他未被 MySQL 服务器或存储引擎报告给线程池的阻塞活动上被阻塞的语句。
  

在每种情况下，为了防止死锁，当语句不能快速完成时，将该语句移至停滞类别，以便线程组可以允许另一个语句开始执行。通过这种设计，当线程执行或长时间阻塞时，线程池将该线程移至停滞类别，并在语句的其余执行过程中，不会阻止其他语句的执行。

可发生的线程最大数量是max_connections和thread_pool_size的总和。这可能发生在所有连接都处于执行模式且每个组创建了一个额外线程来监听更多语句的情况下。这不一定经常发生，但在理论上是可能的。

特权连接

当达到thread_pool_max_transactions_limit定义的限制时，新连接似乎会挂起，直到一个或多个现有事务完成。当尝试在现有连接上启动新事务时也会发生相同情况。如果现有连接被阻塞或长时间运行，访问服务器的唯一方法是使用特权连接。

要建立特权连接，发起连接的用户必须具有TP_CONNECTION_ADMIN权限。特权连接会忽略由thread_pool_max_transactions_limit定义的限制，并允许连接到服务器以增加限制、移除限制或终止正在运行的事务。TP_CONNECTION_ADMIN权限必须显式授予，不会默认授予任何用户。

特权连接可以执行语句和启动事务，并分配给指定为Admin线程组的线程组。

当查询performance_schema.tp_thread_group_stats表时，该表报告每个线程组的统计信息，Admin线程组的统计信息报告在结果集的最后一行。例如，如果SELECT * FROM performance_schema.tp_thread_group_stats\G返回 17 行（每个线程组一行），Admin线程组的统计信息将报告在第 17 行。

7.6.3.4 线程池调优

本节提供了关于确定线程池性能最佳配置的指导，以事务每秒等指标来衡量。

最重要的是线程池中线程组的数量，可以在服务器启动时使用--thread-pool-size选项进行设置；这个值在运行时无法更改。对于这个选项的推荐值取决于主要使用的存储引擎是InnoDB还是MyISAM：

• 如果主要存储引擎是InnoDB，线程池大小的推荐值是主机机器上可用的物理核心数，最大不超过 512。
  
• 如果主要存储引擎是MyISAM，线程池大小应该设置得相对较低。通常情况下，最佳性能在 4 到 8 之间。较高的值可能会对性能产生轻微负面影响，但不会太明显。
  

线程池插件可以处理的并发事务数量上限由thread_pool_max_transactions_limit的值确定。这个系统变量的推荐初始设置是物理核心数乘以 32。您可能需要根据特定工作负载调整这个值；这个值的合理上限是预期的最大并发连接数；Max_used_connections状态变量的值可以作为确定这个值的指导。一个好的方法是将thread_pool_max_transactions_limit设置为这个值，然后在观察吞吐量的影响时逐渐调整它的值。

线程组中允许的最大查询线程数由thread_pool_query_threads_per_group的值确定，可以在运行时进行调整。此值与线程池大小的乘积大致等于可用于处理查询的总线程数。通常获得最佳性能意味着在thread_pool_query_threads_per_group和线程池大小之间为您的应用程序找到适当的平衡。较大的thread_pool_query_threads_per_group值使得在线程组中同时执行长时间查询并阻塞较短查询的可能性较小，当工作负载包括长时间和短时间运行的查询时。您应该记住，当使用较小的线程池大小值和较大的thread_pool_query_threads_per_group值时，每个线程组的连接轮询操作的开销会增加。因此，我们建议将thread_pool_query_threads_per_group的起始值设置为2；将此变量设置为较低的值通常不会提供任何性能优势。

在正常情况下获得最佳性能，我们还建议将thread_pool_algorithm设置为 1 以实现高并发。

此外，thread_pool_stall_limit系统变量的值决定了阻塞和长时间运行语句的处理方式。如果所有阻塞  MySQL  服务器的调用都报告给线程池，那么它将始终知道执行线程何时被阻塞，但这并不总是正确的。例如，可能会在未使用线程池回调进行仪器化的代码中发生阻塞。对于这种情况，线程池必须能够识别似乎被阻塞的线程。这是通过由thread_pool_stall_limit的值确定的超时来完成的，该超时确保服务器不会完全被阻塞。thread_pool_stall_limit的值表示 10 毫秒间隔的数量，因此600（最大值）表示 6 秒。

thread_pool_stall_limit还可以使线程池处理长时间运行的语句。如果允许长时间运行的语句阻塞线程组，那么分配给该组的所有其他连接都将被阻塞，无法开始执行，直到长时间运行的语句完成。在最坏的情况下，这可能需要几个小时甚至几天。

应选择thread_pool_stall_limit的值，使得执行时间超过该值的语句被视为停滞。停滞的语句会产生大量额外开销，因为它们涉及额外的上下文切换，有时甚至涉及额外的线程创建。另一方面，将thread_pool_stall_limit参数设置得太高意味着长时间运行的语句会阻塞一些短时间运行的语句，时间超过必要的时间。较短的等待值允许线程更快地启动。较短的值也更有利于避免死锁情况。较长的等待值对包含长时间运行语句的工作负载有用，以避免在当前语句执行时启动太多新语句。

假设一个服务器执行的工作负载中，即使在服务器负载较重时，99.9%的语句在 100ms 内完成，而剩余的语句在 100ms 至 2 小时之间均匀分布。在这种情况下，将thread_pool_stall_limit设置为 10（10 × 10ms = 100ms）是有意义的。默认值为 6（60ms），适用于主要执行非常简单语句的服务器。

thread_pool_stall_limit 参数可以在运行时更改，以使您能够找到适合服务器工作负载的平衡。假设启用了tp_thread_group_stats表，您可以使用以下查询来确定停滞执行语句的比例：

SELECT SUM(STALLED_QUERIES_EXECUTED) / SUM(QUERIES_EXECUTED)
FROM performance_schema.tp_thread_group_stats;

这个数字应尽可能低。为了减少语句停滞的可能性，增加thread_pool_stall_limit的值。

当一个语句到达时，它实际开始执行之前可以延迟的最长时间是多少？假设满足以下条件：

• 低优先级队列中有 200 个语句在排队。
  
• 高优先级队列中有 10 个语句在排队。
  
• thread_pool_prio_kickup_timer 设置为 10000（10 秒）。
  
• thread_pool_stall_limit 设置为 100（1 秒）。
  

在最坏的情况下，这 10 个高优先级语句代表了 10  个长时间执行的事务。因此，在最坏的情况下，没有语句可以移动到高优先级队列，因为它总是包含等待执行的语句。在 10  秒后，新语句有资格被移动到高优先级队列。然而，在它被移动之前，它之前的所有语句也必须被移动。这可能需要另外 2 秒，因为每秒最多可以将 100  个语句移动到高优先级队列。现在当语句到达高优先级队列时，可能会有许多长时间运行的语句在它前面。在最坏的情况下，每一个都会被阻塞，每个语句之间需要  1 秒才能从高优先级队列中检索到下一个语句。因此，在这种情况下，新语句开始执行前需要 222 秒。

这个例子展示了一个应用程序的最坏情况。如何处理取决于应用程序。如果应用程序对响应时间有很高的要求，它很可能应该在更高的级别自行限制用户。否则，它可以使用线程池配置参数来设置某种最大等待时间。

MySQL8 中文参考（二十一）（5）https://developer.aliyun.com/article/1566174