Solved

Preventing big queries hanging Mysql Server?

Posted on 2008-11-02
5
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 143

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:Umesh
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

696 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