?
Solved

MySQL Memory Leak?

Posted on 2008-10-04
17
Medium Priority
?
1,240 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
[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
  • 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 35

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
RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

 

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 35

Accepted Solution

by:
Duncan Roe earned 2000 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 35

Expert Comment

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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
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.
Suggested Courses

777 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