MySQL 内存使用率过高, 有诸多原因. 普遍原因是使用不当, 还有 MySQL 本身缺陷导致的. 到底是哪方面的问题, 那就需要一个一个进行排查.
本文转载自微信公众号「数据和云」, 作者崔虎龙 . 转载本文请联系数据和云公众号.
MySQL 使用内存上升 90%! 在运维过程中 50% 的几率, 会碰到这样的问题. 算是比较普遍的现象.
MySQL 内存使用率过高, 有诸多原因. 普遍原因是使用不当, 还有 MySQL 本身缺陷导致的. 到底是哪方面的问题, 那就需要一个一个进行排查.
下面介绍排查思路:
1. 参数配置需要确认, 内存是否设置合理
MySQL 内存分为全局和线程级:
全局内存(如: innodb_buffer_pool_size,key_buffer_size,innodb_log_buffer_size).
线程级内存:(如: thread,read,sort,join,tmp 等)只是在需要的时候才分配, 并且在操作完毕之后就释放.
线程级内存: 线程缓存每个连接到 MySQL 服务器的线程都需要有自己的缓冲. 默认分配 thread_stack(256K,512k), 空闲时这些内存是默认使用, 除此之外还有网络缓存, 表缓存等. 大致评估会在 1M~3M 这样的情况. 可通过 pmap 观察内存变化:
- MySQL> SELECT @@query_cache_size,
- @@key_buffer_size,
- @@innodb_buffer_pool_size ,
- @@innodb_log_buffer_size ,
- @@tmp_table_size ,
- @@read_buffer_size,
- @@sort_buffer_size,
- @@join_buffer_size ,
- @@read_rnd_buffer_size,
- @@binlog_cache_size,
- @@thread_stack,
- (SELECT COUNT(host) FROM information_schema.processlist where command<>'Sleep')\G;
- *************************** 1. row ***************************
- @@query_cache_size:1048576
- @@key_buffer_size:8388608
- @@innodb_buffer_pool_size:268435456
- @@innodb_log_buffer_size:8388608
- @@tmp_table_size:16777216
- @@read_buffer_size:131072
- @@sort_buffer_size:1048576
- @@join_buffer_size:1048576
- @@read_rnd_buffer_size:2097152
- @@binlog_cache_size:8388608
- @@thread_stack:524288
- (select count(host) from information_schema.processlist where command<>'Sleep'): 1
备注: query_cache_size 8.0 版本已经废弃掉了.
2. 存储过程 & 函数 & 触发器 & 视图
目前积累的使用经验中, 存储过程 & 函数 & 触发器 & 视图 在 MySQL 场景下是不适合的. 性能不好, 又容易发现内存不释放的问题, 所以建议尽量避免.
存储过程 & 函数
- MySQL 5.7
- MySQL> SELECT db,type,count(*)
- FROM MySQL.proc
- WHERE db not in ('mysql','information_schema','performance_schema','sys')
- GROUP BY db, type;
- MySQL 8.0
- MySQL> SELECT Routine_schema, Routine_type
- FROM information_schema.Routines
- WHERE Routine_schema not in ('mysql','information_schema','performance_schema','sys')
- GROUP BY Routine_schema, Routine_type;
视图
- MySQL> SELECT TABLE_SCHEMA , COUNT(TABLE_NAME)
- FROM information_schema.VIEWS
- WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
- GROUP BY TABLE_SCHEMA ;
触发器
- MySQL> SELECT TRIGGER_SCHEMA, count(*)
- FROM information_schema.triggers
- WHERE TRIGGER_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
- GROUP BY TRIGGER_SCHEMA;
上面通过 MySQL 配置参数和设计层面检查了是否有可能内存泄露的问题. 下面看看怎样分析实际使用的内存情况.
3. 系统库统计查询
总内存使用
- MySQL> SELECT
- SUM(CAST(replace(current_alloc,'MiB','') as DECIMAL(10, 2)) )
- FROM sys.memory_global_by_current_bytes
- WHERE current_alloc like '%MiB%';
分事件统计内存
- MySQL> SELECT event_name,
- SUM(CAST(replace(current_alloc,'MiB','') as DECIMAL(10, 2)) )
- FROM sys.memory_global_by_current_bytes
- WHERE current_alloc like '%MiB%' GROUP BY event_name
- ORDER BY SUM(CAST(replace(current_alloc,'MiB','') as DECIMAL(10, 2)) ) DESC ;
- MySQL> SELECT event_name,
- sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED)
- FROM performance_schema.memory_summary_global_by_event_name
- ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
- LIMIT 10;
账号级别统计
- MySQL> SELECT user,event_name,current_number_of_bytes_used/1024/1024 as MB_CURRENTLY_USED
- FROM performance_schema.memory_summary_by_account_by_event_name
- WHERE host<>"localhost"
- ORDER BY current_number_of_bytes_used DESC LIMIT 10;
备注: 有必要统计用户级别内存, 因为很多环境对接了第三方插件, 模拟从库, 这些插件容易内存不释放.
线程对应 sql 语句, 内存使用统计
- SELECT thread_id,
- event_name,
- sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED)
- FROM performance_schema.memory_summary_by_thread_by_event_name
- ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
- LIMIT 20;
- SELECT m.thread_id tid,
- m.user,
- esc.DIGEST_TEXT,
- m.current_allocated,
- m.total_allocated
- FROM sys.memory_by_thread_by_current_bytes m,
- performance_schema.events_statements_current esc
- WHERE m.`thread_id` = esc.THREAD_ID \G
打开所有内存性能监控, 会影响性能, 需注意
- # 打开
- UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';
- # 关闭
- UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'memory/%';
- # 查看使用
- SELECT * FROM performance_schema.memory_summary_global_by_event_name
- WHERE EVENT_NAME LIKE 'memory/%'
- ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;
系统表内存监控信息
- select * from sys.x$memory_by_host_by_current_bytes;
- select * from sys.x$memory_by_thread_by_current_bytes;
- select * from sys.x$memory_by_user_by_current_bytes;
- select * from sys.x$memory_global_by_current_bytes;
- select * from sys.x$memory_global_total;
- select * from performance_schema.memory_summary_by_account_by_event_name;
- select * from performance_schema.memory_summary_by_host_by_event_name;
- select * from performance_schema.memory_summary_by_thread_by_event_name;
- select * from performance_schema.memory_summary_by_user_by_event_name;
- select * from performance_schema.memory_summary_global_by_event_name;
备注: 找到对应问题事件或线程后, 可以进行排查, 解决内存高的问题.
4. 系统工具查看内存
1)top 命令
显示系统中各个进程的资源占用状况.
Shift + m 键 查看内存排名实际使用内存情况, 关注 RES 指标.
2)free 命令
free-h 命令显示系统内存的使用情况, 包括物理内存, 交换内存 (swap) 和内核缓冲区内存.
used 列显示已经被使用的物理内存和交换空间.
buff/cache 列显示被 buffer 和 cache 使用的物理内存大小.
available 列显示还可以被应用程序使用的物理内存大小.
Swap 行 (第三行) 是交换空间的使用情况.
3)ps 命令
MySQL 相关进程使用内存情况.
- shell > ps eo user,pid,vsz,rss $(pgrep -f 'mysqld')
- USER PID VSZ RSS
- root 215945 12960 2356
- MySQL 217246 1291540 241824
- root 221056 12960 2428
- MySQL 374243 1336924 408752
4)pmap 命令
pmap 是 Linux 调试及运维一个很好的工具, 查看进程的内存映像信息.
用法 1: 执行一段时间记录数据变化, 最少 20 个记录, 下面 22837 是 MySQL pid
while true; do pmap -d 22837 | tail -1; sleep 2; done
用法 2:Linux 命令 pmap MySQL pid 导出内存, 下面 22837 是 MySQL pid
pmap -X -p 22837 > /tmp/memmysql.txt
RSS 就是这个 process 实际占用的物理内存.
Dirty: 脏页的字节数(包括共享和私有的).
Mapping: 占用内存的文件, 或[anon](分配的内存), 或[stack](堆栈).
writeable/private: 进程所占用的私有地址空间大小, 也就是该进程实际使用的内存大小.
1. 首先使用 / top/free/ps 在系统级确定是否有内存泄露. 如有, 可以从 top 输出确定哪一个 process.
2.pmap 工具是能帮助确定 process 是否有 memory leak. 确定 memory leak 的原则: writeable/private ('pmap -d'输出)如果在做重复的操作过程中一直保持稳定增长, 那么一定有内存泄露.
总结
对于 MySQL 内存泄露来说:
从参数设置和设计上尽量合理
通过 ps 库进行排查
Linux 工具进一步确认
官方 bug 里 memory leak 查找, 是否存在修复的版本
以上排查里都没有找到原因, 可以换下服务器或主从切换观察. 也可以进行版本升级(代价不小).
如能提供一个实际环境, 也可以一步一步进行调试, 抓取内存变化, 确定是什么导致内存泄露的问题. 之后提交 bug, 让官方提供修复.
来源: http://database.51cto.com/art/202107/675479.htm