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?

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.

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?

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?

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

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.

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