System load increased due to error query on DB

[Henry Chua]

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

[Steven E. Protter]

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

[Vitaly Karasik_1]

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

[Stuart Browne]

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.