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

4月 27th, 2012 | Posted by | Filed under 数据库

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/tech/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:
MySQL Thread Memory Usage

代码可以看patch
This is the patch:

  5.6_thread_mem_usage.patch (4.1 KiB, 1,848 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, 1,589 hits)

(基于mysql-5.6.6)

  5.5_thread_mem_usage.patch (5.2 KiB, 1,445 hits)

(基于percona-5.5.22)

标签: ,
  1. Ivan
    7月 26th, 201223:41

    随便杀线程么,这样不合理吧?

    [回复]

    硬豆瓣 回复:

    @Ivan, 杀不杀线程倒是次要的,关键是能知道每个线程使用的内存情况,这个挺酷的

    [回复]

  2. Tom.Chuang
    11月 2nd, 201211:27

    感谢您的分享,您的文章对我来说非常实用,
    请教您关于这个patch是不是能用在Mysql 5.5.14 + spider2.28的监控上呢?

    MEN栏位的值是所有线程的总使用量还是单一线程的呢?

    以上问题请教感谢您!!

    [回复]

  3. 大西
    5月 29th, 201316:23

    你好,这个补丁怎么使用呢,是放到源码的哪个文件夹,让编译吗。可以给我一个步骤吗

    [回复]

    P.Linux 回复:

    patch 进源码目录再编译

    [回复]