mysql内存分配参数_MySQL实际内存分配情况介绍

内存是重要的性能参数，常常出现由于异常的sql请求以及待优化的数据库导致内存利用率升高，更有甚者由于OOM导致实例发生HA切换。

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

1. 共享内存

以下为240M内存规格RDS实例的共享内存分配示意：

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_additional_mem_pool_size | 2097152 |

| innodb_buffer_pool_size | 67108864 |

| innodb_log_buffer_size | 1048576 |

| key_buffer_size | 16777216 |

| query_cache_size | 0 |

+---------------------------------+-----------------+

共返回 5 行记录,花费 342.74 ms.

innodb_buffer_pool

该部分缓存是innodb引擎最重要的缓存区域，是通过内存来弥补物理数据文件的重要手段。其中主要包含有数据页、索引页、undo页、insert buffer、自适应哈希索引、锁信息以及数据字典等信息。在进行sql的读和写的操作首先并不是对物理数据文件操作，而是先对buffer_pool进行操作，然后再通过checkpoint等机制写回数据文件。该空间大的优点就是可以提升数据库的性能、加快sql运行速度，缺点是故障恢复速度较慢。在RDS上会采用实例规格配置的80%作为该部分大小(上图即是240M*0.8=192M)。

innodb_log_buffer

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

innodb_additional_mem_pool

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

key_buffer

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

query_cache

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

2. Session私有内存

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

主要包括以下部分(以下为测试实例配置):

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 | 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&read_rnd_buffer

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

sort_buffer

需要执行order by和group by的sql都会分配sort_buffer用来存储排序的中间结果，当排序的过程中如果存储带下大于sort_buffer_size的话会在磁盘生成临时表以完成操作。根据MySQL的文档可知在linux系统中，当分配空间大于2M时会使用mmap() 而不是 malloc() 来进行内存分配，导致效率降低。在RDS上给予256K。

join_buffer

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

binlog_cache

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

tmp_table

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

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