Link to home
Start Free TrialLog in
Avatar of mdoolittle
mdoolittle

asked on

MySQL Memory Leak?

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!

Avatar of wesly_chen
wesly_chen
Flag of United States of America image

You might want to use mtop to see your MySQL resource usage.
http://mtop.sourceforge.net/
Avatar of mdoolittle
mdoolittle

ASKER

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.
Avatar of Duncan Roe
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
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.
MySQL version: 5.0.51a-community-log
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
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.
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.


>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?
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.
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 ?
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.
ASKER CERTIFIED SOLUTION
Avatar of Duncan Roe
Duncan Roe
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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?
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.

Yes you want 2