# mysql keeps using memory and swap , never releases memory **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [March 30, 2014, 9:35am UTC](https://forums.percona.com/t/mysql-keeps-using-memory-and-swap-never-releases-memory/3370 "2014-03-30T09:35:56Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![jason\_asia](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jason\_asia](https://forums.percona.com/u/jason_asia)\ **Post date:** [March 30, 2014, 9:35am UTC](https://forums.percona.com/t/mysql-keeps-using-memory-and-swap-never-releases-memory/3370/1 "2014-03-30T09:35:56Z")
[problem] mysql keep using ram and swap , but it never releasing the memory until it out of memory [environment] physical machine: dell r710,32G RAM,16G SWAP,raid10,146G\*6 disks [mysql version] Server version: 5.0.67-percona-highperf-log Source distribution ( we have another 4 mysql servers, only the 5th one has this problem) [mysql basic configuration] > show variables like ‘%buffer%’; > ±------------------------------±------------+ > | Variable\_name | Value | > ±------------------------------±------------+ > | bulk\_insert\_buffer\_size | 67108864 | > | innodb\_buffer\_pool\_awe\_mem\_mb | 0 | > | innodb\_buffer\_pool\_size | 12884901888 | > | innodb\_log\_buffer\_size | 16777216 | > | join\_buffer\_size | 2097152 | > | key\_buffer\_size | 67108864 | > | myisam\_sort\_buffer\_size | 134217728 | > | net\_buffer\_length | 16384 | > | preload\_buffer\_size | 32768 | > | read\_buffer\_size | 1048576 | > | read\_rnd\_buffer\_size | 16777216 | > | sort\_buffer\_size | 2097152 | > ±------------------------------±------------+ # free -mt total used free shared buffers cached Mem: 24094 24018 75 0 7 1959 -/+ buffers/cache: 22051 2042 Swap: 16386 11089 5296 Total: 40480 35108 5372 # ps aux |grep mysql root 19820 0.0 0.0 78484 1788 pts/0 S+ 22:41 0:00 mysql root 22785 0.0 0.0 61156 664 pts/1 S+ 23:03 0:00 grep mysql root 29703 0.0 0.0 65928 852 ? S Mar28 0:00 /bin/sh /usr/local/mysql/bin/mysqld\_safe --datadir=/home/mysql --pid-file=/home/mysql/mysql.pid mysql 29740 11.9 89.8 43209456 22163564 ? Sl Mar28 364:06 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/home/mysql --user=mysql --pid-file=/home/mysql/mysql.pid --skip-external-locking --port=3306 --socket=/home/mysql/mysql.sock # top top - 23:04:09 up 3 days, 10:56, 2 users, load average: 0.07, 0.16, 0.17 Tasks: 136 total, 1 running, 135 sleeping, 0 stopped, 0 zombie Cpu(s): 0.3%us, 0.1%sy, 0.0%ni, 99.4%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st Mem: 24672344k total, 24592592k used, 79752k free, 7320k buffers Swap: 16779884k total, 11361564k used, 5418320k free, 2007552k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 29740 mysql 15 0 41.2g 21g 42m S 3.0 89.8 364:06.35 mysqld 18325 root 16 0 87028 3308 2572 S 0.0 0.0 0:00.02 sshd 22532 root 15 0 86156 3300 2576 S 0.0 0.0 0:00.03 sshd 19820 root 16 0 78484 1788 1296 S 0.0 0.0 0:00.00 mysql 22534 root 15 0 68152 1652 1232 S 0.0 0.0 0:00.00 bash 18327 root 15 0 68152 1648 1232 S 0.0 0.0 0:00.02 bash 22795 root 15 0 12736 1100 816 R 0.0 0.0 0:00.01 top 29703 root 21 0 65928 852 848 S 0.0 0.0 0:00.00 mysqld\_safe 29644 root 15 0 60672 696 568 S 0.0 0.0 0:01.77 sshd 28292 root 15 0 21640 684 588 S 0.0 0.0 0:02.29 xinetd 28743 root 16 0 74840 552 484 S 0.0 0.0 0:00.17 crond thank u very much ---
**Author:** ![niljoshi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/niljoshi/32/16889_2.png) [@niljoshi](https://forums.percona.com/u/niljoshi)\ **Post date:** [March 31, 2014, 7:08am UTC](https://forums.percona.com/t/mysql-keeps-using-memory-and-swap-never-releases-memory/3370/2 "2014-03-31T07:08:09Z")
Hi, I found some bugs related to memory leak in 5.0.67 but not sure, its related to your issue too. [URL][MySQL Bugs: #45002: memory leak in libmysqlclient.so](http://bugs.mysql.com/bug.php?id=45002%5B/URL%5D) [URL][MySQL Bugs: #33807: mysql\_real\_query memory leak](http://bugs.mysql.com/bug.php?id=33807%5B/URL%5D) Even Percona server has some memory leak issue so for that we have launched some patches. [URL=“[MySQL Binaries Percona build10 - Percona Database Performance Blog](http://www.mysqlperformanceblog.com/2008/12/11/mysql-binaries-percona-build10/)”][http://www.mysqlperformanceblog.com/...rcona-build10/[/URL]](http://www.mysqlperformanceblog.com/...rcona-build10/%5B/URL%5D) Is there any specific reason to stick with 5.0? I would suggest to upgrade to latest version (5.5 OR 5.6) for better performance, also many bugs are resolved in these versions. I would also suggest to check this post to find out where the memory is allocated. OS cache also can be cause of this. [url][http://www.mysqlperformanceblog.com/2014/01/24/mysql-server-memory-usage-2/[/url]](http://www.mysqlperformanceblog.com/2014/01/24/mysql-server-memory-usage-2/%5B/url%5D) ---
**Author:** ![jason\_asia](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jason\_asia](https://forums.percona.com/u/jason_asia)\ **Post date:** [March 31, 2014, 9:20am UTC](https://forums.percona.com/t/mysql-keeps-using-memory-and-swap-never-releases-memory/3370/3 "2014-03-31T09:20:58Z")
hi, niljoshi i’m reading your article “MySQL server memory usage troubleshooting tips”, i’m trying to find out the reason. We have another 4 mysql servers , they are all using mysql 5.0.67-percona-highperf-log Source distribution , so the new one we use 5.0.67 , too The mysql server will use all of the memory and swap when it runs for about 48 hous, if we haven’t find out the reason and resolve it , we have to upgrade to mysql 5.5.20. If we find any new informations , i’ll reply ASAP. thank u very much. ---
**Author:** ![niljoshi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/niljoshi/32/16889_2.png) [@niljoshi](https://forums.percona.com/u/niljoshi)\ **Post date:** [April 1, 2014, 2:29am UTC](https://forums.percona.com/t/mysql-keeps-using-memory-and-swap-never-releases-memory/3370/4 "2014-04-01T02:29:39Z")
Hi, Thanks for your feedback. I’ll wait for new information. ---
**Author:** ![jason\_asia](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jason\_asia](https://forums.percona.com/u/jason_asia)\ **Post date:** [April 1, 2014, 3:03am UTC](https://forums.percona.com/t/mysql-keeps-using-memory-and-swap-never-releases-memory/3370/5 "2014-04-01T03:03:49Z")
Hi, niljoshi I compute the max memory size that mysql will use : SHOW VARIABLES LIKE ‘innodb\_buffer\_pool\_size’; – 12G SHOW VARIABLES LIKE ‘innodb\_additional\_mem\_pool\_size’; – 16M SHOW VARIABLES LIKE ‘innodb\_log\_buffer\_size’; – 16M SHOW VARIABLES LIKE ‘thread\_stack’; – 192K SHOW VARIABLES LIKE ‘max\_connections’; – 200 show variables like ‘tmp\_table\_size’; – 100663296 , 96M SET @kilo\_bytes = 1024; SET @mega\_bytes = @kilo\_bytes \* 1024; SET @giga\_bytes = @mega\_bytes \* 1024; SET @innodb\_buffer\_pool\_size = 12 \* @giga\_bytes; SET @innodb\_additional\_mem\_pool\_size = 16 \* @mega\_bytes; SET @innodb\_log\_buffer\_size = 16 \* @mega\_bytes; SET @thread\_stack = 192 \* @kilo\_bytes; SELECT ( @@key\_buffer\_size + @@query\_cache\_size + @@tmp\_table\_size - @innodb\_buffer\_pool\_size + @innodb\_additional\_mem\_pool\_size - @innodb\_log\_buffer\_size - @@max\_connections \* ( @@read\_buffer\_size + @@read\_rnd\_buffer\_size + @@sort\_buffer\_size - @@join\_buffer\_size + @@binlog\_cache\_size + @thread\_stack ) ) / @giga\_bytes AS MAX\_MEMORY\_GB; – 17581.5000M – 17.1694G But the running environment is like this: top - 16:33:14 up 1 day, 15:45, 2 users, load average: 0.05, 0.12, 0.09 Tasks: 137 total, 1 running, 135 sleeping, 1 stopped, 0 zombie Cpu(s): 2.2%us, 0.4%sy, 0.0%ni, 96.3%id, 0.7%wa, 0.0%hi, 0.3%si, 0.0%st Mem: 24672344k total, 24550744k used, 121600k free, 55452k buffers Swap: 16779884k total, 156k used, 16779728k free, 4564380k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 3154 mysql 15 0 30.7g 18g 7752 S 21.3 79.5 198:21.45 mysqld Call Send SMS Add to Skype You’ll need Skype CreditFree via Skype ---
**Author:** ![jason\_asia](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jason\_asia](https://forums.percona.com/u/jason_asia)\ **Post date:** [April 1, 2014, 3:05am UTC](https://forums.percona.com/t/mysql-keeps-using-memory-and-swap-never-releases-memory/3370/6 "2014-04-01T03:05:15Z")
Hi, niljoshi this the tps and qps info: # values are from " show global status" # TPS | Com\_commit | 8361540 | | Com\_rollback | 0 | | Uptime | 140760 | TPS = (Com\_commit + Com\_rollback ) / Uptime = (8361540 + 0 ) / 140760 = 60 T/s # QPS | Questions | 147005204 | | Uptime | 140760 | QPS = Questions / Uptime = 147005204 / 140760 = 1045 Q/s ---
**Author:** ![jason\_asia](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jason\_asia](https://forums.percona.com/u/jason_asia)\ **Post date:** [April 1, 2014, 3:11am UTC](https://forums.percona.com/t/mysql-keeps-using-memory-and-swap-never-releases-memory/3370/7 "2014-04-01T03:11:16Z")
Hi, niljoshi This is the query cache info: # check query status > show variables like ’ have\_query\_cache '; > ±-----------------±------+ > | Variable\_name | Value | > ±-----------------±------+ > | have\_query\_cache | YES | > ±-----------------±------+ # some values get from “show global variables like ‘query%’”： | query\_alloc\_block\_size | 8192 | | query\_cache\_limit | 2097152 | # 2M | query\_cache\_min\_res\_unit | 2048 | #2048byte | query\_cache\_size | 67108864 | # 64M | query\_cache\_type | ON | # on | query\_cache\_wlock\_invalidate | OFF | | query\_prealloc\_size | 8192 | # Qcache values in “show global status”： | Qcache\_free\_blocks | 179 | # not too many | Qcache\_free\_memory | 66697000 | # 63.6M free | Qcache\_hits | 32494 | | Qcache\_inserts | 174917 | | Qcache\_lowmem\_prunes | 0 | # zero | Qcache\_not\_cached | 7294692 | | Qcache\_queries\_in\_cache | 317 | | Qcache\_total\_blocks | 819 | # count of blocks # show global values Com\_delete | 551886 Com\_insert | 4306455 Com\_update | 53076201 Com\_truncate | 3 Com\_select | 7501372 | # cache hit percentage Qcache\_hists/(Qcache\_hits+Com\_select) = 32494/(32494+7501372) = 4% Qcache\_hists/( Qcache\_hists +Qcache\_inserts) =32494/(32494+174917) = 15.7% # Qcache\_lowmem\_prunes | 0 # average cache used for per query query\_cache\_size | 67108864 Qcache\_free\_memory | 62965144 Qcache\_queries\_in\_cache | 3168 (query\_cache\_size - Qcache\_free\_memory)/ Qcache\_queries\_in\_cache = （67108864-62965144)/ 3168=1308 # query cache memory block gaps Qcache\_total\_blocks | 6839 Qcache\_free\_blocks | 485 Qcache\_total\_blocks/2 \> Qcache\_free\_blocks ---
**Author:** ![jason\_asia](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jason\_asia](https://forums.percona.com/u/jason_asia)\ **Post date:** [April 1, 2014, 3:22am UTC](https://forums.percona.com/t/mysql-keeps-using-memory-and-swap-never-releases-memory/3370/8 "2014-04-01T03:22:20Z")
Hi, This is the memory usage info: the straight line means the mysql server was restarted again; the red line means the “-/+buffer cache used” ; the back line means the “Swap used” ---
**Author:** ![jason\_asia](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jason\_asia](https://forums.percona.com/u/jason_asia)\ **Post date:** [April 1, 2014, 3:31am UTC](https://forums.percona.com/t/mysql-keeps-using-memory-and-swap-never-releases-memory/3370/9 "2014-04-01T03:31:46Z")
Hi,niljoshi i upload the picture of the memory usage info failed. it’s like this: the usage of memory keeps going up , when it’s about 90%, the usage of swap start going up until about 80% percent of swap has been used , then the system’s iowait will go up to 10~30% ---
**Author:** ![jason\_asia](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jason\_asia](https://forums.percona.com/u/jason_asia)\ **Post date:** [April 1, 2014, 3:32am UTC](https://forums.percona.com/t/mysql-keeps-using-memory-and-swap-never-releases-memory/3370/10 "2014-04-01T03:32:00Z")
Hi,niljoshi i upload the picture of the memory usage info failed. it’s like this: the usage of memory keeps going up , when it’s about 90%, the usage of swap start going up until about 80% percent of swap has been used , then the system’s iowait will go up to 10~30% ---
**Author:** ![jason\_asia](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jason\_asia](https://forums.percona.com/u/jason_asia)\ **Post date:** [August 18, 2014, 9:46am UTC](https://forums.percona.com/t/mysql-keeps-using-memory-and-swap-never-releases-memory/3370/11 "2014-08-18T09:46:32Z")
Finally we found out the reason , it’s a bug of mysql 5.0.67. MySQL5.0.67 write the client dynamic information into information\_schema.CLIENT\_STATISTICS , client\_statistics is in memory. Normally, one client will have only one record , but this time, we set “client\_ip client\_hostname” in /etc/hosts , and the length of client\_hostname longer than 16 character , which is longer than length of information\_schema.client\_statistics.client column varchar(16), then mysql will keep update the record about client\_hostname in client\_statistics, but this is a bug, every time mysql update client\_statistics to update client\_hostname’s record , it will insert a new record with wrong values. As the time going , there will be more and more records in client\_statistics , which is in the memory , it won’t be released until you restart mysql server. So, after we change the “client\_ip client\_hostname” in /etc/hosts, restarting mysql server, everything becomes ok. Thanks all.