Solved

Preventing big queries hanging Mysql Server?

Posted on 2008-11-02
5
223 Views
Last Modified: 2012-05-05
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
Comment
Question by:Blueskimonkey
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22862760
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
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
ID: 22863523
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
 
LVL 26

Expert Comment

by:ushastry
ID: 22864416
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
 

Author Closing Comment

by:Blueskimonkey
ID: 31512519
Thanks for your help
0
 

Author Comment

by:Blueskimonkey
ID: 22864964
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now