Killing SQL

matsbs · September 29th, 2004, 12:41 PM

Hi,

I have a rather strange issue with MySQL on my dedicated. The server is rather active (a dual Xeon with 1GB RAM), but every now and then the SQL just hangs and the process is just running and running. This is hogging lots of CPU and the SQL is not responding.

Only way to fix this is to log into the server via SSH and do a kill -9 on the SQL process. This happens a few time per week and is starting to get a bit annoying. Is there any way that I could :

a) Make a cron-job / script that does a kill -9 on the MySQL job if it's been running for more than XX seconds.

b) Limit the ammount of time each SQL job is allowed to run

What would you guys suggest I do?

**patrickb** · September 30th, 2004, 12:37 AM

What you are wanting to do is basically like putting a band-aid on a broken leg. The underlying problem is still there, and I think that is what you should be addressing.

When the CPU load is high, I recommend running "free" and "ps" from SSH. That will give you a lot more info about what is going on at the time. Chances are high its a CPU intensive MYSQL query that is being run on a large database, such as a SUM() query. It may be a simple solution such as upgrading the memory on your server.

September 29th, 2004, 12:41 PM	#1 (permalink)
matsbs Registered User Fresh Surpasser Joined in May 2004 15 posts Gave thanks: 0 Thanked 0 times	Killing SQL Hi, I have a rather strange issue with MySQL on my dedicated. The server is rather active (a dual Xeon with 1GB RAM), but every now and then the SQL just hangs and the process is just running and running. This is hogging lots of CPU and the SQL is not responding. Only way to fix this is to log into the server via SSH and do a kill -9 on the SQL process. This happens a few time per week and is starting to get a bit annoying. Is there any way that I could : a) Make a cron-job / script that does a kill -9 on the MySQL job if it's been running for more than XX seconds. b) Limit the ammount of time each SQL job is allowed to run What would you guys suggest I do? __________________ Server : Dedicated :surpass:

September 30th, 2004, 12:37 AM	#2 (permalink)
patrickb the one who was Super #1 Joined in Jul 2003 Lives in Memphis 1,967 posts Gave thanks: 0 Thanked 3 times	What you are wanting to do is basically like putting a band-aid on a broken leg. The underlying problem is still there, and I think that is what you should be addressing. When the CPU load is high, I recommend running "free" and "ps" from SSH. That will give you a lot more info about what is going on at the time. Chances are high its a CPU intensive MYSQL query that is being run on a large database, such as a SUM() query. It may be a simple solution such as upgrading the memory on your server. __________________ Patrick Warnings: The program(s) might crash unexpectedly or behave otherwise strangely. (But of course, so do many commercial programs on Windows.) --www.gimp.org

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)