• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

Preventing big queries hanging Mysql Server?

Hi.

I am trying to find out if it is possible for Mysql V5 to abort commands / queries if they have been running for too long, recently we have had some users run large update queries on our servers via PHPMyadmin which resulted in this using 100% CPU on the server for several minutes which in turn stopped other services processing requests.

The server in question is Windows 2003 std running MYSQL V5.

0
Blueskimonkey
Asked:
Blueskimonkey
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
AFAIK, you cannot "stop" automatically slow queries.
but you can log those not using indexes:
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
0
 
Roger BaklundCommented:
Actually, there is a trick you can use to stop queries that have been running "too long".

If you have a script connecting as mysql root and issuing "SHOW PROCESSLIST" every x seconds, you can check in the "Time" column how many seconds each query has been running. When the value exceeds your predefined treshold, you can fetch the process id in the "Id" column, and issue "KILL $ID" to kill the connection.
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
also, you can try with "mytop" and/or "innotop" to take care these issues.

mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server, also works with 5.x also..

http://jeremy.zawodny.com/mysql/mytop/

For long run "Prevention is better than cure " so I agree with Angellll on configure your server for slow queries which are taking longer time... and you can make use of "mysqldumpslow" utility to catch those killer queries which are taking longer n longer time...


The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process the slow query log using the mysqldumpslow command to summarize the queries that appear in the log. Use mysqldumpslow --help to see the options that this command supports.



0
 
BlueskimonkeyAuthor Commented:
Thanks for your help
0
 
BlueskimonkeyAuthor Commented:
Thanks all for your answers, points awarded to cxr.

Mytop looks like a good tool but teh server is windows based, we arre also running slow query log and advising customers as we find problems.

Thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now