mysql keeps using memory and swap , never releases memory

jason_asia · March 30, 2014, 9:35am

[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

niljoshi · March 31, 2014, 7:08am

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
[URL]MySQL Bugs: #33807: mysql_real_query memory leak

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/...rcona-build10/[/URL]

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]

jason_asia · March 31, 2014, 9:20am

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.

niljoshi · April 1, 2014, 2:29am

Hi, Thanks for your feedback. I’ll wait for new information.

jason_asia · April 1, 2014, 3:03am

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

jason_asia · April 1, 2014, 3:05am

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

jason_asia · April 1, 2014, 3:11am

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”：

show global values

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

jason_asia · April 1, 2014, 3:22am

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”

jason_asia · April 1, 2014, 3:31am

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%

jason_asia · April 1, 2014, 3:32am

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%

jason_asia · August 18, 2014, 9:46am

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.

Topic		Replies	Views
MySQL never release memory Other MySQL® Questions	10	19897	February 25, 2016
mysql memory consumption, memory leak ? Other MySQL® Questions	3	3984	April 8, 2007
MySQL grows until it crashes Other MySQL® Questions	4	515	August 18, 2008
MySQL Server used memory is not getting decreased Other MySQL® Questions	1	544	February 17, 2016
MYSQL Swapping Issue / Memory Usage Other MySQL® Questions	7	890	October 1, 2007

mysql keeps using memory and swap , never releases memory

free -mt

ps aux |grep mysql

top

values are from " show global status"

TPS

QPS

check query status

some values get from “show global variables like ‘query%’”：

Qcache values in “show global status”：

show global values

cache hit percentage

average cache used for per query

query cache memory block gaps

Related topics