Solved

MySQL Memory Leak?

Posted on 2008-10-04
17
1,199 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
Comment Utility
You might want to use mtop to see your MySQL resource usage.
http://mtop.sourceforge.net/
0
 

Author Comment

by:mdoolittle
Comment Utility
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
Comment Utility
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
 

Author Comment

by:mdoolittle
Comment Utility
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
Comment Utility
MySQL version: 5.0.51a-community-log
0
 

Author Comment

by:mdoolittle
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 38

Expert Comment

by:wesly_chen
Comment Utility
>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yes you want 2
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now