1MK-TABLE-USAGE(1) User Contributed Perl Documentation MK-TABLE-USAGE(1)
2
6 mk-table-usage - Read queries from a log and analyze how they use
7 tables.
8
10 Usage: mk-table-usage [OPTION...] [FILE...]
11 mk-table-usage reads queries from slow query logs and analyzes how they
13 use tables. If no FILE is specified, STDIN is read. Table usage for
14 every query is printed to STDOUT.
15
17 mk-table-use is very low risk because it only reads and examines
18 queries from a log and executes "EXPLAIN EXTENDED" if the
19 "--explain-extended" option is specified.
20 At the time of this release, there are no known bugs that could cause
22 serious harm to users.
23 The authoritative source for updated information is always the online
25 issue tracking system. Issues that affect this tool will be marked as
26 such. You can see a list of such issues at the following URL:
27 <http://www.maatkit.org/bugs/mk-table-usage>.
28 See also "BUGS" for more information on filing bugs and getting help.
30
32 mk-table-usage reads queries from slow query logs and analyzes how they
33 use tables. Table usage indicates more than just which tables are read
34 from or written to by the query, it also indicates data flow: data in
35 and data out. Data flow is determined by the contexts in which tables
36 are used by the query. A single table can be used in several different
37 contexts in the same query. The reported table usage for each query
38 lists every context for every table. This CONTEXT-TABLE list tells how
39 and where data flows, i.e. the query's table usage. The "OUTPUT"
40 section lists the possible contexts and describes how to read a table
41 usage report.
42 Since this tool analyzes table usage, it's important that queries use
44 table-qualified columns. If a query uses only one table, then all
45 columns must be from that table and there's no problem. But if a query
46 uses multiple tables and the columns are not table-qualified, then that
47 creates a problem that can only be solved by knowing the query's
48 database and specifying "--explain-extended". If the slow log does not
49 specify the database used by the query, then you can specify a default
50 database with "--database". There is no other way to know or guess the
51 database, so the query will be skipped. Secondly, if the database is
52 known, then specifying "--explain-extended" causes mk-table-usage to do
53 "EXPLAIN EXTENDED ..." "SHOW WARNINGS" to get the fully qualified
54 query as reported by MySQL (i.e. all identifiers are fully database-
55 and/or table-qualified). For best results, you should specify
56 "--explain-extended" and "--database" if you know that all queries use
57 the same database.
58 Each query is identified in the output by either an MD5 hex checksum of
60 the query's fingerprint or the query's value for the specified
61 "--id-attribute". The query ID is for parsing and storing the table
62 usage reports in a table that is keyed on the query ID. See "OUTPUT"
63 for more information.
64
66 The table usage report that is printed for each query looks similar to
67 the following:
68 Query_id: 0x1CD27577D202A339.1
70 UPDATE t1
71 SELECT DUAL
72 JOIN t1
73 JOIN t2
74 WHERE t1
75 Query_id: 0x1CD27577D202A339.2
77 UPDATE t2
78 SELECT DUAL
79 JOIN t1
80 JOIN t2
81 WHERE t1
82 Usage reports are separated by blank lines. The first line is always
84 the query ID: a unique ID that can be used to parse the output and
85 store the usage reports in a table keyed on this ID. The query ID has
86 two parts separated by a period: the query ID and the target table
87 number.
88 If "--id-attribute" is not specified, then query IDs are automatically
90 created by making an MD5 hex checksum of the query's fingerprint (as
91 shown above, e.g. 0x1CD27577D202A339); otherwise, the query ID is the
92 query's value for the given attribute.
93 The target table number starts at 1 and increments by 1 for each table
95 that the query affects. Only multi-table UPDATE queries can affect
96 multiple tables with a single query, so this number is 1 for all other
97 types of queries. (Multi-table DELETE queries are not supported.) The
98 example output above is from this query:
99 UPDATE t1 AS a JOIN t2 AS b USING (id)
101 SET a.foo="bar", b.foo="bat"
102 WHERE a.id=1;
103 The "SET" clause indicates that two tables are updated: "a" aliased as
105 "t1", and "b" aliased as "t2". So two usage reports are printed, one
106 for each table, and this is indicated in the output by their common
107 query ID but incrementing target table number.
108 After the first line is a variable number of CONTEXT-TABLE lines.
110 Possible contexts are:
111 · SELECT
113 SELECT means that data is taken out of the table for one of two
115 reasons: to be returned to the user as part of a result set, or to
116 be put into another table as part of an INSERT or UPDATE. In the
117 first case, since only SELECT queries return result sets, a SELECT
118 context is always listed for SELECT queries. In the second case,
119 data from one table is used to insert or update rows in another
120 table. For example, the UPDATE query in the example above has the
121 usage:
122 SELECT DUAL
124 This refers to:
126 SET a.foo="bar", b.foo="bat"
128 DUAL is used for any values that does not originate in a table, in
130 this case the literal values "bar" and "bat". If that "SET" clause
131 were "SET a.foo=b.foo" instead, then the complete usage would be:
132 Query_id: 0x1CD27577D202A339.1
134 UPDATE t1
135 SELECT t2
136 JOIN t1
137 JOIN t2
138 WHERE t1
139 The presence of a SELECT context after another context, such as
141 UPDATE or INSERT, indicates where the UPDATE or INSERT retrieves
142 its data. The example immediately above reflects an UPDATE query
143 that updates rows in table "t1" with data from table "t2".
144 · Any other query type
146 Any other query type, such as INSERT, UPDATE, DELETE, etc. may be a
148 context. All these types indicate that the table is written or
149 altered in some way. If a SELECT context follows one of these
150 types, then data is read from the SELECT table and written to this
151 table. This happens, for example, with INSERT..SELECT or UPDATE
152 queries that set column values using values from tables instead of
153 constant values.
154 These query types are not supported:
156 SET
158 LOAD
159 multi-table DELETE
160 · JOIN
162 The JOIN context lists tables that are joined, either with an
164 explicit JOIN in the FROM clause, or implicitly in the WHERE
165 clause, such as "t1.id = t2.id".
166 · WHERE
168 The WHERE context lists tables that are used in the WHERE clause to
170 filter results. This does not include tables that are implicitly
171 joined in the WHERE clause; those are listed as JOIN contexts. For
172 example:
173 WHERE t1.id > 100 AND t1.id < 200 AND t2.foo IS NOT NULL
175 Results in:
177 WHERE t1
179 WHERE t2
180 Only unique tables are listed; that is why table "t1" is listed
182 only once.
183 · TLIST
185 The TLIST context lists tables that are accessed by the query but
187 do not appear in any other context. These tables are usually an
188 implicit full cartesian join, so they should be avoided. For
189 example, the query "SELECT * FROM t1, t2" results in:
190 Query_id: 0xBDDEB6EDA41897A8.1
192 SELECT t1
193 SELECT t2
194 TLIST t1
195 TLIST t2
196 First of all, there are two SELECT contexts, because "SELECT *"
198 selects rows from all tables; "t1" and "t2" in this case.
199 Secondly, the tables are implicitly joined, but without any kind of
200 join condition, which results in a full cartesian join as indicated
201 by the TLIST context for each.
202
204 mk-table-usage exits 1 on any kind of error, or 0 if no errors.
205
207 This tool accepts additional command-line arguments. Refer to the
208 "SYNOPSIS" and usage information for details.
209 --ask-pass
211 Prompt for a password when connecting to MySQL.
212 --charset
214 short form: -A; type: string
215 Default character set. If the value is utf8, sets Perl's binmode
217 on STDOUT to utf8, passes the mysql_enable_utf8 option to
218 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
219 other value sets binmode on STDOUT without the utf8 layer, and runs
220 SET NAMES after connecting to MySQL.
221 --config
223 type: Array
224 Read this comma-separated list of config files; if specified, this
226 must be the first option on the command line.
227 --constant-data-value
229 type: string; default: DUAL
230 Value to print for constant data. Constant data means all data not
232 from tables (or subqueries since subqueries are not supported).
233 For example, real constant values like strings ("foo") and numbers
234 (42), and data from functions like "NOW()". For example, in the
235 query "INSERT INTO t (c) VALUES ('a')", the string 'a' is constant
236 data, so the table usage report is:
237 INSERT t
239 SELECT DUAL
240 The first line indicates that data is inserted into table "t" and
242 the second line indicates that that data comes from some constant
243 value.
244 --[no]continue-on-error
246 default: yes
247 Continue parsing even if there is an error.
249 --create-table-definitions
251 type: array
252 Read "CREATE TABLE" definitions from this list of comma-separated
254 files. If you cannot use "--explain-extended" to fully qualify
255 table and column names, you can save the output of "mysqldump
256 --no-data" to one or more files and specify those files with this
257 option. The tool will parse all "CREATE TABLE" definitions from
258 the files and use this information to qualify table and column
259 names. If a column name is used in multiple tables, or table name
260 is used in multiple databases, these duplicates cannot be
261 qualified.
262 --daemonize
264 Fork to the background and detach from the shell. POSIX operating
265 systems only.
266 --database
268 short form: -D; type: string
269 Default database.
271 --defaults-file
273 short form: -F; type: string
274 Only read mysql options from the given file. You must give an
276 absolute pathname.
277 --explain-extended
279 type: DSN
280 EXPLAIN EXTENDED queries on this host to fully qualify table and
282 column names.
283 --filter
285 type: string
286 Discard events for which this Perl code doesn't return true.
288 This option is a string of Perl code or a file containing Perl code
290 that gets compiled into a subroutine with one argument: $event.
291 This is a hashref. If the given value is a readable file, then mk-
292 query-digest reads the entire file and uses its contents as the
293 code. The file should not contain a shebang (#!/usr/bin/perl)
294 line.
295 If the code returns true, the chain of callbacks continues;
297 otherwise it ends. The code is the last statement in the
298 subroutine other than "return $event". The subroutine template is:
299 sub { $event = shift; filter && return $event; }
301 Filters given on the command line are wrapped inside parentheses
303 like like "( filter )". For complex, multi-line filters, you must
304 put the code inside a file so it will not be wrapped inside
305 parentheses. Either way, the filter must produce syntactically
306 valid code given the template. For example, an if-else branch
307 given on the command line would not be valid:
308 --filter 'if () { } else { }' # WRONG
310 Since it's given on the command line, the if-else branch would be
312 wrapped inside parentheses which is not syntactically valid. So to
313 accomplish something more complex like this would require putting
314 the code in a file, for example filter.txt:
315 my $event_ok; if (...) { $event_ok=1; } else { $event_ok=0; } $event_ok
317 Then specify "--filter filter.txt" to read the code from
319 filter.txt.
320 If the filter code won't compile, mk-query-digest will die with an
322 error. If the filter code does compile, an error may still occur
323 at runtime if the code tries to do something wrong (like pattern
324 match an undefined value). mk-query-digest does not provide any
325 safeguards so code carefully!
326 An example filter that discards everything but SELECT statements:
328 --filter '$event->{arg} =~ m/^select/i'
330 This is compiled into a subroutine like the following:
332 sub { $event = shift; ( $event->{arg} =~ m/^select/i ) && return $event; }
334 It is permissible for the code to have side effects (to alter
336 $event).
337 You can find an explanation of the structure of $event at
339 <http://code.google.com/p/maatkit/wiki/EventAttributes>.
340 Here are more examples of filter code:
342 Host/IP matches domain.com
344 --filter '($event->{host} || $event->{ip} || "") =~
345 m/domain.com/'
346 Sometimes MySQL logs the host where the IP is expected.
348 Therefore, we check both.
349 User matches john
351 --filter '($event->{user} || "") =~ m/john/'
352 More than 1 warning
354 --filter '($event->{Warning_count} || 0) > 1'
355 Query does full table scan or full join
357 --filter '(($event->{Full_scan} || "") eq "Yes") ||
358 (($event->{Full_join} || "") eq "Yes")'
359 Query was not served from query cache
361 --filter '($event->{QC_Hit} || "") eq "No"'
362 Query is 1 MB or larger
364 --filter '$event->{bytes} >= 1_048_576'
365 Since "--filter" allows you to alter $event, you can use it to do
367 other things, like create new attributes.
368 --help
370 Show help and exit.
371 --host
373 short form: -h; type: string
374 Connect to host.
376 --id-attribute
378 type: string
379 Identify each event using this attribute. If not ID attribute is
381 given, then events are identified with the query's checksum: an MD5
382 hex checksum of the query's fingerprint.
383 --log
385 type: string
386 Print all output to this file when daemonized.
388 --password
390 short form: -p; type: string
391 Password to use when connecting.
393 --pid
395 type: string
396 Create the given PID file when running. The file contains the
398 process ID of the daemonized instance. The PID file is removed
399 when the daemonized instance exits. The program checks for the
400 existence of the PID file when starting; if it exists and the
401 process with the matching PID exists, the program exits.
402 --port
404 short form: -P; type: int
405 Port number to use for connection.
407 --progress
409 type: array; default: time,30
410 Print progress reports to STDERR. The value is a comma-separated
412 list with two parts. The first part can be percentage, time, or
413 iterations; the second part specifies how often an update should be
414 printed, in percentage, seconds, or number of iterations.
415 --query
417 type: string
418 Analyze only this given query. If you want to analyze the table
420 usage of one simple query by providing on the command line instead
421 of reading it from a slow log file, then specify that query with
422 this option. The default "--id-attribute" will be used which is
423 the query's checksum.
424 --read-timeout
426 type: time; default: 0
427 Wait this long for an event from the input; 0 to wait forever.
429 This option sets the maximum time to wait for an event from the
431 input. If an event is not received after the specified time, the
432 script stops reading the input and prints its reports.
433 This option requires the Perl POSIX module.
435 --run-time
437 type: time
438 How long to run before exiting. The default is to run forever (you
440 can interrupt with CTRL-C).
441 --set-vars
443 type: string; default: wait_timeout=10000
444 Set these MySQL variables. Immediately after connecting to MySQL,
446 this string will be appended to SET and executed.
447 --socket
449 short form: -S; type: string
450 Socket file to use for connection.
452 --user
454 short form: -u; type: string
455 User for login if not current user.
457 --version
459 Show version and exit.
460
462 These DSN options are used to create a DSN. Each option is given like
463 "option=value". The options are case-sensitive, so P and p are not the
464 same option. There cannot be whitespace before or after the "=" and if
465 the value contains whitespace it must be quoted. DSN options are
466 comma-separated. See the maatkit manpage for full details.
467 · A
469 dsn: charset; copy: yes
471 Default character set.
473 · D
475 dsn: database; copy: yes
477 Database that contains the query review table.
479 · F
481 dsn: mysql_read_default_file; copy: yes
483 Only read default options from the given file
485 · h
487 dsn: host; copy: yes
489 Connect to host.
491 · p
493 dsn: password; copy: yes
495 Password to use when connecting.
497 · P
499 dsn: port; copy: yes
501 Port number to use for connection.
503 · S
505 dsn: mysql_socket; copy: yes
507 Socket file to use for connection.
509 · u
511 dsn: user; copy: yes
513 User for login if not current user.
515
517 You can download Maatkit from Google Code at
518 <http://code.google.com/p/maatkit/>, or you can get any of the tools
519 easily with a command like the following:
520 wget http://www.maatkit.org/get/toolname
522 or
523 wget http://www.maatkit.org/trunk/toolname
524 Where "toolname" can be replaced with the name (or fragment of a name)
526 of any of the Maatkit tools. Once downloaded, they're ready to run; no
527 installation is needed. The first URL gets the latest released version
528 of the tool, and the second gets the latest trunk code from Subversion.
529
531 The environment variable "MKDEBUG" enables verbose debugging output in
532 all of the Maatkit tools:
533 MKDEBUG=1 mk-....
535
537 You need Perl and some core packages that ought to be installed in any
538 reasonably new version of Perl.
539
541 For a list of known bugs see
542 <http://www.maatkit.org/bugs/mk-table-usage>.
543 Please use Google Code Issues and Groups to report bugs or request
545 support: <http://code.google.com/p/maatkit/>. You can also join
546 #maatkit on Freenode to discuss Maatkit.
547 Please include the complete command-line used to reproduce the problem
549 you are seeing, the version of all MySQL servers involved, the complete
550 output of the tool when run with "--version", and if possible,
551 debugging output produced by running with the "MKDEBUG=1" environment
552 variable.
553
555 This program is copyright 2009-2011 Percona Inc. Feedback and
556 improvements are welcome.
557 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
559 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
560 MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
561 This program is free software; you can redistribute it and/or modify it
563 under the terms of the GNU General Public License as published by the
564 Free Software Foundation, version 2; OR the Perl Artistic License. On
565 UNIX and similar systems, you can issue `man perlgpl' or `man
566 perlartistic' to read these licenses.
567 You should have received a copy of the GNU General Public License along
569 with this program; if not, write to the Free Software Foundation, Inc.,
570 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
571
573 Daniel Nichter
574
576 This manual page documents Ver 1.0.1 Distrib 7540 $Revision: 7531 $.
577perl v5.28.0 2011-06-08 MK-TABLE-USAGE(1)