Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

At the moment on our server, my boss has shell access and runs SQL directly through the mysql application, but sometimes he creates a bad query and the MySQL DB dies until i can kill it (or wait out the 10 or so mins it takes for the query to finish dying). The best solution is to not let him do this, but unfortunately you can't always get your way with this sort of thing.

Anyway, is there a way to assign a timeout period for MySQL queries, and limit them to say 60 seconds? I know its possible to do this when using a scripting language (eg, PHP). But is it possible to set a configuration in MySQL directly to prevent this sort of thing from happening? (And hey, Timeout's are always a good thing to have anyway).

NOTE: Running Mandriva, DB is in MyISAM, and this is for SELECT queries only. (Boss uses it for "quick" reports)

share|improve this question
    
Are these select queries or insert/update/delete ones? If they are select queries only, you can setup a mysql slave and give your boss access to slave. –  Khaled May 6 '12 at 12:05
    
Select only. That is a possibility I have been considering as well –  Mattisdada May 6 '12 at 12:11
add comment

4 Answers

up vote 0 down vote accepted

One solution I typically do, if replication / slaves are not an option is this and you're trying to avoid long running report queries;

  • Assess which slow running SELECT queries are repeatedly executed
  • Turn the SELECT statements into report tables: CREATE TABLE report AS SELECT...
  • And fill the report tables nightly or whatever the case may be with a CRON job and that SELECT statement.

In this case your boss would be getting the data a lot quicker in the new report table(s). You probably have a good solution figured out already, but this could still be useful. :)

share|improve this answer
add comment

A simple solution is to use Percona Toolkit's pt-kill option. It has several options and in use by several people.

I would recommend to use slave server but it requires additional resources (server/instance). In this approach, your production server will be isolated and no damage will be done to the business even if your boss writes a worst query.

share|improve this answer
add comment

Based on your response about queries type I can say the following:

If your boss is executing only select statement, you can consider installing and running mysql replication and grant him access to the slave database server. This way your production (master) server will not be affected if he typed a very complicated and time-consuming query.

share|improve this answer
add comment

Not by default, no.

However the Twitter team has released their work on MySQL which exactly implements this (from http://engineering.twitter.com/2012/04/mysql-at-twitter.html):

- Reduce unnecessary work through improved server-side statement timeout support. This allows the server to proactively cancel queries that run longer than a millisecond-granularity timeout.

See https://github.com/twitter/mysql/wiki/Statement-Timeout and https://github.com/twitter/mysql.

A more trivial solution is to run a cronjob every X minutes which searches for queries by your boss and kill them if they run longer than Y seconds.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.