mkill - Kills slow queries

    mkill [--host={mysql_host}] [--dbuser={mysql_user}] 
        [--password={mysqluser_pw}] 
        [--filter-user={regex}] [--filter-host={regex}] [--filter-db={regex}]
        [--filter-command={regex}] [--filter-state={regex}] [--filter-info={{regex}}]
        [--user={user}] [--slow={seconds}] 
        [--daemonize] [--test]

    mkill --help

    mkill --version

This is an alpha version of this program. Please let me know what you think and what additional features would be nice. Future version will most likely perform the same but have different output. One idea i've been thinking about is letting you specify filters which short perl snippets instead of just regexes.

This program kills long running queries based on several criteria including query time, host, user, database, state, and query content.

The following keys are active while mkill is running:

    q - quit

A log of killed queries is sent to STDERR, watched queries are sent to STDOUT. A typical command line would be:

    mkill -sl 180 -fi 'select.*from bad_table' > /var/log/mkill.out 2> /var/log/mkill.kill

All options can be abbreviated by their shortest unique abbreviation.

-?, --help

Show the help screen and exit.

-t, --test

Show what would be done but don't actually kill anything. Good for testing your filter expressions.

-v, --version

Show the version number and exit.

-h {mysql_host}, --host={mysql_host}

By default, the mysqld on localhost is monitored. Specify an alternate host with this option.

-dbu {mysql_user}, --dbuser={mysql_user}

By default, the user 'mysqltop' is used to connect to the database. Specify an alternate user with this option.

-p {mysqluser_pw}, --password={mysqluser_pw}

By default, there is no password associated with the mysqltop user, specify a password with this option.

-sl {seconds}, --slow={seconds}

The number of seconds before a slow query is killed. The default is 180 seconds.

-u {user}, --user={user}

Kill only threads owned by this user.

-fu {regex_pattern}, --filter-user={regex_pattern}

-fh {regex_pattern}, --filter-host={regex_pattern}

-fd {regex_pattern}, --filter-db={regex_pattern}

-fs {regex_pattern}, --filter-state={regex_pattern}

-fc {regex_pattern}, --filter-command={regex_pattern}

-fi {regex_pattern}, --filter-info={regex_pattern}

Filter the queries based on the regex_pattern provided. The regex_pattern is a perl regular expression. The regular expression match is done with case insensitivity. For example, to only kill select statements on the user table, use the following:

    --filter-info='select from user'

or, to be more forgiving for mutil-table joins and extra spaces, use:

    --filter-info='select\s+from\s+.*\buser\b.*where'

Be careful to escape any special shell characters in the regex.

All options can be stored in initialization files. Command line options override options stored in the initialization file(s). The following files are checked for arguments: current direcotry .mkillc, home directory .mkillc, /usr/local/etc/mkillc, /etc/mkillc. Options in the former files override options in the later files.

The format of the initialization file is one option per line. Options are specified just as they would be on the command line. They can be abbreviated and use the one or two hyphen syntax. Comments and blank lines are ignored. The following is an exmple .mkillc file which kills any 'select' statements from 'user1' which last more than 120 seconds.

    #  Only kill 'user1' 'select' queries > 120 seconds
    -fu user1
    -filter-info='select'
    --slow=1   # refresh every one seconds

The most convenient way to setup your system to use mkill is to create a database user called mysqlkill which has no password. For security purposes, this user should have all privileges set to N except Process_priv which must be set to Y.

To grant these privileges, execute the following from the MySQL command prompt

For mysql 4.0.2 and greater:

    mysql> grant super,process on *.* to mysqlkill;
    mysql> grant super,process on *.* to mysqlkill@localhost;
    mysql> flush privileges;

For mysql 3.x and 4.0.1:

    mysql> grant process on *.* to mysqlkill;
    mysql> grant process on *.* to mysqlkill@localhost;
    mysql> flush privileges;

Notes:

•

GRANT only works in MySQL 3.22.11 or later, for earlier versions add the user manually and fix the permissions as noted above.

•

The GRANT to mysqltop and mysqltop@localhost may be modified depending upon which hosts you want to grant access from. In general, you probably want to limit it to the hosts in your domain.

Initially, mkill does not connect to a specific database. Most commands this program issues are non-database specific (SHOW FULL PROCESSLIST, SHOW VARIABLES, KILL id). However, when database-specific commands are needed, mkill will try to connect to the the required database and prompt for a username/password if the default one fails.

To install mkill, run the following shell commands:

    perl Makefile.PL
    make
    make install

The default {install_prefix} is /usr/local which means that mkill is installed in /usr/local/bin/. To change this, run:

    perl Makefile.PL --prefix={install_prefix}

or modify the PREFIX line in Makefile.PL.

Requires the following perl modules:

    Module        Available At
    ------------  --------------------------------------------------------
    DBI           Distributed as Bundle::DBI: http://www.cpan.org/authors/id/TIMB
    DBD::mysql    http://www.cpan.org/authors/id/JWIED
    Getopt::Long  (Distributed with Perl 5)
    Net::Domain   Part of libnet: http://www.cpan.org/authors/id/GBARR/

Marc Prewitt, Chelsea Networks <mprewitt@chelsea.net>

Copyright (C) 2003 Marc Prewitt/Chelsea Networks, under the GNU GPL. mkill comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to redistribute it under certain conditions; see the COPYING file for details.

    $Id: mkill.PL,v 1.6 2004/09/12 21:19:12 mdprewitt Exp $

The get char routines used to quit the program currently require the user to hit the RETURN key on the keyboard after entry. This will soon be fixed.