1MK-DEADLOCK-LOGGER(1) User Contributed Perl DocumentationMK-DEADLOCK-LOGGER(1)
2
3
4

NAME

6       mk-deadlock-logger - Extract and log MySQL deadlock information.
7

SYNOPSIS

9       Usage: mk-deadlock-logger [OPTION...] SOURCE_DSN
10
11       mk-deadlock-logger extracts and saves information about the most recent
12       deadlock in a MySQL server.
13
14       Print deadlocks on SOURCE_DSN:
15
16          mk-deadlock-logger SOURCE_DSN
17
18       Store deadlock information from SOURCE_DSN in test.deadlocks table on
19       SOURCE_DSN (source and destination are the same host):
20
21          mk-deadlock-logger SOURCE_DSN --dest D=test,t=deadlocks
22
23       Store deadlock information from SOURCE_DSN in test.deadlocks table on
24       DEST_DSN (source and destination are different hosts):
25
26          mk-deadlock-logger SOURCE_DSN --dest DEST_DSN,D=test,t=deadlocks
27
28       Daemonize and check for deadlocks on SOURCE_DSN every 30 seconds for 4
29       hours:
30
31          mk-deadlock-logger SOURCE_DSN --dest D=test,t=deadlocks --daemonize --run-time 4h --interval 30s
32

RISKS

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
39       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
45       At the time of this release, we know of no bugs that could cause
46       serious harm to users.
47
48       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
53       See also "BUGS" for more information on filing bugs and getting help.
54

DESCRIPTION

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
64       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
70       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
75         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
84       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

OUTPUT

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
94       server
95           The (source) server on which the deadlock occurred.  This might be
96           useful if you're tracking deadlocks on many servers.
97
98       ts  The date and time of the last detected deadlock.
99
100       thread
101           The MySQL thread number, which is the same as the connection ID in
102           SHOW FULL PROCESSLIST.
103
104       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
108       txn_time
109           How long the transaction was active when the deadlock happened.
110
111       user
112           The connection's database username.
113
114       hostname
115           The connection's host.
116
117       ip  The connection's IP address.  If you specify "--numeric-ip", this
118           is converted to an unsigned integer.
119
120       db  The database in which the deadlock occurred.
121
122       tbl The table on which the deadlock occurred.
123
124       idx The index on which the deadlock occurred.
125
126       lock_type
127           The lock type the transaction held on the lock that caused the
128           deadlock.
129
130       lock_mode
131           The lock mode of the lock that caused the deadlock.
132
133       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
137       victim
138           Whether the transaction was selected as the deadlock victim and
139           rolled back.
140
141       query
142           The query that caused the deadlock.
143

INNODB CAVEATS AND DETAILS

145       InnoDB's output is hard to parse and sometimes there's no way to do it
146       right.
147
148       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
154       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

OPTIONS

167       This tool accepts additional command-line arguments.  Refer to the
168       "SYNOPSIS" and usage information for details.
169
170       --ask-pass
171           Prompt for a password when connecting to MySQL.
172
173       --charset
174           short form: -A; type: string
175
176           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
182       --clear-deadlocks
183           type: string
184
185           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
191             CREATE TABLE test.deadlock_maker(a INT PRIMARY KEY) ENGINE=InnoDB;
192
193           After creating the table and causing a small deadlock, the tool
194           will drop the table again.
195
196       --[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
203       --columns
204           type: hash
205
206           Output only this comma-separated list of columns.  See "OUTPUT" for
207           more details on columns.
208
209       --config
210           type: Array
211
212           Read this comma-separated list of config files; if specified, this
213           must be the first option on the command line.
214
215       --create-dest-table
216           Create the table specified by "--dest".
217
218           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
222       --daemonize
223           Fork to the background and detach from the shell.  POSIX operating
224           systems only.
225
226       --defaults-file
227           short form: -F; type: string
228
229           Only read mysql options from the given file.  You must give an
230           absolute pathname.
231
232       --dest
233           type: DSN
234
235           DSN for where to store deadlocks; specify at least a database (D)
236           and table (t).
237
238           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
242           By default, whitespace in the query column is left intact; use
243           "--[no]collapse" if you want whitespace collapsed.
244
245           The following MAGIC_dest_table is suggested if you want to store
246           all the information mk-deadlock-logger can extract about deadlocks:
247
248            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
268           If you use "--columns", you can omit whichever columns you don't
269           want to store.
270
271       --help
272           Show help and exit.
273
274       --host
275           short form: -h; type: string
276
277           Connect to host.
278
279       --interval
280           type: time
281
282           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
286       --log
287           type: string
288
289           Print all output to this file when daemonized.
290
291       --numeric-ip
292           Express IP addresses as integers.
293
294       --password
295           short form: -p; type: string
296
297           Password to use when connecting.
298
299       --pid
300           type: string
301
302           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
308       --port
309           short form: -P; type: int
310
311           Port number to use for connection.
312
313       --print
314           Print results on standard output.  See "OUTPUT" for more.  By
315           default, enables "--[no]collapse" unless you explicitly disable it.
316
317           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
324       --run-time
325           type: time
326
327           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
332       --set-vars
333           type: string; default: wait_timeout=10000
334
335           Set these MySQL variables.  Immediately after connecting to MySQL,
336           this string will be appended to SET and executed.
337
338       --socket
339           short form: -S; type: string
340
341           Socket file to use for connection.
342
343       --tab
344           Print tab-separated columns, instead of aligned.
345
346       --user
347           short form: -u; type: string
348
349           User for login if not current user.
350
351       --version
352           Show version and exit.
353

DSN OPTIONS

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
361       ·   A
362
363           dsn: charset; copy: yes
364
365           Default character set.
366
367       ·   D
368
369           dsn: database; copy: yes
370
371           Default database.
372
373       ·   F
374
375           dsn: mysql_read_default_file; copy: yes
376
377           Only read default options from the given file
378
379       ·   h
380
381           dsn: host; copy: yes
382
383           Connect to host.
384
385       ·   p
386
387           dsn: password; copy: yes
388
389           Password to use when connecting.
390
391       ·   P
392
393           dsn: port; copy: yes
394
395           Port number to use for connection.
396
397       ·   S
398
399           dsn: mysql_socket; copy: yes
400
401           Socket file to use for connection.
402
403       ·   t
404
405           Table in which to store deadlock information.
406
407       ·   u
408
409           dsn: user; copy: yes
410
411           User for login if not current user.
412

DOWNLOADING

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
418          wget http://www.maatkit.org/get/toolname
419          or
420          wget http://www.maatkit.org/trunk/toolname
421
422       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

ENVIRONMENT

428       The environment variable "MKDEBUG" enables verbose debugging output in
429       all of the Maatkit tools:
430
431          MKDEBUG=1 mk-....
432

SYSTEM REQUIREMENTS

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

BUGS

438       For a list of known bugs see
439       <http://www.maatkit.org/bugs/mk-deadlock-logger>.
440
441       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
445       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

COPYRIGHT, LICENSE AND WARRANTY

452       This program is copyright 2007-2011 Baron Schwartz.  Feedback and
453       improvements are welcome.
454
455       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
459       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
465       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

AUTHOR

470       Baron Schwartz
471

ABOUT MAATKIT

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

VERSION

480       This manual page documents Ver 1.0.21 Distrib 7540 $Revision: 7477 $.
481
482
483
484perl v5.28.1                      2011-06-08             MK-DEADLOCK-LOGGER(1)
Impressum