Linux Certif

Toute la documentation sur la certification Linux LPI

mk-query-advisor.1p

NAME

mk-query-advisor - Analyze queries and advise on possible problems.

SYNOPSIS

    mk-query-advisor /path/to/slow-query.log
 
    # Get queries from tcpdump using mk-query-digest
    mk-query-digest --type tcpdump.txt --print --no-report | mk-query-advisor
 
 

RISKS

The following section is included to inform users about the potential risks, whether known or unknown, of using this tool. The two main categories of risks are those created by the nature of the tool (e.g. read-only tools vs. read-write tools) and those created by bugs.

mk-query-advisor simply reads queries and examines them, and is thus very low risk.

At the time of this release, we know of no bugs that could cause serious harm to users.

The authoritative source for updated information is always the online issue tracking system. Issues that affect this tool will be marked as such. You can see a list of such issues at the following URL: http://www.maatkit.org/bugs/mk-query-advisor <http://www.maatkit.org/bugs/mk-query-advisor>.

See also ``BUGS'' for more information on filing bugs and getting help.

DESCRIPTION

mk-query-advisor examines queries and applies rules to them, trying to find queries that look bad according to the rules. It reports on queries that match the rules, so you can find bad practices or hidden problems in your SQL. By default, it accepts a MySQL slow query log as input.

RULES

These are the rules that mk-query-advisor will apply to the queries it examines. Each rule has three bits of information: an ID, a severity and a description.

The rule's ID is its identifier. We use a seven-character ID, and the naming convention is three characters, a period, and a three-digit number. The first three characters are sort of an abbreviation of the general class of the rule. For example, ALI.001 is some rule related to how the query uses aliases.

The rule's severity is an indication of how important it is that this rule matched a query. We use NOTE, WARN, and CRIT to denote these levels.

The rule's description is a textual, human-readable explanation of what it means when a query matches this rule. Depending on the verbosity of the report you generate, you will see more of the text in the description. By default, you'll see only the first sentence, which is sort of a terse synopsis of the rule's meaning. At a higher verbosity, you'll see subsequent sentences.

ALI.001

severity: note

Aliasing without the AS keyword. Explicitly using the AS keyword in column or table aliases, such as ``tbl AS alias,'' is more readable than implicit aliases such as ``tbl alias''.

ALI.002

severity: warn

Aliasing the '*' wildcard. Aliasing a column wildcard, such as ``SELECT tbl.* col1, col2'' probably indicates a bug in your SQL. You probably meant for the query to retrive col1, but instead it renames the last column in the *-wildcarded list.

ALI.003

severity: note

Aliasing without renaming. The table or column's alias is the same as its real name, and the alias just makes the query harder to read.

ARG.001

severity: warn

Argument with leading wildcard. An argument has a leading wildcard character, such as ``%foo''. The predicate with this argument is not sargable and cannot use an index if one exists.

ARG.002

severity: note

LIKE without a wildcard. A LIKE pattern that does not include a wildcard is potentially a bug in the SQL.

CLA.001

severity: warn

SELECT without WHERE. The SELECT statement has no WHERE clause.

CLA.002

severity: note

ORDER BY RAND(). ORDER BY RAND() is a very inefficient way to retrieve a random row from the results.

CLA.003

severity: note

LIMIT with OFFSET. Paginating a result set with LIMIT and OFFSET is O(n^2) complexity, and will cause performance problems as the data grows larger.

CLA.004

severity: note

Ordinal in the GROUP BY clause. Using a number in the GROUP BY clause, instead of an expression or column name, can cause problems if the query is changed.

CLA.005

severity: warn

ORDER BY constant column.

COL.001

severity: note

SELECT *. Selecting all columns with the * wildcard will cause the query's meaning and behavior to change if the table's schema changes, and might cause the query to retrieve too much data.

COL.002

severity: note

Blind INSERT. The INSERT or REPLACE query doesn't specify the columns explicitly, so the query's behavior will change if the table's schema changes; use ``INSERT INTO tbl(col1, col2) VALUES...'' instead.

LIT.001

severity: warn

Storing an IP address as characters. The string literal looks like an IP address, but is not an argument to INET_ATON(), indicating that the data is stored as characters instead of as integers. It is more efficient to store IP addresses as integers.

LIT.002

severity: warn

Unquoted date/time literal. A query such as ``WHERE col<2010-02-12'' is valid SQL but is probably a bug; the literal should be quoted.

KWR.001

severity: note

SQL_CALC_FOUND_ROWS is inefficient. SQL_CALC_FOUND_ROWS can cause performance problems because it does not scale well; use alternative strategies to build functionality such as paginated result screens.

JOI.001

severity: crit

Mixing comma and ANSI joins. Mixing comma joins and ANSI joins is confusing to humans, and the behavior differs between some MySQL versions.

JOI.002

severity: crit

A table is joined twice. The same table appears at least twice in the FROM clause.

RES.001

severity: warn

Non-deterministic GROUP BY. The SQL retrieves columns that are neither in an aggregate function nor the GROUP BY expression, so these values will be non-deterministic in the result.

RES.002

severity: warn

LIMIT without ORDER BY. LIMIT without ORDER BY causes non-deterministic results, depending on the query execution plan.

STA.001

severity: note

!= is non-standard. Use the <> operator to test for inequality.

SUB.001

severity: crit

IN() and NOT IN() subqueries are poorly optimized. MySQL executes the subquery as a dependent subquery for each row in the outer query. This is a frequent cause of serious performance problems. This might change version 6.0 of MySQL, but for versions 5.1 and older, the query should be rewritten as a JOIN or a LEFT OUTER JOIN, respectively.

OPTIONS

``--query'' and ``--review'' are mutually exclusive.

--ask-pass

Prompt for a password when connecting to MySQL.

--charset

short form: -A; type: string

Default character set. If the value is utf8, sets Perl's binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL.

--config

type: Array

Read this comma-separated list of config files; if specified, this must be the first option on the command line.

--[no]continue-on-error

default: yes

Continue working even if there is an error.

--daemonize

Fork to the background and detach from the shell. POSIX operating systems only.

--defaults-file

short form: -F; type: string

Only read mysql options from the given file. You must give an absolute pathname.

--help

Show help and exit.

--host

short form: -h; type: string

Connect to host.

--ignore-rules

type: hash

Ignore these rule IDs.

Specify a comma-separated list of rule IDs (e.g. LIT.001,RES.002,etc.) to ignore. Currently, the rule IDs are case-sensitive and must be uppercase.

--password

short form: -p; type: string

Password to use when connecting.

--pid

type: string

Create the given PID file when daemonized. The file contains the process ID of the daemonized instance. The PID file is removed when the daemonized instance exits. The program checks for the existence of the PID file when starting; if it exists and the process with the matching PID exists, the program exits.

--port

short form: -P; type: int

Port number to use for connection.

--print-all

Print all queries, even those that do not match any rules.

--query

type: string

Analyze this single query and ignore files and STDIN. This option allows you to supply a single query on the command line. Any files also specified on the command line are ignored.

--report-format

type: string; default: compact

Type of report format: full or compact. In full mode, every query's report contains the description of the rules it matched, even if this information was previously displayed. In compact mode, the repeated information is suppressed, and only the rule ID is displayed.

--review

type: DSN

Analyze queries from this mk-query-digest query review table.

--sample

type: int; default: 1

How many samples of the query to show.

--set-vars

type: string; default: wait_timeout=10000

Set these MySQL variables. Immediately after connecting to MySQL, this string will be appended to SET and executed.

--socket

short form: -S; type: string

Socket file to use for connection.

--type

type: Array

The type of input to parse (default slowlog). The permitted types are slowlog and genlog.

--user

short form: -u; type: string

User for login if not current user.

--verbose

short form: -v; cumulative: yes; default: 1

Increase verbosity of output. At the default level of verbosity, the program prints only the first sentence of each rule's description. At higher levels, the program prints more of the description. See also ``--report-format''.

--version

Show version and exit.

--where

type: string

Apply this WHERE clause to the SELECT query on the ``--review'' table.

DSN OPTIONS

These DSN options are used to create a DSN. Each option is given like "option=value". The options are case-sensitive, so P and p are not the same option. There cannot be whitespace before or after the "=" and if the value contains whitespace it must be quoted. DSN options are comma-separated. See the maatkit manpage for full details.

*

A

dsn: charset; copy: yes

Default character set.

*

D

dsn: database; copy: yes

Database that contains the query review table.

*

F

dsn: mysql_read_default_file; copy: yes

Only read default options from the given file

*

h

dsn: host; copy: yes

Connect to host.

*

p

dsn: password; copy: yes

Password to use when connecting.

*

P

dsn: port; copy: yes

Port number to use for connection.

*

S

dsn: mysql_socket; copy: yes

Socket file to use for connection.

*

t

Table to use as the query review table.

*

u

dsn: user; copy: yes

User for login if not current user.

DOWNLOADING

You can download Maatkit from Google Code at <http://code.google.com/p/maatkit/>, or you can get any of the tools easily with a command like the following:

    wget http://www.maatkit.org/get/toolname
    or
    wget http://www.maatkit.org/trunk/toolname
 
 

Where "toolname" can be replaced with the name (or fragment of a name) of any of the Maatkit tools. Once downloaded, they're ready to run; no installation is needed. The first URL gets the latest released version of the tool, and the second gets the latest trunk code from Subversion.

ENVIRONMENT

The environment variable "MKDEBUG" enables verbose debugging output in all of the Maatkit tools:

    MKDEBUG=1 mk-....
 
 

SYSTEM REQUIREMENTS

You need the following Perl modules: DBI and DBD::mysql.

BUGS

For list of known bugs see http://www.maatkit.org/bugs/mk-query-advisor <http://www.maatkit.org/bugs/mk-query-advisor>.

Please use Google Code Issues and Groups to report bugs or request support: <http://code.google.com/p/maatkit/>. You can also join #maatkit on Freenode to discuss Maatkit.

Please include the complete command-line used to reproduce the problem you are seeing, the version of all MySQL servers involved, the complete output of the tool when run with ``--version'', and if possible, debugging output produced by running with the "MKDEBUG=1" environment variable.

COPYRIGHT, LICENSE AND WARRANTY

This program is copyright 2009-2010 Percona Inc. Feedback and improvements are welcome.

THIS PROGRAM IS PROVIDED ``AS IS'' AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, version 2; OR the Perl Artistic License. On UNIX and similar systems, you can issue `man perlgpl' or `man perlartistic' to read these licenses.

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.

AUTHOR

Baron Schwartz, Daniel Nichter

ABOUT MAATKIT

This tool is part of Maatkit, a toolkit for power users of MySQL. Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the primary code contributors. Both are employed by Percona. Financial support for Maatkit development is primarily provided by Percona and its clients.

VERSION

This manual page documents Ver 1.0.0 Distrib 6839 $Revision: 6831 $.