Google Cloud 的 Cloud SQL for MySQL 是一项全托管式数据库服务,可帮助您在 Google Cloud Platform 上设置、维护、管理和控制 MySQL、PostgreSQL 或 SQL Server 数据库。Cloud SQL 在其舰队中运行大量 MySQL 数据库,您经常会看到一些 MySQL 实例占用大量内存并因内存不足 (OOM) 错误而崩溃。本文将帮助您排查 MySQL 内存问题。
如果某个进程尝试分配内存,但因服务器没有可用的内存而失败,则会出现内存不足 (OOM) 情况。这通常会产生 Linux OOM Killer,Linux 内核在系统内存严重不足时会采用该进程,从而终止数据库进程。
我们并不想要由 OOM 条件导致的数据库崩溃,因为数据库进程是使用 SIGKILL 指令终止的,该指令使应用没有机会正常关闭 MySQL。这会导致以下问题:
从 5.7 版开始,MySQL 在 performance_schema 中添加了内存插桩,这有助于了解 MySQL 的内存分配情况。本文将帮助您使用 performance_schema 来排查 MySQL 内存问题。
每个 Cloud SQL 实例都由一个主机 Google Cloud 服务器上运行的虚拟机 (VM) 提供支持。每个虚拟机运行数据库程序(如 MySQL Server)以及提供支持性的服务代理(如日志记录和监控)。Cloud SQL 会为操作系统 (OS)、服务代理预留一部分内存,并为 mysqld 等数据库程序分配剩余内存。
下表显示了 mysqld 进程的内存分配以及每种机器类型的默认 InnoDB 缓冲区池配置。
机器类型 | 实例 RAM GIB(字节) | mysqld 内存 (GB) | InnoDB 缓冲区池 GiB(字节) | 缓冲区池(实例 RAM 的百分比) | 缓冲区池(占 mysqld 内存的百分比) |
db-g1-small | 1.7(1825361101) | 1.11 | 0.625(671088640) | 37% | 56% |
db-custom-1-3840 | 3.75(4026531840) | 2.97 | 1.375(1476395008) | 37% | 46% |
db-custom-2-7680 | 7.5(8053063680) | 6.72 | 4(4294967296) | 53% | 60% |
db-custom-2-13312 | 13(13958643712) | 12 | 9(9663676416) | 69% | 75% |
db-custom-4-15360 | 15(16106127360) | 14 | 10.5(11274289152) | 70% | 76% |
db-custom-4-26624 | 26(27917287424) | 24 | 19(20401094656) | 73% | 79% |
db-custom-8-30720 | 30(32212254720) | 28 | 22(23622320128) | 73% | 80% |
db-custom-8-53248 | 52(55834574848) | 48 | 38(40802189312) | 73% | 79% |
db-custom-16-61440 | 60(64424509440) | 55 | 44(47244640256) | 73% | 80% |
db-custom-16-106496 | 104(111669149696) | 96 | 75(80530636800) | 72% | 78% |
db-custom-32-122880 | 120(128849018880) | 110 | 87(93415538688) | 73% | 79% |
db-custom-32-212992 | 208(223338299392) | 191 | 150(161061273600) | 72% | 78% |
db-custom-64-245760 | 240(257698037760) | 221 | 173(185757335552) | 72% | 78% |
db-custom-96-368640 | 360(386547056640) | 331 | 260(279172874240) | 72% | 79% |
db-custom-64-425984 | 416(446676598784) | 383 | 300(322122547200) | 72% | 78% |
db-custom-96-638976 | 624(670014898176) | 574 | 450(483183820800) | 72% | 78% |
机器类型
实例 RAM GIB(字节)
mysqld 内存 (GB)
InnoDB 缓冲区池 GiB(字节)
缓冲区池(实例 RAM 的百分比)
缓冲区池(占 mysqld 内存的百分比)
db-g1-small
1.7(1825361101)
1.11
0.625(671088640)
37%
56%
db-custom-1-3840
3.75(4026531840)
2.97
1.375(1476395008)
37%
46%
db-custom-2-7680
7.5(8053063680)
6.72
4(4294967296)
53%
60%
db-custom-2-13312
13(13958643712)
12
9(9663676416)
69%
75%
db-custom-4-15360
15(16106127360)
14
10.5(11274289152)
70%
76%
db-custom-4-26624
26(27917287424)
24
19(20401094656)
73%
79%
db-custom-8-30720
30(32212254720)
28
22(23622320128)
73%
80%
db-custom-8-53248
52(55834574848)
48
38(40802189312)
73%
79%
db-custom-16-61440
60(64424509440)
55
44(47244640256)
73%
80%
db-custom-16-106496
104(111669149696)
96
75(80530636800)
72%
78%
db-custom-32-122880
120(128849018880)
110
87(93415538688)
73%
79%
db-custom-32-212992
208(223338299392)
191
150(161061273600)
72%
78%
db-custom-64-245760
240(257698037760)
221
173(185757335552)
72%
78%
db-custom-96-368640
360(386547056640)
331
260(279172874240)
72%
79%
db-custom-64-425984
416(446676598784)
383
300(322122547200)
72%
78%
db-custom-96-638976
624(670014898176)
574
450(483183820800)
72%
78%
对于具有 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 秒)
以下是每种分配类型的详细信息。
分配类型 | 详细信息 |
memory/innodb/buf_buf_pool | InnoDB 缓冲区池所使用的内存 |
memory/temptable/physical_ram | 内部临时表所使用的内存 |
memory/innodb/hash0hash | InnoDB 哈希表所使用的内存 |
memory/innodb/memory | 各种 InnoDB 缓冲区所使用的内存 |
memory/sql/TABLE | TABLE 对象及其内存根所使用的内存 |
分配类型
详细信息
memory/innodb/buf_buf_pool
InnoDB 缓冲区池所使用的内存
memory/temptable/physical_ram
内部临时表所使用的内存
memory/innodb/hash0hash
InnoDB 哈希表所使用的内存
memory/innodb/memory
各种 InnoDB 缓冲区所使用的内存
memory/sql/TABLE
TABLE 对象及其内存根所使用的内存
此视图汇总了按用户分组的内存用量,如果某个用户有多个连接,则会汇总该用户所有实例的内存用量。
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 结构如何使用内存。系统架构视图使信息更易于解读。如果您遇到与内存相关的问题,此功能将帮助您找出问题的根源,您可以通过更改配置/查询来解决相应问题。
例如:
如果您遇到 MySQL 实例使用的内存高于正常用量的情况,且内存用量不断增加,直到服务器耗尽内存,而 performance_schema 表明总内存与插桩内存存在差距,或者无法指明内存消耗在什么地方,则说明系统可能存在内存泄漏情况。如果您怀疑存在内存泄漏情况,可以采取以下措施: