• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

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

?
0
marrowyung
Asked:
marrowyung
  • 3
  • 2
1 Solution
 
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
 
marrowyungAuthor 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
 
Ray PaseurCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
marrowyungAuthor 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
 
marrowyungAuthor 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now