P.Linux Laboratory

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/database/mysql_memory_usage_monitor.html

为了国际友人看得懂，以后我的博客都同时提供中英文版。:)
For foreign friends can understand, all of my blog at the same time in English in the future.

使用MySQL中我经常发现mysqld的内存使用会涨的很快（Buffer Pool是大页分配的），以至于使用SWAP，到底Server层用了多少内存，没有一个监控机制，所以第一步我编写了个patch（基于5.6.6）来监控每个线程用了多少内存，一旦mysqld进程使用太多内存，就去看哪些线程用的多，杀掉这些线程。
I often found mysqld process use memory will grow up very fast(InnoDB Buffer Pool used large page), lead to mysqld use SWAP. How many memory MySQL Server(Threads) used? no monitor now! So I write a patch based on MySQL 5.6.6 first, it can monitor how many memory used each threads. If I found mysqld process used too many memory, I can watch which threads used more memory, and kill them.

打上补丁后的效果像这样：
This is the effect after patched:

代码可以看patch
This is the patch:

  5.6_thread_mem_usage.patch (4.1 KiB, 3,763 hits)

基本方法就是在my_malloc和my_free中增加回调函数（@淘宝丁奇 提供的思路，太帅了），获取调用my_malloc和my_free函数的THD描述符，用THD中新加的malloc_size字段去记录申请和释放内存，其实my_realloc也应该去更新malloc_size，暂时还没加进去。
The method is add callback function on my_malloc/my_free(Xiaobin Lin give me this Callback idea) to get the THD which call my_malloc/my_free. And use a variable named “malloc_size” on THD to record how many memory malloc/free. In fact, my_realloc is also need calc malloc_size, but I have not add it on this version.

然后使用malloc_usable_size函数在free时判断指针申请了多少内存，在GCC 4.2以上可以使用malloc_size(pointor)去判断。
And then, I use malloc_usable_size function to get the size of pointor which will be free. After GCC 4.2, we can use malloc_size to get it.

下一步我会分类监控，把每个线程sort_buffer/join_buffer/net_buffer等线程级内存都分类统计出来占用多少，方便更直观的监控。
Next step, I will monitor the size of sort_buffer/join_buffer/net_buffer in each threads, not only total size each threads.

这是新版补丁，计算了my_realloc重分配的内存:
This is the new patch, calc my_realloc size:

  5.6_thread_mem_usage_ver2.patch (5.0 KiB, 3,132 hits)

  5.5_thread_mem_usage.patch (5.2 KiB, 2,980 hits)