跳转至

内存概述

MySQL分配缓冲区和缓存以提高数据库操作的性能。默认配置旨在让MySQL服务器在具有大约512MB RAM的虚拟机上启动。您可以通过增加某些缓存和缓冲区相关系统变量的值来提高 MySQL 性能。您还可以修改默认配置以在内存有限的系统上运行 MySQL。

MySQL的内存占用主要由两部分组成，global_buffers 与 all_thread_buffers，其中 global_buffers 为全局共享缓存，all_thread_buffers 为所有线程独立缓存

MySQL以两种方式使用内存：

1、永久保留供其使用的内存 – 此类称为"全局缓冲区"的内存在服务器启动期间从操作系统获取，不会释放到任何其他进程。

2、根据请求动态请求的内存 – MySQL使用"线程缓冲区"，这是在处理新查询时从操作系统请求的内存。执行查询后，此内存将释放回操作系统。

因此，您可以说MySQL的内存使用量是"全局缓冲区+（线程缓冲区x允许的最大连接数）"。

内存是重要的性能参数，常常出现由于异常的 SQL 请求以及待优化的数据库导致内存利用率升高的情况，严重时还会出现由于 OOM 导致实例发生 HA 切换的情况。

一个常见问题是实例占用大量内存或内存不足 (OOM) 问题。运行内存利用率高的数据库实例通常会导致性能问题、停滞，甚至数据库停机。

某些 MySQL 内存块在全局内使用。这意味着所有查询工作负载共享内存位置，始终被占用，并且仅在 MySQL 进程停止时释放。

某些内存块基于会话，这意味着一旦会话关闭，该会话使用的内存就会释放回系统。

MySQL 的内存大体可以分为共享内存和 session 私有内存两部分，下面将详细介绍各部分的构成。

https://opensource.actionsky.com/20201009-mysql/

共享内存

MySQL 在服务器启动时分配全局缓冲区，这些缓冲区在所有连接之间共享。MySQL 的大部分内存被全局缓冲区（例如 innodb_buffer_pool_size、innodb_log_buffer_size、key_buffer_size 等）消耗了。

  mysql>
mysql>   show variables where variable_name in (
    ->     'innodb_buffer_pool_size','innodb_log_buffer_size','innodb_additional_mem_pool_size','key_buffer_size','query_cache_size'
    ->     );
+-------------------------+-------------+
| Variable_name           | Value       |
+-------------------------+-------------+
| innodb_buffer_pool_size | 60129542144 |
| innodb_log_buffer_size  | 16777216    |
| key_buffer_size         | 8388608     |
+-------------------------+-------------+
3 rows in set (0.00 sec)

mysql>

参数说明：

  • innodb_buffer_pool

    该部分缓存是 Innodb 引擎最重要的缓存区域，是通过内存来弥补物理数据文件的重要手段。其中主要包含数据页、索引页、undo 页、insert buffer、自适应哈希索引、锁信息以及数据字典等信息。在进行 SQL 读和写的操作时，首先并不是对物理数据文件操作，而是先对 buffer_pool 进行操作，然后再通过 checkpoint 等机制写回数据文件。该空间的优点是可以提升数据库的性能、加快 SQL 运行速度，缺点是故障恢复速度较慢。

  • innodb_log_buffer

    该部分主要存放 redo log 的信息，在 RDS 上会设置 1 M 的大小。InnoDB 会首先将 redo log 写在这里，然后按照一定频率将其刷新回重做日志文件中。该空间不需要太大，因为一般情况下该部分缓存会以较快频率刷新至 redo log（Master Thread 会每秒刷新、事务提交时会刷新、其空间少于 ½ 时同样会刷新）。

  • innodb_additional_mem_pool

    该部分主要存放 InnoDB 内的一些数据结构，在 RDS 中统一设置为 2 M。通常是在 buffer_pool 中申请内存的时候还需要在额外内存中申请空间存储该对象的结构信息。该大小主要与表数量有关，表数量越大需要更大的空间。

  • key_buffer

    该部分是 MyISAM 表的重要缓存区域，所有实例统一为 16 M。该部分主要存放 MyISAM 表的键。MyISAM 表不同于 InnoDB 表，其缓存的索引缓存是放在 key_buffer 中的，而数据缓存则存储于操作系统的内存中。RDS 的系统是 MyISAM 引擎的，因此在 RDS 中是给予该部分一定量的空间的。

  • query_cache

    该部分是对查询结果做缓存以减少解析 SQL 和执行 SQL 的花销，在 RDS 上关闭了该部分的缓存。主要适合于读多写少的应用场景，因为它是按照 SQL 语句的 hash 值进行缓存的，当表数据发生变化后即失效。

Session 私有内存

共享内存中介绍的内存空间是实例创建时即分配的内存空间，并且是所有连接共享的。而出现 OOM异常的实例都是由于下面各个连接私有的内存造成的。

mysql>
mysql>  show variables where variable_name in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','binlog_cache_size','tmp_table_size' );
+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| binlog_cache_size    | 32768      |
| join_buffer_size     | 262144     |
| read_buffer_size     | 131072     |
| read_rnd_buffer_size | 262144     |
| sort_buffer_size     | 268435456  |
| tmp_table_size       | 2147483648 |
+----------------------+------------+
6 rows in set (0.00 sec)

mysql>
mysql>

参数说明：

  • read_buffer&read_rnd_buffer

    分别存放了对顺序和随机扫描（例如按照排序的顺序访问）的缓存，RDS 给每个 session 设置 256 K 的大小。当 thread 进行顺序或随机扫描数据时会首先扫描该 buffer 空间以避免更多的物理读。

  • sort_buffer_size

    sort_buffer_size 是一个connection级参数，在每个connection需要buffer的时候，一次性分配的内存。sort_buffer_size并不是越大越好，过大的设置+高并发可能会耗尽系统内存资源。需要执行 order by 和 group by 的 SQL 都会分配 sort_buffer，用于存储排序的中间结果，MySQL上默认是256 K。在排序过程中，若存储量大于sort_buffer_size，则会在磁盘生成临时表以完成操作。在 Linux 系统中，当分配空间大于 2M 时会使用 mmap() 而不是 malloc() 来进行内存分配，导致效率降低。

  • join_buffer_size

    MySQL 仅支持 nest loop 的 join 算法，RDS 设置 256 K 的大小。处理逻辑是驱动表的一行和非驱动表联合查找，这时就可以将非驱动表放入 join_buffer，不需要访问拥有并发保护机制的 buffer_pool。

  • binlog_cache

    该区域用来缓存该 thread 的 binlog 日志，RDS 设置 256 K 的大小。在一个事务还没有 commit 之前会先将其日志存储于 binlog_cache 中，等到事务 commit 后会将其 binlog 刷回磁盘上的 binlog 文件以持久化。

  • tmp_table

    不同于上面各个 session 层次的 buffer，这个参数可以在控制台上修改。该参数是指用户内存临时表的大小，如果该 thread 创建的临时表超过它设置的大小会把临时表转换为磁盘上的一张 MyISAM 临时表。如果用户在执行事务时遇到类似如下这样的错误，可以考虑增大 tmp_table 的值。

MySQL内存占用分析

在 MySQL 使用过程中，你一定出现过内存飙升、OOM、内存长期处于高位等内存异常现象。为了排查内存异常，我们需要分析内存占用情况，找出内存占用大户。

内存监控表

我们可以从以下几张表中分析内存，每张表对应不同的维度，有用户维度、主机维度、进程维度。为了打开 performance_schema 功能，我们需要在 MySQL 配置中设置 performance_schema = ON。

SELECT * FROM information_schema.tables WHERE table_schema='performance_schema' AND table_name LIKE '%memory%';

-- performance_schema.memory_summary_by_account_by_event_name
-- performance_schema.memory_summary_by_host_by_event_name
-- performance_schema.memory_summary_by_thread_by_event_name
-- performance_schema.memory_summary_by_user_by_event_name
-- performance_schema.memory_summary_global_by_event_name

--查看 MySQL 总消耗内存
select * from sys.memory_global_total;



--根据事件，查看内存总体利用情况
select * from sys.memory_global_by_current_bytes limit 10;



--查看线程内存占用情况
select thread_id,event_name,CURRENT_NUMBER_OF_BYTES_USED/1024/1024 from performance_schema.memory_summary_by_thread_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 20;