RDS MySQL实例的内存是重要的性能参数,常常出现由于异常的SQL请求以及待优化的数据库导致内存利用率升高的情况,严重时还会出现由于OOM导致实例发生HA切换的情况。RDS 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'
);
如下是内存规格为240 MB的RDS MySQL实例的共享内存分配情况的查询结果:
+---------------------------------+-----------------+
| 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运行速度,缺点是故障恢复速度较慢。
innodb_log_buffer
该部分主要存放redo log的信息,在RDS MySQL实例中会设置1 MB的大小,InnoDB会首先将redo log写在这里,然后按照一定频率将其刷新回重做日志文件中。该空间不需要太大,因为一般情况下该部分缓存会以较快频率刷新至redo log(Master Thread会每秒刷新、事务提交时会刷新、其空间少于1/2时同样会刷新)。
innodb_additional_mem_pool
该部分主要存放InnoDB内的一些数据结构,在RDS MySQL实例中统一设置为2 MB。通常是在buffer_pool中申请内存的时候还需要在额外内存中申请空间存储该对象的结构信息。该大小主要与表数量有关,表数量越大需要更大的空间。
key_buffer
该部分是MyISAM表的重要缓存区域,所有实例统一为16 MB。该部分主要存放MyISAM表的键。MyISAM表不同于InnoDB表,其缓存的索引缓存是放在key_buffer中的,而数据缓存则存储于操作系统的内存中。 RDS MySQL实例的系统是MyISAM引擎的,因此在RDS MySQL实例中是给予该部分一定量的空间。
query_cache
该部分是对查询结果做缓存以减少解析SQL和执行SQL的开销,在RDS MySQL实例中关闭了该部分的缓存。主要适合于读多写少的应用场景,因为它是按照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&read_rnd_buffer
分别存放了对顺序和随机扫描(例如按照排序的顺序访问)的缓存,RDS MySQL实例给每个Session设置256 KB的大小。当thread进行顺序或随机扫描数据时会首先扫描该buffer空间以避免更多的物理读。
sort_buffer
需要执行order by和group by的SQL都会分配sort_buffer,用于存储排序的中间结果,在RDS MySQL实例中设置256 KB。在排序过程中,若存储量大于sort_buffer_size,则会在磁盘生成临时表以完成操作。 Linux 系统中,当分配空间大于2 MB时会使用
mmap()
而不是malloc()
来进行内存分配,导致效率降低。join_buffer
RDS MySQL仅支持nest loop的join算法,RDS MySQL实例设置256 KB的大小,处理逻辑是驱动表的一行和非驱动表联合查找,这时就可以将非驱动表放入join_buffer,不需要访问拥有并发保护机制的buffer_pool。
binlog_cache
该区域用来缓存该thread的binlog日志,RDS MySQL实例设置256 KB的大小。在一个事务还没有commit之前会先将其日志存储于binlog_cache中,等到事务commit后会将其binlog刷回磁盘上的binlog文件以持久化。
tmp_table
不同于上面各个Session层次的buffer,这个参数可以在控制台上修改。该参数是指用户内存临时表的大小,如果该thread创建的临时表超过它设置的大小会把临时表转换为磁盘上的一张MyISAM临时表。如果用户在执行事务时遇到类似如下这样的错误,可以考虑增大tmp_table的值。
[Err] 1114 - The table '/home/mysql/data3081/tmp/#sql_6197_2' is full