MySQL 内存异常增长分析

一、背景及现象

近期客户线上报障反馈MySQL内存一直增长，最终导致线上出现OOM情况。内存增长趋势如下，每天早上8点到晚上8点内存持续增长，每天增加数GB内存。

通过初步分析，客户的各项内存相关参数配置正常，buffer pool等共享内存区总共不超过13GB，但是客户内存最高使用可达到30多GB，也就是客户的连接中的私有内存消耗了接近20GB内存，这是非常不正常的现象。

这里笔者做了如下推测：

客户使用了query cache
客户在会话级别设置了join_buffer_size等参数
客户的SQL语句较复杂可能导致某个地方内存泄漏
客户连接断开内存没有释放

前面两个很好验证，通过参数设置知道客户没有开启query cache，在MySQL层面及客户层面确认了没有配置会话级别参数，下面我们就来看看如何验证后面两个推测。

二、复现步骤

2.1、验证是否有内存泄漏

采样几个复杂的SQL语句，通过valgrind工具进行分析，语句类似如下：

valgrind 工具分析结果如下：

==41943==
==41943== HEAP SUMMARY:
==41943==     in use at exit: 72,736 bytes in 2 blocks
==41943==   total heap usage: 225,890 allocs, 225,888 frees, 895,435,272 bytes allocated
==41943==
==41943== 32 bytes in 1 blocks are still reachable in loss record 1 of 2
==41943==    at 0x4C2B955: calloc (vg_replace_malloc.c:711)
==41943==    by 0x548C60F: _dlerror_run (in /usr/lib64/libdl-2.17.so)
==41943==    by 0x548C040: dlopen@@GLIBC_2.2.5 (in /usr/lib64/libdl-2.17.so)
==41943==    by 0x15B556D: plugin_dl_add(st_mysql_lex_string const*, int) (sql_plugin.cc:535)
==41943==    by 0x15B629C: plugin_add(st_mem_root*, st_mysql_lex_string const*, st_mysql_lex_string const*, int*, char**, int) (sql_plugin.cc:918)
==41943==    by 0x15B7D40: plugin_load(st_mem_root*, int*, char**) (sql_plugin.cc:1692)
==41943==    by 0x15B78E3: plugin_init(int*, char**, int) (sql_plugin.cc:1574)
==41943==    by 0xF561DA: init_server_components() (mysqld.cc:4033)
==41943==    by 0xF572CD: mysqld_main(int, char**) (mysqld.cc:4673)
==41943==    by 0xF50275: main (main.cc:25)
==41943==
==41943== 72,704 bytes in 1 blocks are still reachable in loss record 2 of 2
==41943==    at 0x4C29BC3: malloc (vg_replace_malloc.c:299)
==41943==    by 0x591FF2F: pool (eh_alloc.cc:117)
==41943==    by 0x591FF2F: __static_initialization_and_destruction_0 (eh_alloc.cc:244)
==41943==    by 0x591FF2F: _GLOBAL__sub_I_eh_alloc.cc (eh_alloc.cc:307)
==41943==    by 0x400F9C2: _dl_init (in /usr/lib64/ld-2.17.so)
==41943==    by 0x4001179: ??? (in /usr/lib64/ld-2.17.so)
==41943==    by 0x9: ???
==41943==    by 0x1FFF000396: ???
==41943==    by 0x1FFF0003C0: ???
==41943==    by 0x1FFF0003E7: ???
==41943==    by 0x1FFF000401: ???
==41943==    by 0x1FFF000420: ???
==41943==    by 0x1FFF000457: ???
==41943==    by 0x1FFF000464: ???
==41943==
==41943== LEAK SUMMARY:
==41943==    definitely lost: 0 bytes in 0 blocks
==41943==    indirectly lost: 0 bytes in 0 blocks
==41943==      possibly lost: 0 bytes in 0 blocks
==41943==    still reachable: 72,736 bytes in 2 blocks
==41943==         suppressed: 0 bytes in 0 blocks

结果显示没有内存泄漏

2.2、验证连接断开内存是否有释放

既然没有内存泄漏，那是否是连接释放了内存没有回收导致的呢？同样是抽样几个复杂的SQL批量向MySQL建立300个连接，然后在MySQL中批量kill掉，在这个过程中观察内存是否有变化。

通过反复执行上述步骤，可以观察到内存是一直增长的，这个过程虽然比较极端，但是复现了客户的情况，然后深入到MySQL源码中查看线程断开释放内存的相关逻辑：


extern "C" void *handle_connection(void *arg)
{
  Global_THD_manager *thd_manager= Global_THD_manager::get_instance();
  Connection_handler_manager *handler_manager=
    Connection_handler_manager::get_instance();
  Channel_info* channel_info= static_cast<Channel_info*>(arg);
  bool pthread_reused MY_ATTRIBUTE((unused))= false;

  if (my_thread_init())
  {
    connection_errors_internal++;
    channel_info->send_error_and_close_channel(ER_OUT_OF_RESOURCES, 0, false);
    handler_manager->inc_aborted_connects();
    Connection_handler_manager::dec_connection_count();
    delete channel_info;
    my_thread_exit(0);
    return NULL;
  }

  for (;;)
  {
    THD *thd= init_new_thd(channel_info);
    if (thd == NULL)
    {
      connection_errors_internal++;
      handler_manager->inc_aborted_connects();
      Connection_handler_manager::dec_connection_count();
      break; // We are out of resources, no sense in continuing.
    }

#ifdef HAVE_PSI_THREAD_INTERFACE
    if (pthread_reused)
    {
      /*
        Reusing existing pthread:
        Create new instrumentation for the new THD job,
        and attach it to this running pthread.
      */
      PSI_thread *psi= PSI_THREAD_CALL(new_thread)
        (key_thread_one_connection, thd, thd->thread_id());
      PSI_THREAD_CALL(set_thread_os_id)(psi);
      PSI_THREAD_CALL(set_thread)(psi);
    }
#endif

#ifdef HAVE_PSI_THREAD_INTERFACE
    /* Find the instrumented thread */
    PSI_thread *psi= PSI_THREAD_CALL(get_thread)();
    /* Save it within THD, so it can be inspected */
    thd->set_psi(psi);
#endif /* HAVE_PSI_THREAD_INTERFACE */
    mysql_thread_set_psi_id(thd->thread_id());
    mysql_thread_set_psi_THD(thd);
    mysql_socket_set_thread_owner(
      thd->get_protocol_classic()->get_vio()->mysql_socket);

    thd_manager->add_thd(thd);

    if (thd_prepare_connection(thd))
      handler_manager->inc_aborted_connects();
    else
    {
      while (thd_connection_alive(thd))
      {
        if (do_command(thd))
          break;
      }
      end_connection(thd);
    }
    close_connection(thd, 0, false, false);

    thd->get_stmt_da()->reset_diagnostics_area();
    thd->release_resources();

    // Clean up errors now, before possibly waiting for a new connection.
    ERR_remove_state(0);

    thd_manager->remove_thd(thd);
    Connection_handler_manager::dec_connection_count();

#ifdef HAVE_PSI_THREAD_INTERFACE
    /*
      Delete the instrumentation for the job that just completed.
    */
    thd->set_psi(NULL);
    PSI_THREAD_CALL(delete_current_thread)();
#endif /* HAVE_PSI_THREAD_INTERFACE */

    delete thd;

    if (abort_loop) // Server is shutting down so end the pthread.
      break;

    channel_info= Per_thread_connection_handler::block_until_new_connection();
    if (channel_info == NULL)
      break;
    pthread_reused= true;
  }

  my_thread_end();
  my_thread_exit(0);
  return NULL;
}

发现在连接中断之后close_connection、thd->release_resources、delete thd等方法将相关内存都释放掉了。但是这里有一个比较有疑点的逻辑，就是block_until_new_connection方法，详细查看其逻辑其实是MySQL线程缓存机制的逻辑，这个逻辑可以说是MySQL线程池的初级版本，它的思想比较简单就是在连接释放后最终阻塞在那里，等待下次新建连接的时候再复用这个连接，这里可缓存的线程数量由thread_cache_size参数控制。

查看线上客户环境thread_cache_size参数设置为100，查看已经缓存的线程数量在70左右徘徊。

这里初步怀疑跟线程缓存有关系，于是调整thread_cache_size=0，重复跑之前的测试步骤，发现内存也会增长，但是在连接被kill之后内存迅速恢复到原有水平，基本上没有增长。

那这里可能会有如下疑问：

MySQL已经释放了内存，为什么内存没有回收，即使有线程缓存，但是缓存之前内存都主动释放了。
thread_cache_size线上大家配置的都差不多，为什么只有这个实例会有问题。

针对第一个问题，我们就需要看一下我们使用的内存分配器的内存释放策略了，目前我们线上默认使用的jemalloc内存分配器，它默认的内存释放策略如下：

针对超过4MB的大内存（通过mmap分配的），在释放的时候直接调用munmap释放，直接归还给操作系统。
针对小内存Jemalloc默认会在线程级别维护一个tcache，默认情况下不会释放内存，内部有GC机制来释放，由dirty_decay_ms和muzzy_decay_ms参数控制。

这里推测是否是Jemalloc的小内存的cache机制导致内存没有归还给操作系统呢，再次测试设置thread_cache_size=100，设置jemalloc参数：MALLOC_CONF="tcache:false,dirty_decay_ms:0,muzzy_decay_ms:0"

然后重复跑之前测试步骤，发现内存可以正常释放了，那到这里也证实了这个推测。

针对第二个问题，为什么只有这个实例会有问题，笔者在自己的测试环境造了大量数据并且做了Join操作重复上述的动作，发现内存增长的非常有限。线上这个实例其主要原因是SQL语句非常复杂，Join频次非常高而这里面造成大量小内存的分配，最终导致这些内存一直积压到tcache中。

这里对比下线上和我自己的数据测试后生成的jeprof结果：

先放个样例图：

由于图比较大，详细请看附件：

线上：
测试：

通过上述对比我们可以看到在SQL运行时线上环境分配了大量的内存，而我自己测试的场景SQL运行时分配的内存基本可以忽略不计。

这里笔者还做了一个有趣的测试，调整了join_buffer_size大小为100MB 再进行测试，最后发现join_buffer_size这部分内存马上就回收了，这也跟jemalloc的内存回收机制有关系，针对大内存分配会立即归还给操作系统。

上述只是说的jemalloc的测试结果，笔者还尝试默认的glibc的malloc，不管是否有设置thread_cache_size，内存都不会释放，这个跟分配器本身的机制也有关系，也可以通过配置参数强制使其释放，这个在之前event scheduler 内存泄漏的wiki中有提到，有兴趣的同学也可以测试下tcmalloc。

三、问题原因

通过上述的问题复现，这里我们总结下该问题的具体原因，要复现该问题需要具备以下三个条件：

配置较大的thread_cache_size
业务场景较复杂，sql语句会造成大量小内存分配
使用jemalloc等内存分配器

一句话总结下原因就是：Jemalloc在线程级别维护了tcache，针对小内存不会马上释放，客户的场景恰好会造成大量小内存的分配并且加上MySQL的thread_cache_size的机制会导致线程中的tcache一直堆积，导致内存越来越高。

那内存会一直涨下去吗，其实只要在客户的连接数和业务场景不变的情况下内存涨一个高值基本不会有太大的变化了，因为tcache是可以复用的，只要tcache中内存够用就不会再次进行分配了。不过这里也不排除Jemalloc本身会造成内存碎片，碎片率约为20%。

这里会有一个疑问，为什么客户在腾讯没有问题，通过客户反馈的情况来看，客户使用了腾讯自研的连接池功能，避开了这个问题。

四、解决方案

那针对这个问题有什么好的解决方案吗，最简单的方案就是设置thread_cache_size=0，其实官方也在多个内存增长问题中提到建议关闭thread_cache_size参数。

如果客户的场景就是高频的短链接希望开启这个参数呢，这里推荐使用KingSQL的连接池功能。

这里总结下，这个问题阻塞这么久的根本原因在于对操作系统和内存分配器的机制了解不够充分，同样MySQL在最开始实现连接缓存的时候也没有考虑到这点，最终导致这个问题。涉及到内存、CPU等操作系统资源问题，往往从底层往上推思路会清晰一些，因为上层业务十分复杂，变量较多比较难模拟。

MySQL 内存异常增长分析

一、背景及现象

二、复现步骤

2.1、验证是否有内存泄漏

2.2、验证连接断开内存是否有释放

三、问题原因

四、解决方案

五、相关链接

zbdba

引用和评论

MySQL XA 事务并发执行问题（slave lock time out）

MySQL 回收表碎片实践教程

紧急避坑 | MySQL 含有下划线的数据库名在特殊情况下导致权限丢失

Mysql梳理1

mysql梳理2

关于建表字段是否该使用not null这个问题你怎么看?