0

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 long running queries (typically selects in my case) from taking out the server?

NOTE: Running Mandriva, DB is in MyISAM, and this is for SELECT queries only.

2
  • 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
0

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. :)

2

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.

1

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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