topic Re: System load increased due to error query on DB in Operating System - Linux

System load increased due to error query on DB

Henry Chua — Tue, 15 May 2007 09:07:19 GMT

Hi guys,

I have a query regarding system resources. I recently discover that my load on my system is very high. This is caused by user making accidentally making very big query on my system mysql service and thereafter their connection are broken thus this query remain as zombi. Should this be prevented by editing mysql query set on the webpage or write a script to kill detect and kill those zombi?

Hope that you can help

Regards
Henry

Re: System load increased due to error query on DB

Steven E. Protter — Tue, 15 May 2007 09:41:53 GMT

Shalom,

The connection should not terminate. You might want to update your system and see if mysql patches help.

More likely this is a user education issue. They need to properly terminate their sessions on mysql and stop writing system killing queries.

SEP

Re: System load increased due to error query on DB

Vitaly Karasik_1 — Wed, 16 May 2007 01:42:09 GMT

You can just write a script for killing long-running queries - for example, >3 minutes.

Re: System load increased due to error query on DB

Stuart Browne — Wed, 16 May 2007 23:00:09 GMT

If a user purposely drops out of a CLI session, or a network failure of some sort occurs, then query will continue to run.

They shouldn't be zombie's, they should just be queries-that-close-when-they-finish.

Using 'mysqladmin processlist' will help you pinpoint this, as they query time will increase, whilst other queries come and go.

Using 'mysqladmin kill ' will clear the individual queries.

That being said, automating the killing off of rogue/bad queries might not be in your best interest, especially if you have some long running queries.

Do not *NOT* just 'kill -KILL' anything. You *WILL* corrupt your tables that way.