MySQL 实际内存分配

男娘i 2023-07-08 09:14 8阅读 0赞

### 背景 ###

**MySQL 版本:** MySQL 5.7.20  
**硬件配置:** 8C + 32GB + 1000GB

## MySQL实际内存分配 ##

MySQL 的内存是一个重要的性能参数，常常出现由于异常的SQL请求以及待优化的数据库导致内存利用率升高的情况，严重时还会出现由于OOM导致数据库宕机，在没有做HA的情况下导致整个业务线的瘫痪。

MySQL的内存大体可以分为**共享内存**和**session私有内存**两部分。

### 共享内存 ###

执行如下命令，即可查询示例的共享内存参数分配情况:

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_additional_mem_pool_size | 8388608         |
    | innodb_buffer_pool_size         | 25769803776     |
    | innodb_log_buffer_size          | 8388608         |
    | key_buffer_size                 | 16777216        |
    | query_cache_size                | 3145728         |
    +---------------------------------+-----------------+
    共返回 5 行记录,花费 342.74 ms.

**参数说明**  
**◎ innodb\_buffer\_pool\_size**  
  该部分缓存是Innodb引擎最重要的缓存区域，是通过内存来弥补物理数据文件的重要手段，数据库实例上会采用实例规格配置的75%作为该部分大小。其中主要包括数据页、索引页、undo页、insert buffer、自适应哈希索引、锁信息以及数据字典等信息。在进行SQL读和写的操作时，首先并不是对物理数据文件操作，而是先对buffer\_pool进行操作，然后再通过checkpoint等机制写回数据文件。该空间的优点是可以提升数据库性能、加快SQL运行速度，缺点是故障恢复速度较慢。  
**◎ innodb\_log\_buffer\_size**  
  该部分主要存放redo log的信息，在RDS上会设置1M的大小。InnoDB会首先将redo log写在这里，然后按照一定频率将其刷新回重做日志文件中。该空间不需要太大，因为一般情况下该部分缓存会以较快刷新频率刷新至redo log(Master Thread会每秒刷新、事务提交时会刷新、其空间少于1/2时同样会刷新)。  
**◎ innodb\_additional\_mem\_pool\_size**  
  该部分主要存放InnoDB内的一些数据结构，在RDS中统一设置为2M。通常是在buffer\_pool中申请内存的时候还需要在额外内存中申请空间存储该对象的结构信息。该大小主要与表数量有关，表数量越大需要的空间越大。  
**◎ key\_buffer**  
  该部分是MyISAM表的重要缓存区域，所有实例统一为16M。该部分主要存放MyISAM表的键。MyISAM表不同于InnoDB表，其缓存的索引缓存是放在key\_buffer中的，而数据缓存则存储于操作系统的内存中。RDS的系统是MyISAM引擎的，因此在RDS中是给予该部分一定量的空间的。  
**◎ query\_cache**  
  该部分是对查询结果做缓存以减少解析SQL和执行SQL的花销，在RDS上关闭了该部分的缓存。主要适合于读多写少的应用场景，因为他是按照SQL语句的hash值进行缓存的，当表数据发生变化后即失效。

### Session私有内存 ###

共享内存中介绍的内存空间是实例创建时即分配的内存空间，并且是所有连接共享的。而出现OOM异常的实例都是由于下面各个连接私有的内存造成的。  
执行如下命令，查询示例的session私有内存分配情况:

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       | 262144          |
    | join_buffer_size        | 262144          |
    | read_buffer_size        | 262144          |
    | read_rnd_buffer_size    | 262144          |
    | sort_buffer_size        | 262144          |
    | tmp_table_size          | 262144          |
    +-------------------------+-----------------+
    共返回 6 行记录,花费 356.54 ms.

**参数说明**  
**◎ read\_buffer\_size & read\_rnd\_buffer\_size**  
  分别存放了对顺序和随机扫描(例如按照排序的顺序访问)的缓存，RDS给每个session设置256K的大小。当thread进行顺序或随机扫描数据是会首先扫描该buffer空间避免更多的物理读。  
**◎ sort\_buffer\_size**  
  需要执行order by和group by的SQL都会分配sort buffer，用于存储排序的中间结果，在RDS上设置256K。在排序过程中，若存储量大于sort\_buffer\_size，则会在磁盘生成临时表以完成操作。在Linux系统中，当分配空间大于2M是会使用mmap()而不是malloc()来进行内存分配，导致效率降低。  
**◎ join\_buffer\_size**  
  MySQL仅支持nest loop的join算法，RDS设置256K的大小。处理逻辑是驱动表的一行和非驱动表联合查找，这时就可以 将非驱动表放入join\_buffer，不需要访问拥有并发保护机制的buffer\_pool。  
**◎ binlog\_cache\_size**  
  该区域用来缓存该thread的binlog日志，RDS设置256K的大小。在一个事务还没有commit之前会将其日志存储于binlog\_cache中，等到事务commit后会将binlog刷回磁盘上的binlog文件以持久化。  
**◎ tmp\_table\_size**  
  不同于上面各个session层次的buffer，这个参数可以在控制台上修改。该参数是指用户内存临时表的大小，如果该thread创建的临时表超过它设置的大小会把临时表转换为磁盘上的一张MyISAM临时表。如果用户在执行事务时遇到类似如下这样的错误，可以考虑增大tmp\_table的值.

[Err] 1114 - The table '/home/mysql/data3081/tmp/#sql_6197_2' is full