?
Solved

MySQL RAM taking too much.

Posted on 2013-06-05
6
Medium Priority
?
467 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 111

Accepted Solution

by:
Ray Paseur earned 2000 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
WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

 
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 111

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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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.
Suggested Courses

765 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