1MK-KILL(1) User Contributed Perl Documentation MK-KILL(1)
2
6 mk-kill - Kill MySQL queries that match certain criteria.
7
9 Kill queries running longer than 60s:
10 mk-kill --busy-time 60 --kill
12 Print, do not kill, queries running longer than 60s:
14 mk-kill --busy-time 60 --print
16 Check for sleeping processes and kill them all every 10s:
18 mk-kill --match-command Sleep --kill --no-only-oldest --interval 10
20 Print all login processes:
22 mk-kill --match-state login --print --no-only-oldest
24
26 The following section is included to inform users about the potential
27 risks, whether known or unknown, of using this tool. The two main
28 categories of risks are those created by the nature of the tool (e.g.
29 read-only tools vs. read-write tools) and those created by bugs.
30 mk-kill is designed to kill queries if you use the "--kill" option is
32 given, and that might disrupt your database's users, of course. You
33 should test with the <"--print"> option, which is safe, if you're
34 unsure what the tool will do.
35 At the time of this release, we know of no bugs that could cause
37 serious harm to users.
38 The authoritative source for updated information is always the online
40 issue tracking system. Issues that affect this tool will be marked as
41 such. You can see a list of such issues at the following URL:
42 http://www.maatkit.org/bugs/mk-kill <http://www.maatkit.org/bugs/mk-
43 kill>.
44 See also "BUGS" for more information on filing bugs and getting help.
46
48 mk-kill captures queries from SHOW PROCESSLIST, filters them, and then
49 either kills or prints them. This is also known as a "slow query
50 sniper" in some circles. The idea is to watch for queries that might
51 be consuming too many resources, and kill them.
52 For brevity, we talk about killing queries, but they may just be
54 printed (or some other future action) depending on what options are
55 given.
56 Normally mk-kill connects to MySQL to get queries from SHOW
58 PROCESSLIST. Alternatively, it can read SHOW PROCESSLIST output from
59 files. In this case, mk-kill does not connect to MySQL and "--kill"
60 has no effect. You should use "--print" instead when reading files.
61 The ability to read a file (or - for STDIN) allows you to capture SHOW
62 PROCESSLIST and test it later with mk-kill to make sure that your
63 matches kill the proper queries. There are a lot of special rules to
64 follow, such as "don't kill replication threads," so be careful to not
65 kill something important!
66 Two important options to know are "--busy-time" and
68 "--[no]only-oldest". First, whereas most match/filter options match
69 their corresponding value from SHOW PROCESSLIST (e.g. "--match-command"
70 matches a query's Command value), the Time value is matched by
71 "--busy-time". See also "--interval".
72 Secondly, "--[no]only-oldest" affects which matching query is killed.
74 By default, the matching query with the highest Time value is killed.
75 So if you want to match and kill ALL queries for a certain criteria
76 regardless of which is the "oldest", you must specify
77 "--no-only-oldest".
78 mk-kill is a work in progress, and there is much more it could do.
80
82 If only "--kill" then there is no output. If only "--print" then a
83 timestamped KILL statement if printed for every query that would have
84 been killed, like:
85 # 2009-07-15T15:04:01 KILL 8 (Query 42 sec) SELECT * FROM huge_table
87 The line shows a timestamp, the query's Id (8), its Time (42 sec) and
89 its Info (usually the query SQL).
90 If both "--kill" and "--print" are given, then matching queries are
92 killed and a line for each like the one above is printed.
93 Any command executed by "--execute-command" is responsible for its own
95 output and logging. After being executed, mk-kill has no control or
96 interaction with the command.
97
99 Specify at least one of "--kill", "--kill-query", "--print" or
100 "--execute-command".
101 --ask-pass
103 Prompt for a password when connecting to MySQL.
104 --charset
106 short form: -A; type: string
107 Default character set. If the value is utf8, sets Perl's binmode
109 on STDOUT to utf8, passes the mysql_enable_utf8 option to
110 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
111 other value sets binmode on STDOUT without the utf8 layer, and runs
112 SET NAMES after connecting to MySQL.
113 --config
115 type: Array
116 Read this comma-separated list of config files; if specified, this
118 must be the first option on the command line.
119 --daemonize
121 Fork to the background and detach from the shell. POSIX operating
122 systems only.
123 --defaults-file
125 short form: -F; type: string
126 Only read mysql options from the given file. You must give an
128 absolute pathname.
129 --heartbeat
131 Print information to STDOUT about what is being done.
132 --help
134 Show help and exit.
135 --host
137 short form: -h; type: string
138 Connect to host.
140 --interval
142 type: time
143 How often to check for queries to kill. If "--busy-time" is not
145 given, then the default interval is 30 seconds. Else the default
146 is half as often as "--busy-time". If both "--interval" and
147 "--busy-time" are given, then the explicit "--interval" value is
148 used.
149 --iterations
151 type: int; default: 1
152 How many times to iterate through the find-and-kill cycle. If 0,
154 iterate to infinity. See also "--run-time".
155 --log
157 type: string
158 Print all output to this file when daemonized.
160 --password
162 short form: -p; type: string
163 Password to use when connecting.
165 --pid
167 type: string
168 Create the given PID file when daemonized. The file contains the
170 process ID of the daemonized instance. The PID file is removed
171 when the daemonized instance exits. The program checks for the
172 existence of the PID file when starting; if it exists and the
173 process with the matching PID exists, the program exits.
174 --port
176 short form: -P; type: int
177 Port number to use for connection.
179 --run-time
181 type: time
182 How long to run before exiting.
184 --set-vars
186 type: string; default: wait_timeout=10000
187 Set these MySQL variables. Immediately after connecting to MySQL,
189 this string will be appended to SET and executed.
190 --socket
192 short form: -S; type: string
193 Socket file to use for connection.
195 --user
197 short form: -u; type: string
198 User for login if not current user.
200 --version
202 Show version and exit.
203 --wait-after-kill
205 type: time
206 Wait after killing a query, before looking for more to kill. The
208 purpose of this is to give blocked queries a chance to execute, so
209 we don't kill a query that's blocking a bunch of others, and then
210 kill the others immediately afterwards.
211 --wait-before-kill
213 type: time
214 Wait before killing a query. The purpose of this is to give
216 "--execute-command" a chance to see the matching query and gather
217 other MySQL or system information before it's killed.
218 MATCHES
220 These options specify what criteria a query must match for "mk-kill" to
221 either "--kill", "--print" or "--execute-command" for it. The ignore
222 commands take precedence. The matches for command, db, host, etc.
223 correspond to the columns returned by SHOW PROCESSLIST: Command, db,
224 Host, etc. All pattern matches are case-senstive.
225 --all
227 group: Matches
228 Kill all connections that are not ignored.
230 If no ignore options are specified, then every connection is killed
232 (except replication threads, unless "--replication-threads" is also
233 specified). If some ignore options are specified, then every
234 connection except ignored connections are killed.
235 Implies "--no-only-oldest".
237 --busy-time
239 type: time; group: Matches
240 Kill connections that have been running for longer than this time.
242 The queries must be in Command=Query status. This matches a
243 query's Time value as reported by SHOW PROCESSLIST.
244 --idle-time
246 type: time; group: Matches
247 Kill connections that have been idle/sleeping for longer than this
249 time. The queries must be in Command=Sleep status. This matches a
250 query's Time value as reported by SHOW PROCESSLIST.
251 --ignore-command
253 type: string; group: Matches
254 Ignore queries whose Command matches this Perl regex.
256 See "--match-command".
258 --ignore-db
260 type: string; group: Matches
261 Ignore queries whose db (database) matches this Perl regex.
263 See "--match-db".
265 --ignore-host
267 type: string; group: Matches
268 Ignore queries whose Host matches this Perl regex.
270 See "--match-host".
272 --ignore-info
274 type: string; group: Matches
275 Ignore queries whose Info (query) matches this Perl regex.
277 See "--match-info".
279 --[no]ignore-self
281 default: yes; group: Matches
282 Don't kill mk-kill's own connection.
284 --ignore-state
286 type: string; group: Matches; default: Locked
287 Ignore queries whose State matches this Perl regex. The default is
289 to keep threads from being killed if they are locked waiting for
290 another thread.
291 See "--match-state".
293 --ignore-user
295 type: string; group: Matches
296 Ignore queries whose user matches this Perl regex.
298 See "--match-user".
300 --match-command
302 type: string; group: Matches
303 Kill only queries whose Command matches this Perl regex.
305 Common Command values are:
307 Query
309 Sleep
310 Binlog Dump
311 Connect
312 Delayed insert
313 Execute
314 Fetch
315 Init DB
316 Kill
317 Prepare
318 Processlist
319 Quit
320 Reset stmt
321 Table Dump
322 See http://dev.mysql.com/doc/refman/5.1/en/thread-commands.html
324 <http://dev.mysql.com/doc/refman/5.1/en/thread-commands.html> for a
325 full list and description of Command values.
326 --match-db
328 type: string; group: Matches
329 Kill only queries whose db (database) matches this Perl regex.
331 --match-host
333 type: string; group: Matches
334 Kill only queries whose Host matches this Perl regex.
336 The Host value often time includes the port like "host:port".
338 --match-info
340 type: string; group: Matches
341 Kill only queries whose Info (query) matches this Perl regex.
343 The Info column of the processlist shows the query that is being
345 executed or NULL if no query is being executed.
346 --match-state
348 type: string; group: Matches
349 Kill only queries whose State matches this Perl regex.
351 Common State values are:
353 Locked
355 login
356 copy to tmp table
357 Copying to tmp table
358 Copying to tmp table on disk
359 Creating tmp table
360 executing
361 Reading from net
362 Sending data
363 Sorting for order
364 Sorting result
365 Table lock
366 Updating
367 See
369 http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html
370 <http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html>
371 for a full list and description of State values.
372 --match-user
374 type: string; group: Matches
375 Kill only queries whose User matches this Perl regex.
377 --[no]only-oldest
379 default: yes; group: Matches
380 Only kill the single oldest query. This is to prevent killing
382 queries that aren't really long-running, they're just long-waiting.
383 This sorts matching queries by Time and kills the one with the
384 highest Time value.
385 This option is disabled if "--all" is specified.
387 --replication-threads
389 group: Matches
390 Allow matching and killing replication threads.
392 By default, matches do not apply to replication threads; i.e.
394 replication threads are completely ignored. Specifying this option
395 allows matches to match (and potentially kill) replication threads
396 on masters and slaves.
397 ACTIONS
399 When a query matches, one or more of these actions is taken. The
400 actions are taken in this order: "--print", "--execute-command",
401 "--kill". If given, mk-kill sleeps for "--wait-before-kill" before
402 "--kill" (if "--kill" is given). This order allows "--execute-command"
403 to see the output of "--print" and the query before "--kill". This may
404 be helpful because mk-kill does not pass any information to
405 "--execute-command".
406 --execute-command
408 type: string; group: Actions
409 Execute this command when a query matches.
411 After the command is executed, mk-kill has no control over it, so
413 the command is responsible for its own info gathering, logging,
414 interval, etc. The command is excuted each time a query matches,
415 so be careful that the command behaves well when multiple instances
416 are ran. No information from mk-kill is passed to the command.
417 See also "--wait-before-kill".
419 --kill
421 group: Actions
422 Kill the connection for matching queries.
424 This option makes mk-kill kill the connections (a.k.a. processes,
426 threads) that have matching queries. Use "--kill-query" if you
427 only want to kill individual queries and not their connections. If
428 both "--kill" and "--kill-query" are specified, only "--kill-query"
429 is used.
430 Unless "--print" is also given, no other information is printed
432 that shows that mk-kill matched and killed a query.
433 See also "--wait-before-kill" and "--wait-after-kill".
435 --kill-query
437 group: Actions
438 Kill matching queries.
440 This option makes mk-kill kill matching queries. This requires
442 MySQL 5.0 or newer. Unlike "--kill" which kills the connection for
443 matching queries, this option only kills the query, not its
444 connection. If both "--kill" and "--kill-query" are specified,
445 only "--kill-query" is used.
446 --print
448 group: Actions
449 Print a KILL statement for matching queries; does not actually kill
451 queries.
452 If you just want to see which queries match and would be killed
454 without actually killing them, specify "--print". To both kill and
455 print matching queries, specify both "--kill" and "--print".
456
458 These DSN options are used to create a DSN. Each option is given like
459 "option=value". The options are case-sensitive, so P and p are not the
460 same option. There cannot be whitespace before or after the "=" and if
461 the value contains whitespace it must be quoted. DSN options are
462 comma-separated. See the maatkit manpage for full details.
463 · A
465 dsn: charset; copy: yes
467 Default character set.
469 · D
471 dsn: database; copy: yes
473 Default database.
475 · F
477 dsn: mysql_read_default_file; copy: yes
479 Only read default options from the given file
481 · h
483 dsn: host; copy: yes
485 Connect to host.
487 · p
489 dsn: password; copy: yes
491 Password to use when connecting.
493 · P
495 dsn: port; copy: yes
497 Port number to use for connection.
499 · S
501 dsn: mysql_socket; copy: yes
503 Socket file to use for connection.
505 · u
507 dsn: user; copy: yes
509 User for login if not current user.
511
513 You can download Maatkit from Google Code at
514 <http://code.google.com/p/maatkit/>, or you can get any of the tools
515 easily with a command like the following:
516 wget http://www.maatkit.org/get/toolname
518 or
519 wget http://www.maatkit.org/trunk/toolname
520 Where "toolname" can be replaced with the name (or fragment of a name)
522 of any of the Maatkit tools. Once downloaded, they're ready to run; no
523 installation is needed. The first URL gets the latest released version
524 of the tool, and the second gets the latest trunk code from Subversion.
525
527 The environment variable "MKDEBUG" enables verbose debugging output in
528 all of the Maatkit tools:
529 MKDEBUG=1 mk-....
531
533 You need Perl, DBI, DBD::mysql, and some core packages that ought to be
534 installed in any reasonably new version of Perl.
535
537 For list of known bugs see http://www.maatkit.org/bugs/mk-kill
538 <http://www.maatkit.org/bugs/mk-kill>.
539 Please use Google Code Issues and Groups to report bugs or request
541 support: <http://code.google.com/p/maatkit/>. You can also join
542 #maatkit on Freenode to discuss Maatkit.
543 Please include the complete command-line used to reproduce the problem
545 you are seeing, the version of all MySQL servers involved, the complete
546 output of the tool when run with "--version", and if possible,
547 debugging output produced by running with the "MKDEBUG=1" environment
548 variable.
549
551 This program is copyright 2009-2010 Baron Schwartz. Feedback and
552 improvements are welcome.
553 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
555 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
556 MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
557 This program is free software; you can redistribute it and/or modify it
559 under the terms of the GNU General Public License as published by the
560 Free Software Foundation, version 2; OR the Perl Artistic License. On
561 UNIX and similar systems, you can issue `man perlgpl' or `man
562 perlartistic' to read these licenses.
563 You should have received a copy of the GNU General Public License along
565 with this program; if not, write to the Free Software Foundation, Inc.,
566 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
567
569 Baron Schwartz
570
572 This tool is part of Maatkit, a toolkit for power users of MySQL.
573 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
574 primary code contributors. Both are employed by Percona. Financial
575 support for Maatkit development is primarily provided by Percona and
576 its clients.
577
579 This manual page documents Ver 0.9.6 Distrib 6839 $Revision: 6831 $.
580perl v5.12.1 2010-08-01 MK-KILL(1)