如何对 Amazon RDS for MySQL 数据库中可用内存不足的问题进行故障排除?

2 分钟阅读
0

我在运行一个 Amazon Relational Database Service (Amazon RDS) for MySQL 实例。我的可用内存不足,数据库内存不足,或者内存不足导致应用程序出现延迟问题。

简短描述

要解决内存不足的问题,请先检查 Amazon RDS for MySQL 如何使用内存。有关更多信息,请参阅 MySQL 网站上的 8.12.4.1 MySQL 如何使用内存。识别使用内存的组件。然后,在实例和数据库级别寻找瓶颈。最后,监控这些组件的指标并配置会话以实现最佳性能。

解决方法

查看 RDS for MySQL 如何使用内存

为了优化性能,RDS for MySQL 会将实例上 80% 到 90% 的可用内存分配给默认参数。如果您设置的参数使用更多内存,则修改其他参数以使用更少的内存进行补偿。

要计算您的 RDS for MySQL 数据库实例的大致内存使用量,请使用以下公式: MySQL 最大内存使用量 = 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_size 参数设定的是 RAM 中的内存区域,InnoDB 在此缓存数据库表和索引相关数据。较大的缓冲池可以减少转移回磁盘的 I/O 操作。默认情况下,innodb_buffer_pool_size 最多使用分配给 Amazon RDS 数据库实例的 75% 的可用内存:innodb_buffer_pool_size = DBInstanceClassMemory*3/4

要确定内存使用来源,请先检查 innodb_buffer_pool_size。然后,如果需要,修改自定义参数组中的参数值以减少 innodb_buffer_pool_size

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

MySQL 线程

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

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

此外,MySQL 会创建临时内部表来执行某些操作。当表达到 tmp_table_sizemax_heap_table_size 的最低值时,MySQL 会将表从基于内存的表转换为基于磁盘的表。如果多个会话创建临时内部表,则您可能会看到内存使用量增加。为了减少内存使用量,请勿在查询中使用临时表。

注意:当您增加tmp_table_sizemax_heap_table_size 值时,可以将更大的临时表存放内存中。要验证 MySQL 是否创建了隐式临时表,请使用 created_tmp_tables 变量。有关此变量的更多信息,请参阅 MySQL 网站上的 Created_tmp_tables

JOIN 和 SORT 操作

在 JOIN 和 SORT 操作期间,如果 MySQL 分配多个相同类型的缓冲区,例如 join_buffer_sizesort_buffer_size,则内存使用量将会增加。例如,MySQL 分配一个 JOIN 缓冲区来执行两个表的 JOIN 操作。对于使用多表 JOIN 且所有查询都需要 JOIN 缓冲区的查询,MySQL 分配的 JOIN 缓冲区比表总数少一个。如果您使用过高的值配置会话变量,则如果未对查询进行优化,会遇到问题。为诸如 join_buffer_sizesort_buffer_size 等会话级变量分配所需的最小内存。

**注意:**如果您对 MYISAM 表执行批量插入,则 MySQL 会使用 bulk_insert_buffer_size 字节的内存。有关详细信息,请参阅使用 MySQL 的最佳实践

性能架构

如果您开启性能详情,MySQL 会在您启动实例时和服务器操作期间为性能架构分配内部缓冲区。有关性能架构如何使用内存的更多信息,请参阅 MySQL 网站上的 29.17 性能架构内存分配模型

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

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 列设置为 YES

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

监控您的实例的内存使用量

CloudWatch 指标

当可用内存不足时,使用 Amazon RDS 控制台上的“Monitoring”(监控)选项卡,监控 DatabaseConnectionsCPUUtilizationReadIOPSWriteIOPS Amazon CloudWatch 指标。

对于 DatabaseConnections,与数据库建立的每个连接都需要为其分配内存。因此,数据库连接激增可能导致可用内存迅速减少。使用以下公式计算估计的最大 max_connections 配额: DBInstanceClassMemory/12582880。要检查你是否超过了这个配额,请检查 DatabaseConnections 指标。

要检查内存压力,请监控 SwapUsageFreeableMemory CloudWatch 指标。高交换使用率和低可用内存可能会给您的实例带来较高的内存压力。最佳实践是将内存压力水平保持在 95% 以下。高内存压力会影响数据库性能。有关更多信息,请参阅为什么尽管内存足够,我的 Amazon RDS 数据库实例仍在使用虚拟内存?

增强监控

要监控数据库实例的资源利用率,请激活增强监控。然后,将粒度设置为 1 到 5 秒之间。默认粒度为 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;

**注意:**将 thread-ID 替换为线程 ID。

解决可用内存不足的问题

确保为数据库分配了足够的资源来运行查询。对于 Amazon RDS,分配的资源量取决于实例类型。此外,某些查询(例如存储过程)在运行时可能会无限制地占用内存。将大型查询分成较小的查询,以避免长时间运行的事务。

要查看数据库中的所有活动连接和查询,请使用 SHOW FULL PROCESSLIST 命令。有关 SHOW FULL PROCESSLIST 和示例命令的更多信息,请参阅 MySQL 网站上的 13.7.5.29 SHOW PROCESSLIST 语句。如果您发现带有 JOIN 或 SORTS 操作的查询运行了很长时间,则必须有足够的 RAM 让优化器来计算计划。此外,如果您确定需要临时表的查询,则必须有额外的内存来分配给该表。

要查看长时间运行的事务、内存使用率统计信息或锁定,请使用 SHOW ENGINE INNODB STATUS 命令。有关 SHOW ENGINE 命令的更多信息,请参阅 MySQL 网站上的 13.7.5.15 SHOW ENGINE 语句。查看输出并检查 BUFFER POOL AND MEMORY 条目以了解有关 InnoDB 内存分配的信息,例如 Total Memory AllocatedInternal Hash TablesBuffer Pool Size。InnoDB 状态还提供有关锁存、锁和死锁的信息。如果您的工作负载经常遇到死锁,请修改自定义参数组中的 innodb_lock_wait_timeout 参数。发生死锁时,InnoDB 依赖 innodb_lock_wait_timeout 设置来回滚事务。

要优化数据库性能,请优化和调整查询。使用 Amazon RDS 性能详情来监控数据库实例并识别任何存在问题的查询。另外,在 FreeableMemory 指标上设置 CloudWatch 警报,这样当可用内存已经使用了 95% 时,您就会收到通知。最佳实践是保持至少 5% 的实例内存处于空闲状态。

定期升级您的实例的 MySQL 次要版本。早期的次要版本可能包含与内存泄漏相关的错误。有关 MySQL 版本的更多信息,请参阅 MySQL 网站上的 MySQL 8.0 发行说明

相关信息

Amazon RDS 的监控工具