Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
PT-TABLE-USAGE(1)     User Contributed Perl Documentation    PT-TABLE-USAGE(1)

       pt-table-usage -	Analyze	how queries use	tables.

       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.

       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:

       o   Read	the tool's documentation

       o   Review the tool's known "BUGS"

       o   Test	the tool on a non-production server

       o   Backup your production server and verify the	backups

       pt-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.

       The tool	prints a usage report for each table in	every query, similar
       to the following:

	 Query_id: 0x1CD27577D202A339.1
	 JOIN t1
	 JOIN t2
	 WHERE t1

	 Query_id: 0x1CD27577D202A339.2
	 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)

       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:

       o   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:


	   This	refers to:


	   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" 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".

       o   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

       o   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 " =".

       o   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

	     WHERE > 100 AND < 200 AND IS NOT NULL

	   Results in:

	     WHERE t1
	     WHERE t2

	   The tool lists only distinct	tables;	that is	why table "t1" is
	   listed only once.

       o   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.

       pt-table-usage exits 1 on any kind of error, or 0 if no errors.

       This tool accepts additional command-line arguments.  Refer to the
       "SYNOPSIS" and usage information	for details.

	   Prompt for a	password when connecting to MySQL.

	   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.

	   type: Array

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

	   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

	   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.

	   default: yes

	   Continue to work even if there is an	error.

	   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

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

	   short form: -D; type: string

	   Default database.

	   short form: -F; type: string

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

	   type: DSN

	   A server to execute EXPLAIN EXTENDED	queries. This may be necessary
	   to resolve ambiguous	(unqualified) column and table names.

	   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

	   Show	help and exit.

	   short form: -h; type: string

	   Connect to host.

	   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.

	   type: string

	   Print all output to this file when daemonized.

	   short form: -p; type: string

	   Password to use when	connecting.  If	password contains commas they
	   must	be escaped with	a backslash: "exam\,ple"

	   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.

	   short form: -P; type: int

	   Port	number to use for connection.

	   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.

	   type: string

	   Analyze the specified query instead of reading a log	file.

	   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.

	   type: time

	   How long to run before exiting.  The	default	is to run forever (you
	   can interrupt with CTRL-C).

	   type: Array

	   Set the MySQL variables in this comma-separated list	of
	   "variable=value" pairs.

	   By default, the tool	sets:


	   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

	   short form: -S; type: string

	   Socket file to use for connection.

	   short form: -u; type: string

	   User	for login if not current user.

	   Show	version	and exit.

       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.

       o   A

	   dsn:	charset; copy: yes

	   Default character set.

       o   D

	   copy: no

	   Default database.

       o   F

	   dsn:	mysql_read_default_file; copy: no

	   Only	read default options from the given file

       o   h

	   dsn:	host; copy: yes

	   Connect to host.

       o   p

	   dsn:	password; copy:	yes

	   Password to use when	connecting.  If	password contains commas they
	   must	be escaped with	a backslash: "exam\,ple"

       o   P

	   dsn:	port; copy: yes

	   Port	number to use for connection.

       o   S

	   dsn:	mysql_socket; copy: no

	   Socket file to use for connection.

       o   u

	   dsn:	user; copy: yes

	   User	for login if not current user.

       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.

       You need	Perl, DBI, DBD::mysql, and some	core packages that ought to be
       installed in any	reasonably new version of Perl.

       For a list of known bugs, see

       Please report bugs at <>.
       Include the following information in your bug report:

       o   Complete command-line used to run the tool

       o   Tool	"--version"

       o   MySQL version of all	servers	involved

       o   Output from the tool	including STDERR

       o   Input files (log/dump/config	files, etc.)

       If possible, include debugging output by	running	the tool with
       "PTDEBUG"; see "ENVIRONMENT".

       Visit <>	to download
       the latest release of Percona Toolkit.  Or, get the latest release from
       the command line:




       You can also get	individual tools from the latest release:


       Replace "TOOL" with the name of any tool.

       Daniel Nichter

       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 <> to learn
       about other free, open-source software from Percona.

       This program is copyright 2012-2016 Percona LLC and/or its affiliates.


       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.

       pt-table-usage 2.2.17

perl v5.24.1			  2016-03-07		     PT-TABLE-USAGE(1)


Want to link to this manual page? Use this URL:

home | help