排查 MySQL 内存问题

如果某个进程尝试分配内存，但因服务器没有可用的内存而失败，就会出现内存不足 (OOM) 情况。这通常会导致 Linux OOM Killer，Linux 内核在系统内存严重不足时会使用该进程来终止数据库进程。

因 OOM 条件而导致数据库崩溃的情况并不理想，因为使用 SIGKILL 指令终止数据库进程，使得应用没有机会正常关闭 MySQL。这会导致以下问题：

• MySQL 服务器已宕机（与此同时，它会完成崩溃恢复，这可能需要几分钟），影响用户和业务

• MySQL 服务器需要额外的时间来完全预热缓冲区和缓存，才能实现峰值性能
• 由于意外崩溃而导致数据损坏的可能性

从 5.7 版开始，MySQL 在 performance_schema 中添加了内存插桩，有助于了解 MySQL 的内存分配情况。本文将帮助您使用 performance_schema 排查 MySQL 内存问题。

每个 Cloud SQL 实例都由一个主机 Google Cloud 服务器上运行的虚拟机 (VM) 提供支持。每个虚拟机运行数据库程序（如 MySQL Server）以及提供支持性的服务代理（如日志记录和监控）。Cloud SQL 会为操作系统 (OS)、服务代理预留部分内存，并为 mysqld 等数据库程序分配剩余内存。

下表显示了 mysqld 进程的内存分配情况以及每种机器类型的默认 InnoDB 缓冲区池配置。

对于具有 10GB RAM 及以上 RAM 的实例，mysqld 进程只能使用 92% 的实例 RAM，而 InnoDB 缓冲区池配置为 mysqld 进程可用内存的 75-80%。除 InnoDB 缓冲区池外，MySQL 还会分配多个缓冲区和缓存以提高性能。如需了解 MySQL 中的内存分配，请参阅这篇文章。

当 mysqld 内存使用量超过 mysqld 进程配置的上限时，Linux OOM 终止程序会终止 mysqld 进程，因为它是实例内存占用量最大的进程。

performance_schema 是一项 MySQL 功能，用于在较低级别监控服务器执行情况。Cloud SQL for MySQL 允许在 RAM 大小为 3 GB 或更高的实例上启用 performance_schema。从 MySQL 8.0 版开始，在具有至少 15 GB RAM 的实例中，此功能默认处于启用状态。

在 MySQL 5.6 和 5.7 版中，performance_schema 默认处于停用状态。您可以使用数据库标志将其启用。

启用 performance_schema 会产生一些性能开销，并且还会影响内存。如需了解内存分配，请参阅性能架构内存分配模型。

performance_schema 内存插桩有助于监控 MySQL 中的内存使用情况。内存插桩的名称采用 memory/code_area/instrument_name 形式，其中 code_area 是一个值，例如 sql 或 Innodb，instrument_name 是插桩详细信息。

默认情况下，MySQL 5.7 会启用一些内存插桩，但并非所有内存插桩。您可以通过更新 performance_schema.setup_instruments 表的“已启用”列来启用它们。

mysql> select version();

+-------------------+

| version()         |

+-------------------+

| 5.7.39-google-log |

+-------------------+

集合中的 1 行（0.00 秒）

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |       70 |

| NO      |      306 |

+---------+----------+

集合 2 行（0.00 秒）

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

查询成功，306 行受到影响（0.00 秒）

匹配的行数：376；已更改的数量：306；警告数：0

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |      376 |

+---------+----------+

集合中的 1 行（0.00 秒）

在运行时启用内存插桩将仅计算启用插桩后创建的内存对象。因此，建议您等待服务器收集到足够的数据来排查问题。

默认情况下，MySQL 8.0 会启用所有内存插桩。

mysql> select version();

+---------------+

| version()     |

+---------------+

| 8.0.28-google |

+---------------+

集合中的 1 行（0.00 秒）

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |      490 |

+---------+----------+

集合中的 1 行（0.00 秒）

系统架构包含许多视图，这些视图将 performance_schema 数据汇总成易于理解的形式。大多数视图成对出现。二者的名称相同，但其中一个名称的前缀是 x$。MySQL 5.7 和 8.0 中提供以下视图，用于查看不同级别的内存分配。

mysql> use sys;

数据库已更改

mysql> show tables like '%memory%';

+-------------------------------------+

| Tables_in_sys (%memory%)            |

+-------------------------------------+

| memory_by_host_by_current_bytes     |

| memory_by_thread_by_current_bytes   |

| memory_by_user_by_current_bytes     |

| memory_global_by_current_bytes      |

| memory_global_total                 |

| x$memory_by_host_by_current_bytes   |

| x$memory_by_thread_by_current_bytes |

| x$memory_by_user_by_current_bytes   |

| x$memory_global_by_current_bytes    |

| x$memory_global_total               |

+-------------------------------------+

集中 10 行（0.02 秒）

sys.memory_global_total 视图以可读格式汇总总内存用量。

mysql> select * from sys.memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

| 12.43 GiB       |

+-----------------+

集合中有 1 行（0.44 秒）

sys.x$memory_global_total 显示相同的信息，但以字节为单位。

mysql> select * from sys.x$memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

|     13351482992 |

+-----------------+

集中 1 行（1.39 秒）

不带 x$ 前缀的视图旨在提供更便于用户阅读的输出。以原始形式显示相同值的带有 x$ 前缀的视图主要适用于对数据执行自行处理的其他工具。

sys.memory_global_by_current_bytes 视图显示按分配类型 (event_name) 分组的当前内存用量，例如 memory/innodb/buf_buf_pool（InnoDB 缓冲区池）。默认情况下，行按所用内存量降序排序。

在我们深入了解每种分配类型之前，最好先大致了解一下每个代码区域（例如 Innodb、sql、performance_schema 等）的内存用量。

以下查询按代码区域聚合当前分配的内存。

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,

FORMAT_BYTES(SUM(current_alloc)) AS current_alloc

FROM sys.x$memory_global_by_current_bytes

GROUP BY SUBSTRING_INDEX(event_name,'/',2)

ORDER BY SUM(current_alloc) DESC;

+---------------------------+---------------+

| code_area                 | current_alloc |

+---------------------------+---------------+

| memory/innodb             | 11.28 GiB     |

| memory/performance_schema | 520.02 MiB    |

| memory/temptable          | 514.00 MiB    |

| memory/sql                | 106.12 MiB    |

| memory/mysys              | 43.25 MiB     |

| memory/vio                | 372.15 KiB    |

| memory/myisam             |  696 bytes    |

| memory/csv                |   88 bytes    |

| memory/blackhole          |   88 bytes    |

+---------------------------+---------------+

集中 9 行（1.02 秒）

此输出显示，innodb 的内存消耗量最大，其后是 performance_schema 和临时表。

现在，查询 sys.memory_global_by_current_bytes 就会直接显示 MySQL 内部结构的内存用量，如 event_name 列所述。如果您正在排查内存异常增加的问题，可以通过查看此视图的输出来确定原因。

mysql> select event_name,current_alloc,high_alloc from sys.memory_global_by_current_bytes limit 5;

+-------------------------------+---------------+------------+

| event_name                    | current_alloc | high_alloc |

+-------------------------------+---------------+------------+

| memory/innodb/buf_buf_pool    | 10.72 GiB     | 10.72 GiB  |

| memory/temptable/physical_ram | 514.00 MiB    | 515.00 MiB |

| memory/innodb/hash0hash       | 292.69 MiB    | 292.69 MiB |

| memory/innodb/memory          | 191.77 MiB    | 197.94 MiB |

| memory/sql/TABLE              | 60.39 MiB     | 62.35 MiB  |

+-------------------------------+---------------+------------+

集中 5 行（0.45 秒）

以下是每种分配类型的详细信息。

此视图汇总了按用户分组的内存用量，如果某个用户有多个连接，则会汇总该用户所有实例的内存用量。

mysql> select user,current_allocated from memory_by_user_by_current_bytes;

+-----------------+-------------------+

| user            | current_allocated |

+-----------------+-------------------+

| sbtest          | 60.30 MiB         |

| background      | 2.68 MiB          |

| root            | 1.52 MiB          |

| event_scheduler | 16.38 KiB         |

+-----------------+-------------------+

集中 4 行（1.16 秒）

如果您对每个应用/作业使用不同的用户（推荐），此视图有助于确定特定应用或作业是否消耗了高内存。

此视图汇总了按主机名分组的内存使用情况。

mysql> select host,current_allocated from memory_by_host_by_current_bytes;

+------------+-------------------+

| host       | current_allocated |

+------------+-------------------+

| 10.128.0.8 | 62.10 MiB         |

| background | 2.70 MiB          |

| 127.0.0.1  | 311.01 KiB        |

| localhost  | 16.38 KiB         |

+------------+-------------------+

集中 4 行（1.58 秒）

此视图有助于确定每个客户端主机的内存用量。如今，拥有多个客户端/应用服务器的情况十分常见，此视图有助于确定是否有任何特定客户端或工作负载导致了高内存用量。您也可以先在一个客户端/应用服务器上部署更改，监控其对内存的影响（通过将内存使用情况与其他客户端主机进行比较），并做出明智的决策。

此视图汇总了内存使用情况（按线程分组），这有助于查找占用大部分内存的线程。

mysql> select thread_id,user,current_allocated from sys.memory_by_thread_by_current_bytes limit 5;

+-----------+-------------------+-------------------+

| thread_id | user              | current_allocated |

+-----------+-------------------+-------------------+

|     12999 | sbtest@10.128.0.8 | 3.80 MiB          |

|     12888 | sbtest@10.128.0.8 | 3.75 MiB          |

|     12760 | sbtest@10.128.0.8 | 3.68 MiB          |

|     13128 | sbtest@10.128.0.8 | 3.67 MiB          |

|     13221 | sbtest@10.128.0.8 | 3.47 MiB          |

+-----------+-------------------+-------------------+

集中 5 行（2.29 秒）

如果您发现某个特定线程消耗了高内存，可以使用 thread_id 过滤 MySQL 慢查询日志或常规日志，并确定导致内存增加的会话/查询。

对高内存用量进行问题排查是一项具有挑战性的任务。幸运的是， performance_schema 内存插桩可帮助用户了解内部 MySQL 结构如何使用内存。系统架构视图使信息更易于解释。如果您遇到与内存相关的问题，此功能将帮助您找出问题的根源，您可以通过更改配置/查询来解决问题。

例如：

1. 如果内部临时表需要大量内存，您可以使用 tmp_table_size 限制临时表的大小，或者减小 innodb_buffer_pool_size 以便为每个会话的缓冲区腾出空间
2. 如果特定线程消耗高内存，您可以使用 thread_id 过滤慢查询日志以确定有问题的查询，并对其进行调整以减少该线程/会话的内存占用量

如果您遇到 MySQL 实例使用的内存高于正常用量的情况，且内存用量不断增加，直到服务器耗尽内存，而 performance_schema 表明总内存与插桩内存存在差距，或者无法指明内存消耗在什么地方，则说明系统可能存在内存泄漏情况。如果您怀疑存在内存泄漏情况，可以采取以下措施：

• 仔细查看 MySQL 配置，并确认没有过度分配缓冲区/缓存。这篇文章将帮助您了解 MySQL 如何为不同的缓冲区/缓存分配内存。
• 确定导致内存问题的功能/配置/查询，并尝试重现问题。
• 查看同一系列中下一个次要版本的 MySQL 版本说明，看看是否修复了具有类似行为的任何 bug。
• 将 MySQL 升级到最新的次要版本，看看是否能解决内存问题。
• 搜索 MySQL bug 数据库，查看是否有其他社区用户报告过类似问题。
• 如果同一个问题不存在现有 bug，请使用可重现的测试用例创建新 bug。