Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

OS: Redhat Enterprise Linux Server Release 5.3 (Tikanga)

Architecture: Intel Xeon 64Bit

MySQL Server 5.5.20 Enterprise Server advanced edition.

Application: Liferay.

My database size is 200MB. RAM is 64GB. The memory consumption increases gradually and we run out of memory. Then only rebooting releases all the memory, but then process of memory consumption starts again and reaches 63-64GB in less than a day.

Parameters detail:

key_buffer_size=16M

innodb_buffer_pool_size=3GB

inndb_buffer_pool_instances=3

max_connections=1000

innodb_flush_method=O_DIRECT

innodb_change_buffering=inserts

read_buffer_size=2M

read_rnd_buffer_size=256K

It's a serious production server issue that I am facing. What could be the reason behind this and how to resolve.

This is the report of 2pm today, after Linux was rebooted yesterday @ around 10pm.

Output of free -m

             total       used       free     shared    buffers     cached
Mem:         64455      22053      42402          0       1544       1164
-/+ buffers/cache:      19343      45112
Swap:        74998          0      74998



Output of vmstat 2 5

   procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------    
   r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
   0  0      0 43423976 1583700 1086616    0    0     1   173   22   27  1  1 98  0  0
   2  0      0 43280200 1583712 1228636    0    0     0   146 1265  491  2  2 96  1  0
   0  0      0 43421940 1583724 1087160    0    0     0   138 1469  738  2  1 97  0  0
   1  0      0 43422604 1583728 1086736    0    0     0  5816 1615  934  1  1 97  0  0
   0  0      0 43422372 1583732 1086752    0    0     0  2784 1323  545  2  1 97  0  0

Output of top -n 3 -b


top - 14:16:22 up 16:32,  5 users,  load average: 0.79, 0.77, 0.93
Tasks: 345 total,   1 running, 344 sleeping,   0 stopped,   0 zombie
Cpu(s):  1.0%us,  0.9%sy,  0.0%ni, 98.1%id,  0.1%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  66002772k total, 22656292k used, 43346480k free,  1582152k buffers
Swap: 76798724k total,        0k used, 76798724k free,  1163616k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                     
 6434 mysql     15   0 4095m 841m 5500 S 113.5  1.3 426:53.69 mysqld                     
    1 root      15   0 10344  680  572 S  0.0  0.0   0:03.09 init                        
    2 root      RT  -5     0    0    0 S  0.0  0.0   0:00.01 migration/0                 
    3 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/0                 
    4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0                  
    5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.01 migration/1                                               

share|improve this question
4  
I wonder if this is a typical case of HELP! Linux Ate My RAM! ( linuxatemyram.com ) or do you have actual problems? Does the server swap itself to death? Slow down to a crawl within a day? Something else terrible? –  Janne Pikkarainen Jun 20 '12 at 10:09
    
I have checked indexes, set parameters as suggested by MySQL Support team, but in vain.. :( –  Sunrays Jun 20 '12 at 10:16
    
You didn't actually reply my question. :-) –  Janne Pikkarainen Jun 20 '12 at 10:19
2  
@sunrays have you checked the size of the individusl processes on your system using top which is what Dom was suggesting? Is MySQL the largest? Is it the one growing over time? (MySQL is usually good about not having memory leaks. It's very likely your culprit is something else - you need to look at individual processes, not just aggregate usage) –  voretaq7 Jun 20 '12 at 13:08
1  
I see nothing in what you posted that even hints that you have a problem and even less to implicate MySQL as the cause of it. Exactly what is the problem and why do you point the finger at MySQL? Memory usage is not a problem in itself. –  John Gardeniers Jun 21 '12 at 10:45

1 Answer 1

up vote 1 down vote accepted

I had a similar issue, and basically I changed the mysqltuner.pl script and made it more verbose and know what happened.

Basically, the memory usage, if you are using any variation of my-innodb-heavy-4G.cnf config file, the major part of memory using will be nearly like this:

memory usage = min(tmp_table_size, max_heap_table_size) 
    + key_buffer_size + query_cache_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 + thread_stack + join_buffer_size
        )
    )

This sum have not all factors, please refer mysqltuner.pl script code (and run it) to see them all.

So, it seems you need to lower a lot read_buffer_size, read_rnd_buffer_size, sort_buffer_size, thread_stack and join_buffer_size, since its sum is multiplied by 1000 from max_connections.

Other solution is to lower a little bit the max_connections number. With this huge memory for thread buffers, innodb_buffer_pool_size and all InnoDB related variables becomes a minor issue.

You can also try to figure out if your applications really a huge amount of sort_buffer_size and join_buffer_size. If not, put these values down.

Hope it helped.

share|improve this answer
    
@ Lucas Thanks for the suggestions. Actually the problem was with the Application, where the connections was not releasing memory even after the connection was closed. The application is liferay and as I mentioned earlier we set two parameters in that : testOnBorrow=''true' and validationQuery="SELECT". Now the server is stable. Still I would keep your points in mind.. :) –  Sunrays Jun 28 '12 at 14:21
    
@ Lucas I tried executing the mysqltuner.pl script but didnt get any output. It would be nice if you could give a link or share a document on how to use it.. –  Sunrays Jun 28 '12 at 14:24
1  
@Sunrays you only need to have perl installed at your server. Install perl, download mysqltuner (# wget http://mysqltuner.pl/mysqltuner.pl) and run perl mysqltuner.pl . It will ask you MySQL's user and password, will run some tests and suggest you what you could tune up in your server. But be careful, run at the same time in other tty some memory monitor, like htop to take care if mysqltuner do something that eats much memory. –  LucasBr Jun 29 '12 at 14:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.