LinuxQuestions.org
Support LQ: Use code LQ3 and save $3 on Domain Registration
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 03-22-2004, 03:31 PM   #1
linuxboy69
Member
 
Registered: Oct 2003
Distribution: Redhat 9
Posts: 138

Rep: Reputation: 15
Problems with MySQL


My box with MySQL hangs every once and awhile because it keeps on taking up more and more ram. I use the command top and it just shows the memory used up in ram raising until the box hangs. Anybody that uses mysql, do you know why this happens?
 
Old 03-22-2004, 03:53 PM   #2
ricstirato
Member
 
Registered: Jan 2004
Location: Gießen, Germany
Distribution: Xubuntu 12.04, Mythbuntu, Ubuntu Server 12.04
Posts: 174

Rep: Reputation: 24
This can happen when you execute a query over several tables (with probably a lot of records each) without using JOIN, e.g. something like

SELECT t1.*, t2.*, t3.* FROM t1, t2, t3

If every table (t1, t2, t3) has 500 records and the average record in every table has 200 bytes, you get

500 * 500 * 500 = 125.000.000 result rows, giving 125.000.000 * (200 + 200 + 200) = 69.8 GB


You can try to reproduce this by creating some random data and execute the above query.
 
Old 03-22-2004, 04:09 PM   #3
linuxboy69
Member
 
Registered: Oct 2003
Distribution: Redhat 9
Posts: 138

Original Poster
Rep: Reputation: 15
I am going to try that but I realize that I didn't word the problem very clearly in my post earlier. I have a couple gigs of ram on my database server and if I restart it. It starts out as using 200 megs but after an hour it uses 400 megs then in another hour it will use 700 megs. The number never goes down. The longer mysql is running the more memory ends up getting taken up. The process is slow but seems to only be corrected by restarting it which we all know isn't very good. Can you think of what might cause this?
 
Old 03-22-2004, 04:43 PM   #4
trickykid
LQ Guru
 
Registered: Jan 2001
Posts: 24,149

Rep: Reputation: 234Reputation: 234Reputation: 234
Quote:
Originally posted by linuxboy69
I am going to try that but I realize that I didn't word the problem very clearly in my post earlier. I have a couple gigs of ram on my database server and if I restart it. It starts out as using 200 megs but after an hour it uses 400 megs then in another hour it will use 700 megs. The number never goes down. The longer mysql is running the more memory ends up getting taken up. The process is slow but seems to only be corrected by restarting it which we all know isn't very good. Can you think of what might cause this?
Well its fairly normal for Linux to use up all possible ram, caches it, etc. If your totally concerned, have you tried to perform some memory tests, could be a leak of some kind, etc.

And how many use this database server at a given time?
 
Old 03-22-2004, 04:57 PM   #5
linuxboy69
Member
 
Registered: Oct 2003
Distribution: Redhat 9
Posts: 138

Original Poster
Rep: Reputation: 15
It goes through a couple hundred queries an hour I would say. I know that linux tries to use all available ram but does that include your swap memory as well?

Does linux have any built in commands to test the memory?

Last edited by linuxboy69; 03-22-2004 at 05:01 PM.
 
Old 03-22-2004, 05:53 PM   #6
jsokko
Member
 
Registered: Mar 2004
Location: Phila, PA
Distribution: SuSE 9.0 / RH 9 / Slackware 9.1
Posts: 110

Rep: Reputation: 15
This usually happens because of faulty circular logic in SQL... meaning someone's running a bad query, running up the DB cache. You should be able to track it down via the log. If you're using PHP or JSP engine you should check the app log too to see where it might be hanging. You should also check the status of MySQL server using MySQLCC or Administrator if you're in a test / non-prod environment... or learn how to use the MySQL CLI to check status.


which version of MySQL are you using?


J
 
Old 03-23-2004, 10:48 AM   #7
linuxboy69
Member
 
Registered: Oct 2003
Distribution: Redhat 9
Posts: 138

Original Poster
Rep: Reputation: 15
I am running mysql 4.0.16.

I looked in the error log but it doesn't have anything usefull in it. Just to clarify, it is not the service that hangs but rather the whole computer. The only service that we use on it though is the database. I will have to look into MySQLCC.
 
Old 03-23-2004, 11:46 AM   #8
jsokko
Member
 
Registered: Mar 2004
Location: Phila, PA
Distribution: SuSE 9.0 / RH 9 / Slackware 9.1
Posts: 110

Rep: Reputation: 15
Oh yes, MySQL will hang your whole machine if someone wrote a faulty circular logic query that makes your mem usage climb to nowhere... I'd highly recommend going through MySQL's documentation on administration. It's a neccessary read.

As for your clients who are hitting the DB server -- what client are they using and how are they creating their queries? are they using CLI? some 3rd party GUI?

MySQLCC has a nifty feature that escapes *some* memory intensive faulty queries. it also checks some syntax for you on the fly. I'd highly recommend it even to users. Just make sure their privs are set properly on the server.

J
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. NoviceW Linux - Networking 17 09-17-2014 03:13 PM
ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. suziecorbett Linux - Software 8 10-09-2008 02:52 AM
Problems with MySQL on SuSE: Can't Connect (/var/lib/mysql/mysql.sock) neocookie Linux - Software 8 02-08-2008 12:48 AM
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. Dannux Linux - Software 3 03-24-2006 09:44 AM
DBD::MySQL + MySQL install problems. Elleroth Linux - Software 4 03-24-2006 07:53 AM


All times are GMT -5. The time now is 04:48 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration