1MK-KILL(1) User Contributed Perl Documentation MK-KILL(1)
2
6 mk-kill - Kill MySQL queries that match certain criteria.
7
9 Usage: mk-kill [OPTION]... [FILE...]
10 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 Kill queries running longer than 60s:
17 mk-kill --busy-time 60 --kill
19 Print, do not kill, queries running longer than 60s:
21 mk-kill --busy-time 60 --print
23 Check for sleeping processes and kill them all every 10s:
25 mk-kill --match-command Sleep --kill --victims all --interval 10
27 Print all login processes:
29 mk-kill --match-state login --print --victims all
31 See which queries in the processlist right now would match:
33 mysql -e "SHOW PROCESSLIST" | mk-kill --busy-time 60 --print
35
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 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 At the time of this release, we know of no bugs that could cause
48 serious harm to users.
49 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 See also "BUGS" for more information on filing bugs and getting help.
56
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 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 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 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 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 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 mk-kill is a work in progress, and there is much more it could do.
93
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 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 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 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 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
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 # 2009-07-15T15:04:01 KILL 8 (Query 42 sec) SELECT * FROM huge_table
129 The line shows a timestamp, the query's Id (8), its Time (42 sec) and
131 its Info (usually the query SQL).
132 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 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
141 Specify at least one of "--kill", "--kill-query", "--print",
142 "--execute-command" or "--stop".
143 "--any-busy-time" and "--each-busy-time" are mutually exclusive.
145 "--kill" and "--kill-query" are mutually exclusive.
147 This tool accepts additional command-line arguments. Refer to the
149 "SYNOPSIS" and usage information for details.
150 --ask-pass
152 Prompt for a password when connecting to MySQL.
153 --charset
155 short form: -A; type: string
156 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 --config
164 type: Array
165 Read this comma-separated list of config files; if specified, this
167 must be the first option on the command line.
168 --daemonize
170 Fork to the background and detach from the shell. POSIX operating
171 systems only.
172 --defaults-file
174 short form: -F; type: string
175 Only read mysql options from the given file. You must give an
177 absolute pathname.
178 --group-by
180 type: string
181 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 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 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 --help
204 Show help and exit.
205 --host
207 short form: -h; type: string; default: localhost
208 Connect to host.
210 --interval
212 type: time
213 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 See also "--run-time".
221 --log
223 type: string
224 Print all output to this file when daemonized.
226 --password
228 short form: -p; type: string
229 Password to use when connecting.
231 --pid
233 type: string
234 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 --port
242 short form: -P; type: int
243 Port number to use for connection.
245 --[no]strip-comments
247 default: yes
248 Remove SQL comments from queries in the Info column of the
250 PROCESSLIST.
251 --run-time
253 type: time
254 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 --sentinel
262 type: string; default: /tmp/mk-kill-sentinel
263 Exit if this file exists.
265 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 --set-vars
271 type: string; default: wait_timeout=10000
272 Set these MySQL variables. Immediately after connecting to MySQL,
274 this string will be appended to SET and executed.
275 --socket
277 short form: -S; type: string
278 Socket file to use for connection.
280 --stop
282 Stop running instances by creating the "--sentinel" file.
283 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 --user
289 short form: -u; type: string
290 User for login if not current user.
292 --version
294 Show version and exit.
295 --victims
297 type: string; default: oldest
298 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 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 all Kill all queries in the class.
311 all-but-oldest
313 Kill all but the oldest query. This is the inverse of the
314 "oldest" value.
315 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 --wait-after-kill
323 type: time
324 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 --wait-before-kill
331 type: time
332 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 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 See also "GROUP, MATCH AND KILL".
346 --match-all
348 group: Query Matches
349 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 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 --busy-time
366 type: time; group: Query Matches
367 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 --idle-time
373 type: time; group: Query Matches
374 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 --ignore-command
380 type: string; group: Query Matches
381 Ignore queries whose Command matches this Perl regex.
383 See "--match-command".
385 --ignore-db
387 type: string; group: Query Matches
388 Ignore queries whose db (database) matches this Perl regex.
390 See "--match-db".
392 --ignore-host
394 type: string; group: Query Matches
395 Ignore queries whose Host matches this Perl regex.
397 See "--match-host".
399 --ignore-info
401 type: string; group: Query Matches
402 Ignore queries whose Info (query) matches this Perl regex.
404 See "--match-info".
406 --[no]ignore-self
408 default: yes; group: Query Matches
409 Don't kill mk-kill's own connection.
411 --ignore-state
413 type: string; group: Query Matches; default: Locked
414 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 See "--match-state".
420 --ignore-user
422 type: string; group: Query Matches
423 Ignore queries whose user matches this Perl regex.
425 See "--match-user".
427 --match-command
429 type: string; group: Query Matches
430 Match only queries whose Command matches this Perl regex.
432 Common Command values are:
434 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 See <http://dev.mysql.com/doc/refman/5.1/en/thread-commands.html>
451 for a full list and description of Command values.
452 --match-db
454 type: string; group: Query Matches
455 Match only queries whose db (database) matches this Perl regex.
457 --match-host
459 type: string; group: Query Matches
460 Match only queries whose Host matches this Perl regex.
462 The Host value often time includes the port like "host:port".
464 --match-info
466 type: string; group: Query Matches
467 Match only queries whose Info (query) matches this Perl regex.
469 The Info column of the processlist shows the query that is being
471 executed or NULL if no query is being executed.
472 --match-state
474 type: string; group: Query Matches
475 Match only queries whose State matches this Perl regex.
477 Common State values are:
479 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 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 --match-user
499 type: string; group: Query Matches
500 Match only queries whose User matches this Perl regex.
502 --replication-threads
504 group: Query Matches
505 Allow matching and killing replication threads.
507 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 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 See also "GROUP, MATCH AND KILL".
519 --any-busy-time
521 type: time; group: Class Matches
522 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 See "--each-busy-time" for more details.
529 --each-busy-time
531 type: time; group: Class Matches
532 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 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 --query-count
542 type: int; group: Class Matches
543 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 --verbose
552 short form: -v
553 Print information to STDOUT about what is being done.
555 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 See also "GROUP, MATCH AND KILL".
565 --execute-command
567 type: string; group: Actions
568 Execute this command when a query matches.
570 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 See also "--wait-before-kill".
578 --kill
580 group: Actions
581 Kill the connection for matching queries.
583 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 Unless "--print" is also given, no other information is printed
589 that shows that mk-kill matched and killed a query.
590 See also "--wait-before-kill" and "--wait-after-kill".
592 --kill-query
594 group: Actions
595 Kill matching queries.
597 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 --print
604 group: Actions
605 Print a KILL statement for matching queries; does not actually kill
607 queries.
608 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
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 · A
621 dsn: charset; copy: yes
623 Default character set.
625 · D
627 dsn: database; copy: yes
629 Default database.
631 · F
633 dsn: mysql_read_default_file; copy: yes
635 Only read default options from the given file
637 · h
639 dsn: host; copy: yes
641 Connect to host.
643 · p
645 dsn: password; copy: yes
647 Password to use when connecting.
649 · P
651 dsn: port; copy: yes
653 Port number to use for connection.
655 · S
657 dsn: mysql_socket; copy: yes
659 Socket file to use for connection.
661 · u
663 dsn: user; copy: yes
665 User for login if not current user.
667
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 wget http://www.maatkit.org/get/toolname
674 or
675 wget http://www.maatkit.org/trunk/toolname
676 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
683 The environment variable "MKDEBUG" enables verbose debugging output in
684 all of the Maatkit tools:
685 MKDEBUG=1 mk-....
687
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
693 For a list of known bugs see <http://www.maatkit.org/bugs/mk-kill>.
694 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 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
706 This program is copyright 2009-2011 Baron Schwartz. Feedback and
707 improvements are welcome.
708 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 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 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
724 Baron Schwartz, Daniel Nichter
725
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
734 This manual page documents Ver 0.9.10 Distrib 7540 $Revision: 7531 $.
735perl v5.28.1 2011-06-08 MK-KILL(1)