MySQL 内存问题排查思路

简介：MySQL 内存持续增长的问题虽然不会经常发生，但是如果偶尔发生了，也会让我们不知所措。

一 、如果碰到MySQL 内存持续升高问题的主要情况有4种：

1. MySQL 没有合理设置 innodb_buffer_pool_size

2. 服务器上还有一些其他进程可以分配内内存。

3. MySQL 内存泄漏。

4. 内存缓慢增长属于正常现象，但存在因为一些慢sql频繁执行并因MySQL的内存分配使用了系统glibc，而glibc本身的内存分配算法存在缺陷，导致内存释放不完全。

二、MySQL 内存持续排查思路：

  1 基于服务器的检查：

    1.1  通过检查 MySQL 错误日志和 Linux 日志文件（即 /var/log/messages 或 /var/log/syslog）来识别崩溃。您可能会看到一个条目说 OOM Killer 杀死了 MySQL。每当 MySQL 被 OOM 杀死时，“dmesg”也会显示有关它周围情况的详细信息。

    1.2 检查可用内存：

free -g

cat /proc/meminfo

  1.3  使用命令 top 或 htop 检查哪些应用程序正在使用 RAM（参见常驻内存与虚拟内存）

  1.4 检查MySQL配置：检查/etc/my.cnf或一般的/etc/my*（包括/etc/mysql/*等文件）。MySQL 可能使用不同的 my.cnf(  run ps ax| grep mysql ) 运行。

  1.5 运行  vmstat 5 5 以 查看系统是否通过虚拟内存进行读/写以及是否正在交换。

  1.6 对于非生产环境，我们可以使用其他工具（如Valgrind、gdb等）来检查MySQL的使用情况。

2  现在我们可以通过MySQL运行机制以便查找潜在的内存泄漏因素。MySQL 在很多地方分配内存，尤其是：

   2.1表缓存

   2.2启用 Performance_schema功能

show engine performance_schema status 并查看最后一行。

   2.3 InnoDB（运行  show engine innodb status 并检查缓冲池部分，为 buffer_pool 和相关缓存分配的内存）

   2.4 在内存中的临时表（找到运行内存中的所有表：select * from information_schema.tables where engine='MEMORY'）

  2.5 Prepared 语句，当它没有被解除分配时（通过运行 show global status like 检查通过解除分配命令准备的命令的数量  Com_prepare_sql;

show global status like 'Com_dealloc_sql'

3 从 5.7 开始，我们可以基于 performance_schema 查询内存使用情况。使用方法：

3.1 开启收集内存的统计信息

UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

3.2 运行sql ：

select event_name, current_alloc, high_alloc from sys.memory_global_by_current_bytes where current_count > 0;

3.3 通常情况下，第二部的结果集会展示具体的代码模块使用了比较多的内存。它通常是不言自明的，我们可以搜索mysql的bugs 或者可以去检查 MySQL 源代码。

举个例子， https://bugs.mysql.com/bug.php?id=86821 ，这篇文章展示了 mysql为触发器分配了过多的内存。

mysql> select event_name, current_alloc, high_alloc from memory_global_by_current_bytes where current_count > 0;
+--------------------------------------------------------------------------------+---------------+-------------+
| event_name                                                                     | current_alloc | high_alloc  |
+--------------------------------------------------------------------------------+---------------+-------------+
| memory/innodb/buf_buf_pool                                                     | 7.29 GiB      | 7.29 GiB    |
| memory/sql/sp_head::main_mem_root                                              | 3.21 GiB      | 3.62 GiB    |
...

虽然 buf_buf_pool 占用了7G ，但是系统依然为存储过程对象分配3G内存，显然分配的内存太大了。根据文档描述 sp_head 代表这个存储程序的一个实例，它可能是任何类型（存储过程、函数、触发器、事件）。在上述情况下，这个mysql有潜在的内存泄漏。

注意:

其实官方并不承认 存储过程对象导致内存使用量持续增加是个bug。官方给的建议是调整参数  table_open_cache_instances。

另外我们可以通过如下语句 查看具体是哪个模块在消耗大量内存。

mysql> select  substring_index(
    ->     substring_index(event_name, '/', 2),
    ->     '/',
    ->     -1
    ->   )  as event_type,
    ->   round(sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) as MB_CURRENTLY_USED
    -> from performance_schema.memory_summary_global_by_event_name
    -> group by event_type
    -> having MB_CURRENTLY_USED>0;
+--------------------+-------------------+
| event_type         | MB_CURRENTLY_USED |
+--------------------+-------------------+
| innodb             |              0.61 |
| memory             |              0.21 |
| performance_schema |            106.26 |
| sql                |              0.79 |
+--------------------+-------------------+
4 rows in set (0.00 sec)

三 、jemalloc 在 mysql 当中的使用：

yum install jemalloc
在mysqld_safe中，最前面添加如下信息:
export LD_PRELOAD="/lib64/libjemalloc.so.1"
重启启动mysql实例

使用pt工具确认是否是用你了jemalloc
pt-mysql-summary  -S /tmp/mysql-3320.sock --user root --password 123456|grep -A 5 "Memory management" 
显示如下信息，则代表使用了jemalloc