为MySQL增加线程内存监控 (MySQL Thread Memory Usage Monitor)

为了国际友人看得懂，以后我的博客都同时提供中英文版。:) For foreign friends can understand, all of my blog at the same time in English in the future. 使用MySQL中我经常发现mysqld的内存使用会涨的很快(Buffer Pool是大页分配的)，以至于使用SWAP，

为了国际友人看得懂，以后我的博客都同时提供中英文版。:)

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:

Note: There is a file embedded within this post, please visit this post to download the file.

基本方法就是在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:

Note: There is a file embedded within this post, please visit this post to download the file. (基于mysql-5.6.6)

原文地址：为MySQL增加线程内存监控 (MySQL Thread Memory Usage Monitor), 感谢原作者分享。