排查 Azure Database for MySQL - 灵活服务器中的低内存问题

适用于：Azure Database for MySQL - 单一服务器 Azure Database for MySQL - 灵活服务器

为了帮助确保 Azure Database for MySQL 灵活服务器实例达到最佳性能，请务必实现适当的内存分配和利用率。 默认情况下，创建 Azure Database for MySQL 灵活服务器实例时，可用的物理内存取决于为工作负载选择的层级和大小。 此外，为缓冲区和缓存分配内存以改进数据库操作。 有关详细信息，请参阅 MySQL 如何使用内存。

请注意，Azure Database for MySQL 灵活服务器会消耗内存以实现尽可能多的缓存命中。 因此，内存利用率通常会徘徊在实例可用物理内存的 80-90% 之间。 除非查询工作负载的进度出现问题，否则这不是问题。 但是，出于以下原因，你可能会遇到内存不足问题：

• 配置了太大的缓冲区。
• 运行次优查询。
• 查询执行联接和对大型数据集进行排序。
• 将数据库服务器上的最大连接数设置得太高。

服务器的大部分内存由 InnoDB 的全局缓冲区和缓存使用，其中包括 innodb_buffer_pool_size、innodb_log_buffer_size、key_buffer_size 和 query_cache_size 等组件。

innodb_buffer_pool_size 参数的值指定 InnoDB 缓存数据库表和索引相关数据的内存区域。 MySQL 尝试在缓冲池中容纳尽可能多的表和索引相关数据。 缓冲池越大，转移到磁盘的 I/O 操作就越少。

监视内存使用量

Azure Database for MySQL 灵活服务器提供一系列指标来衡量数据库实例的性能。 若要更好地了解数据库服务器的内存利用率，请查看“主机内存百分比”或“内存百分比”指标。

如果你发现内存利用率突然增加并且可用内存迅速下降，请监视其他指标，例如“主机 CPU 百分比”、“总连接数”和“IO 百分比”，以确定工作负载的突然激增是否是问题的根源。

请务必注意，与数据库服务器建立的每个连接都需要分配一定数量的内存。 因此，数据库连接激增可能会导致内存短缺。

内存利用率高的原因

让我们看看导致 MySQL 中内存利用率高的更多原因。 这些原因取决于工作负载的特征。

临时表的增加

MySQL 使用“临时表”，临时表是一种特殊类型的表，旨在存储临时结果集。 在会话期间，可多次重复使用临时表。 由于创建的任何临时表都是会话的本地表，因此不同的会话可以有不同的临时表。 在有许多会话执行大型临时结果集编译的生产系统中，你应该定期检查全局状态计数器 created_tmp_tables，它跟踪高峰时段创建的临时表的数量。 大量内存中临时表可能会快速导致 Azure Database for MySQL 灵活服务器实例中的可用内存不足。

使用 MySQL，临时表大小由两个参数的值决定，如下表所述。

建议

若要排查与临时表相关的内存不足问题，请考虑以下建议。

• 在增加 tmp_table_size 值之前，请验证数据库是否已正确索引，尤其是对于联接中涉及的列和按操作分组的列。 对基础表使用适当的索引会限制创建的临时表数。 在不验证索引的情况下增加此参数和 max_heap_table_size 参数的值可能会导致低效查询在没有索引的情况下运行，并创建不必要的临时表。
• 调整 max_heap_table_size 和 tmp_table_size 参数的值以满足工作负载的需求。
• 如果为 max_heap_table_size 和 tmp_table_size 参数设置的值太低，临时表可能会定期溢写到存储中，从而增加查询的延迟。 可使用全局状态计数器 created_tmp_disk_tables 跟踪溢写到磁盘的临时表。 通过比较 created_tmp_disk_tables 和 created_tmp_tables 变量的值，可查看已创建的内部磁盘上临时表的数量与已创建的内部临时表的总数之比。

表缓存

作为多线程系统，MySQL 维护表文件描述符的缓存，这样这些表就可以被多个会话同时独立地打开。 MySQL 使用一些内存和 OS 文件描述符来维护此表缓存。 变量 table_open_cache 定义了表缓存的大小。

建议

若要排查与表缓存相关的内存不足问题，请考虑以下建议。

• 参数 table_open_cache 指定所有线程打开的表的数量。 增加此值会增加 mysqld 所需的文件描述符数。 可通过检查 show global status 计数器中的 opens_tables 状态变量来检查是否需要增加表缓存。 以增量方式增加此参数的值以适应工作负载。
• 将 table_open_cache 设置得过低可能会导致 Azure Database for MySQL 灵活服务器花费更多时间来打开和关闭查询处理所需的表。
• 将此值设置得太高可能会导致使用更多内存，操作系统运行文件描述符，从而导致拒绝连接或无法处理查询。

其他缓冲区和查询缓存

排查与内存不足相关的问题时，可使用更多缓冲区和缓存来帮助解决问题。

网络缓冲区 (net_buffer_length)

网络缓冲区是每个客户端线程的连接和线程缓冲区的大小，可以增长到为 max_allowed_packet 指定的值。 例如，如果查询语句很大，则所有插入/更新都具有非常大的值，则增加 net_buffer_length 参数的值将有助于提高性能。

联接缓冲区 (join_buffer_size)

当联接无法使用索引时，联接缓冲区将分配给缓存表行。 如果在没有索引的情况下数据库执行了许多联接，请考虑添加索引以加快联接速度。 如果无法添加索引，请考虑增加 join_buffer_size 参数的值，该值指定每个连接分配的内存量。

排序缓冲区 (sort_buffer_size)

排序缓冲区用于对某些 ORDER BY 和 GROUP BY 查询执行排序。 如果你在 SHOW GLOBAL STATUS 输出中看到每秒有很多次 Sort_merge_passes，请考虑增加 sort_buffer_size 值以加快 ORDER BY 或 GROUP BY 操作的速度，这些操作无法使用查询优化或更好的索引来改进。

避免任意增加 sort_buffer_size 值，除非你有相关信息另有说明。 按连接分配此缓冲区的内存。 在 MySQL 文档中，“服务器系统变量”文章指出，在 Linux 上，有两个阈值 256 KB 和 2 MB，使用较大的值会显着减慢内存分配。 因此，请避免将 sort_buffer_size 值增加到 2M 以上，因为性能损失大于任何好处。

查询缓存 (query_cache_size)

查询缓存是用于缓存查询结果集的内存区域。 query_cache_size 参数确定为缓存查询结果分配的内存量。 默认情况下，查询缓存被禁用。 此外，查询缓存在 MySQL 5.7.20 版本中已弃用，并在 MySQL 8.0 版本中已删除。 如果解决方案中当前启用了查询缓存，则在禁用它之前，请确认没有任何依赖于它的查询。

计算缓冲区缓存命中率

在 Azure Database for MySQL 灵活服务器环境中，缓冲区缓存命中率对于了解缓冲池是否可以容纳工作负载请求很重要，通常就经验而言，比较好的做法是使缓冲池缓存命中率始终超过 99%。

若要计算读取请求的 InnoDB 缓冲池命中率，可运行 SHOW GLOBAL STATUS 以检索计数器“Innodb_buffer_pool_read_requests”和“Innodb_buffer_pool_reads”，然后使用下面显示的公式计算值。

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

请考虑以下示例。

mysql> show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 197   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_read_requests | 22479167 |
+----------------------------------+----------+
1 row in set (0.00 sec)

使用上述值，计算读取请求的 InnoDB 缓冲池命中率会产生以下结果：

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100 

Buffer hit ratio = 99.99%

除了选择语句缓冲区缓存命中率之外，对于任何 DML 语句，对 InnoDB 缓冲池的写入都发生在后台。 但是，如果必须读取或创建页面并且没有可用的干净页面，则还必须先等待页面刷新。

Innodb_buffer_pool_wait_free 计数器计算这种情况发生的次数。 大于 0 的 Innodb_buffer_pool_wait_free 是一个强有力的指标，表明 InnoDB 缓冲池太小，需要增加缓冲池大小或实例大小以适应进入数据库的写入。

建议

• 确保数据库分配了足够的资源来运行查询。 有时，可能需要纵向扩展实例大小以获取更多的物理内存，使缓冲区和缓存以适应工作负载。
• 通过将大型或长时间运行的事务分解为较小的事务来避免这些事务。
• 使用对“主机内存百分比”的警报，以便在系统超过任何指定阈值时收到通知。
• 使用 Query Performance Insight 或 Azure 工作簿来识别任何有问题或运行缓慢的查询，然后对其进行优化。
• 对于生产数据库服务器，请定期收集诊断信息，以确保一切顺利运行。 如果不是，请排查并解决你识别的任何问题。

后续步骤

若要查找同行对你的最重要问题的回答，或者要发布或回答问题，请访问 Stack Overflow。