Solved

MySQL Memory Leak?

Posted on 2008-10-04
17
1,218 Views
Last Modified: 2013-12-16
I have a website that performs beautifully- it is a DRUPAL website. 4GB of ram. My.cnf is configured I think correctly. Randmonly- and without apparent reason MySQL memory spikes through the roof takes over entire machine and crashes server.  Last time site ran fine for 8 days- I crawled the site- stress tested the site with 1000s of user- no crash- everything looks fine. But in prodcution, randomly and without warning MySQL memory rises exponentially in about 5 minutes from 5% of memory to > 80% and then machine locks up website gives an error of "too many MySQL connections" before dying altoether and taking down server requring hard re-boot. This is driving me crazy and my Web client also who wants his website to not crash- randomly. Apache logs show nothing unusual nor do MySQL logs- that I can see. Does not appear to be a DOS attack, I have researched all DRUPAL memory config settings for Drupal websites.  I also cannot duplicate the crash in any testing environment- no matter how many users I hit the site with.

It started to crash the other day- I was watching memory and saw MySQL memory climbing FAST. I restarted- MySQL service and everything was fine. Site stayed up and so did server. Was stable again for days afterward.

1. Is there a way to restrict MySQL memory - or restart MySQL service if it takes over > X% of memory?
2. How can I tell which Query caused MySQL to balloon out of control?

Thanks!

0
Comment
Question by:mdoolittle
  • 10
  • 4
  • 3
17 Comments
 
LVL 38

Expert Comment

by:wesly_chen
ID: 22641755
You might want to use mtop to see your MySQL resource usage.
http://mtop.sourceforge.net/
0
 

Author Comment

by:mdoolittle
ID: 22642354
Thanks- i have MTOP running.  While crawling site it shows just a few slow queries that disapear- then when MySQL crashes- I can't tell what was on the screen anyway. I have the Log_Slow_Queries set in My.cnf- didn't see anything helpful there either. This crash is too random. That is why I think I better worry about restarting service before machine crashes at least then the site won't go down and my client can have their website that they paid for.
0
 
LVL 34

Expert Comment

by:Duncan Roe
ID: 22642585
When you say it  needs a hard reboot, do you mean the system has actually crashed? (with no console login for instance)
Do you have error-correcting RAM? Might be worth running memtest86 for a while - but the site will be down while you do that
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.

 

Author Comment

by:mdoolittle
ID: 22642596
Yes - no console, no ssh, nothing. Still pings sometimes.  It is at a hosting company- a dedicated server. I assume they put in the right memory.  Requires a ticket to tech-support to hard-reboot. Which takes about 20 minutes so when the site crashes website is offline for like 40 minutes. Just crazy and my client will not go live of course- until this 'random' crashing stops.
0
 

Author Comment

by:mdoolittle
ID: 22642598
MySQL version: 5.0.51a-community-log
0
 

Author Comment

by:mdoolittle
ID: 22642613
Almost forgot- we kept upgrading the server at  hosting company thinking it was a hardware problem- we are now at 'biggest' server - so t his can't be a memory problem. Happened on 3 different machines
0
 
LVL 38

Expert Comment

by:wesly_chen
ID: 22642729
After reboot, do you see any error messages in system log
/var/adm/messages (for Solaris OS)
/var/log/messages  (For Linux)
What OS do you use?  Could you post the following result here?
uname -a
egrep -i 'error|panic|memory'  /var/log/messages (or /var/adm/messages)

So we have more information to help you.
0
 

Author Comment

by:mdoolittle
ID: 22643267
2.6.9-67.ELsmp #1 SMP Fri Nov 16 12:48:03 EST 2007 i686 i686 i386 GNU/Linux

No memory error messages in /var/log/messages


This computer is our recent upgrade and last crash I was able re-start MySQL before it took server down.


0
 
LVL 38

Expert Comment

by:wesly_chen
ID: 22643367
>This computer is our recent upgrade
What kind of upgrade have been done on this computer?
OS upgrade or MySQL upgrade? What OS version (uname -a)?
Does it crash that often before upgrade?
0
 

Author Comment

by:mdoolittle
ID: 22643404
This line was what I got when I did uname -a
2.6.9-67.ELsmp #1 SMP Fri Nov 16 12:48:03 EST 2007 i686 i686 i386 GNU/Linux

When I say upgrade I mean this was a move to a new server. We moved to new hardware, new computer with more memory, dual CPU etc. Old hardware had same problem with this website. We were hoping new server with 2x the old memory and CPU power would help. It has not. Same MySQL version MySQL version: 5.0.51a-community-log.

Before on old server with 2GB of memory (this new server has 4GB) it crashed more often. But this new server as had the same problem.
0
 
LVL 38

Expert Comment

by:wesly_chen
ID: 22643461
OK, it is Red Hat Enterprise Linux 4 (or CentOS 4) 32bit OS.

Do you see any error message in /var/lib/mysql/error.log ?
0
 

Author Comment

by:mdoolittle
ID: 22671537
No error.log at all. I have written a script to re-start MySQL if memory increases.  I will look in error log when crash happens again.
0
 
LVL 34

Accepted Solution

by:
Duncan Roe earned 500 total points
ID: 22673377
You could change the system's Virtual Memory (vm) policy so that no process is allowed to obtain more memory than can be swapped out (no overcommit). This should at least stop your system from crashing. Read Documentation/sysctl/vm.txt and Documentation/filesystems/proc.txt in your Linux source directory. Essentially you need to do "echo 2 >/proc/sys/vm/overcommit_memory" to enable overcommit denial (I guess you'd have to raise a ticket). Decide what you'd like for overcommit_ratio first (I think it defaults to 50). Any program trying to allocate too much memory will then get an error return - you might start to get error messages logged then, if you're lucky.
0
 

Author Closing Comment

by:mdoolittle
ID: 31503064
Thanks a lot- a very good solution. I am running script now that just checks percentage usage and tries to re-start. I will try this.
0
 

Author Comment

by:mdoolittle
ID: 22674937
Duncan- our OVERCOMMIT_MEMORY is set to 0
That should raise an error if not enough physical memory. Shouldn't that return an error code when MySQL tries to malloc too much? Why would 2 help? That just adds in swap at commit_ratio. I would think 0 is stricter and would raise same errors?
0
 

Author Comment

by:mdoolittle
ID: 22675507
Seems there are two versions of docs- so this is correct? 2 = NO OVER COMMIT OVER RATIO?

Overcommit ParametersThe overcommit_memory and overcommit_ratio parameters specify if and how to over commit physical memory.

" overcommit_memory = <0|1|2>

0  Root allowed to heuristically over allocate memory slightly, but any obvious over commitment is refused.
1  Always allow applications to over commit physical memory. Useful for some scientific applications, which allocate large amounts of memory, but don't actually touch all of the allocated pages.
2  Never allow over commitment of memory. Refuse any request greater than overcommit_ratio = ## % of physical RAM. In these cases, malloc will fail.

0
 
LVL 34

Expert Comment

by:Duncan Roe
ID: 22676697
Yes you want 2
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
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…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

773 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