高性能 MySQL 第四版（GPT 重译）（一）(2)

高性能 MySQL 第四版（GPT 重译）（一）(1)https://developer.aliyun.com/article/1484250

监控查询延迟

MySQL 引入了许多长期需要的增强功能来跟踪查询运行时间，随着应用代码的变化，您应该绝对使用监控堆栈来跟踪这些趋势。然而，这仍然不能完全反映客户体验，特别是考虑到现代软件架构的设计方式。除了内部跟踪的延迟之外，您还需要了解应用程序如何感知延迟以及当感知延迟增加时会发生什么。这意味着除了直接跟踪数据库服务器的查询延迟之外，您还应该通过工具让客户端报告查询完成时间，以便尽可能接近客户体验。从客户端摄取所有这些样本指标（特别是当您的基础设施规模扩大时）可以使用付费工具如 Datadog 或 SolarWinds Database Performance Monitor，甚至使用开源工具如 PMM。这是一个需要与组织的应用开发人员密切合作的领域。您需要了解应用团队如何从应用程序角度衡量这一点，并使用跟踪工具如 Honeycomb 或 Lightstep 来增加对异常值的洞察力。

监控错误

您是否需要跟踪并警报每次发生的错误？这取决于情况。

在运行服务中的 MySQL 客户端存在错误并不意味着一定有什么东西出了问题。在分布式系统的世界中，有许多情况下客户端可能遇到间歇性错误，并且在许多情况下，通过简单重试失败的查询可以解决。然而，发生错误的速率，跨越处理基础设施中数据库查询的服务群体，可能是潜在问题的关键指标。以下是一些客户端错误的例子，通常可能只是噪音，但如果它们的速率加快，则可能是问题的迹象：

锁等待超时

你的客户报告这种错误急剧增加可能是源节点上的行锁争用升级的迹象，事务不断重试仍然失败。这可能是写入停机的前兆。

中止连接

客户报告突然出现大量中止连接可能是你在客户端和数据库实例之间的任何访问层存在问题的指标。不追踪这些问题可能导致大量客户端重试，消耗资源。

MySQL 服务器跟踪的一个可以帮助你的东西是名为Connection_errors_xxx的服务器变量集，其中xxx是不同类型的连接错误。任何这些计数器的突然增加都可能是一个强烈的指示，告诉你当前有一些新的异常情况。

是否有错误，一个单个实例意味着有问题需要处理？是的。

例如，收到 MySQL 实例正在以只读模式运行的错误是一个问题的迹象，即使这些错误并不经常发生。这可能意味着你刚刚将一个复制实例提升为源，但它仍在只读模式下运行（你确实在只读模式下运行复制实例，对吧？），这意味着对于你的集群来说写入的停机时间。或者这可能意味着在发送写流量到复制实例的访问层中存在一些问题。在这两种情况下，这不是一个通过重试解决的间歇性问题的迹象。

另一个服务器端错误，表明存在重大问题的标志是“连接过多”或操作系统级别的“无法创建新线程”。这些是你的应用层创建并保留的连接数超过了数据库服务器配置允许的数量的迹象，无论是在服务器max_connections变量还是 MySQL 进程允许打开的线程数方面。这些错误会立即转换为 5xx 错误传递给你的应用程序，并且根据你的应用程序设计，也可能对你的客户产生影响。

正如你所看到的，衡量性能并选择围绕 SLIs 构建哪些错误，这既是一个技术问题，也是一个沟通和社交问题，所以你应该做好准备。

主动监控

正如我们所说，SLO 监控侧重于你的客户是否满意。这有助于让你专注于在客户不满意时改善他们的体验，以及在其他任务上，比如减少重复劳动时。这忽略了一个关键领域：主动监控。

如果我们回到我们的在线商店示例以及我们如何设想监控客户体验，我们可以进一步阐述。想象一下，你没有遇到任何组件的重大故障，但你注意到有越来越多的客户支持票证报告“缓慢”或偶尔出现的错误，似乎会自行消失。你如何追踪这样的行为？如果你不已经对多个信号的基准性能有一个很好的想法，这可能是一项非常困难的任务。你用来触发值班警报的仪表板和脚本可以称为稳态监控。这让你知道在给定系统中是否发生了意外情况，无论是否有变化。它们是在客户经历故障之前为你提供领先指标的重要工具。

在监控中需要取得平衡的一点是，它始终需要是可操作的，同时也需要是真正的领先指标。对于数据库磁盘空间已满时发出警报已经太晚了，因为服务已经停止了，但是在 80%时发出警报可能太慢，或者如果增长速率不那么快，则可能不够可操作。

让我们谈谈你可以监控的有用信号，这些信号与实际客户影响没有直接关联。

磁盘增长

跟踪磁盘增长是一种你可能不会考虑直到它成为问题的指标。一旦它成为问题，解决问题可能会耗费时间并影响你的业务。了解如何跟踪它，制定缓解计划，并知道适当的警报阈值肯定是更好的选择。

有许多策略可以用来监控磁盘增长。让我们从最理想到最低限来分解它们。

如果你的监控工具允许的话，跟踪磁盘空间使用量的增长速率可能非常有用。总会有一些情况，可用磁盘空间会相对迅速减少，使你的可用性受到威胁。长时间运行的具有大型撤消日志或更改表的事务是为什么你可能会迅速接近磁盘满的例子。有许多事故故事表明，过多的日志记录或给定数据集的插入模式的更改直到“数据库”耗尽磁盘空间才被发现。然后才会触发各种警报。

如果跟踪增长速率不可行（并非所有监控工具都提供此功能），你可以设置多个阈值，较低的警告只在工作时间触发，较高的更关键值作为非工作时间值班的警报。这使团队在工作时间之前有一个预警，以避免事情变得严重到需要叫醒某人。

如果你既不能监控增长速率，也不能为同一指标定义多个阈值，那么你至少需要确定一个磁盘空间使用量的单一阈值，在达到该阈值时向你的值班工程师发出警报。这个阈值需要足够低，以便在团队评估触发原因并考虑长期缓解措施时采取一些行动并释放磁盘空间。考虑评估磁盘的最大吞吐量（MB/s），并利用这一点来帮助计算在最大流量吞吐量下填满磁盘需要多长时间。你需要这么长的前期时间来避免事件发生。

我们在第四章中讨论了与 MySQL 如何使用磁盘空间以及在这些决策中考虑磁盘空间增长相关的操作系统和硬件配置。应该预期，希望你的业务会发展到某个程度，以至于你无法将所有数据存储在一组服务器集群中。即使你在一个可以为你扩展卷的云环境中运行，你仍然需要对此进行规划，因此你总是希望有一个空闲磁盘空间的阈值，以便你有时间进行规划并进行所需的扩展而不会惊慌。

这里的要点是确保你对磁盘空间增长有一些监控，即使你认为现在还为时过早，还不需要。这是几乎每个人都准备不足的增长轴之一。

连接增长

随着业务的增长，一个常见的线性增长层是你的应用层。你将需要更多的实例来支持登录、购物车、处理请求，或者产品背景可能是什么。所有这些添加的实例开始向数据库主机打开越来越多的连接。你可以通过添加副本、使用复制作为扩展措施，甚至使用中间件层如 ProxySQL 来将前端的增长与直接在数据库上的连接负载分离来缓解这种增长。

在流量增长的同时，数据库服务器可以支持有限数量的连接池，这是通过服务器设置max_connections配置的。一旦连接到服务器的总数达到最大值，你的数据库将不允许任何新连接，这是导致无法再向数据库打开新连接的常见原因之一，从而增加用户的错误。

监控连接增长是为了确保资源不会耗尽，从而危及数据库的可用性。这种风险可能以两种不同的方式出现：

• 应用层打开了很多未使用的连接，无故增加连接风险。这种情况的明显迹象是看到连接计数(threads_connected)很高，但threads_running仍然很低。
  
• 应用层正在积极使用大量连接，有可能过载数据库。你可以通过看到threads_connected和threads_running都很高（数百？数千？）且不断增加来区分这种状态。
  

在设置连接计数监控时需要考虑的一个有用的事情是依赖于百分比而不是绝对数字。threads_connected/max_connections的百分比显示了你的应用节点数量增长将带你接近数据库允许的最大连接池。这有助于监控连接增长问题的第一个阶段。

另外，你应该跟踪和警报数据库主机的繁忙程度，正如我们之前解释的，可以通过threads_running的值来看到。通常，如果这个值增长到一百以上的线程，你会开始看到增加的 CPU 使用率和内存使用率，这是数据库主机负载高的一般迹象。这对于数据库的可用性是一个直接的关注点，因为它可能升级到 MySQL 进程被操作系统杀死。一个常见的快速解决方案是使用 kill 进程命令或自动化使用它的工具，比如pt-kill，战术性地减轻负载，然后通过查询分析来查明数据库陷入这种状态的原因，这是我们之前描述过的。

警告

连接风暴是生产系统中的情况，应用层感知到查询延迟增加，并响应地向数据库层打开更多连接。这可能会给数据库增加大量负载，因为它处理大量新连接的涌入，这会消耗资源，无法满足查询请求。连接风暴可能导致max_connections中可用连接数量突然减少，增加数据库可用性风险。

复制延迟

MySQL 具有一种本地复制功能，可以将数据从一个服务器（源）发送到一个或多个额外的服务器，称为副本。数据在源上写入和在副本上可用之间的延迟称为复制延迟。如果你的应用从副本读取数据，延迟可能会导致数据看起来不一致，因为你向尚未赶上所有更改的副本发送读取请求。在社交媒体的例子中，用户可能会评论其他人发布的内容。这些数据被写入源，然后复制到副本。当用户尝试查看他们的回复时，如果应用发送请求到一个滞后的服务器，副本可能尚未有数据。这可能会让用户感到困惑，认为他们的评论没有保存。我们在第九章中更详细地介绍了对抗复制延迟的策略。

延迟是那些可能触发事件的急性 SLI 指标之一。它也是需要更多架构变化的长期趋势指示。在长期情况下，即使你从未遇到影响客户体验的复制延迟，它仍然表明，至少间歇性地，源节点的写入量超过了副本在当前配置下的写入量。它可以成为你写入容量的煤矿中的警报。如果听取建议，它可以防止未来发生全面事件。

警告

警惕将有关复制延迟的信息告知他人。立即可行的纠正措施并不总是可能的。同样，如果你不从副本中读取数据，请考虑监控系统对此情况如何积极地提醒他人。尤其是在非工作时间接收到的警报应始终是可操作的。

复制延迟是那些既影响即时又战术决策的指标之一，但长期关注其趋势可以帮助你避免更大的业务影响，并使你走在增长曲线的前面。

I/O 利用率

数据库工程师永无止境的努力之一是“尽可能多地在内存中完成工作，因为这样更快。”虽然这确实是准确的，但我们也知道我们不可能 100%地做到这一点，因为那意味着我们的数据完全适合内存，而在这种情况下，“规模”还不是我们需要花费精力的事情。

随着数据库基础设施的扩展和数据不再适合内存，你会意识到下一个最好的方法是不要从磁盘中读取太多数据，以至于查询被卡在那些宝贵的 I/O 周期中等待它们的轮次。即使在几乎所有东西都运行在固态驱动器上的这个时代，这仍然是真实的。随着数据规模的增长和查询需要扫描更多数据来满足请求，你会发现 I/O 等待可能会成为你的流量增长的瓶颈。

监控磁盘 I/O 活动有助于在影响客户之前提前了解性能下降。有一些事项可以监控以实现这一目标。像 iostat 这样的工具可以帮助你监控 I/O 等待。你希望监控并提醒如果你的数据库服务器有很多线程处于 IOwait 中，这表明它们在排队等待某些磁盘资源可用。你可以通过跟踪 IOutil 作为一个有意义的时间段的运行图，比如一天或两天，甚至一周。IOutil 报告为整个系统磁盘访问容量的百分比。在一个不运行备份的主机上，如果持续时间接近 100%，这可能表明存在全表扫描和低效查询。你还想监控你的磁盘 I/O 容量的整体利用率作为一个百分比，因为这可以预警你的磁盘访问成为未来数据库性能的瓶颈。

自增空间

在使用 MySQL 时较少为人知的一个雷区是，自增主键默认创建为有符号整数，并且可能会用尽键空间。当你进行了足够多的插入操作时，自增键达到了其数据类型的最大可能值。在长期基础上计划应该监控哪些指标时，监控使用自增作为主键的任何表的剩余整数空间是一个简单的操作，几乎肯定会在未来为你节省一些重大事件的痛苦，因为你可以提前预测到需要更大的键空间。

如何监控这个关键空间？您有几个选项。如果您已经使用 PMM 及其 Prometheus 导出器，这是内置的，您只需要打开标志-collect.auto_increment.columns。如果您的团队不使用 Prometheus，您可以使用以下查询，可以将其修改为度量生产者或警报，告诉您何时任何表接近可能的最大关键空间。此查询依赖于information_schema，其中包含有关数据库实例中表的所有元数据：

SELECT
    t.TABLE_SCHEMA AS `schema`,
    t.TABLE_NAME AS `table`,
    t.AUTO_INCREMENT AS `auto_increment`,
    c.DATA_TYPE AS `pk_type`,
    (
        t.AUTO_INCREMENT /
        (CASE DATA_TYPE
            WHEN 'tinyint'
                THEN IF(COLUMN_TYPE LIKE '%unsigned',
                    255,
                    127
                )
            WHEN 'smallint'
                THEN IF(COLUMN_TYPE LIKE '%unsigned',
                    65535,
                    32767
                )
            WHEN 'mediumint'
                THEN IF(COLUMN_TYPE LIKE '%unsigned',
                    16777215,
                    8388607
                )
            WHEN 'int'
                THEN IF(COLUMN_TYPE LIKE '%unsigned',
                    4294967295,
                    2147483647
                )
            WHEN 'bigint'
                THEN IF(COLUMN_TYPE LIKE '%unsigned',
                    18446744073709551615,
                    9223372036854775807
                )
        END / 100)
    ) AS `max_value`
    FROM information_schema.TABLES t
    INNER JOIN information_schema.COLUMNS c
        ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
        AND t.TABLE_NAME = c.TABLE_NAME
    WHERE
        t.AUTO_INCREMENT IS NOT NULL
        AND c.COLUMN_KEY = 'PRI'
        AND c.DATA_TYPE LIKE '%int'
;

在一般情况下以及专门管理自动增量时，选择主键时需要考虑很多微妙和上下文，我们将在第六章中进行讨论。

备份创建/恢复时间

长期规划不仅涉及业务正常运行时的增长，还涉及在可接受的时间范围内进行恢复。我们将在第十章中更深入地讨论如何考虑灾难恢复，以及在第十三章中讨论它如何成为您的合规控制职责的一部分，但我们在这里提到它是为了指出一个好的灾难恢复计划只有在您重新审视并调整其目标时才能起作用。

如果您的数据库达到一个恢复备份所需时间超过可接受时间以恢复业务的关键功能的大小，即使其他一切都正常运行，您也需要考虑调整 MTTR 目标，更改“关键功能”的定义，或找到缩短备份恢复时间的方法。在制定灾难恢复计划时需要考虑的一些事项：

• 对于这个恢复目标，要非常具体，并且如果需要的话，要查看支持该功能子集的数据是否需要在一个单独的集群中，以实现这一期望的现实性。
  
• 如果将数据在多个较小的实例中进行功能分区不可行，则整个数据集现在都处于通过备份恢复的目标下。从备份中恢复所需时间最长的数据集将驱动此恢复过程完成时间。
  
• 确保有自动化的测试方法（我们将在第十章中涵盖一些示例）。监视从文件恢复备份到已经赶上自备份创建以来的所有更改的运行数据库所需的时间，并将该指标存储在一个足够长的保留期内，以查看长期（至少一年）的趋势。如果不自动化监控，这是一个可能被忽视并且变得令人惊讶地长的指标之一。
  

在我们即将描述的许多示例长期指标中，您会发现我们几乎总是指出需要对数据进行功能分片或水平分片。这里的目标是明确指出，如果在容量问题是主要贡献原因的情况下考虑分片，那么您很可能考虑得太晚了。将数据分解为可管理的部分的工作并不是在您的数据对一个集群来说太大时才开始，而是在您仍在确定为提供成功的客户体验而设定目标时。

了解恢复数据所需的时间可以帮助设定在真正灾难发生时该做什么的期望。它还可以让您意识到可能需要比业务希望的时间更长。这是需要分片的前兆。

测量长期性能

选择日常运营的服务水平指标（SLIs）和服务水平目标（SLOs）只是一个开始。你需要确保自己没有把森林误认为是树木，而是专注于具体的主机指标而不是检查整体系统性能和客户体验结果。在这一部分，我们将介绍您可以使用的策略来思考系统的整体长期健康状况。

了解您的业务节奏

了解您的业务流量节奏非常重要，因为这将始终是您的所有 SLOs 都经受最严格测试并受到最重要客户最严格审查的时候。业务节奏可能意味着高峰流量时间比“平均”高出数倍，如果您的数据库基础设施没有准备好，这将产生许多后果。在数据库基础设施的背景下，这可能意味着每秒要处理数倍的请求，应用服务器的连接负载更大，或者如果写操作间歇性失败，收入影响更大。以下是一些业务节奏的示例，这些示例应该帮助您了解您的公司所处的业务周期：

电子商务网站

11 月底至年底是许多国家最繁忙的时期，在线商店可能会看到销售额增加数倍。这意味着更多的购物车，更多的同时销售，以及同一年的任何其他时间相比更多的收入影响。

人力资源软件

在美国，11 月通常是许多员工在被称为“开放选项”的时间内进行福利选举的时候，这将带来更多的流量。

在线鲜花供应商

情人节将是一年中最忙碌的时候，会有更多人订购花束送货。

正如您所看到的，这些业务周期可以根据业务填充的客户需求而有很大的变化。对于您的业务周期和其对业务收入、声誉以及您应该做出多少准备以满足需求而不影响您负责运行的系统的稳定性的影响，您必须意识到这一点至关重要。

当衡量支撑业务的数据库基础设施的性能时，重要的是不要将性能测量与工程组织正在跟踪的其他重要指标分开。数据库性能应该是关于技术堆栈性能的更大对话的一部分，而不应被视为特例。尽可能使用与您的工程组织其余部分相同的工具。您希望依赖于确定数据库层性能的指标和仪表板与应用层指标一样易于访问，甚至在同一仪表板上。无论您使用什么技术或供应商，这种思维方式都将在创造一个每个人都投入到整个堆栈性能并减少工程师可能感受到的功能编写和支持它们的数据库之间的隔阂的环境中发挥作用。

有效跟踪您的指标

在进行业务的长期规划时，有许多事情需要考虑，其中包括但不限于：

• 规划未来的容量
  
• 预见何时需要进行重大改进以及何时足够进行渐进式变化
  
• 规划运行基础设施的成本增加
  

您需要能够不仅在某一特定时间点测量数据存储基础设施的健康状况，还要在长期基础上趋势性能的改善或恶化。这意味着不仅要确定 SLIs 和 SLOs，还要找出哪些 SLIs 和 SLOs 在长期趋势中仍然是有价值的、高信号的指标。您可能会发现，并非所有可用于短期值班决策的指标也适用于长期业务规划。

在深入讨论哪些指标对长期规划至关重要之前，让我们谈谈一些能够支持长期趋势监控的工具。

使用监控工具检查性能

在即时“我们当前是否处于事故”意义上和长期跟踪和趋势意义上，衡量性能都很重要。保存您关心的指标的工具与指标本身一样重要。如果选择了一个好的 SLI，但随后无法适当地查看其随时间的趋势，以一种与组织其他指标相关的方式，那又有什么用呢？

监控工具领域正在迅速发展，对于如何进行监控有很多不同的看法。这里的目标是增加透明度，关注跟踪结果而不是产出。在确保基础架构成功的领域中，追踪成功是一个团队运动。

在这里不讨论具体的工具，而是列出一些在考虑一个工具是否适合这种长期趋势时需要考虑的重要特性和方面。

拒绝平均值

无论您是作为工程组织自行管理指标解决方案，还是使用软件即服务（SaaS），都要注意您的指标解决方案如何对长期存储的数据进行归一化处理。许多解决方案默认将长期数据聚合为平均值（Graphite 是最早这样做的之一），这是一个大问题。如果您需要查看一个指标在几周以上时间段内的趋势，平均值将会平滑下降峰值，这意味着如果您想知道您的磁盘 I/O 利用率是否会在接下来的一年内翻倍，平均数据点的图表很可能会给您一种虚假的安全感。在趋势化几个月的数据时，始终查看峰值，这样您就可以保持偶发性峰值在视图中的准确性。

百分位数是您的朋友

百分位数依赖于对给定时间跨度内的数据点进行排序，并根据目标百分位数（即，如果您寻找第 95 个百分位数，则删除前 5%）来删除最高值。这是使您查看的数据在视��上更类似于我们查看 SLIs 和 SLOs 的一种绝佳方式。如果您可以使显示您的查询响应时间的图表显示第 95 个百分位数，那么您可以更容易地将其与您希望实现的应用请求完成的 SLO 相匹配，并使数据库指标对您的客户支持团队和工程师团队等人员有意义，而不仅仅是对数据库工程团队有意义。

长期保留期和性能

这似乎是显而易见的，但是当尝试显示长时间跨度时，监控工具的性能很重要。如果您正在评估用于业务指标趋势的解决方案，您需要确保在要求越来越长时间跨度的数据时，用户体验如何变化。一个指标解决方案只有在能够提供数据的可用性方面才算好，而不仅仅是摄入速度或数据保留时间。

现在我们已经描述了长期监控工具应该是什么样子，让我们讨论一下到目前为止我们所涵盖的所有内容如何指导您的数据架构选择 SLIs 和 SLOs。

使用 SLOs 指导您的整体架构

在您的业务不断增长的同时保持一致且良好的客户体验绝非易事。随着业务规模的增长，即使保持相同的 SLOs，更不用说设定更雄心勃勃的目标，也变得越来越困难。以可用性为例：每个人都希望数据的读写都能保持尽可能多的连续运行时间。但是，您想要实现的 SLOs 越严格，工作就会变得越昂贵，因为您的数据库每秒事务数或其规模也会成倍增长。

使用我们已经讨论过的 SLIs 和 SLOs，您可以找到增长点，从而有意义地开始将数据分割为功能性分片或数据分区。我们将在第十一章中更详细地讨论使用分片来扩展 MySQL，但这里要强调的重要一点是，告诉您系统当前表现如何的相同 SLIs 和 SLOs 也可以指导您知道何时是投资扩展 MySQL 的时机，以便个别集群在保持维护客户体验的 SLOs 范围内仍然可管理。

拥有一个可以处理短期和长期指标，并能以有用的方式趋势变化的度量解决方案是跟踪战术绩效指标以及数据库基础设施长期影响趋势的一个非常重要的部分。

摘要

在将可靠性工程概念应用于监控数据库基础设施的过程中，不断改进和重新审视您的指标和目标非常重要。它们并不是在您第一次定义一些 SLIs 和 SLOs 后就一成不变的。随着业务的增长，您将更深入地了解客户的体验，这应该推动您改进 SLIs 和 SLOs。

在选择指标并为其分配目标时，请意识到您始终专注于代表客户体验。此外，不要将所有精力都集中在显示事故发生时的指标上，而是花一些时间监控可以帮助您预防事故的事项。这一切都是为了积极主动地保护客户体验。

我们建议在三个关键领域提前设定目标：延迟、可用性和错误。这三个领域可以很好地表明您的客户是否满意。此外，请确保您还在连接增长、磁盘空间、磁盘 I/O 和延迟方面进行积极监控。

我们希望本章能帮助您成功地将可靠性工程应用于监控 MySQL，随着公司规模的扩大。

¹ Nicole Forsgren，加速：精益软件和 DevOps 的科学（IT Revolution Press，2018）。https://oreil.ly/Bfvda

² 我们强烈推荐阅读实施服务水平目标 by Alex Hidalgo（O’Reilly）。

第三章. 性能模式

由 Sveta Smirnova 贡献

在高负载下调整数据库性能是一个迭代循环。每次您进行更改以调整数据库性能时，您需要了解更改是否产生了影响。您的查询是否比以前运行得更快？锁是否减慢了应用程序，或者它们完全消失了？内存使用量是否改变？等待磁盘的时间是否改变？一旦您了解如何回答这些问题，您将能够更快速、更自信地评估和应对日常情况。

性能模式是一个存储回答这些问题所需数据的数据库。本章将帮助您了解性能模式的工作原理、其局限性以及如何最好地使用它——以及其伴随的sys模式——来揭示 MySQL 内部发生的常见信息。

性能模式简介

性能模式提供了 MySQL 服务器内部运行操作的低级度量标准。为了解释性能模式的工作原理，我需要提前介绍两个概念。

第一个是工具。工具指的是我们想要捕获信息的 MySQL 代码的任何部分。例如，如果我们想要收集关于元数据锁的信息，我们需要启用wait/lock/metadata/sql/mdl工具。

第二个概念是消费者，它只是一个存储有关哪些代码被检测的信息的表。如果我们检测查询，消费者将记录关于执行次数、未使用索引次数、花费的时间等信息。消费者是大多数人与性能模式紧密相关的内容。

性能模式的一般功能如图 3-1 所示。

图 3-1. 数据库中运行查询的流程，展示了performance_schema如何收集和聚合数据，然后呈现给数据库管理员

当应用用户连接到 MySQL 并执行一个被检测的指令时，performance_schema将每个检查的调用封装成两个宏，然后将结果记录在相应的消费者表中。这里的要点是启用工具会调用额外的代码，这意味着工具会消耗 CPU。

工具元素

在performance_schema中，setup_instruments表包含所有支持的工具列表。所有工具的名称都由斜杠分隔的部分组成。我将使用以下示例来帮助您理解这些名称是如何命名的：

• statement/sql/select
  
• wait/synch/mutex/innodb/autoinc_mutex
  

工具名称的最左边部分表示工具的类型。因此，statement表示该工具是一个语句，wait表示它是一个等待，依此类推。

名称字段中的其余元素从左到右表示从一般到具体的子系统。在上面的示例中，select是sql子系统的一部分，属于statement类型。或者autoinc_mutex属于innodb，是更通用的mutex类的一部分，而mutex又是wait类型的更通用的sync工具的一部分。

大多数工具名称都是自描述的。如示例中所示，statement/sql/select是一个SELECT查询，而wait/synch/mutex/innodb/autoinc_mutex是 InnoDB 在自增列上设置的互斥体。setup_instruments表中还有一个DOCUMENTATION列，其中可能包含更多细节：

mysql> SELECT * FROM performance_schema.setup_instruments
    -> WHERE DOCUMENTATION IS NOT NULL LIMIT 5, 5\G
*************************** 1\. row ***************************
 NAME: statement/sql/error
 ENABLED: YES
 TIMED: YES
 PROPERTIES:
 VOLATILITY: 0
 DOCUMENTATION: Invalid SQL queries (syntax error).
*************************** 2\. row ***************************
 NAME: statement/abstract/Query
 ENABLED: YES
 TIMED: YES
 PROPERTIES: mutable
 VOLATILITY: 0
 DOCUMENTATION: SQL query just received from the network. At this point, the 
 real statement type is unknown, the type will be refined after SQL parsing.
*************************** 3\. row ***************************
 NAME: statement/abstract/new_packet
 ENABLED: YES
 TIMED: YES
 PROPERTIES: mutable
 VOLATILITY: 0
 DOCUMENTATION: New packet just received from the network. At this point, 
the real command type is unknown, the type will be refined after reading
the packet header.
*************************** 4\. row ***************************
 NAME: statement/abstract/relay_log
 ENABLED: YES
 TIMED: YES
 PROPERTIES: mutable
 VOLATILITY: 0
 DOCUMENTATION: New event just read from the relay log. At this point, the 
real statement type is unknown, the type will be refined after parsing the event.
*************************** 5\. row ***************************
 NAME: memory/performance_schema/mutex_instances
 ENABLED: YES
 TIMED: NULL
 PROPERTIES: global_statistics
 VOLATILITY: 1
 DOCUMENTATION: Memory used for table performance_schema.mutex_instances
5 rows in set (0,00 sec)

不幸的是，对于许多工具，DOCUMENTATION列可能为NULL，因此您需要使用工具名称、直觉和对 MySQL 源代码的了解来理解特定工具检查的内容。

消费者组织

正如我之前提到的，消费者是仪器发送信息的目的地。性能模式将仪器结果存储在许多表中；事实上，MySQL Community 8.0.25 中包含了 110 个performance_schema表。要理解它们的用途，最好将它们分组。

当前和历史数据

事件被放入以以下方式结尾的表中：

*_current

目前在服务器上发生的事件

*_history

每个线程的最后 10 个已完成事件

*_history_long

每个线程全局最后 10,000 个已完成事件

*_history和*_history_long表的大小是可配置的。

可用的当前和历史数据包括：

events_waits

低级服务器等待，例如获取互斥锁

events_statements

SQL 语句

events_stages

概要信息，例如创建临时表或发送数据

events_transactions

事务

摘要表和摘要

摘要表包含有关表建议的聚合信息。例如，memory_summary_by_thread_by_event_name表包含每个 MySQL 线程的用户连接或任何后台线程的聚合内存使用情况。

摘要是通过消除查询中的变体来聚合查询的一种方式。看以下查询示例：

SELECT user,birthdate FROM users WHERE user_id=19;
SELECT user,birthdate FROM users WHERE user_id=13;
SELECT user,birthdate FROM users WHERE user_id=27;

此查询的摘要将是：

SELECT user,birthdate FROM users WHERE user_id=?

这使得性能模式能够跟踪摘要的延迟等指标，而无需保留每个查询的各种变体。

实例

实例指的是 MySQL 安装中可用的对象实例。例如，file_instances表包含文件名以及访问这些文件的线程数。

设置

设置表用于运行时设置performance_schema。

其他表

还有其他表的名称不遵循严格的模式。例如，metadata_locks表保存有关元数据锁的数据。在讨论performance_schema可以帮助解决的问题时，我将在本章稍后介绍其中的一些。

资源消耗

性能模式收集的数据保存在内存中。您可以通过设置消费者的最大大小来限制其使用的内存量。performance_schema中的一些表支持自动缩放。这意味着它们在启动时分配最小内存量，并根据需要调整其大小。但是，一旦分配了内存，即使禁用了特定仪器并截断了表，也永远不会释放这些内存。

正如我之前提到的，每个仪器调用都会添加两个宏调用来存储数据��performance_schema中。这意味着您仪器化越多，CPU 使用率就会越高。对 CPU 利用率的实际影响取决于具体的仪器。例如，与查询期间仅调用一次的与语句相关的仪器不同，等待仪器可能会更频繁地调用。例如，要扫描具有一百万行的 InnoDB 表，引擎将需要设置并释放一百万行锁。如果您仪器化锁定，CPU 使用率可能会显著增加。但是，如果启用语句仪器，同一查询将需要一个调用来确定它是statement/sql/select。因此，如果启用语句仪器，您不会注意到 CPU 负载的增加。内存或元数据锁仪器也是如此。

限制

在讨论如何设置和使用performance_schema之前，了解其局限性是很重要的：

它必须由 MySQL 组件支持。

例如，假设您正在使用内存仪器来计算哪个 MySQL 组件或线程使用了大部分内存。您发现使用最多内存的组件是一个不支持内存仪器的存储引擎。在这种情况下，您将无法找到内存去向。

仅在特定仪器和消费者启用后才收集数据。

例如，如果您启动了一个禁用了所有仪器的服务器，然后决定对内存使用进行仪器化，您将无法知道由全局缓冲区（例如 InnoDB 缓冲池）分配的确切数量，因为在启用内存仪器化之前它已经被分配。

释放内存很困难。

您可以在启动时限制消费者的大小，或者让它们自动调整大小。在后一种情况下，它们在启动时不分配内存，而只有在启用数据收集时才分配内存。然而，即使您稍后禁用特定的工具或消费者，除非重新启动服务器，否则内存不会被释放。

在本章的其余部分，我将假设您已经了解这些限制，因此我不会特别关注它们。

系统模式

自 MySQL 5.7 版本以来，标准 MySQL 发行版包括一个名为sys模式的performance_schema数据的伴随模式。该模式仅由performance_schema上的视图和存储过程组成。虽然它旨在使您与performance_schema的体验更加顺畅，但它本身不存储任何数据。

注意

sys模式非常方便，但您需要记住它只访问存储在performance_schema表中的数据。如果您需要sys模式中不可用的数据，请检查它是否存在于performance_schema中的基础表中。

理解线程

MySQL 服务器是多线程软件。它的每个组件都使用线程。例如，可能��由主线程或存储引擎创建的后台线程，也可能是为用户连接创建的前台线程。每个线程至少有两个唯一标识符：一个操作系统线程 ID，例如，在 Linux 的ps -eLf命令的输出中可见，以及一个内部 MySQL 线程 ID。在performance_schema的大多数表中，这个内部 MySQL 线程 ID 称为THREAD_ID。此外，每个前台线程都有一个分配的PROCESSLIST_ID：连接标识符，在SHOW PROCESSLIST命令输出中可见，或者在使用 MySQL 命令行客户端连接时的“Your MySQL connection id is”字符串中可见。

警告

THREAD_ID不等于PROCESSLIST_ID！

performance_schema中的threads表包含服务器中存在的所有线程：

mysql> SELECT NAME, THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID 
    -> FROM performance_schema.threads;
+------------------------+-----------+----------------+--------------+
| NAME                   | THREAD_ID | PROCESSLIST_ID | THREAD_OS_ID |
+------------------------+-----------+----------------+--------------+
| thread/sql/main        |         1 |           NULL |       797580 |
| thread/innodb/io_ib... |         3 |           NULL |       797583 |
| thread/innodb/io_lo... |         4 |           NULL |       797584 |
...
| thread/sql/slave_io    |        42 |              5 |       797618 |
| thread/sql/slave_sql   |        43 |              6 |       797619 |
| thread/sql/event_sc... |        44 |              7 |       797620 |
| thread/sql/signal_h... |        45 |           NULL |       797621 |
| thread/mysqlx/accep... |        46 |           NULL |       797623 |
| thread/sql/one_conn... |     27823 |          27784 |       797695 |
| thread/sql/compress... |        48 |              9 |       797624 |
+------------------------+-----------+----------------+--------------+
44 rows in set (0.00 sec)

除了线程编号信息外，threads表包含与SHOW PROCESSLIST输出相同的数据以及一些附加列，例如RESOURCE_GROUP或PARENT_THREAD_ID。

警告

性能模式在各处使用THREAD_ID，而PROCESSLIST_ID仅在threads表中可用。如果您需要获取PROCESSLIST_ID，例如为了终止持有锁的连接，您需要查询threads表以获取其值。

threads表可以与许多其他表连接，以提供有关正在运行的查询的附加信息（例如，查询数据，锁定，互斥锁或打开的表实例）。

在本章的其余部分，我希望您熟悉这个表以及THREAD_ID的含义。

配置

性能模式的一些部分只能在服务器启动时更改：启用或禁用性能模式本身以及与收集数据的内存使用和限制相关的变量。性能模式仪器和消费者可以动态启用或禁用。

提示

您可以启动性能模式，所有消费者和仪器都被禁用，并且只在您期望问题发生之前启用那些需要解决特定问题的仪器。这样，您将不会在不需要的地方花费任何资源在性能模式上，也不会因为过度仪器化而使系统陷入困境。

启用和禁用性能模式

要启用或禁用性能模式，请将变量performance_schema相应地设置为ON或OFF。这是一个只读变量，只能在配置文件中或在 MySQL 服务器启动时通过命令行参数更改。

启用和禁用仪器

仪器可以启用或禁用。要查看仪器的状态，可以查询setup_instruments表：

mysql> SELECT * FROM performance_schema.setup_instruments 
    -> WHERE NAME='statement/sql/select'\G
*************************** 1\. row ***************************
 NAME: statement/sql/select
 ENABLED: NO
 TIMED: YES
 PROPERTIES:
 VOLATILITY: 0
 DOCUMENTATION: NULL
1 row in set (0.01 sec)

正如我们所见，ENABLED是NO；这告诉我们我们目前没有对SELECT查询进行仪器化。

有三种选项可以启用或禁用performance_schema仪器：

• 使用setup_instruments表。
  
• 调用sys模式中的ps_setup_enable_instrument存储过程。
  
• 使用启动参数performance-schema-instrument。
  

更新语句

第一种方法是使用UPDATE语句更改列值：

mysql> UPDATE performance_schema.setup_instruments
    -> SET ENABLED='YES' WHERE NAME='statement/sql/select';
Query OK, 1 rows affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

由于这是标准 SQL，您也可以使用通配符来启用所有 SQL 语句的仪器：

mysql> UPDATE performance_schema.setup_instruments
    -> SET ENABLED='YES' WHERE NAME LIKE statement/sql/%';
Query OK, 167 rows affected (0.00 sec)
Rows matched: 167 Changed: 167 Warnings: 0

此方法在重新启动之间不会持久化。

存储过程 sys

sys模式提供了两个存储过程—ps_setup_enable_instrument和ps_setup_disable_instrument—它们通过参数传递启用和禁用仪器。这两个例程都支持通配符。如果要启用或禁用所有支持的仪器，请使用通配符'%'：

mysql> CALL sys.ps_setup_enable_instrument('statement/sql/select');
+----------------------+
| summary              |
+----------------------+
| Enabled 1 instrument |
+----------------------+
1 row in set (0.01 sec)

此方法实际上与前一种方法完全相同，包括在重新启动之间不会持久化。

启动选项

如前所述，这两种方法都允许您在线更改performance_schema配置，但不会在服务器重新启动之间存储该更改。如果要在重新启动之间保存特定仪器的选项，请使用配置参数performance-schema-instrument。

此变量支持performance-schema-instrument='instrument_name=value'语法，其中instrument_name是仪器名称，value为启用仪器的ON、TRUE或1；禁用的为OFF、FALSE或0；对于计数而不是TIMED的为COUNTED。您可以多次指定此选项以启用或禁用不同的仪器。该选项还支持通配符：

performance-schema-instrument='statement/sql/select=ON'

警告

如果指定了多个选项，则较长的仪器字符串优先于较短的，无论顺序如何。

启用和禁用消费者

与仪器一样，消费者可以通过以下方式启用或禁用：

• 更新性能模式中的setup_consumers表
  
• 在sys模式中使用存储过程ps_setup_enable_consumer和ps_setup_disable_consumer
  
• 设置performance-schema-consumer配置参数
  

有 15 个可能的消费者。其中一些具有相当自明的名称，但有一些消费者的名称需要更多解释，列在表 3-1 中。

表 3-1. 消费者及其目的

为仪器给出的示例对于消费者是可重复的，使用所述方法。

为特定对象调整监控

性能模式允许您为特定对象类型、模式和名称启用和禁用监控。这是在setup_objects表中完成的。

OBJECT_TYPE列可能具有五个值之一：EVENT、FUNCTION、PROCEDURE、TABLE和TRIGGER。此外，您可以指定OBJECT_SCHEMA和OBJECT_NAME。支持通配符。

例如，要禁用test数据库中触发器的performance_schema，请使用以下语句：

mysql> INSERT INTO performance_schema.setup_objects
    -> (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, ENABLED) 
    -> VALUES ('TRIGGER', 'test', '%', 'NO');

如果要为名为my_trigger的触发器添加异常，请使用以下语句：

mysql> INSERT INTO performance_schema.setup_objects
    -> (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, ENABLED) 
    -> VALUES ('TRIGGER', 'test', 'my_trigger', 'YES');

当performance_schema决定是否需要对特定对象进行仪器化时，首先搜索更具体的规则，然后退而求其次。例如，如果用户在触发test.my_trigger的表上运行查询，它将检查触发器触发的语句。但如果用户在触发名为test.some_other_trigger的触发器的表上运行查询，则不会检查触发器。

对于对象没有配置文件选项。如果需要在重新启动期间保留对此表的更改，您需要编写这些INSERT语句到一个 SQL 文件中，并使用init_file选项在启动时加载 SQL 文件。

调整线程监视

setup_threads表包含一个可以监视的后台线程列表。ENABLED列指定特定线程的仪器化是否已启用。HISTORY列指定特定线程的仪器化事件是否也应存储在_history和_history_long表中。

例如，要禁用事件调度程序（thread/sql/event_scheduler）的历史记录，请运行：

mysql> UPDATE performance_schema.setup_threads SET HISTORY='NO' 
    -> WHERE NAME='thread/sql/event_scheduler';

setup_threads表不存储用户线程的设置。为此，存在setup_actors表，其中包含表 3-2 中描述的列。

表 3-2。setup_actors表中包含的列

要为特定帐户指定规则，请使用以下命令：

mysql> INSERT INTO performance_schema.setup_actors 
    -> (HOST, USER, ENABLED, HISTORY) 
    -> VALUES ('localhost', 'sveta', 'YES', 'NO'), 
    -> ('example.com', 'sveta', 'YES', 'YES'), 
    -> ('localhost', '%', 'NO', 'NO');

此语句启用了sveta@localhost和sveta@example.com的仪器化，禁用了sveta@localhost的历史记录，并禁用了所有其他从localhost连接的用户的仪器化和历史记录。

与对象监视一样，线程和参与者没有配置文件选项。如果需要在重新启动期间保留对此表的更改，您需要将这些INSERT语句写入 SQL 文件，并使用init_file选项在启动时加载 SQL 文件。

高性能 MySQL 第四版（GPT 重译）（一）(3)https://developer.aliyun.com/article/1484263