Solved

Preventing big queries hanging Mysql Server?

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 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