本篇介绍MySQL如何使用内存。MySQL主要将内存分配在三个部分,服务器、存储引擎及连接会话。服务器部分包含线程缓存、主机缓存及临时表,存储引擎部分包括缓冲池、日志缓冲,连接会话部分包括排序缓冲和联接缓冲。
MySQL在两个范围内分配内存,全局范围和会话范围。
全局内存:全局范围使用的内存在服务器启动时分配,在服务器关机时释放,由服务器进程和它的线程共享。如果服务器使用了全部的物理内存,操作系统发生SWAP,此时会影响MySQL的性能,并有可能导致服务器宕机。用户能够指定的缓冲和缓存包括如下:
授权表缓冲
存储引擎的缓冲,例如,InnoDB的日志缓冲(“innodb_log_buffer_size”)
表开启缓存,用于保存表开启的描述符(“table_open_cache”)
会话级内存:会话范围的内存为每个线程动态分配/回收,这部分内存大部分用于处理查询结果,还会有一些内存专门用来管理连接缓冲和线程栈。使用的内存大小和每个会话相关,例如,“sort_buffer_size”设置为1M,假设有100个连接,意味着合计100M的内存用于全部的排序缓冲。此外,一些缓冲可以为每个连接分配多个实例,例如,联接缓冲和临时表。
日志文件和缓冲
当客户端执行一个事务时,首先需要将更改的数据操作写入日志缓冲,之后再写入日志文件。以InnoDB为例,InnoDB在事务提交时需要将缓冲内的日志信息写入Redo日志,如果期间发生崩溃,Redo日志可以用于自动恢复,当MySQL服务器重启时,MySQL将对日志里记载的内容再次回放,以确保表中包含全部已提交的事务。
InnoDB 缓冲池
InnoDB维护一个或多个缓冲池(Buffer Pool),用于在内存中缓存经常使用的数据和索引。当用户发起一个读取查询时,InnoDB首先检查该部分数据页是否在缓冲池中,如果缓冲池中不存在,下一步,InnoDB将从表空间请求数据,InnoDB将数据页放入缓冲池,后服务将结果返回客户端。因此,合理缓冲池有利于提升MySQL的性能。
配置缓冲池
使用大量的内存可以减少磁盘的I/O,通常情况下用户应将“innodb_buffer_pool_size”设置为主机内存的70%~80%。对于一台专门安装MySQL的主机,用户可以按照如下步骤进行配置:
计算操作系统和相关管理软件(例如,备份软件)使用的内存
分配50%~80%的内存给缓冲池(需要考虑会话级别内存的使用需求,以16G内存的服务器为例,分配给缓冲池的内存大约为12G)
初始配置时,可以根据大连接数的值(“max_connections”)对缓存池进行配置
“max_connections=151”时,设置70%~80%
“max_connections=1000”时,设置60%~70%
“max_connections=5000”时,设置50%
监视内存的实际使用情况,适当调整,避免操作系统发生SWAP
注意,InnoDB对每个缓冲池使用单一互斥机制,确保内存或其他资源互斥,防止产生并发访问问题。如果缓冲池管理大量的页,这些页无法同时并行提供给多个客户端使用。用户可以设置多个缓冲池,每个缓存池由各自的互斥机制进行保护,因此可以改善InnoDB的并发性。