Kill slow mysql queries

Rate this post

A Shell script for killing slow MySQL queries:

#!/bin/sh

# Credentials for a MySQL user with PROCESS, SUPER permissions
USERNAME=
PASSWORD=

# MySQL Server location
HOST=
PORT=3306

TIMEOUT=60 # 1 minute
TARGET_USER= # MySQL user to monitor

MYSQL=”mysql -u $USERNAME –password=$PASSWORD -h $HOST -P $PORT -B”

$MYSQL -N -e ‘SHOW FULL PROCESSLIST’ | cut -f 1,2,6 | awk ‘{ if ($3 > $TIMEOUT && $2 == $TARGET_USER) print “KILL ” $1 “;” }’

Source: https://gist.github.com/paulrosania/2883869

You can set the cron job to do the activity every minutes.

Learn more – How to schedule cron job

Linux Cron Job – Schedule Job in linux: crontab

 

Related Posts

  • 53
    I connect to mysql from my Linux shell. Every now and then I run a SELECT query that is too big. It prints and prints and I already know this is not what I meant. I would like to stop the query. Hitting Ctrl+C (a couple of times) kills mysql…
    Tags: mysql, query, shell, killing, processlist, kill, linux
  • 48
    To enable the query log, put this in /etc/my.cnf in the [mysqld] section log = /path/to/query.log #works for mysql < 5.1.29 Also, to enable it from MySQL console SET general_log = 1; Remember that this logfile can grow very big on a busy server. update: With mysql 5.1.29+ , the log option is deprecated. To specify…
    Tags: mysql, set, query, server, queries
  • 47
    Very often we need to extract last 1 month, 2 months data from mysql DB. Here we will show easy way to get the data of given interval. SELECT * from testtable WHERE startdate BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND DATE_SUB(NOW(), INTERVAL 2 MONTH) The above query will return records whose order…
    Tags: mysql, query, learn
  • 40
    MySQL stores accounts in the user table of the mysql system database. An account is defined in terms of a user name and the client host or hosts from which the user can connect to the server. The account may also have a password. There are several distinctions between the…
    Tags: mysql, user, password, server
  • 40
    The core logic of a database application is performed through SQL statements, whether issued directly through an interpreter or submitted behind the scenes through an API. The tuning guidelines of this post will help to speed up all kinds of MySQL applications. The guidelines cover SQL operations that read and…
    Tags: query, queries, mysql, set

Satya Prakash

VOIP Expert: More than 8 years of experience in Asterisk Development and Call Center operation Management. Unique Combination of Skill Set as IT, Analytics and operation management.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: