MySQL偽memory leak問題

Problem

我用的server是Debian 64bit (AMD64), DB server是 5.0.32，有8G memory，但MySQL使用一陣子後，用top和free會看到8G的memory都被吃掉，但沒有任何process用到這麼多memory，而且swap沒有被用到，關掉MySQL daemon後memory的用量也沒改變。

Solution

查到兩篇類似的苦主：

• mysql memory consumption, memory leak ?, Sat, 07 April 2007
• MySQL 4.0.14/4.0.15, RedHat9 (smp 2.4.20), memory problem, July 19th, 2005

從第二篇裡找到解法：

Despite the fact that “top”, “/proc/meminfor”, “free” show me that 98% of all memory on server is used that memory is not really used by MySQL nor kernel (as I can see from /proc/slabinfo).

…

I wrote simple C app. which tries to allocate as much memory as possible calling malloc() in a loop I found how much free memory _really_ available. Looks like it is Linux reporting problem, but same time this _only_ happen to me with memory previously used by MySQL server. I can see memory returned back to free memory pool as soon as other applications exit.

上面說是top和free報錯數據，MySQL query結束後，query用掉的memory有還回去，但top和free的統計數據裡卻沒有減少。

我也寫了個 C app來試，如他所言一般，程式結束後memory回來了，但查了半天找不到單一process的maximum memory usage定義在那，有人說在limits.h裡，INT_MAX就是maximum memory，但顯然是錯的，我設到比它大的值，anyway，下面是我「要回」memory的 code，因為設到15*會超出上限被killed，只好設12*並同時跑多個，但想想這個數字也不太對，它比8G大，但跑起來大概只用了2G吧，不知道process到底怎麼跑的

#include <stdio.h> #include <stdlib.h> enum { MAX = 12000000000 }; char s[MAX]; int main(void) { int i; for (i=0; i<MAX; i++) s[i] = i; sleep(10); return 0; }

另外聽小豬說在Debian的MySQL 5.1上沒這問題，也許是已修正的bug吧。

備註：query cache相關指令

http://dev.mysql.com/doc/refman/5.0/en/flush.html
http://dev.mysql.com/doc/refman/5.0/en/query-cache-status-and-maintenance.html

The RESET QUERY CACHE statement removes all query results from the query cache. The FLUSH TABLES statement also does this.

還有可以用SHOW VARIABLES查環境設定，像是myisam_max_sort_file_size、max_binlog_cache_size。