Solved

MySQL RAM taking too much.

Posted on 2013-06-05
6
460 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
  • 3
  • 2
6 Comments
 
LVL 34

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 109

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 109

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

828 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