mysql内存相关知识介绍

参考文章：

《记一次Mysql占用内存过高的优化过程》

《MySQL参数 之 innodb_buffer_pool_size》

《如何在MySQL中分配innodb_buffer_pool_size》

        写在开头：本文为学习后的总结，可能有不到位的地方，错误的地方，欢迎各位指正。

前言

        本文产生的背景是此前项目上的一次线上故障，在我司DBA的协助下得到了处理，但是也发现了自己对于mysql的了解之匮乏，这里对新了解到的知识进行初步的总结介绍。

目录

前言

一、问题介绍

 二、Mysql内存组成

        1、mysql内存介绍

        2、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后缀!