Kill slow mysql queries

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
  • 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
  • 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
  • 39
    Clear Memory Cache on Linux centos RESET QUERY CACHE will clear out the query cache, but needs RELOAD privileges. RESET QUERY CACHE; or FLUSH QUERY CACHE does NOT clear out the query cache, it simply defrags it leaving the cached query results in place. FLUSH QUERY CACHE;      
    Tags: query, mysql, server, linux

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: