1MK-KILL(1)            User Contributed Perl Documentation           MK-KILL(1)
2
3
4

NAME

6       mk-kill - Kill MySQL queries that match certain criteria.
7

SYNOPSIS

9       Usage: mk-kill [OPTION]... [FILE...]
10
11       mk-kill kills MySQL connections.  mk-kill connects to MySQL and gets
12       queries from SHOW PROCESSLIST if no FILE is given.  Else, it reads
13       queries from one or more FILE which contains the output of SHOW
14       PROCESSLIST.  If FILE is -, mk-kill reads from STDIN.
15
16       Kill queries running longer than 60s:
17
18         mk-kill --busy-time 60 --kill
19
20       Print, do not kill, queries running longer than 60s:
21
22         mk-kill --busy-time 60 --print
23
24       Check for sleeping processes and kill them all every 10s:
25
26         mk-kill --match-command Sleep --kill --victims all --interval 10
27
28       Print all login processes:
29
30         mk-kill --match-state login --print --victims all
31
32       See which queries in the processlist right now would match:
33
34          mysql -e "SHOW PROCESSLIST" | mk-kill --busy-time 60 --print
35

RISKS

37       The following section is included to inform users about the potential
38       risks, whether known or unknown, of using this tool.  The two main
39       categories of risks are those created by the nature of the tool (e.g.
40       read-only tools vs. read-write tools) and those created by bugs.
41
42       mk-kill is designed to kill queries if you use the "--kill" option is
43       given, and that might disrupt your database's users, of course.  You
44       should test with the <"--print"> option, which is safe, if you're
45       unsure what the tool will do.
46
47       At the time of this release, we know of no bugs that could cause
48       serious harm to users.
49
50       The authoritative source for updated information is always the online
51       issue tracking system.  Issues that affect this tool will be marked as
52       such.  You can see a list of such issues at the following URL:
53       <http://www.maatkit.org/bugs/mk-kill>.
54
55       See also "BUGS" for more information on filing bugs and getting help.
56

DESCRIPTION

58       mk-kill captures queries from SHOW PROCESSLIST, filters them, and then
59       either kills or prints them.  This is also known as a "slow query
60       sniper" in some circles.  The idea is to watch for queries that might
61       be consuming too many resources, and kill them.
62
63       For brevity, we talk about killing queries, but they may just be
64       printed (or some other future action) depending on what options are
65       given.
66
67       Normally mk-kill connects to MySQL to get queries from SHOW
68       PROCESSLIST.  Alternatively, it can read SHOW PROCESSLIST output from
69       files.  In this case, mk-kill does not connect to MySQL and "--kill"
70       has no effect.  You should use "--print" instead when reading files.
71       The ability to read a file (or - for STDIN) allows you to capture SHOW
72       PROCESSLIST and test it later with mk-kill to make sure that your
73       matches kill the proper queries.  There are a lot of special rules to
74       follow, such as "don't kill replication threads," so be careful to not
75       kill something important!
76
77       Two important options to know are "--busy-time" and "--victims".
78       First, whereas most match/filter options match their corresponding
79       value from SHOW PROCESSLIST (e.g. "--match-command" matches a query's
80       Command value), the Time value is matched by "--busy-time".  See also
81       "--interval".
82
83       Second, "--victims" controls which matching queries from each class are
84       killed.  By default, the matching query with the highest Time value is
85       killed (the oldest query).  See the next section, "GROUP, MATCH AND
86       KILL", for more details.
87
88       Usually you need to specify at least one "--match" option, else no
89       queries will match.  Or, you can specify "--match-all" to match all
90       queries that aren't ignored by an "--ignore" option.
91
92       mk-kill is a work in progress, and there is much more it could do.
93

GROUP, MATCH AND KILL

95       Queries pass through several steps to determine which exactly will be
96       killed (or printed--whatever action is specified).  Understanding these
97       steps will help you match precisely the queries you want.
98
99       The first step is grouping queries into classes.  The "--group-by"
100       option controls grouping.  By default, this option has no value so all
101       queries are grouped into one, big default class.  All types of matching
102       and filtering (the next step) are applied per-class.  Therefore, you
103       may need to group queries in order to match/filter some classes but not
104       others.
105
106       The second step is matching.  Matching implies filtering since if a
107       query doesn't match some criteria, it is removed from its class.
108       Matching happens for each class.  First, queries are filtered from
109       their class by the various "Query Matches" options like "--match-user".
110       Then, entire classes are filtered by the various "Class Matches"
111       options like "--query-count".
112
113       The third step is victim selection, that is, which matching queries in
114       each class to kill.  This is controlled by the "--victims" option.
115       Although many queries in a class may match, you may only want to kill
116       the oldest query, or all queries, etc.
117
118       The forth and final step is to take some action on all matching queries
119       from all classes.  The "Actions" options specify which actions will be
120       taken.  At this step, there are no more classes, just a single list of
121       queries to kill, print, etc.
122

OUTPUT

124       If only "--kill" then there is no output.  If only "--print" then a
125       timestamped KILL statement if printed for every query that would have
126       been killed, like:
127
128         # 2009-07-15T15:04:01 KILL 8 (Query 42 sec) SELECT * FROM huge_table
129
130       The line shows a timestamp, the query's Id (8), its Time (42 sec) and
131       its Info (usually the query SQL).
132
133       If both "--kill" and "--print" are given, then matching queries are
134       killed and a line for each like the one above is printed.
135
136       Any command executed by "--execute-command" is responsible for its own
137       output and logging.  After being executed, mk-kill has no control or
138       interaction with the command.
139

OPTIONS

141       Specify at least one of "--kill", "--kill-query", "--print",
142       "--execute-command" or "--stop".
143
144       "--any-busy-time" and "--each-busy-time" are mutually exclusive.
145
146       "--kill" and "--kill-query" are mutually exclusive.
147
148       This tool accepts additional command-line arguments.  Refer to the
149       "SYNOPSIS" and usage information for details.
150
151       --ask-pass
152           Prompt for a password when connecting to MySQL.
153
154       --charset
155           short form: -A; type: string
156
157           Default character set.  If the value is utf8, sets Perl's binmode
158           on STDOUT to utf8, passes the mysql_enable_utf8 option to
159           DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL.  Any
160           other value sets binmode on STDOUT without the utf8 layer, and runs
161           SET NAMES after connecting to MySQL.
162
163       --config
164           type: Array
165
166           Read this comma-separated list of config files; if specified, this
167           must be the first option on the command line.
168
169       --daemonize
170           Fork to the background and detach from the shell.  POSIX operating
171           systems only.
172
173       --defaults-file
174           short form: -F; type: string
175
176           Only read mysql options from the given file.  You must give an
177           absolute pathname.
178
179       --group-by
180           type: string
181
182           Apply matches to each class of queries grouped by this SHOW
183           PROCESSLIST column.  In addition to the basic columns of SHOW
184           PROCESSLIST (user, host, command, state, etc.), queries can be
185           matched by "fingerprint" which abstracts the SQL query in the
186           "Info" column.
187
188           By default, queries are not grouped, so matches and actions apply
189           to all queries.  Grouping allows matches and actions to apply to
190           classes of similar queries, if any queries in the class match.
191
192           For example, detecting cache stampedes (see "all-but-oldest" under
193           "--victims" for an explanation of that term) requires that queries
194           are grouped by the "arg" attribute.  This creates classes of
195           identical queries (stripped of comments).  So queries "SELECT c
196           FROM t WHERE id=1" and "SELECT c FROM t WHERE id=1" are grouped
197           into the same class, but query c<"SELECT c FROM t WHERE id=3"> is
198           not identical to the first two queries so it is grouped into
199           another class. Then when "--victims" "all-but-oldest" is specified,
200           all but the oldest query in each class is killed for each class of
201           queries that matches the match criteria.
202
203       --help
204           Show help and exit.
205
206       --host
207           short form: -h; type: string; default: localhost
208
209           Connect to host.
210
211       --interval
212           type: time
213
214           How often to check for queries to kill.  If "--busy-time" is not
215           given, then the default interval is 30 seconds.  Else the default
216           is half as often as "--busy-time".  If both "--interval" and
217           "--busy-time" are given, then the explicit "--interval" value is
218           used.
219
220           See also "--run-time".
221
222       --log
223           type: string
224
225           Print all output to this file when daemonized.
226
227       --password
228           short form: -p; type: string
229
230           Password to use when connecting.
231
232       --pid
233           type: string
234
235           Create the given PID file when daemonized.  The file contains the
236           process ID of the daemonized instance.  The PID file is removed
237           when the daemonized instance exits.  The program checks for the
238           existence of the PID file when starting; if it exists and the
239           process with the matching PID exists, the program exits.
240
241       --port
242           short form: -P; type: int
243
244           Port number to use for connection.
245
246       --[no]strip-comments
247           default: yes
248
249           Remove SQL comments from queries in the Info column of the
250           PROCESSLIST.
251
252       --run-time
253           type: time
254
255           How long to run before exiting.  By default mk-kill runs forever,
256           or until its process is killed or stopped by the creation of a
257           "--sentinel" file.  If this option is specified, mk-kill runs for
258           the specified amount of time and sleeps "--interval" seconds
259           between each check of the PROCESSLIST.
260
261       --sentinel
262           type: string; default: /tmp/mk-kill-sentinel
263
264           Exit if this file exists.
265
266           The presence of the file specified by "--sentinel" will cause all
267           running instances of mk-kill to exit.  You might find this handy to
268           stop cron jobs gracefully if necessary.  See also "--stop".
269
270       --set-vars
271           type: string; default: wait_timeout=10000
272
273           Set these MySQL variables.  Immediately after connecting to MySQL,
274           this string will be appended to SET and executed.
275
276       --socket
277           short form: -S; type: string
278
279           Socket file to use for connection.
280
281       --stop
282           Stop running instances by creating the "--sentinel" file.
283
284           Causes mk-kill to create the sentinel file specified by
285           "--sentinel" and exit.  This should have the effect of stopping all
286           running instances which are watching the same sentinel file.
287
288       --user
289           short form: -u; type: string
290
291           User for login if not current user.
292
293       --version
294           Show version and exit.
295
296       --victims
297           type: string; default: oldest
298
299           Which of the matching queries in each class will be killed.  After
300           classes have been matched/filtered, this option specifies which of
301           the matching queries in each class will be killed (or printed,
302           etc.).  The following values are possible:
303
304           oldest
305               Only kill the single oldest query.  This is to prevent killing
306               queries that aren't really long-running, they're just long-
307               waiting.  This sorts matching queries by Time and kills the one
308               with the highest Time value.
309
310           all Kill all queries in the class.
311
312           all-but-oldest
313               Kill all but the oldest query.  This is the inverse of the
314               "oldest" value.
315
316               This value can be used to prevent "cache stampedes", the
317               condition where several identical queries are executed and
318               create a backlog while the first query attempts to finish.
319               Since all queries are identical, all but the first query are
320               killed so that it can complete and populate the cache.
321
322       --wait-after-kill
323           type: time
324
325           Wait after killing a query, before looking for more to kill.  The
326           purpose of this is to give blocked queries a chance to execute, so
327           we don't kill a query that's blocking a bunch of others, and then
328           kill the others immediately afterwards.
329
330       --wait-before-kill
331           type: time
332
333           Wait before killing a query.  The purpose of this is to give
334           "--execute-command" a chance to see the matching query and gather
335           other MySQL or system information before it's killed.
336
337   QUERY MATCHES
338       These options filter queries from their classes.  If a query does not
339       match, it is removed from its class.  The "--ignore" options take
340       precedence.  The matches for command, db, host, etc. correspond to the
341       columns returned by SHOW PROCESSLIST: Command, db, Host, etc.  All
342       pattern matches are case-sensitive by default, but they can be made
343       case-insensitive by specifying a regex pattern like "(?i-xsm:select)".
344
345       See also "GROUP, MATCH AND KILL".
346
347       --match-all
348           group: Query Matches
349
350           Match all queries that are not ignored.  If no ignore options are
351           specified, then every query matches (except replication threads,
352           unless "--replication-threads" is also specified).  This option
353           allows you to specify negative matches, i.e. "match every query
354           except..." where the exceptions are defined by specifying various
355           "--ignore" options.
356
357           This option is not the same as "--victims" "all".  This option
358           matches all queries within a class, whereas "--victims" "all"
359           specifies that all matching queries in a class (however they
360           matched) will be killed.  Normally, however, the two are used
361           together because if, for example, you specify "--victims" "oldest",
362           then although all queries may match, only the oldest will be
363           killed.
364
365       --busy-time
366           type: time; group: Query Matches
367
368           Match queries that have been running for longer than this time.
369           The queries must be in Command=Query status.  This matches a
370           query's Time value as reported by SHOW PROCESSLIST.
371
372       --idle-time
373           type: time; group: Query Matches
374
375           Match queries that have been idle/sleeping for longer than this
376           time.  The queries must be in Command=Sleep status.  This matches a
377           query's Time value as reported by SHOW PROCESSLIST.
378
379       --ignore-command
380           type: string; group: Query Matches
381
382           Ignore queries whose Command matches this Perl regex.
383
384           See "--match-command".
385
386       --ignore-db
387           type: string; group: Query Matches
388
389           Ignore queries whose db (database) matches this Perl regex.
390
391           See "--match-db".
392
393       --ignore-host
394           type: string; group: Query Matches
395
396           Ignore queries whose Host matches this Perl regex.
397
398           See "--match-host".
399
400       --ignore-info
401           type: string; group: Query Matches
402
403           Ignore queries whose Info (query) matches this Perl regex.
404
405           See "--match-info".
406
407       --[no]ignore-self
408           default: yes; group: Query Matches
409
410           Don't kill mk-kill's own connection.
411
412       --ignore-state
413           type: string; group: Query Matches; default: Locked
414
415           Ignore queries whose State matches this Perl regex.  The default is
416           to keep threads from being killed if they are locked waiting for
417           another thread.
418
419           See "--match-state".
420
421       --ignore-user
422           type: string; group: Query Matches
423
424           Ignore queries whose user matches this Perl regex.
425
426           See "--match-user".
427
428       --match-command
429           type: string; group: Query Matches
430
431           Match only queries whose Command matches this Perl regex.
432
433           Common Command values are:
434
435             Query
436             Sleep
437             Binlog Dump
438             Connect
439             Delayed insert
440             Execute
441             Fetch
442             Init DB
443             Kill
444             Prepare
445             Processlist
446             Quit
447             Reset stmt
448             Table Dump
449
450           See <http://dev.mysql.com/doc/refman/5.1/en/thread-commands.html>
451           for a full list and description of Command values.
452
453       --match-db
454           type: string; group: Query Matches
455
456           Match only queries whose db (database) matches this Perl regex.
457
458       --match-host
459           type: string; group: Query Matches
460
461           Match only queries whose Host matches this Perl regex.
462
463           The Host value often time includes the port like "host:port".
464
465       --match-info
466           type: string; group: Query Matches
467
468           Match only queries whose Info (query) matches this Perl regex.
469
470           The Info column of the processlist shows the query that is being
471           executed or NULL if no query is being executed.
472
473       --match-state
474           type: string; group: Query Matches
475
476           Match only queries whose State matches this Perl regex.
477
478           Common State values are:
479
480             Locked
481             login
482             copy to tmp table
483             Copying to tmp table
484             Copying to tmp table on disk
485             Creating tmp table
486             executing
487             Reading from net
488             Sending data
489             Sorting for order
490             Sorting result
491             Table lock
492             Updating
493
494           See
495           <http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html>
496           for a full list and description of State values.
497
498       --match-user
499           type: string; group: Query Matches
500
501           Match only queries whose User matches this Perl regex.
502
503       --replication-threads
504           group: Query Matches
505
506           Allow matching and killing replication threads.
507
508           By default, matches do not apply to replication threads; i.e.
509           replication threads are completely ignored.  Specifying this option
510           allows matches to match (and potentially kill) replication threads
511           on masters and slaves.
512
513   CLASS MATCHES
514       These matches apply to entire query classes.  Classes are created by
515       specifying the "--group-by" option, else all queries are members of a
516       single, default class.
517
518       See also "GROUP, MATCH AND KILL".
519
520       --any-busy-time
521           type: time; group: Class Matches
522
523           Match query class if any query has been running for longer than
524           this time.  "Longer than" means that if you specify 10, for
525           example, the class will only match if there's at least one query
526           that has been running for greater than 10 seconds.
527
528           See "--each-busy-time" for more details.
529
530       --each-busy-time
531           type: time; group: Class Matches
532
533           Match query class if each query has been running for longer than
534           this time.  "Longer than" means that if you specify 10, for
535           example, the class will only match if each and every query has been
536           running for greater than 10 seconds.
537
538           See also "--any-busy-time" (to match a class if ANY query has been
539           running longer than the specified time) and "--busy-time".
540
541       --query-count
542           type: int; group: Class Matches
543
544           Match query class if it has at least this many queries.  When
545           queries are grouped into classes by specifying "--group-by", this
546           option causes matches to apply only to classes with at least this
547           many queries.  If "--group-by" is not specified then this option
548           causes matches to apply only if there are at least this many
549           queries in the entire SHOW PROCESSLIST.
550
551       --verbose
552           short form: -v
553
554           Print information to STDOUT about what is being done.
555
556   ACTIONS
557       These actions are taken for every matching query from all classes.  The
558       actions are taken in this order: "--print", "--execute-command",
559       "--kill"/"--kill-query".  This order allows "--execute-command" to see
560       the output of "--print" and the query before "--kill"/"--kill-query".
561       This may be helpful because mk-kill does not pass any information to
562       "--execute-command".
563
564       See also "GROUP, MATCH AND KILL".
565
566       --execute-command
567           type: string; group: Actions
568
569           Execute this command when a query matches.
570
571           After the command is executed, mk-kill has no control over it, so
572           the command is responsible for its own info gathering, logging,
573           interval, etc.  The command is executed each time a query matches,
574           so be careful that the command behaves well when multiple instances
575           are ran.  No information from mk-kill is passed to the command.
576
577           See also "--wait-before-kill".
578
579       --kill
580           group: Actions
581
582           Kill the connection for matching queries.
583
584           This option makes mk-kill kill the connections (a.k.a. processes,
585           threads) that have matching queries.  Use "--kill-query" if you
586           only want to kill individual queries and not their connections.
587
588           Unless "--print" is also given, no other information is printed
589           that shows that mk-kill matched and killed a query.
590
591           See also "--wait-before-kill" and "--wait-after-kill".
592
593       --kill-query
594           group: Actions
595
596           Kill matching queries.
597
598           This option makes mk-kill kill matching queries.  This requires
599           MySQL 5.0 or newer.  Unlike "--kill" which kills the connection for
600           matching queries, this option only kills the query, not its
601           connection.
602
603       --print
604           group: Actions
605
606           Print a KILL statement for matching queries; does not actually kill
607           queries.
608
609           If you just want to see which queries match and would be killed
610           without actually killing them, specify "--print".  To both kill and
611           print matching queries, specify both "--kill" and "--print".
612

DSN OPTIONS

614       These DSN options are used to create a DSN.  Each option is given like
615       "option=value".  The options are case-sensitive, so P and p are not the
616       same option.  There cannot be whitespace before or after the "=" and if
617       the value contains whitespace it must be quoted.  DSN options are
618       comma-separated.  See the maatkit manpage for full details.
619
620       ·   A
621
622           dsn: charset; copy: yes
623
624           Default character set.
625
626       ·   D
627
628           dsn: database; copy: yes
629
630           Default database.
631
632       ·   F
633
634           dsn: mysql_read_default_file; copy: yes
635
636           Only read default options from the given file
637
638       ·   h
639
640           dsn: host; copy: yes
641
642           Connect to host.
643
644       ·   p
645
646           dsn: password; copy: yes
647
648           Password to use when connecting.
649
650       ·   P
651
652           dsn: port; copy: yes
653
654           Port number to use for connection.
655
656       ·   S
657
658           dsn: mysql_socket; copy: yes
659
660           Socket file to use for connection.
661
662       ·   u
663
664           dsn: user; copy: yes
665
666           User for login if not current user.
667

DOWNLOADING

669       You can download Maatkit from Google Code at
670       <http://code.google.com/p/maatkit/>, or you can get any of the tools
671       easily with a command like the following:
672
673          wget http://www.maatkit.org/get/toolname
674          or
675          wget http://www.maatkit.org/trunk/toolname
676
677       Where "toolname" can be replaced with the name (or fragment of a name)
678       of any of the Maatkit tools.  Once downloaded, they're ready to run; no
679       installation is needed.  The first URL gets the latest released version
680       of the tool, and the second gets the latest trunk code from Subversion.
681

ENVIRONMENT

683       The environment variable "MKDEBUG" enables verbose debugging output in
684       all of the Maatkit tools:
685
686          MKDEBUG=1 mk-....
687

SYSTEM REQUIREMENTS

689       You need Perl, DBI, DBD::mysql, and some core packages that ought to be
690       installed in any reasonably new version of Perl.
691

BUGS

693       For a list of known bugs see <http://www.maatkit.org/bugs/mk-kill>.
694
695       Please use Google Code Issues and Groups to report bugs or request
696       support: <http://code.google.com/p/maatkit/>.  You can also join
697       #maatkit on Freenode to discuss Maatkit.
698
699       Please include the complete command-line used to reproduce the problem
700       you are seeing, the version of all MySQL servers involved, the complete
701       output of the tool when run with "--version", and if possible,
702       debugging output produced by running with the "MKDEBUG=1" environment
703       variable.
704

COPYRIGHT, LICENSE AND WARRANTY

706       This program is copyright 2009-2011 Baron Schwartz.  Feedback and
707       improvements are welcome.
708
709       THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
710       WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
711       MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
712
713       This program is free software; you can redistribute it and/or modify it
714       under the terms of the GNU General Public License as published by the
715       Free Software Foundation, version 2; OR the Perl Artistic License.  On
716       UNIX and similar systems, you can issue `man perlgpl' or `man
717       perlartistic' to read these licenses.
718
719       You should have received a copy of the GNU General Public License along
720       with this program; if not, write to the Free Software Foundation, Inc.,
721       59 Temple Place, Suite 330, Boston, MA  02111-1307  USA.
722

AUTHOR

724       Baron Schwartz, Daniel Nichter
725

ABOUT MAATKIT

727       This tool is part of Maatkit, a toolkit for power users of MySQL.
728       Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
729       primary code contributors.  Both are employed by Percona.  Financial
730       support for Maatkit development is primarily provided by Percona and
731       its clients.
732

VERSION

734       This manual page documents Ver 0.9.10 Distrib 7540 $Revision: 7531 $.
735
736
737
738perl v5.28.1                      2011-06-08                        MK-KILL(1)
Impressum