Solved

MySQL RAM taking too much.

Posted on 2013-06-05
6
462 Views
Last Modified: 2013-06-16
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

?
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 35

Expert Comment

by:Seth Simmons
ID: 39224502
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39224535
"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
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39225356
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:marrowyung
ID: 39228341
"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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39228755
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39252144
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question