排查 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 的实例，将 mysqld 进程限制为使用 92% 的实例 RAM，InnoDB 缓冲区池在 mysqld 进程可用内存的 75-80% 范围内。除了 InnoDB 缓冲区池之外，MySQL 还分配了多个缓冲区和缓存以提高性能。请参阅这篇文章，了解 MySQL 中的内存分配。

当 mysqld 内存用量超出为 mysqld 进程配置的上限时，Linux OOM killer 会终止 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 表的“ENABLED”列来启用它们。

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。