GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages
MKILL(1) User Contributed Perl Documentation MKILL(1)

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.

Hey! The above document had some coding errors, which are explained below:
Around line 789:
'=end' without a target? (Should be "=end showsatus")
2022-05-14 perl v5.32.1

Search for    or go to Top of page |  Section 1 |  Main Index

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with ManDoc.