MySQL RAM taking too much.

Dear all,

Right now we have a problem that the RAM of MySQL taking too much and we have alert that RAM token is 99.31% and swapping is 11.58%.

when we do show full processlist we see this:

157103 | readinsight | 172.16.0.31:46763 | WebHynet | Sleep   |     364 |                                                                             | NULL             |
| 157153 | readinsight | 172.16.0.31:46838 | WebHynet | Sleep   |    1730 |                                                                             | NULL             |
| 157154 | readinsight | 172.16.0.31:46840 | WebHynet | Sleep   |     700 |                                                                             | NULL             |
| 157177 | readinsight | 172.16.0.31:46862 | WebHynet | Sleep   |     707 |                                                                             | NULL             |
| 157217 | readinsight | 172.16.0.31:46951 | WebHynet | Sleep   |    1445 |                                                                             | NULL             |
| 157277 | readinsight | 172.16.0.31:47033 | WebHynet | Sleep   |     303 |                                                                             | NULL             |
| 157278 | readinsight | 172.16.0.30:54027 | WebHynet | Sleep   |     455 |                                                                             | NULL             |
| 157279 | readinsight | 172.16.0.31:47034 | WebHynet | Sleep   |     957 |                                                                             | NULL             |
| 157291 | readinsight | 172.16.0.31:47053 | WebHynet | Sleep   |      91 |                                                                             | NULL             |
| 157296 | readinsight | 172.16.0.31:47061 | WebHynet | Sleep   |     849 |                                                                             | NULL             |
| 157302 | readinsight | 172.16.0.30:54089 | WebHynet | Sleep   |     821 |                                                                             | NULL             |
| 157304 | readinsight | 172.16.0.31:47068 | WebHynet | Sleep   |     773 |                                                                             | NULL             |
| 157306 | readinsight | 172.16.0.30:54103 | WebHynet | Sleep   |     752 |                                                                             | NULL             |
| 157307 | readinsight | 172.16.0.31:47078 | WebHynet | Sleep   |     483 |                                                                             | NULL             |
| 157319 | readinsight | 172.16.0.31:47094 | WebHynet | Sleep   |      93 |                                                                             | NULL             |
| 157322 | readinsight | 172.16.0.31:47100 | WebHynet | Sleep   |     240 |                                                                             | NULL             |
| 157325 | readinsight | 172.16.0.31:47113 | WebHynet | Sleep   |     645 |                                                                             | NULL             |
| 157327 | readinsight | 172.16.0.31:47116 | WebHynet | Sleep   |     633 |                                                                             | NULL             |
| 157330 | readinsight | 172.16.0.30:54170 | WebHynet | Sleep   |     299 |                                                                             | NULL             |
| 157337 | readinsight | 172.16.0.31:47138 | WebHynet | Sleep   |     556 |                                                                             | NULL             |
| 157345 | readinsight | 172.16.0.30:54217 | WebHynet | Sleep   |     366 |                                                                             | NULL             |
| 157347 | readinsight | 172.16.0.31:47167 | WebHynet | Sleep   |     404 |                                                                             | NULL             |
| 157361 | readinsight | 172.16.0.30:54261 | WebHynet | Sleep   |     330 |                                                                             | NULL             |
| 157363 | readinsight | 172.16.0.30:54267 | WebHynet | Sleep   |     325 |                                                                             | NULL             |
| 157371 | readinsight | 172.16.0.31:47240 | WebHynet | Sleep   |     240 |                                                                             | NUL| 157291 | readinsight | 172.16.0.31:47053 | WebHynet | Sleep   |      95 |                                                                             | NULL             |
| 157296 | readinsight | 172.16.0.31:47061 | WebHynet | Sleep   |     853 |                                                                             | NULL             |
| 157302 | readinsight | 172.16.0.30:54089 | WebHynet | Sleep   |     825 |                                                                             | NULL             |
| 157304 | readinsight | 172.16.0.31:47068 | WebHynet | Sleep   |     777 |                                                                             | NULL             |
| 157306 | readinsight | 172.16.0.30:54103 | WebHynet | Sleep   |     756 |                                                                             | NULL             |
| 157307 | readinsight | 172.16.0.31:47078 | WebHynet | Sleep   |     487 |                                                                             | NULL             |
| 157319 | readinsight | 172.16.0.31:47094 | WebHynet | Sleep   |      97 |                                                                             | NULL             |
| 157322 | readinsight | 172.16.0.31:47100 | WebHynet | Sleep   |     244 |                                                                             | NULL             |
| 157325 | readinsight | 172.16.0.31:47113 | WebHynet | Sleep   |     649 |                                                                             | NULL             |
| 157327 | readinsight | 172.16.0.31:47116 | WebHynet | Sleep   |     637 |                                                                             | NULL             |
| 157330 | readinsight | 172.16.0.30:54170 | WebHynet | Sleep   |     303 |                                                                             | NULL             |
| 157337 | readinsight | 172.16.0.31:47138 | WebHynet | Sleep   |     560 |                                                                             | NULL             |
| 157345 | readinsight | 172.16.0.30:54217 | WebHynet | Sleep   |     370 |                                                                             | NULL             |
| 157347 | readinsight | 172.16.0.31:47167 | WebHynet | Sleep   |     408 |                                                                             | NULL             |
| 157361 | readinsight | 172.16.0.30:54261 | WebHynet | Sleep   |     334 |                                                                             | NULL             |
| 157363 | readinsight | 172.16.0.30:54267 | WebHynet | Sleep   |     329 |                                                                             | NULL             |
| 157371 | readinsight | 172.16.0.31:47240 | WebHynet | Sleep   |     244 |                                                                             | NULL             |
| 157379 | readinsight | 172.16.0.31:47257 | WebHynet | Sleep   |     189 |                                                                             | NULL             |
| 157383 | readinsight | 172.16.0.30:54352 | WebHynet | Sleep   |      35 |                                                                             | NULL             |
| 157385 | readinsight | 172.16.0.30:54362 | WebHynet | Sleep   |      32 |                                                                             | NULL             |
| 157386 | readinsight | 172.16.0.31:47271 | WebHynet | Sleep   |     150 |                                                                             | NULL             |
| 157388 | readinsight | 172.16.0.30:54365 | WebHynet | Sleep   |     134 |                                                                             | NULL             |
| 157392 | readinsight | 172.16.0.30:54368 | WebHynet | Sleep   |      68 |                                                                             | NULL             |
| 157395 | readinsight | 172.16.0.31:47282 | WebHynet | Sleep   |     126 |                                                                             | NULL             |
| 157397 | readinsight | 172.16.0.31:47283 | WebHynet | Sleep   |      95 |                                                                             | NULL             |
| 157399 | readinsight | 172.16.0.30:54395 | WebHynet | Sleep   |       8 |                                                                             | NULL             |
| 157400 | readinsight | 172.16.0.30:54419 | WebHynet | Sleep   |      72 |                                                                             | NULL             |
| 157401 | readinsight | 172.16.0.30:54420 | WebHynet | Sleep   |      72 |                                                                             | NULL             |
| 157402 | readinsight | 172.16.0.30:54421 | WebHynet | Sleep   |      68 |                                                                             | NULL             |
| 157406 | root        | localhost         | NULL     | Query   |       0 | NULL                                                                        | show processlist |
| 157407 | readinsight | 172.16.0.30:54449 | WebHynet | Sleep   |      32 |                                                                             | NULL             |
| 157412 | readinsight | 172.16.0.30:54466 | WebHynet | Sleep   |      12 |                                                                             | NULL             |
| 157413 | readinsight | 172.16.0.30:54471 | WebHynet | Sleep   |      13 |                                                                             | NULL             |
| 157414 | readinsight | 172.16.0.30:54483 | WebHynet | Sleep   |       8 |                                                                             | NULL             |
+--------+-------------+-------------------+----------+---------+---------+-----------------------------------------------------------------------------+------------------+

Open in new window


all the RAM, 64GB is token by the MysQL because of this.

Then how can we fix this? the mysQL is not going to automatically manage the RAM itself ?some said this is some kind of problem, is due to the face that the PHP code is using persistent connections to MySQL.

What is your experience on this ?

This link:
http://stackoverflow.com/questions/243644/how-to-setup-a-connection-timeout-depending-of-the-user-login-in-mysql

tells that we can change the
interactive_timeout=180
wait_timeout=180

in order to change the connection time out, any experience on this ?

other said we have to fix some PHP code as there might be some abnormal exist of PHP connection and this make some PHP code,  any link has more detail on how to improve this coding?

functionA () {
   $link=mysql_connect(...)
   $result=mysql_query('select * from table1 where valueA=1');
   while ($row = mysql_fetch_assoc($result) ) {
      if ($row[valueB] == 10) {
          # this is undesired valueB, so we exit
          exit;  <--- this may cause non-closed connection, should add "mysql_close($link)" here.
      } else {
          # do seomething
      }
   }
   mysql_close($link);
}

Open in new window

?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
This author seems to address the timeouts fairly well:
http://www.serveridol.com/2012/04/13/mysql-interactive_timeout-vs-wait_timeout/

PHP code is using persistent connections to MySQL.
This is almost never a good idea.  Typically, each PHP script is started by one HTTP request.  The PHP script creates a response document, sends it, and the whole process is complete.  Unless your PHP scripts are doing something very out of the ordinary, I would try removing the persistent connections and just use ordinary connections.

A little more about HTTP request and response is available here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/A_11271-Understanding-Client-Server-Protocols-and-Web-Applications.html

Some of my MySQL settings are as follows:
connect_timeout => 10
interactive_timeout => 28800
table_lock_wait_timeout => 50
wait_timeout => 28800
0
 
Seth SimmonsSr. Systems AdministratorCommented:
what operating system are you running this on?  have you looked at how much memory mysql is actually using?  have you changed any parameters regarding mysql memory usage such as pool size, temp table space, etc.?  i'm not a developer, but i would first look at how mysql is configured; though it is possible bad php code could contribute to this behavior
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"what operating system are you running this on? "

Redhat LInux

"have you looked at how much memory mysql is actually using?"

64GB all eat by MySQL

"have you changed any parameters regarding mysql memory usage such as pool size, temp table space, etc.?  "

not yet, but someone confirm this:

interactive_timeout=180
wait_timeout=180
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
marrowyungSenior Technical architecture (Data)Author Commented:
"Some of my MySQL settings are as follows:
connect_timeout => 10
interactive_timeout => 28800
table_lock_wait_timeout => 50
wait_timeout => 28800 "

So you always do not have any problem about that ?
0
 
Ray PaseurCommented:
If I had a problem with it, I wouldn't have suggested it!

Please also, see this link:
http://support.experts-exchange.com/customer/portal/articles/481419
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
you mean this...... ?

"As an expert, you are expected to provide a solution that fully addresses the asker’s question and shows that you understand the issue. If you post a link to another page or site, you should post an explanation of the link and how it can help the asker answer his question."'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.