1MK-INDEX-USAGE(1) User Contributed Perl Documentation MK-INDEX-USAGE(1)
2
6 mk-index-usage - Read queries from a log and analyze how they use
7 indexes.
8
10 mk-index-usage /path/to/slow.log --host localhost
11
13 The following section is included to inform users about the potential
14 risks, whether known or unknown, of using this tool. The two main
15 categories of risks are those created by the nature of the tool (e.g.
16 read-only tools vs. read-write tools) and those created by bugs.
17 This tool is read-only. It tries to read a log of queries and EXPLAIN
19 them. It also gathers information about all tables in all databases.
20 It should be very low-risk.
21 At the time of this release, we know of no bugs that could cause
23 serious harm to users.
24 The authoritative source for updated information is always the online
26 issue tracking system. Issues that affect this tool will be marked as
27 such. You can see a list of such issues at the following URL:
28 http://www.maatkit.org/bugs/mk-index-usage
29 <http://www.maatkit.org/bugs/mk-index-usage>.
30 See also "BUGS" for more information on filing bugs and getting help.
32
34 This tool connects to a MySQL database server, reads through a query
35 log, and asks MySQL how it will use each query. When it is finished,
36 it prints out a report on indexes that the queries didn't use.
37 The query log needs to be in MySQL's slow query log format. If you
39 need to input a different format, you can use mk-query-digest to
40 translate the formats. If you don't specify a filename, the tool reads
41 from STDIN.
42 The tool runs two stages. In the first stage, the tool takes inventory
44 of all the tables and indexes in your database, so it can compare the
45 existing indexes to those that were actually used by the queries in the
46 log. In the second stage, it runs EXPLAIN on each query in the query
47 log. If the query is not a SELECT, it tries to transform it to a
48 roughly equivalent SELECT query. This is not a perfect process, but it
49 is good enough to be useful.
50 The tool skips the EXPLAIN step for queries that are exact duplicates
52 of those seen before. It assumes that the same query will generate the
53 same EXPLAIN plan as it did previously (usually a safe assumption, and
54 generally good for performance), and simply increments the count of
55 times that the indexes were used. However, queries that have the same
56 fingerprint but different checksums will be re-EXPLAINed. Queries that
57 have different literal constants can have different execution plans,
58 and this is important to measure.
59 After EXPLAIN-ing the query, it is necessary to try to map aliases in
61 the query back to the original table names. For example, consider the
62 EXPLAIN plan for the following query:
63 SELECT * FROM tbl1 AS foo;
65 The EXPLAIN output will show access to table "foo", and that must be
67 translated back to "tbl1". This process involves complex parsing. It
68 is generally very accurate, but there is some chance that it might not
69 work right. If you find cases where it fails, submit a bug report and
70 a reproducible test case.
71 Queries that cannot be EXPLAIN'ed will cause all subsequent queries
73 with the same fingerprint to be blacklisted. This is to reduce the
74 work they cause, and prevent them from continuing to print error
75 messages. However, at least in this stage of the tool's development,
76 it is my opinion that it's not a good idea to pre-emtively silence
77 these, or prevent them from being EXPLAIN'ed at all. I am looking for
78 lots of feedback on how to improve things like the query parsing. So
79 please submit your test cases based on the errors the tool prints!
80
82 After it reads all the events in the log, the tool prints out DROP
83 statements for every index that was not used. It skips indexes for
84 tables that were never accessed by any queries in the log, to avoid
85 false-positive results.
86 If you don't specify "--quiet", the tool also outputs warnings about
88 statements that cannot be EXPLAIN'ed and similar. These go to standard
89 error.
90 Progress reports are enabled by default (see "--progress"). These also
92 go to standard error.
93
95 --ask-pass
96 Prompt for a password when connecting to MySQL.
97 --charset
99 short form: -A; type: string
100 Default character set. If the value is utf8, sets Perl's binmode
102 on STDOUT to utf8, passes the mysql_enable_utf8 option to
103 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
104 other value sets binmode on STDOUT without the utf8 layer, and runs
105 SET NAMES after connecting to MySQL.
106 --config
108 type: Array
109 Read this comma-separated list of config files; if specified, this
111 must be the first option on the command line.
112 --defaults-file
114 short form: -F; type: string
115 Only read mysql options from the given file. You must give an
117 absolute pathname.
118 --drop
120 type: Hash; default: non-unique
121 Suggest dropping only these types of unusued indexes.
123 By default mk-index-usage will only suggest to drop unused
125 secondary indexes, not primary or unique indexes. You can specify
126 which types of unused indexes the tool suggests to drop: primary,
127 unique, non-unique, all.
128 A separate "ALTER TABLE" statement for each type is printed. So if
130 you specify "--drop all" and there is a primary key and a non-
131 unique index, the "ALTER TABLE ... DROP" for each will be printed
132 on separate lines.
133 --help
135 Show help and exit.
136 --host
138 short form: -h; type: string
139 Connect to host.
141 --password
143 short form: -p; type: string
144 Password to use when connecting.
146 --port
148 short form: -P; type: int
149 Port number to use for connection.
151 --progress
153 type: array; default: time,30
154 Print progress reports to STDERR. The value is a comma-separated
156 list with two parts. The first part can be percentage, time, or
157 iterations; the second part specifies how often an update should be
158 printed, in percentage, seconds, or number of iterations.
159 --quiet
161 short form: -q
162 Do not print any warnings. Also disables "--progress".
164 --set-vars
166 type: string; default: wait_timeout=10000
167 Set these MySQL variables. Immediately after connecting to MySQL,
169 this string will be appended to SET and executed.
170 --socket
172 short form: -S; type: string
173 Socket file to use for connection.
175 --user
177 short form: -u; type: string
178 User for login if not current user.
180 --version
182 Show version and exit.
183
185 These DSN options are used to create a DSN. Each option is given like
186 "option=value". The options are case-sensitive, so P and p are not the
187 same option. There cannot be whitespace before or after the "=" and if
188 the value contains whitespace it must be quoted. DSN options are
189 comma-separated. See the maatkit manpage for full details.
190 · A
192 dsn: charset; copy: yes
194 Default character set.
196 · D
198 dsn: database; copy: yes
200 Database to connect to.
202 · F
204 dsn: mysql_read_default_file; copy: yes
206 Only read default options from the given file
208 · h
210 dsn: host; copy: yes
212 Connect to host.
214 · p
216 dsn: password; copy: yes
218 Password to use when connecting.
220 · P
222 dsn: port; copy: yes
224 Port number to use for connection.
226 · S
228 dsn: mysql_socket; copy: yes
230 Socket file to use for connection.
232 · u
234 dsn: user; copy: yes
236 User for login if not current user.
238
240 You can download Maatkit from Google Code at
241 <http://code.google.com/p/maatkit/>, or you can get any of the tools
242 easily with a command like the following:
243 wget http://www.maatkit.org/get/toolname
245 or
246 wget http://www.maatkit.org/trunk/toolname
247 Where "toolname" can be replaced with the name (or fragment of a name)
249 of any of the Maatkit tools. Once downloaded, they're ready to run; no
250 installation is needed. The first URL gets the latest released version
251 of the tool, and the second gets the latest trunk code from Subversion.
252
254 The environment variable "MKDEBUG" enables verbose debugging output in
255 all of the Maatkit tools:
256 MKDEBUG=1 mk-....
258
260 You need Perl and some core packages that ought to be installed in any
261 reasonably new version of Perl.
262
264 For list of known bugs see http://www.maatkit.org/bugs/mk-index-usage
265 <http://www.maatkit.org/bugs/mk-index-usage>.
266 Please use Google Code Issues and Groups to report bugs or request
268 support: <http://code.google.com/p/maatkit/>. You can also join
269 #maatkit on Freenode to discuss Maatkit.
270 Please include the complete command-line used to reproduce the problem
272 you are seeing, the version of all MySQL servers involved, the complete
273 output of the tool when run with "--version", and if possible,
274 debugging output produced by running with the "MKDEBUG=1" environment
275 variable.
276
278 This program is copyright 2010 Baron Schwartz. Feedback and
279 improvements are welcome.
280 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
282 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
283 MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
284 This program is free software; you can redistribute it and/or modify it
286 under the terms of the GNU General Public License as published by the
287 Free Software Foundation, version 2; OR the Perl Artistic License. On
288 UNIX and similar systems, you can issue `man perlgpl' or `man
289 perlartistic' to read these licenses.
290 You should have received a copy of the GNU General Public License along
292 with this program; if not, write to the Free Software Foundation, Inc.,
293 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
294
296 Baron Schwartz, Daniel Nichter
297
299 This tool is part of Maatkit, a toolkit for power users of MySQL.
300 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
301 primary code contributors. Both are employed by Percona. Financial
302 support for Maatkit development is primarily provided by Percona and
303 its clients.
304
306 This manual page documents Ver 0.9.0 Distrib 6839 $Revision: 6831 $.
307perl v5.12.1 2010-08-01 MK-INDEX-USAGE(1)