MySQL 如何使用内存
MySQL 实例消耗了大量内存或遇到内存不足 (OOM) 问题，这是一个常见问题。在内存压力下运行的数据库实例通常会导致性能问题、停滞甚至应用停机。
在开始为 MySQL 实例分配内存之前，请务必先了解 MySQL 如何使用内存。本文将重点介绍 MySQL 的哪些功能会导致内存消耗，这通常会导致 OOM 问题，还讨论了 Google Cloud 的全代管式产品 Cloud SQL for MySQL 如何设置内存管理。
全局缓冲区
MySQL 会在服务器启动时分配全局缓冲区，并且这些缓冲区会在所有连接之间共享。MySQL 的大部分内存都由全局缓冲区（例如 innodb_buffer_pool_size、innodb_log_buffer_size、key_buffer_size 等）消耗。
InnoDB 缓冲区池
InnoDB 缓冲区池通常是 MySQL 实例中最大的内存消耗方。它使用 innodb_buffer_pool_size 参数进行配置。它用于缓存表数据和索引、更改缓冲区、自适应哈希索引和其他内部结构。Cloud SQL for MySQL 是 Google Cloud 的代管式 MySQL 产品，可根据实例大小配置 innodb_buffer_pool_size，最高可配置 72% 的实例内存。
InnoDB 为缓冲区和关联的数据结构预留额外的内存，分配的内存总大小大约比指定缓冲区池大小多 10%。您可以在 show engine innodb status\G 输出中查看 InnoDB 缓冲区池内存用量。
|
mysql> show engine innodb status\G … ---------------------- 缓冲区池和内容 ---------------------- 分配的大内存总量 11511349248 … |
InnoDB 日志缓冲区
InnoDB 日志缓冲区用于保存要写入磁盘上的 InnoDB 重做日志文件的更改。它使用 innodb_log_buffer_size 进行配置。 MySQL 社区默认值为 16 MB，Cloud SQL for MySQL 也使用相同大小。
键缓冲区大小
MySQL 使用键缓冲区在内存中缓存 MyISAM 索引。它使用 key_buffer_size 进行配置。 MySQL 社区默认值为 8 MB，Cloud SQL for MySQL 也使用相同大小。Cloud SQL for MySQL 不支持 MyISAM 表，因此您可以将其保留为默认值。
查询缓存大小
query_cache_size 配置变量定义了为缓存查询结果分配的内存量。在 MySQL Community 5.7 和 Cloud SQL for MySQL 5.7 中，该功能默认处于停用状态。
查询缓存因严重的扩缩问题而广为人知，因此在 MySQL 5.7.20 中已被弃用，并在 MySQL 8.0 中已移除。如果您仍在 MySQL 5.7 实例中使用查询缓存，请检查它是否对您的工作负载确实有用。如需了解详情，请参阅此博客。
全局缓存
MySQL 会分配在所有连接之间共享的全局缓存，这些缓存会动态分配，而配置变量则为其定义上限。
表缓存
MySQL 使用表缓存加快表的打开速度。 MySQL 表缓存分为两部分，即打开表的缓存和表定义的缓存，分别使用 table_open_cache 和 table_definition_cache 进行配置。
table_open_cache 是内存缓存，用于存储所有已连接线程上已打开表的文件描述符。 增加此值会增加 mysqld 程序（也称为 MySQL 服务器）所需的文件描述符数量。请确保您的操作系统可以处理 table_open_cache 设置隐含的打开文件描述符的数量。
多个客户端会话可以同时访问指定表，并且每个并发客户端会话均可单独打开该表。因此，您可能会看到打开的表数高于服务器中的表数。当表缓存已满时，服务器会从当前最不常使用的表开始释放当前未使用的表。
table_definition_cache 是用于存储表定义的内存缓存。它具有全局性，并且可在所有连接之间共享。
CloudSQL for MySQL 5.7 使用 2000 和 1400 作为 table_open_cache 和 table_definition_cache 的默认值。
CloudSQL for MySQL 8.0 使用 4000 和 2000 作为 table_open_cache 和 table_definition_cache 的默认值。
线程缓存
对于每个客户端连接，MySQL 会分配一个专用线程来执行所有查询，并将结果返回给客户端，直到客户端断开连接为止。MySQL 会缓存线程，这样就不必为每个连接创建和销毁线程。线程缓存中的线程数使用 thread_cache_size 变量进行配置。
CloudSQL for MySQL 使用 48 作为 thread_cache_size 的默认值。
InnoDB 数据字典缓存
InnoDB 拥有自己的缓存来存储表定义，这与表打开缓存和表定义缓存不同。您可以在 show engine innodb status\G 输出中检查为 InnoDB 数据字典分配的内存。
|
---------------------- 缓冲区池和内容 ---------------------- … 字典内存分配了 65816817 |
table_definition_cache 对 InnoDB 数据字典缓存中的表实例数量设置了软限制，如果 InnoDB 数据字典缓存中的表实例数量超过了 table_definition_cache 限制，则 LRU 机制会开始将表实例标记为逐出并最终将其从此缓存中移除
这是否表示 InnoDB 数据字典缓存中的表实例数始终低于 table_definition_cache 限制？事实并非如此，具有外键关系的表实例不在 LRU 列表中。这些实例会保持缓存状态，并会导致表实例超出 table_definition_cache 限制，从而增加内存使用量。具有外键关系的表消耗的内存仅在 MySQL 关停/重启事件时释放。此问题同时存在于 MySQL 5.7 和 8.0 中，并且存在已知通过验证的 bug。
如果您的 MySQL 实例中有大量具有外键关系的表，则 InnoDB 数据字典缓存可能会占用多个 GB 的内存。在配置 MySQL 缓冲区/缓存时，它往往会被忽略，这可能是出现意外的高内存用量或内存不足 (OOM) 问题的原因之一。
一个示例输出，显示了一个占用 InnoDB 数据字典缓存 4.16 GB 大小的小型实例。
|
$ mysql -e "show engine innodb status\G" | grep -i memory 缓冲区池和内容 分配的总大内存 7696023552 字典内存分配了 4465193358 |
会话缓冲区
MySQL 的另一项消耗内存的功能是会话缓冲区。这些缓冲区是基于每个会话分配的，在某些情况下，可以为单个查询（尤其是 join_buffer_size）分配多个缓冲区实例。
这些缓冲区仅在查询需要时（用于排序、联接、索引/全表扫描等）进行分配；但当需要这些缓冲区时，即使需要非常小的部分，系统也会将其分配到完整大小。将这些缓冲区设置为较高的值可能会导致内存浪费。
MySQL 社区和 Cloud SQL for MySQL 中的默认值相同。
二进制日志缓存
MySQL 使用二进制日志缓存来保存事务运行期间对二进制日志所做的更改。它使用 binlog_cache_size 进行配置。 如果启用了二进制日志记录 (log_bin=ON)，则系统会为每个客户端分配二进制日志缓存。
在 MySQL 社区和 Cloud SQL for MySQL 中，binlog_cache_size 的默认值是相同的。
临时表
MySQL 在处理某些类型的查询（如 GROUP BY、ORDER BY、DISTINCT 和 UNION）时，会创建内部临时表来存储中间结果。这些内部临时表首先在内存中创建，然后在达到最大大小时转换为磁盘表。内部临时表的大小上限确定为 tmp_table_size 和 max_heap_table_size 变量的最小值。
在 MySQL 社区和 Cloud SQL for MySQL 中，tmp_table_size 和 max_heap_table_size 的默认值相同。
注意：由于每个会话的缓冲区和内存中的临时表会为每个连接分别分配内存，因此如果连接数量较大，则整体内存用量可能会非常高。建议不要将这些值设置得过高，请尝试为您的工作负载找到最佳值。
每个连接的内存
每个线程只需要少量内存即可管理客户端连接。以下变量可控制其大小。
- thread_stack：每个线程的堆栈大小，默认值为 256KB。
- net_buffer_length：每个客户端都与一个连接缓冲区和 net_buffer_length 的结果缓冲区相关联。此值可以进一步增加到 max_allowed_packet。
Performance_schema
启用 performance_schema 有助于在低级别监控 MySQL 服务器执行情况。Performance_schema 会动态分配内存，且只有在 MySQL 关停/重启时才会释放。
Cloud SQL for MySQL 允许在 RAM 大小为 15 GB 或更高的实例上启用 performance_schema，从 MySQL 8.0.26 版本开始，默认处于启用状态。 对于 MySQL 5.6、5.7 和 8.0.18 版本，Performance_schema 默认处于停用状态，可以使用数据库标志将其启用。
总结
Cloud SQL for MySQL 会自动配置与内存相关的参数，以实现开箱即用的性能。如果工作负载需要更大的缓存来支持大量表和/或连接，您的实例可能仍会遇到 OOM 问题。许多线程同时分配会话缓冲区也会导致内存相关问题。为全局缓冲区/缓存分配的内存越多，可用于连接、会话缓冲区的内存就越少，反之亦然。找到良好的平衡是关键所在。
如果您的工作负载需要更大的缓存/会话缓冲区，您可以使用 innodb_buffer_pool_size 缩减全局缓冲区的大小。您可以使用数据库标志更改 Cloud SQL for MySQL 实例的配置参数。 如果您仍然遇到 OOM 或性能问题，则可以升级实例大小以增加内存。
相关产品和服务
Google Cloud 提供旨在满足您业务需求的代管式 MySQL 数据库，可以完成包括弃用本地数据中心、运行 SaaS 应用和迁移核心业务系统在内的各种任务。
