如何解决 Amazon RDS for MySQL 数据库中可用内存低的问题?

上次更新日期:2022 年 2 月 14 日

我正在运行 Amazon Relational Database Service (Amazon RDS) for MySQL 实例。我看到可用内存很低,数据库内存不足,或者内存不足导致应用程序出现延迟。如何识别哪些资源在使用内存,如何解决可用内存低的问题?

简短描述

在 Amazon RDS for MySQL 中,您可以监控四种内存状态:

  • 活动:数据库进程或线程主动消耗的内存。
  • 缓冲区:缓冲区是内存中的临时空间,用于存放数据块。
  • 可用内存:可供使用的内存。
  • 缓存:缓存是一种将数据暂时存储在内存中,以便能够快速检索数据的技术。

默认情况下,当您创建 Amazon RDS for MySQL 实例时,系统会分配缓冲区和缓存以改进数据库操作。Amazon RDS for MySQL 还有一个内部内存组件(例如 key_buffers_size 或 query_cache_siz),用于创建内部临时表以执行某些操作。

当您使用 Amazon RDS for MySQL 时,请务必了解 MySQL 如何使用和分配内存。确定正在使用内存的组件后,您可以在实例级别和数据库级别查找瓶颈。然后,监控这些具体指标并配置会话以获得最佳性能。

解决方法

MySQL 如何使用内存

在 Amazon RDS for MySQL 中,实例上 80% 到 90% 的可用内存使用默认参数进行分配。此分配对于性能而言是最佳的,但如果您设置使用更多内存的参数,则可修改其他参数以使用更少的内存来进行补偿。

您可以如下所示计算 RDS for MySQL 数据库实例的大致内存使用量:

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

缓冲池

全局缓冲区和缓存包括 Innodb_buffer_pool_sizeInnodb_log_buffer_sizekey_buffer_size query_cache_size 等组件。innodb_buffer_pool_size 参数是 RAM 的内存区域,其中 innodb 会缓存数据库表和与索引相关的数据。较大的缓冲池需要较少的转移回磁盘的输入/输出操作。默认情况下,innodb_buffer_pool_size 最多使用分配给 Amazon RDS 数据库实例的 75% 的可用内存:

innodb_buffer_pool_size = {DBInstanceClassMemory*3/4}

确保首先查看此参数以识别正在使用内存的资源。然后,考虑通过修改自定义参数组中的参数值来缩小 innodb_buffer_pool_size 的值。

例如,可以将默认的 DBInstanceClassMemory*3/4 减少到 *5/8*1/2。确保实例的 BufferCacheHitRatio 值不会太低。如果 BufferCacheHitRatio 值很低,可能需要增加实例大小,以便拥有更多 RAM。有关更多信息,请参阅为 Amazon RDS for MySQL 配置参数的最佳实践,第 1 部分:与性能相关的参数

MySQL 线程

内存还会分配给连接到 MySQL 数据库实例的每个 MySQL 线程。以下线程需要分配的内存:

  • thread_stack
  • net_buffer_length
  • read_buffer_size
  • sort_buffer_size
  • join_buffer_size
  • max_heap_table_size
  • tmp_table_size

此外,MySQL 还会创建内部临时表以执行某些操作。这些表最初创建作为基于内存的表。当这些表的大小达到 tmp_table_sizemax_heap_table_size 指定的值(以最小值为准)时,则表将转换为基于磁盘的表。当多个会话创建内部临时表时,您可能会看到内存利用率上升。要降低内存利用率,请避免在查询中使用临时表。

注意:当您提高限额 tmp_table_sizemax_heap_table_size 时,较大的临时表就能够存储在内存中。要确认是否已创建隐式临时表,请使用 created_tmp_tables 变量。有关此变量的更多信息,请参阅 MySQL 网站上 created_tmp_tables

JOIN 和 SORT 操作

如果在 JOIN 或 SORT 操作期间分配了同一类型的多个缓冲区(例如 join_buffer_sizesort_buffer_size),内存使用量将增加。例如,MySQL 会分配一个 JOIN 缓冲区以在两个表之间执行 JOIN。如果查询涉及多个表 JOIN,并且所有查询都需要 JOIN 缓冲区,那么 MySQL 分配的 JOIN 缓冲区数量会比表的总数少一个。如果未优化查询,则使用过高的值配置会话变量可能会引发问题。您可以为会话级别变量分配最小内存,例如 join_buffer_size、join_buffer_sizesort_buffer_size。有关更多信息,请参阅使用数据库参数组

如果对 MYISAM 表执行批量插入,请使用 bulk_insert_buffer_size 个字节的内存。有关更多信息,请参阅 MySQL 存储引擎使用最佳实践

性能架构

如果您为 Amazon RDS for MySQL 上的情能详情启用了性能架构,那么性能架构会消耗内存。启用性能架构后,MySQL 会在实例启动时和服务器操作期间分配内部缓冲区。有关性能架构如何使用内存的更多信息,请参阅 MySQL 文档中的性能架构内存分配模型

除了性能架构表之外,还可以使用 MySQL sys 架构。例如,您可以使用 performance_schema 事件来显示为性能架构使用的内部缓冲区分配的内存量。或者,您也可以通过运行这样的查询来查看分配了多少内存:

SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

setup_instruments 表采用“memory/code_area/instrument_name”的格式列出了内存设备。要启用内存检测,请更新 setup_instruments 表中相关工具的 ENABLED(已启用)列:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

监控实例的内存使用情况

Amazon CloudWatch 指标

当可用内存不足时,监控 DatabaseConnectionsCPUUtilizationReadIOPSWriteIOPSAmazon CloudWatch 指标

对于 DatabaseConnections,务必要注意,需要为与数据库建立的每个连接分配一定量的内存。因此,数据库连接激增可能会导致可用内存减少。在 Amazon RDS 中,max_connections 的软限制计算方式如下:

{DBInstanceClassMemory/12582880}

通过查看 Amazon CloudWatch 中的 DatabaseConnections 指标来监控您是否超出了这一软限制。

此外,除 FreeableMemory 之外,还可以通过监控 SwapUsage 的 CloudWatch 指标来检查内存压力。如果您发现使用了大量的交换区,并且 FreeableMemory 的值较低,那么您的实例可能正面临较高的内存压力。高内存压力会影响数据库性能。最佳做法是将内存压力水平保持在 95% 以下。有关更多信息,请参阅在内存足够时,为什么我的 Amazon RDS 实例会使用交换内存?

增强监控

要监控数据库实例上的资源利用率,请参阅启用增强监控。然后,将粒度设置为一到五秒(默认值为 60 秒)。通过增强监控功能,您可以实时监控可用的活动内存。

您还可以通过列出数据库实例的线程来监控占用最多 CPU 和内存的线程:

mysql> select THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID from performance_schema.threads;

然后,将 thread_OS_ID 映射到 thread_ID:

select p.* from information_schema.processlist p, performance_schema.threads t 
where p.id=t.processlist_id and t.thread_os_id=<Thread ID from EM processlist>;

排查可用内存低的问题

如果您遇到可用内存较低的问题,请考虑采用以下问题排查技巧:

  • 确保为数据库分配了足够的资源来运行查询。使用 Amazon RDS 时,分配的资源量取决于实例类型。此外,某些查询(例如存储的过程)在运行时可能会占用无限量的内存。
  • 通过将大型查询分解为多个较小的查询来避免长时间运行的事务。
  • 要查看数据库中的所有活动连接和查询,请使用 SHOW FULL PROCESSLIST 命令。如果您观察到使用 JOIN 或 SORE 操作的查询运行时间较长,那么优化程序必须有足够的 RAM 才能计算计划。此外,如果您确定有需要临时表的查询,则必须有额外的内存分配给该表。
  • 要查看长时间运行的事务、内存利用率统计数据和锁定,请使用 SHOW ENGINE INNODB STATUS 命令。查看输出结果并检查缓冲池和内存条目。缓冲池和内存条目提供有关 InnoDB 内存分配的信息,例如“分配的内存总量”、“内部哈希表”和“缓冲池大小”。InnoDB 状态还有助于提供有关锁存器、锁定和死锁的其他信息。
  • 如果您的工作负载经常遭遇死锁,请修改自定义参数组中的 innodb_lock_wait_timeout 参数。InnoDB 依赖 innodb_lock_wait_timeout 设置在发生死锁时回滚事务。
  • 要优化数据库性能,请确保正确调整查询。否则,您可能会遇到性能问题并延长等待时间。
  • 使用 Amazon RDS 性能详情来帮助您监控数据库实例并识别任何有问题的查询。
  • 监控 Amazon CloudWatch 指标(例如 CPU 利用率、IOPS、内存和交换使用情况),以使实例不会受到限制。
  • 对 FreeableMemory 指标设置 CloudWatch 警报,以便在可用内存达到 95% 时收到通知。最佳做法是保持至少 5% 的可用实例内存。
  • 定期将您的实例升级到更新的次要版本的 MySQL。较旧的次要版本更有可能包含与内存泄漏相关的错误。