Solved

Preventing big queries hanging Mysql Server?

Posted on 2008-11-02
5
232 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

778 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