Solved

MySQL Memory Leak?

Posted on 2008-10-04
17
1,230 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
converting integer data type to time data type in sql 4 48
issue with DB import 1 36
Can't connect to FTP 18 110
MySqli Real Escape String and SQL Injection 1 49
Creating and Managing Databases with phpMyAdmin in cPanel.
Fine Tune your automatic Updates for Ubuntu / Debian
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

733 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