参考文章:
《MySQL参数 之 innodb_buffer_pool_size》
《如何在MySQL中分配innodb_buffer_pool_size》
写在开头:本文为学习后的总结,可能有不到位的地方,错误的地方,欢迎各位指正。
前言
本文产生的背景是此前项目上的一次线上故障,在我司DBA的协助下得到了处理,但是也发现了自己对于mysql的了解之匮乏,这里对新了解到的知识进行初步的总结介绍。
目录
一、问题介绍
某日,项目上突然出现mysql连接的异常中断,初步排查发现mysql数据库服务器的内存占用率极高。
一开始我怀疑存在长事务阻塞,于是使用show full processlist;查看是否存在阻塞的sql。无果后求助于DBA,调整了mysql的内存配置后起到了一定的缓解作用。于是就有了今天这篇文章,下面我们就来简单介绍下mysql内存相关知识。
二、Mysql内存组成
1、mysql内存介绍
MySQL的内存结构主要由两部分组成:
(1)global_buffers,全局共享缓存。主要用于缓存数据页、索引页、数据字典等常用数据,数据库的绝大部分内存都被此占用,这类缓存一般都为常驻内存
(2)all_thread_buffers,所有线程级独立缓存。针对每个数据库连接会话独立分配的缓存,独立缓存的总量与连接数成正比,连接数越高,总的独立缓存便越大,但是这类缓存往往会随着连接关闭而释放,并非常驻内存
总独立缓存 = 所有会话级别分配的缓存
数据库总内存 = 全局缓存 + 总独立缓存
2、mysql内存结构
MySQL使用总内存 = global_buffers(右半边) + all_thread_buffers(左半边)
global_buffers:Sharing + InnoDB_Buffer_Pool
innodb_buffer_pool_size - InnoDB高速缓冲,行数据、索引缓冲,以及事务锁、自适应哈希等
innodb_log_buffer_size - InnoDB REDO日志缓冲,提高REDO日志写入效率
key_buffer_size - MyISAM表索引高速缓冲,提高MyISAM表索引读写效率
query_cache_size - 查询高速缓存,缓存查询结果,提高反复查询返回效率
table_open_cache - 表空间文件描述符缓存,提高数据表打开效率
table_definition_cache - 表定义文件描述符缓存,提高数据表打开效率
all_thread_buffers:max_threads(当前活跃连接数) * (会话级内存分配总和)
read_buffer_size - 顺序读缓冲,提高顺序读效率
read_rnd_buffer_size - 随机读缓冲,提高随机读效率
sort_buffer_size - 排序缓冲,提高排序效率
join_buffer_size - 表连接缓冲,提高表连接效率
binlog_cache_size - 二进制日志缓冲,提高二进制日志写入效率
tmp_table_size - 内存临时表,提高临时表存储效率
thread_stack - 线程堆栈,暂时寄存SQL语句/存储过程
thread_cache_size - 线程缓存,降低多次反复打开线程开销
net_buffer_length - 线程持连接缓冲以及读取结果缓冲
bulk_insert_buffer_size - MyISAM表批量写入数据缓冲
《记一次Mysql占用内存过高的优化过程》中可以查看更具体的参数介绍
PS:以上不熟悉也没关系,这里有工具可以直接计算出mysql的内存,帮助我们进行问题分析,
内存分析工具(左侧一列为mysql默认大小)
三、注意点
我们都知道内存的大小直接关系到CPU的利用率,内存小了会导致频繁的页面切换,导致CPU利用率的下降,但是内存又不可能无限增加,因此需要我们对mysql的内存机制有一定的了解
(1)使用命令看到MySQL占用了很大的内存,且一直不释放,是不是MySQL有问题?
因为global_buffers是常驻内存,MySQL并不会主动释放这块内存(其他数据库也基本是这个套路),其中占比最高的是Innodb_buffer_pool,而 thread_buffers则会伴随连接关闭而释放内存,所以MySQL通常会长期占用很多内存(主要是由于Innodb_buffer_pool),这点是很正常的。
(注意:参数主要作用是缓存innodb表的索引,数据,插入数据时的缓冲。mysql服务器设置的大小: 操作系统内存的70%-80%最佳。此外,这个参数设置的过大,会导致system的swap空间被占用,导致操作系统变慢,从而减低sql查询的效率。)
(2)Innodb_buffer_pool_size的查看与修改?
show global variables like '%innodb_buffer_pool_size%';
方式一:直接配置/etc/my.cnf中innodb_buffer_pool_size参数的值,但是要重启MySQL才可以生效.
innodb_buffer_pool_size = 8G
方法二:5.7版本之后可以通过set global命令在线修改innodb_buffer_pool_size的值,而不需要重启MySQL.
mysql> set global innodb_buffer_pool_size = 16*1024*1024*1024; //配置成16G,不支持G后缀!