14
pt-query-advisor - Analyze queries and advise on possible problems.
22
Usage: pt-query-advisor [OPTION...] [FILE]
24
pt-query-advisor analyzes queries and advises on possible problems.
25
Queries are given either by specifying slowlog files, --query, or --review.
30
# Analyzer all queries in the given slowlog
31
pt-query-advisor /path/to/slow-query.log
33
# Get queries from tcpdump using pt-query-digest
34
pt-query-digest --type tcpdump.txt --print --no-report | pt-query-advisor
36
# Get queries from a general log
37
pt-query-advisor --type genlog mysql.log
46
The following section is included to inform users about the potential risks,
47
whether known or unknown, of using this tool. The two main categories of risks
48
are those created by the nature of the tool (e.g. read-only tools vs. read-write
49
tools) and those created by bugs.
51
pt-query-advisor simply reads queries and examines them, and is thus
54
At the time of this release there is a bug that may cause an infinite (or
55
very long) loop when parsing very large queries.
57
The authoritative source for updated information is always the online issue
58
tracking system. Issues that affect this tool will be marked as such. You can
59
see a list of such issues at the following URL:
60
`http://www.percona.com/bugs/pt-query-advisor <http://www.percona.com/bugs/pt-query-advisor>`_.
62
See also "BUGS" for more information on filing bugs and getting help.
70
pt-query-advisor examines queries and applies rules to them, trying to
71
find queries that look bad according to the rules. It reports on
72
queries that match the rules, so you can find bad practices or hidden
73
problems in your SQL. By default, it accepts a MySQL slow query log
82
These are the rules that pt-query-advisor will apply to the queries it
83
examines. Each rule has three bits of information: an ID, a severity
86
The rule's ID is its identifier. We use a seven-character ID, and the
87
naming convention is three characters, a period, and a three-digit
88
number. The first three characters are sort of an abbreviation of the
89
general class of the rule. For example, ALI.001 is some rule related
90
to how the query uses aliases.
92
The rule's severity is an indication of how important it is that this
93
rule matched a query. We use NOTE, WARN, and CRIT to denote these
96
The rule's description is a textual, human-readable explanation of
97
what it means when a query matches this rule. Depending on the
98
verbosity of the report you generate, you will see more of the text in
99
the description. By default, you'll see only the first sentence,
100
which is sort of a terse synopsis of the rule's meaning. At a higher
101
verbosity, you'll see subsequent sentences.
108
Aliasing without the AS keyword. Explicitly using the AS keyword in
109
column or table aliases, such as "tbl AS alias," is more readable
110
than implicit aliases such as "tbl alias".
118
Aliasing the '\*' wildcard. Aliasing a column wildcard, such as
119
"SELECT tbl.\* col1, col2" probably indicates a bug in your SQL.
120
You probably meant for the query to retrieve col1, but instead it
121
renames the last column in the \*-wildcarded list.
129
Aliasing without renaming. The table or column's alias is the same as
130
its real name, and the alias just makes the query harder to read.
138
Argument with leading wildcard. An argument has a leading
139
wildcard character, such as "%foo". The predicate with this argument
140
is not sargable and cannot use an index if one exists.
148
LIKE without a wildcard. A LIKE pattern that does not include a
149
wildcard is potentially a bug in the SQL.
157
SELECT without WHERE. The SELECT statement has no WHERE clause.
165
ORDER BY RAND(). ORDER BY RAND() is a very inefficient way to
166
retrieve a random row from the results.
174
LIMIT with OFFSET. Paginating a result set with LIMIT and OFFSET is
175
O(n^2) complexity, and will cause performance problems as the data
184
Ordinal in the GROUP BY clause. Using a number in the GROUP BY clause,
185
instead of an expression or column name, can cause problems if the
194
ORDER BY constant column.
202
GROUP BY or ORDER BY different tables will force a temp table and filesort.
210
ORDER BY different directions prevents index from being used. All tables
211
in the ORDER BY clause must be either ASC or DESC, else MySQL cannot use
220
SELECT \*. Selecting all columns with the \* wildcard will cause the
221
query's meaning and behavior to change if the table's schema
222
changes, and might cause the query to retrieve too much data.
230
Blind INSERT. The INSERT or REPLACE query doesn't specify the
231
columns explicitly, so the query's behavior will change if the
232
table's schema changes; use "INSERT INTO tbl(col1, col2) VALUES..."
241
Storing an IP address as characters. The string literal looks like
242
an IP address, but is not an argument to INET_ATON(), indicating that
243
the data is stored as characters instead of as integers. It is
244
more efficient to store IP addresses as integers.
252
Unquoted date/time literal. A query such as "WHERE col<2010-02-12"
253
is valid SQL but is probably a bug; the literal should be quoted.
261
SQL_CALC_FOUND_ROWS is inefficient. SQL_CALC_FOUND_ROWS can cause
262
performance problems because it does not scale well; use
263
alternative strategies to build functionality such as paginated
272
Mixing comma and ANSI joins. Mixing comma joins and ANSI joins
273
is confusing to humans, and the behavior differs between some
282
A table is joined twice. The same table appears at least twice in the
291
Reference to outer table column in WHERE clause prevents OUTER JOIN,
292
implicitly converts to INNER JOIN.
300
Exclusion join uses wrong column in WHERE. The exclusion join (LEFT
301
OUTER JOIN with a WHERE clause that is satisfied only if there is no row in
302
the right-hand table) seems to use the wrong column in the WHERE clause. A
303
query such as "... FROM l LEFT OUTER JOIN r ON l.l=r.r WHERE r.z IS NULL"
304
probably ought to list r.r in the WHERE IS NULL clause.
312
Non-deterministic GROUP BY. The SQL retrieves columns that are
313
neither in an aggregate function nor the GROUP BY expression, so
314
these values will be non-deterministic in the result.
322
LIMIT without ORDER BY. LIMIT without ORDER BY causes
323
non-deterministic results, depending on the query execution plan.
331
!= is non-standard. Use the <> operator to test for inequality.
339
IN() and NOT IN() subqueries are poorly optimized. MySQL executes the subquery
340
as a dependent subquery for each row in the outer query. This is a frequent
341
cause of serious performance problems. This might change version 6.0 of MySQL,
342
but for versions 5.1 and older, the query should be rewritten as a JOIN or a
343
LEFT OUTER JOIN, respectively.
353
"--query" and "--review" are mutually exclusive.
355
This tool accepts additional command-line arguments. Refer to the
356
"SYNOPSIS" and usage information for details.
361
Prompt for a password when connecting to MySQL.
367
short form: -A; type: string
369
Default character set. If the value is utf8, sets Perl's binmode on
370
STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and
371
runs SET NAMES UTF8 after connecting to MySQL. Any other value sets
372
binmode on STDOUT without the utf8 layer, and runs SET NAMES after
381
Read this comma-separated list of config files; if specified, this must be the
382
first option on the command line.
386
--[no]continue-on-error
390
Continue working even if there is an error.
396
Fork to the background and detach from the shell. POSIX
397
operating systems only.
403
short form: -D; type: string
405
Connect to this database. This is also used as the default database
406
for "--[no]show-create-table" if a query does not use database-qualified
413
short form: -F; type: string
415
Only read mysql options from the given file. You must give an absolute
422
type: string; default: rule_id
424
Group items in the report by this attribute. Possible attributes are:
430
========= ==========================================================
431
rule_id Items matching the same rule ID
432
query_id Queries with the same ID (the same fingerprint)
433
none No grouping, report each query and its advice individually
446
short form: -h; type: string
456
Ignore these rule IDs.
458
Specify a comma-separated list of rule IDs (e.g. LIT.001,RES.002,etc.)
459
to ignore. Currently, the rule IDs are case-sensitive and must be uppercase.
465
short form: -p; type: string
467
Password to use when connecting.
475
Create the given PID file when daemonized. The file contains the process
476
ID of the daemonized instance. The PID file is removed when the
477
daemonized instance exits. The program checks for the existence of the
478
PID file when starting; if it exists and the process with the matching PID
479
exists, the program exits.
485
short form: -P; type: int
487
Port number to use for connection.
493
Print all queries, even those that do not match any rules. With
494
"--group-by" \ ``none``\ , non-matching queries are printed in the main report
495
and profile. For other "--group-by" values, non-matching queries are only
496
printed in the profile. Non-matching queries have zeros for \ ``NOTE``\ , \ ``WARN``\
497
and \ ``CRIT``\ in the profile.
505
Analyze this single query and ignore files and STDIN. This option
506
allows you to supply a single query on the command line. Any files
507
also specified on the command line are ignored.
513
type: string; default: compact
515
Type of report format: full or compact. In full mode, every query's
516
report contains the description of the rules it matched, even if this
517
information was previously displayed. In compact mode, the repeated
518
information is suppressed, and only the rule ID is displayed.
526
Analyze queries from this pt-query-digest query review table.
532
type: int; default: 1
534
How many samples of the query to show.
540
type: string; default: wait_timeout=10000
542
Set these MySQL variables. Immediately after connecting to MySQL, this string
543
will be appended to SET and executed.
547
--[no]show-create-table
551
Get \ ``SHOW CREATE TABLE``\ for each query's table.
553
If host connection options are given (like "--host", "--port", etc.)
554
then the tool will also get \ ``SHOW CREATE TABLE``\ for each query. This
555
information is needed for some rules like JOI.004. If this option is
556
disabled by specifying \ ``--no-show-create-table``\ then some rules may not
563
short form: -S; type: string
565
Socket file to use for connection.
573
The type of input to parse (default slowlog). The permitted types are
580
short form: -u; type: string
582
User for login if not current user.
588
short form: -v; cumulative: yes; default: 1
590
Increase verbosity of output. At the default level of verbosity, the
591
program prints only the first sentence of each rule's description. At
592
higher levels, the program prints more of the description. See also
599
Show version and exit.
607
Apply this WHERE clause to the SELECT query on the "--review" table.
617
These DSN options are used to create a DSN. Each option is given like
618
\ ``option=value``\ . The options are case-sensitive, so P and p are not the
619
same option. There cannot be whitespace before or after the \ ``=``\ and
620
if the value contains whitespace it must be quoted. DSN options are
621
comma-separated. See the percona-toolkit manpage for full details.
626
dsn: charset; copy: yes
628
Default character set.
634
dsn: database; copy: yes
636
Database that contains the query review table.
642
dsn: mysql_read_default_file; copy: yes
644
Only read default options from the given file
658
dsn: password; copy: yes
660
Password to use when connecting.
668
Port number to use for connection.
674
dsn: mysql_socket; copy: yes
676
Socket file to use for connection.
682
Table to use as the query review table.
690
User for login if not current user.
700
Visit `http://www.percona.com/software/ <http://www.percona.com/software/>`_ to download the latest release of
701
Percona Toolkit. Or, to get the latest release from the command line:
706
wget percona.com/latest/percona-toolkit/PKG
709
Replace \ ``PKG``\ with \ ``tar``\ , \ ``rpm``\ , or \ ``deb``\ to download the package in that
710
format. You can also get individual tools from the latest release:
715
wget percona.com/latest/percona-toolkit/TOOL
718
Replace \ ``TOOL``\ with the name of any tool.
726
The environment variable \ ``PTDEBUG``\ enables verbose debugging output to STDERR.
727
To enable debugging and capture all output to a file, run the tool like:
732
PTDEBUG=1 pt-query-advisor ... > FILE 2>&1
735
Be careful: debugging output is voluminous and can generate several megabytes
744
You need Perl, DBI, DBD::mysql, and some core packages that ought to be
745
installed in any reasonably new version of Perl.
753
For a list of known bugs, see `http://www.percona.com/bugs/pt-query-advisor <http://www.percona.com/bugs/pt-query-advisor>`_.
755
Please report bugs at `https://bugs.launchpad.net/percona-toolkit <https://bugs.launchpad.net/percona-toolkit>`_.
756
Include the following information in your bug report:
759
\* Complete command-line used to run the tool
767
\* MySQL version of all servers involved
771
\* Output from the tool including STDERR
775
\* Input files (log/dump/config files, etc.)
779
If possible, include debugging output by running the tool with \ ``PTDEBUG``\ ;
788
Baron Schwartz and Daniel Nichter
791
*********************
792
ABOUT PERCONA TOOLKIT
793
*********************
796
This tool is part of Percona Toolkit, a collection of advanced command-line
797
tools developed by Percona for MySQL support and consulting. Percona Toolkit
798
was forked from two projects in June, 2011: Maatkit and Aspersa. Those
799
projects were created by Baron Schwartz and developed primarily by him and
800
Daniel Nichter, both of whom are employed by Percona. Visit
801
`http://www.percona.com/software/ <http://www.percona.com/software/>`_ for more software developed by Percona.
804
********************************
805
COPYRIGHT, LICENSE, AND WARRANTY
806
********************************
809
This program is copyright 2010-2011 Percona Inc.
810
Feedback and improvements are welcome.
812
THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
813
WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
814
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
816
This program is free software; you can redistribute it and/or modify it under
817
the terms of the GNU General Public License as published by the Free Software
818
Foundation, version 2; OR the Perl Artistic License. On UNIX and similar
819
systems, you can issue \`man perlgpl' or \`man perlartistic' to read these
822
You should have received a copy of the GNU General Public License along with
823
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
824
Place, Suite 330, Boston, MA 02111-1307 USA.
832
Percona Toolkit v1.0.0 released 2011-08-01
added
removed
docs/user/pt-query-advisor.rst
