PT-TABLE-USAGE(1) User Contributed Perl Documentation PT-TABLE-USAGE(1)NAMEpt-table-usage - Analyze how queries use tables.
SYNOPSIS
Usage: pt-table-usage [OPTIONS] [FILES]
pt-table-usage reads queries from a log and analyzes how they use
tables. If no FILE is specified, it reads STDIN. It prints a report
for each query.
RISKS
Percona Toolkit is mature, proven in the real world, and well tested,
but all database tools can pose a risk to the system and the database
server. Before using this tool, please:
· Read the tool's documentation
· Review the tool's known "BUGS"
· Test the tool on a non-production server
· Backup your production server and verify the backups
DESCRIPTIONpt-table-usage reads queries from a log and analyzes how they use
tables. The log should be in MySQL's slow query log format.
Table usage is more than simply an indication of which tables the query
reads or writes. It also indicates data flow: data in and data out.
The tool determines the data flow by the contexts in which tables
appear. A single query can use a table in several different contexts
simultaneously. The tool's output lists every context for every table.
This CONTEXT-TABLE list indicates how data flows between tables. The
"OUTPUT" section lists the possible contexts and describes how to read
a table usage report.
The tool analyzes data flow down to the level of individual columns, so
it is helpful if columns are identified unambiguously in the query. If
a query uses only one table, then all columns must be from that table,
and there's no difficulty. But if a query uses multiple tables and the
column names are not table-qualified, then it is necessary to use
"EXPLAIN EXTENDED", followed by "SHOW WARNINGS", to determine to which
tables the columns belong.
If the tool does not know the query's default database, which can occur
when the database is not printed in the log, then "EXPLAIN EXTENDED"
can fail. In this case, you can specify a default database with
"--database". You can also use the "--create-table-definitions" option
to help resolve ambiguities.
OUTPUT
The tool prints a usage report for each table in every query, similar
to the following:
Query_id: 0x1CD27577D202A339.1
UPDATE t1
SELECT DUAL
JOIN t1
JOIN t2
WHERE t1
Query_id: 0x1CD27577D202A339.2
UPDATE t2
SELECT DUAL
JOIN t1
JOIN t2
WHERE t1
The first line contains the query ID, which by default is the same as
those shown in pt-query-digest reports. It is an MD5 checksum of the
query's "fingerprint," which is what remains after removing literals,
collapsing white space, and a variety of other transformations. The
query ID has two parts separated by a period: the query ID and the
table number. If you wish to use a different value to identify the
query, you can specify the "--id-attribute" option.
The previous example shows two paragraphs for a single query, not two
queries. Note that the query ID is identical for the two, but the
table number differs. The table number increments by 1 for each table
that the query updates. Only multi-table UPDATE queries can update
multiple tables with a single query, so the table number is 1 for all
other types of queries. (The tool does not support multi-table DELETE
queries.) The example output above is from this query:
UPDATE t1 AS a JOIN t2 AS b USING (id)
SET a.foo="bar", b.foo="bat"
WHERE a.id=1;
The "SET" clause indicates that the query updates two tables: "a"
aliased as "t1", and "b" aliased as "t2".
After the first line, the tool prints a variable number of CONTEXT-
TABLE lines. Possible contexts are as follows:
· SELECT
SELECT means that the query retrieves data from the table for one
of two reasons. The first is to be returned to the user as part of
a result set. Only SELECT queries return result sets, so the report
always shows a SELECT context for SELECT queries.
The second case is when data flows to another table as part of an
INSERT or UPDATE. For example, the UPDATE query in the example
above has the usage:
SELECT DUAL
This refers to:
SET a.foo="bar", b.foo="bat"
The tool uses DUAL for any values that do not originate in a table,
in this case the literal values "bar" and "bat". If that "SET"
clause were "SET a.foo=b.foo" instead, then the complete usage
would be:
Query_id: 0x1CD27577D202A339.1
UPDATE t1
SELECT t2
JOIN t1
JOIN t2
WHERE t1
The presence of a SELECT context after another context, such as
UPDATE or INSERT, indicates where the UPDATE or INSERT retrieves
its data. The example immediately above reflects an UPDATE query
that updates rows in table "t1" with data from table "t2".
· Any other verb
Any other verb, such as INSERT, UPDATE, DELETE, etc. may be a
context. These verbs indicate that the query modifies data in some
way. If a SELECT context follows one of these verbs, then the
query reads data from the SELECT table and writes it to this table.
This happens, for example, with INSERT..SELECT or UPDATE queries
that use values from tables instead of constant values.
These query types are not supported: SET, LOAD, and multi-table
DELETE.
· JOIN
The JOIN context lists tables that are joined, either with an
explicit JOIN in the FROM clause, or implicitly in the WHERE
clause, such as "t1.id = t2.id".
· WHERE
The WHERE context lists tables that are used in the WHERE clause to
filter results. This does not include tables that are implicitly
joined in the WHERE clause; those are listed as JOIN contexts. For
example:
WHERE t1.id > 100 AND t1.id < 200 AND t2.foo IS NOT NULL
Results in:
WHERE t1
WHERE t2
The tool lists only distinct tables; that is why table "t1" is
listed only once.
· TLIST
The TLIST context lists tables that the query accesses, but which
do not appear in any other context. These tables are usually an
implicit cartesian join. For example, the query "SELECT * FROM t1,
t2" results in:
Query_id: 0xBDDEB6EDA41897A8.1
SELECT t1
SELECT t2
TLIST t1
TLIST t2
First of all, there are two SELECT contexts, because "SELECT *"
selects rows from all tables; "t1" and "t2" in this case.
Secondly, the tables are implicitly joined, but without any kind of
join condition, which results in a cartesian join as indicated by
the TLIST context for each.
EXIT STATUSpt-table-usage exits 1 on any kind of error, or 0 if no errors.
OPTIONS
This tool accepts additional command-line arguments. Refer to the
"SYNOPSIS" and usage information for details.
--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.
--constant-data-value
type: string; default: DUAL
Table to print as the source for constant data (literals). This is
any data not retrieved from tables (or subqueries, because
subqueries are not supported). This includes literal values such
as strings ("foo") and numbers (42), or functions such as "NOW()".
For example, in the query "INSERT INTO t (c) VALUES ('a')", the
string 'a' is constant data, so the table usage report is:
INSERT t
SELECT DUAL
The first line indicates that the query inserts data into table
"t", and the second line indicates that the inserted data comes
from some constant value.
--[no]continue-on-error
default: yes
Continue to work even if there is an error.
--create-table-definitions
type: array
Read "CREATE TABLE" definitions from this list of comma-separated
files. If you cannot use "--explain-extended" to fully qualify
table and column names, you can save the output of "mysqldump
--no-data" to one or more files and specify those files with this
option. The tool will parse all "CREATE TABLE" definitions from
the files and use this information to qualify table and column
names. If a column name appears in multiple tables, or a table
name appears in multiple databases, the ambiguities cannot be
resolved.
--daemonize
Fork to the background and detach from the shell. POSIX operating
systems only.
--database
short form: -D; type: string
Default database.
--defaults-file
short form: -F; type: string
Only read mysql options from the given file. You must give an
absolute pathname.
--explain-extended
type: DSN
A server to execute EXPLAIN EXTENDED queries. This may be necessary
to resolve ambiguous (unqualified) column and table names.
--filter
type: string
Discard events for which this Perl code doesn't return true.
This option is a string of Perl code or a file containing Perl code
that is compiled into a subroutine with one argument: $event. If
the given value is a readable file, then pt-table-usage reads the
entire file and uses its contents as the code.
Filters are implemented in the same fashion as in the pt-query-
digest tool, so please refer to its documentation for more
information.
--help
Show help and exit.
--host
short form: -h; type: string
Connect to host.
--id-attribute
type: string
Identify each event using this attribute. The default is to use a
query ID, which is an MD5 checksum of the query's fingerprint.
--log
type: string
Print all output to this file when daemonized.
--password
short form: -p; type: string
Password to use when connecting.
--pid
type: string
Create the given PID file. The tool won't start if the PID file
already exists and the PID it contains is different than the
current PID. However, if the PID file exists and the PID it
contains is no longer running, the tool will overwrite the PID file
with the current PID. The PID file is removed automatically when
the tool exits.
--port
short form: -P; type: int
Port number to use for connection.
--progress
type: array; default: time,30
Print progress reports to STDERR. The value is a comma-separated
list with two parts. The first part can be percentage, time, or
iterations; the second part specifies how often an update should be
printed, in percentage, seconds, or number of iterations.
--query
type: string
Analyze the specified query instead of reading a log file.
--read-timeout
type: time; default: 0
Wait this long for an event from the input; 0 to wait forever.
This option sets the maximum time to wait for an event from the
input. If an event is not received after the specified time, the
tool stops reading the input and prints its reports.
This option requires the Perl POSIX module.
--run-time
type: time
How long to run before exiting. The default is to run forever (you
can interrupt with CTRL-C).
--set-vars
type: Array
Set the MySQL variables in this comma-separated list of
"variable=value" pairs.
By default, the tool sets:
wait_timeout=10000
Variables specified on the command line override these defaults.
For example, specifying "--set-vars wait_timeout=500" overrides the
defaultvalue of 10000.
The tool prints a warning and continues if a variable cannot be
set.
--socket
short form: -S; type: string
Socket file to use for connection.
--user
short form: -u; type: string
User for login if not current user.
--version
Show version and exit.
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 percona-toolkit manpage for full details.
· A
dsn: charset; copy: yes
Default character set.
· D
copy: no
Default database.
· F
dsn: mysql_read_default_file; copy: no
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: no
Socket file to use for connection.
· u
dsn: user; copy: yes
User for login if not current user.
ENVIRONMENT
The environment variable "PTDEBUG" enables verbose debugging output to
STDERR. To enable debugging and capture all output to a file, run the
tool like:
PTDEBUG=1 pt-table-usage ... > FILE 2>&1
Be careful: debugging output is voluminous and can generate several
megabytes of output.
SYSTEM REQUIREMENTS
You need Perl, DBI, DBD::mysql, and some core packages that ought to be
installed in any reasonably new version of Perl.
BUGS
For a list of known bugs, see
<http://www.percona.com/bugs/pt-table-usage>.
Please report bugs at <https://bugs.launchpad.net/percona-toolkit>.
Include the following information in your bug report:
· Complete command-line used to run the tool
· Tool "--version"
· MySQL version of all servers involved
· Output from the tool including STDERR
· Input files (log/dump/config files, etc.)
If possible, include debugging output by running the tool with
"PTDEBUG"; see "ENVIRONMENT".
DOWNLOADING
Visit <http://www.percona.com/software/percona-toolkit/> to download
the latest release of Percona Toolkit. Or, get the latest release from
the command line:
wget percona.com/get/percona-toolkit.tar.gz
wget percona.com/get/percona-toolkit.rpm
wget percona.com/get/percona-toolkit.deb
You can also get individual tools from the latest release:
wget percona.com/get/TOOL
Replace "TOOL" with the name of any tool.
AUTHORS
Daniel Nichter
ABOUT PERCONA TOOLKIT
This tool is part of Percona Toolkit, a collection of advanced command-
line tools for MySQL developed by Percona. Percona Toolkit was forked
from two projects in June, 2011: Maatkit and Aspersa. Those projects
were created by Baron Schwartz and primarily developed by him and
Daniel Nichter. Visit <http://www.percona.com/software/> to learn
about other free, open-source software from Percona.
COPYRIGHT, LICENSE, AND WARRANTY
This program is copyright 2012-2015 Percona LLC and/or its affiliates.
THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
MERCHANTABILITY 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.
VERSIONpt-table-usage 2.2.14
perl v5.20.2 2015-04-10 PT-TABLE-USAGE(1)
