mysql 5.7 内存使用率过高
经常在项目上碰到在没有大并发活跃SQL的情况下，为啥还会出现 MySQL 实际占用物理内存比 InnoDB_Buffer_Pool 的配置高很多而且不释放的现象？起初是怀疑被 performance_schema 吃掉了或是 MySQL 存在内存泄露，熟悉了 MySQL 和 Linux 的内存管理之后发现并非如此。
故障上报
- 收到db服务器内存告警之后，登录服务器，执行
free -m看到该机器 32G 内存，只剩下 516M 可用，使用top看到 mysqld 内存使用率 77.6%（约24.8G）
[root@221.115 ~]# free -m
total used free shared buff/cache available
Mem: 32115 30797 595 402 722 516
Swap: 0 0 0
[root@221.115 ~]# top
top - 09:55:28 up 377 days, 20:01, 1 user, load average: 0.19, 0.16, 0.17
Tasks: 153 total, 1 running, 82 sleeping, 0 stopped, 0 zombie
KiB Mem : 32886604 total, 609688 free, 31536764 used, 740152 buff/cache
KiB Swap: 0 total, 0 free, 0 used. 529060 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
13334 mysql 20 0 30.4g 24.3g 0 S 1.7 77.6 156566:00 mysqld
373603 mongodb 20 0 7190520 5.3g 2088 S 0.3 16.7 1430:26 mongod
1807 root 20 0 1400668 29628 4144 S 0.7 0.1 732:10.61 telescope
833 root 20 0 11.2g 233696 0 S 0.3 0.7 296:26.97 java
- 查看 my.cnf 看到
innodb_buffer_pool_size配置为 16G
[root@221.115 ~]# cat /etc/my.cnf | grep innodb_buffer_pool_size
innodb_buffer_pool_size = 16777216K
- 登入 mysql 确认
@@innodb_buffer_pool_size确实为 16G，且连接数只有 22，那么剩下的 8.8G（24.8-16）内存用到哪里了呢？
(root@localhost) [(none)]> SELECT @@version,@@innodb_buffer_pool_size;
+---------------+---------------------------+
| @@version | @@innodb_buffer_pool_size |
+---------------+---------------------------+
| 5.7.34-37-log | 17179869184 |
+---------------+---------------------------+
(root@localhost) [(none)]> select count(*) from information_schema.processlist;
+----------+
| count(*) |
+----------+
| 22 |
+----------+
(root@localhost) [(none)]> show full processlist;
+----------+-----------------+-----------------------+-----------------+---------+----------+------------------------+-----------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----------+-----------------+-----------------------+-----------------+---------+----------+------------------------+-----------------------+-----------+---------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 52617438 | Waiting on empty queue | NULL | 0 | 0 |
| 64804101 | user_xxljob | 192.168.221.111:26551 | xxljob_alpha | Sleep | 0 | | NULL | 0 | 0 |
| 64804107 | user_xxljob | 192.168.221.111:26553 | xxljob_alpha | Sleep | 0 | | NULL | 0 | 163 |
| 64804116 | user_xxljob | 192.168.221.111:26555 | xxljob_alpha | Sleep | 13 | | NULL | 0 | 1 |
| 64804126 | user_xxljob | 192.168.221.111:26557 | xxljob_alpha | Sleep | 215 | | NULL | 1 | 0 |
| 64804200 | user_xxljob | 192.168.221.111:26559 | xxljob_alpha | Sleep | 126 | | NULL | 1 | 0 |
| 64804212 | user_xxljob | 192.168.221.111:26561 | xxljob_alpha | Sleep | 110 | | NULL | 1 | 0 |
| 64804229 | user_xxljob | 192.168.221.111:26563 | xxljob_alpha | Sleep | 87 | | NULL | 1 | 0 |
| 64804246 | user_xxljob | 192.168.221.111:26565 | xxljob_alpha | Sleep | 69 | | NULL | 1 | 0 |
| 64804259 | root | localhost | NULL | Query | 0 | starting | show full processlist | 0 | 0 |
| 64804276 | user_idspapidoc | 192.168.221.138:64761 | idspapidocument | Sleep | 25 | | NULL | 1 | 0 |
| 64804279 | user_idspapidoc | 192.168.221.138:64763 | idspapidocument | Sleep | 24 | | NULL | 1 | 0 |
| 64804280 | user_xxljob | 192.168.221.111:26567 | xxljob_alpha | Sleep | 24 | | NULL | 1 | 0 |
| 64804281 | user_idspapidoc | 192.168.221.138:64765 | idspapidocument | Sleep | 23 | | NULL | 1 | 0 |
| 64804282 | user_idspapidoc | 192.168.221.138:64767 | idspapidocument | Sleep | 22 | | NULL | 1 | 0 |
| 64804283 | user_xxljob | 192.168.221.111:26569 | xxljob_alpha | Sleep | 21 | | NULL | 1 | 0 |
| 64804286 | user_idspapidoc | 192.168.221.138:64769 | idspapidocument | Sleep | 19 | | NULL | 1 | 0 |
| 64804287 | user_idspapidoc | 192.168.221.138:64771 | idspapidocument | Sleep | 15 | | NULL | 1 | 0 |
| 64804296 | user_idspapidoc | 192.168.221.138:64773 | idspapidocument | Sleep | 12 | | NULL | 1 | 0 |
| 64804297 | user_idspapidoc | 192.168.221.138:64775 | idspapidocument | Sleep | 11 | | NULL | 1 | 0 |
| 64804298 | user_idspapidoc | 192.168.221.138:64777 | idspapidocument | Sleep | 10 | | NULL | 1 | 0 |
| 64804301 | user_idspapidoc | 192.168.221.138:64779 | idspapidocument | Sleep | 6 | | NULL | 1 | 0 |
+----------+-----------------+-----------------------+-----------------+---------+----------+------------------------+-----------------------+-----------+---------------+
22 rows in set (0.00 sec)
知识储备
- MySQL 的内存占用主要由两部分组成，global_buffers（全局共享缓存，创建实例即分配）与 all_thread_buffers（所有线程独立缓存），如下图所示：
- 如上图示，global_buffers = Sharing + InnoDB_Buffer_Pool
- 如上图示，all_thread_buffers = 当前活跃连接数 * (Thread memory)
- 其中 InnoDB_Buffer_Pool 是 MySQL 中内存占用中最大的一块，为常驻内存，除非 MySQL 进程退出才会释放。
- 而另外一块比较吃内存的就是线程缓存。例如常见的 join_buffer、sort_buffer、read_buffer 等，通常与连接数成正比。即连接数越高，并发越高，线程缓存占用总量就越高，但是这类缓存往往会随着连接关闭而释放，并非常驻内存。一些特殊的 SQL 或字段类型会导致单个线程可能分配多次缓存，因此当出现 OOM 异常，都是由各个连接的私有内存造成的，通过限制数据库的连接数和优化低效 SQL，可降低内存利用率过高的风险
- 开启 performance_schema 的内存监控功能，查询
memory_summary开头的表来分析内存使用情况
#.mysql总内存
SELECT (@@key_buffer_size + @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + 200 * (@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size+ @@join_buffer_size + @@binlog_cache_size + @@thread_stack))/1024/1024/1024 AS total_memory;
#.global 共享内存分配情况
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');
#.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');
#.找到MySQL里到底谁消耗了更多内存
select event_name,SUM_NUMBER_OF_BYTES_ALLOC
from performance_schema.memory_summary_global_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc LIMIT 20;
#.查看都有哪些内部线程消耗了更多内存
select THREAD_ID, EVENT_NAME, COUNT_ALLOC, SUM_NUMBER_OF_BYTES_ALLOC/1024/1024, CURRENT_NUMBER_OF_BYTES_USED/1024/1024
from performance_schema.memory_summary_by_thread_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 20;
#.查看thread pool相关设置
show variables like 'thread%';
select thread_id, user, current_allocated, total_allocated from sys.memory_by_thread_by_current_bytes limit 20;
发生原因
- 低效的 SQL 语句或数据库参数设置不当都可能会导致内存利用率升高
- mysql 5.7 的一个已知问题（bug#91710），只能重启mysql服务以释放内存
解决方案
- 执行
show full processlist;看看有没有什么异常的进程，比如：长时间的进程，或者Binlog Dump - 优化慢 SQL，减少 session 级的私有内存使用量
- 减少无效的长连接，在不影响业务的情况下，降低程序侧的连接池配置或者降低程序侧的并发度
- 针对 mysql 5.7 建议 采用Jemalloc实现mysql服务端内存回收
附1：推荐 jemalloc 管理内存
- 推荐用 Jemalloc 替代glibc库的malloc内存管理方式，它可以使内存快速释放给操作系统，减少系统内存使用过高而被OOM，适合多线程下内存分配管理，它与 Google TCMalloc 皆为内存管理器领域最高水平。更多关于malloc的信息，请参考 --malloc-lib=[lib_name]
如何确认mysqld已加载jemalloc
- 执行如下命令若有返回，则代表 mysqld 已加载了 jemalloc
[root@localhost ~]# yum install -y -q lsof
[root@localhost ~]# lsof -p `pidof mysqld` | grep -i jemalloc
mysqld 13334 mysql mem REG 8,1 1929280 939276 /opt/mysql/lib/mysql/libjemalloc.so.1
如何配置mysqld加载jemalloc
- 编译安装 jemalloc
cd /opt/
wget -c http://iso.sqlfans.cn/linux/jemalloc-5.3.0.tar.bz2
# yum install -y -q bzip2
tar jxvf jemalloc-5.3.0.tar.bz2
cd jemalloc-5.3.0
./configure --prefix=/usr/local/jemalloc
make -j 4 && make install
- 找到 jemalloc 动态库的位置，修改 mysqld_safe，通过指定 LD_PRELOAD 环境变量加载 jemalloc
[root@localhost ~]# find / -name libjemalloc.so
/usr/local/jemalloc/lib/libjemalloc.so
[root@localhost ~]# grep -A 2 executing /opt/mysql/bin/mysqld_safe
# executing mysqld_safe
export LD_PRELOAD=/usr/local/jemalloc/lib/libjemalloc.so
- 重启mysql服务
mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3306.sock shutdown
mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf &
- 确认 mysqld 已加载了 jemalloc
[root@localhost ~]# lsof -p `pidof mysqld` | grep -i jemalloc
mysqld 30531 mysql mem REG 253,1 5960136 1064343 /usr/local/jemalloc/lib/libjemalloc.so.2
[root@localhost ~]# ll /usr/local/jemalloc/lib/libjemalloc.so*
lrwxrwxrwx 1 root root 16 Mar 19 15:27 /usr/local/jemalloc/lib/libjemalloc.so -> libjemalloc.so.2
-rwxr-xr-x 1 root root 5960136 Mar 19 15:27 /usr/local/jemalloc/lib/libjemalloc.so.2
附2：注意 FEDERATED 引擎
- 注意：FEDERATED 引擎的内存使用不受 innodb_buffer_pool_size 的限制
- 示例：如下 aaa.students 这张表使用的是 FEDERATED 存储引擎，它不存储任何真实数据，只是一个“快捷方式”，对这张表执行的所有 INSERT、UPDATE、DELETE 或 SELECT 操作，都会被实时转发到远程服务器上去执行（示例为本机 bbb.student）
mysql> show create table aaa.student;
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
# .... 省略其他字段
PRIMARY KEY (`id`),
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://user:password@127.0.0.1:3306/bbb/student' |
mysql> show create table bbb.student;
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`school_id` int(11) NOT NULL,
# .... 省略其他字段
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=6811210 DEFAULT CHARSET=utf8 |
- 分析：FEDERATED 引擎的内存消耗主要来自查询执行过程，而非数据缓存。当查询FEDERATED表时，MySQL会将远程表的数据拉取到本地服务器的内存中进行处理，这部分内存由会话级别的缓冲区（如 join_buffer_size、sort_buffer_size）或全局查询执行缓冲区来承载。
- 分析：FEDERATED 引擎为何会消耗大量内存？主要是全表扫描导致的内存黑洞
官方文档明确指出，如果查询无法使用索引，服务器会从远程表中获取所有行并在本地进行过滤。
这意味着一个查询可能会将整个远程表的数据拉取到本地内存，即使你只加了WHERE或LIMIT条件也是如此。
历史Bug报告甚至记录过，查询一个50GB的FEDERATED表会导致本地MySQL消耗高达50GB的虚拟内存，最终引发系统内存溢出（OOM）。
- 解决方案：针对本示例，可以将 FEDERATED 表改成视图，这样还可以使用远程表的现有索引
RENAME TABLE aaa.student TO aaa.student_fed_bak;
CREATE VIEW aaa.student AS SELECT * FROM bbb.student;