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