1MK-DEADLOCK-LOGGER(1) User Contributed Perl DocumentationMK-DEADLOCK-LOGGER(1)
2
6 mk-deadlock-logger - Extract and log MySQL deadlock information.
7
9 Print deadlocks on SOURCE_DSN:
10 mk-deadlock-logger SOURCE_DSN
12 Store deadlock information from SOURCE_DSN in test.deadlocks table on
14 SOURCE_DSN (source and destination are the same host):
15 mk-deadlock-logger SOURCE_DSN --dest D=test,t=deadlocks
17 Store deadlock information from SOURCE_DSN in test.deadlocks table on
19 DEST_DSN (source and destination are different hosts):
20 mk-deadlock-logger SOURCE_DSN --dest DEST_DSN,D=test,t=deadlocks
22 Daemonize and check for deadlocks on SOURCE_DSN every 30 seconds for 4
24 hours:
25 mk-deadlock-logger SOURCE_DSN --dest D=test,t=deadlocks --daemonize --run-time 4h --interval 30s
27
29 The following section is included to inform users about the potential
30 risks, whether known or unknown, of using this tool. The two main
31 categories of risks are those created by the nature of the tool (e.g.
32 read-only tools vs. read-write tools) and those created by bugs.
33 mk-deadlock-logger is a read-only tool unless you specify a "--dest"
35 table. In some cases polling SHOW INNODB STATUS too rapidly can cause
36 extra load on the server. If you're using it on a production server
37 under very heavy load, you might want to set "--interval" to 30 seconds
38 or more.
39 At the time of this release, we know of no bugs that could cause
41 serious harm to users.
42 The authoritative source for updated information is always the online
44 issue tracking system. Issues that affect this tool will be marked as
45 such. You can see a list of such issues at the following URL:
46 http://www.maatkit.org/bugs/mk-deadlock-logger
47 <http://www.maatkit.org/bugs/mk-deadlock-logger>.
48 See also "BUGS" for more information on filing bugs and getting help.
50
52 mk-deadlock-logger extracts deadlock data from a MySQL server.
53 Currently only InnoDB deadlock information is available. You can print
54 the information to standard output, store it in a database table, or
55 both. If neither "--print" nor "--dest" are given, then the deadlock
56 information is printed by default. If only "--dest" is given, then the
57 deadlock information is only stored. If both options are given, then
58 the deadlock informat is printed and stored.
59 The source host can be specified using one of two methods. The first
61 method is to use at least one of the standard connection-related
62 command line options: "--defaults-file", "--password", "--host",
63 "--port", "--socket" or "--user". These options only apply to the
64 source host; they cannot be used to specify the destination host.
65 The second method to specifiy the source host, or the optional
67 destination host using "--dest", is a DSN. A DSN is a special syntax
68 that can be either just a hostname (like "server.domain.com" or
69 1.2.3.4), or a "key=value,key=value" string. Keys are a single letter:
70 KEY MEANING
72 === =======
73 h Connect to host
74 P Port number to use for connection
75 S Socket file to use for connection
76 u User for login if not current user
77 p Password to use when connecting
78 F Only read default options from the given file
79 If you omit any values from the destination host DSN, they are filled
81 in with values from the source host, so you don't need to specify them
82 in both places. "mk-deadlock-logger" reads all normal MySQL option
83 files, such as ~/.my.cnf, so you may not need to specify username,
84 password and other common options at all.
85
87 You can choose which columns are output and/or saved to "--dest" with
88 the "--columns" argument. The default columns are as follows:
89 server
91 The (source) server on which the deadlock occurred. This might be
92 useful if you're tracking deadlocks on many servers.
93 ts The date and time of the last detected deadlock.
95 thread
97 The MySQL thread number, which is the same as the connection ID in
98 SHOW FULL PROCESSLIST.
99 txn_id
101 The InnoDB transaction ID, which InnoDB expresses as two unsigned
102 integers. I have multiplied them out to be one number.
103 txn_time
105 How long the transaction was active when the deadlock happened.
106 user
108 The connection's database username.
109 hostname
111 The connection's host.
112 ip The connection's IP address. If you specify "--numeric-ip", this
114 is converted to an unsigned integer.
115 db The database in which the deadlock occurred.
117 tbl The table on which the deadlock occurred.
119 idx The index on which the deadlock occurred.
121 lock_type
123 The lock type the transaction held on the lock that caused the
124 deadlock.
125 lock_mode
127 The lock mode of the lock that caused the deadlock.
128 wait_hold
130 Whether the transaction was waiting for the lock or holding the
131 lock. Usually you will see the two waited-for locks.
132 victim
134 Whether the transaction was selected as the deadlock victim and
135 rolled back.
136 query
138 The query that caused the deadlock.
139
141 InnoDB's output is hard to parse and sometimes there's no way to do it
142 right.
143 Sometimes not all information (for example, username or IP address) is
145 included in the deadlock information. In this case there's nothing for
146 the script to put in those columns. It may also be the case that the
147 deadlock output is so long (because there were a lot of locks) that the
148 whole thing is truncated.
149 Though there are usually two transactions involved in a deadlock, there
151 are more locks than that; at a minimum, one more lock than transactions
152 is necessary to create a cycle in the waits-for graph. mk-deadlock-
153 logger prints the transactions (always two in the InnoDB output, even
154 when there are more transactions in the waits-for graph than that) and
155 fills in locks. It prefers waited-for over held when choosing lock
156 information to output, but you can figure out the rest with a moment's
157 thought. If you see one wait-for and one held lock, you're looking at
158 the same lock, so of course you'd prefer to see both wait-for locks and
159 get more information. If the two waited-for locks are not on the same
160 table, more than two transactions were involved in the deadlock.
161
163 --ask-pass
164 Prompt for a password when connecting to MySQL.
165 --charset
167 short form: -A; type: string
168 Default character set. If the value is utf8, sets Perl's binmode
170 on STDOUT to utf8, passes the mysql_enable_utf8 option to
171 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
172 other value sets binmode on STDOUT without the utf8 layer, and runs
173 SET NAMES after connecting to MySQL.
174 --clear-deadlocks
176 type: string
177 Use this table to create a small deadlock. This usually has the
179 effect of clearing out a huge deadlock, which otherwise consumes
180 the entire output of "SHOW INNODB STATUS". The table must not
181 exist. mk-deadlock-logger will create it with the following
182 MAGIC_clear_deadlocks structure:
183 CREATE TABLE test.deadlock_maker(a INT PRIMARY KEY) ENGINE=InnoDB;
185 After creating the table and causing a small deadlock, the tool
187 will drop the table again.
188 --[no]collapse
190 Collapse whitespace in queries to a single space. This might make
191 it easier to inspect on the command line or in a query. By
192 default, whitespace is collapsed when printing with "--print", but
193 not modified when storing to "--dest". (That is, the default is
194 different for each action).
195 --columns
197 type: hash
198 Output only this comma-separated list of columns. See "OUTPUT" for
200 more details on columns.
201 --config
203 type: Array
204 Read this comma-separated list of config files; if specified, this
206 must be the first option on the command line.
207 --create-dest-table
209 Create the table specified by "--dest".
210 Normally the "--dest" table is expected to exist already. This
212 option causes mk-deadlock-logger to create the table automatically
213 using the suggested table structure.
214 --daemonize
216 Fork to the background and detach from the shell. POSIX operating
217 systems only.
218 --defaults-file
220 short form: -F; type: string
221 Only read mysql options from the given file. You must give an
223 absolute pathname.
224 --dest
226 type: DSN
227 DSN for where to store deadlocks; specify at least a database (D)
229 and table (t).
230 Missing values are filled in with the same values from the source
232 host, so you can usually omit most parts of this argument if you're
233 storing deadlocks on the same server on which they happen.
234 By default, whitespace in the query column is left intact; use
236 "--[no]collapse" if you want whitespace collapsed.
237 The following MAGIC_dest_table is suggested if you want to store
239 all the information mk-deadlock-logger can extract about deadlocks:
240 CREATE TABLE deadlocks (
242 server char(20) NOT NULL,
243 ts datetime NOT NULL,
244 thread int unsigned NOT NULL,
245 txn_id bigint unsigned NOT NULL,
246 txn_time smallint unsigned NOT NULL,
247 user char(16) NOT NULL,
248 hostname char(20) NOT NULL,
249 ip char(15) NOT NULL, -- alternatively, ip int unsigned NOT NULL
250 db char(64) NOT NULL,
251 tbl char(64) NOT NULL,
252 idx char(64) NOT NULL,
253 lock_type char(16) NOT NULL,
254 lock_mode char(1) NOT NULL,
255 wait_hold char(1) NOT NULL,
256 victim tinyint unsigned NOT NULL,
257 query text NOT NULL,
258 PRIMARY KEY (server,ts,thread)
259 ) ENGINE=InnoDB
260 If you use "--columns", you can omit whichever columns you don't
262 want to store.
263 --help
265 Show help and exit.
266 --host
268 short form: -h; type: string
269 Connect to host.
271 --interval
273 type: time
274 How often to check for deadlocks. If no "--run-time" is specified,
276 mk-deadlock-logger runs forever, checking for deadlocks at every
277 interval. See also "--run-time".
278 --log
280 type: string
281 Print all output to this file when daemonized.
283 --numeric-ip
285 Express IP addresses as integers.
286 --password
288 short form: -p; type: string
289 Password to use when connecting.
291 --pid
293 type: string
294 Create the given PID file when daemonized. The file contains the
296 process ID of the daemonized instance. The PID file is removed
297 when the daemonized instance exits. The program checks for the
298 existence of the PID file when starting; if it exists and the
299 process with the matching PID exists, the program exits.
300 --port
302 short form: -P; type: int
303 Port number to use for connection.
305 --print
307 Print results on standard output. See "OUTPUT" for more. By
308 default, enables "--[no]collapse" unless you explicitly disable it.
309 If "--interval" or "--run-time" is specified, only new deadlocks
311 are printed at each interval. A fingerprint for each deadlock is
312 created using "--columns" server, ts and thread (even if those
313 columns were not specified by "--columns") and if the current
314 deadlock's fingerprint is different from the last deadlock's
315 fingerprint, then it is printed.
316 --run-time
318 type: time
319 How long to run before exiting. By default mk-deadlock-logger runs
321 once, checks for deadlocks, and exits. If "--run-time" is
322 specified but no "--interval" is specified, a default 1 second
323 interval will be used.
324 --set-vars
326 type: string; default: wait_timeout=10000
327 Set these MySQL variables. Immediately after connecting to MySQL,
329 this string will be appended to SET and executed.
330 --socket
332 short form: -S; type: string
333 Socket file to use for connection.
335 --tab
337 Print tab-separated columns, instead of aligned.
338 --user
340 short form: -u; type: string
341 User for login if not current user.
343 --version
345 Show version and exit.
346
348 These DSN options are used to create a DSN. Each option is given like
349 "option=value". The options are case-sensitive, so P and p are not the
350 same option. There cannot be whitespace before or after the "=" and if
351 the value contains whitespace it must be quoted. DSN options are
352 comma-separated. See the maatkit manpage for full details.
353 · A
355 dsn: charset; copy: yes
357 Default character set.
359 · D
361 dsn: database; copy: yes
363 Default database.
365 · F
367 dsn: mysql_read_default_file; copy: yes
369 Only read default options from the given file
371 · h
373 dsn: host; copy: yes
375 Connect to host.
377 · p
379 dsn: password; copy: yes
381 Password to use when connecting.
383 · P
385 dsn: port; copy: yes
387 Port number to use for connection.
389 · S
391 dsn: mysql_socket; copy: yes
393 Socket file to use for connection.
395 · t
397 Table in which to store deadlock information.
399 · u
401 dsn: user; copy: yes
403 User for login if not current user.
405
407 You can download Maatkit from Google Code at
408 <http://code.google.com/p/maatkit/>, or you can get any of the tools
409 easily with a command like the following:
410 wget http://www.maatkit.org/get/toolname
412 or
413 wget http://www.maatkit.org/trunk/toolname
414 Where "toolname" can be replaced with the name (or fragment of a name)
416 of any of the Maatkit tools. Once downloaded, they're ready to run; no
417 installation is needed. The first URL gets the latest released version
418 of the tool, and the second gets the latest trunk code from Subversion.
419
421 The environment variable "MKDEBUG" enables verbose debugging output in
422 all of the Maatkit tools:
423 MKDEBUG=1 mk-....
425
427 You need Perl, DBI, DBD::mysql, and some core packages that ought to be
428 installed in any reasonably new version of Perl.
429
431 For list of known bugs see
432 http://www.maatkit.org/bugs/mk-deadlock-logger
433 <http://www.maatkit.org/bugs/mk-deadlock-logger>.
434 Please use Google Code Issues and Groups to report bugs or request
436 support: <http://code.google.com/p/maatkit/>. You can also join
437 #maatkit on Freenode to discuss Maatkit.
438 Please include the complete command-line used to reproduce the problem
440 you are seeing, the version of all MySQL servers involved, the complete
441 output of the tool when run with "--version", and if possible,
442 debugging output produced by running with the "MKDEBUG=1" environment
443 variable.
444
446 This program is copyright 2007-2010 Baron Schwartz. Feedback and
447 improvements are welcome.
448 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
450 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
451 MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
452 This program is free software; you can redistribute it and/or modify it
454 under the terms of the GNU General Public License as published by the
455 Free Software Foundation, version 2; OR the Perl Artistic License. On
456 UNIX and similar systems, you can issue `man perlgpl' or `man
457 perlartistic' to read these licenses.
458 You should have received a copy of the GNU General Public License along
460 with this program; if not, write to the Free Software Foundation, Inc.,
461 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
462
464 Baron Schwartz
465
467 This tool is part of Maatkit, a toolkit for power users of MySQL.
468 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
469 primary code contributors. Both are employed by Percona. Financial
470 support for Maatkit development is primarily provided by Percona and
471 its clients.
472
474 This manual page documents Ver 1.0.21 Distrib 6839 $Revision: 6831 $.
475perl v5.12.1 2010-08-01 MK-DEADLOCK-LOGGER(1)