.\" Automatically generated by Pod::Man 4.11 (Pod::Simple 3.35) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' . ds C` . ds C' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is >0, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .\" .\" Avoid warning from groff about undefined register 'F'. .de IX .. .nr rF 0 .if \n(.g .if rF .nr rF 1 .if (\n(rF:(\n(.g==0)) \{\ . if \nF \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{\ . nr % 0 . nr F 2 . \} . \} .\} .rr rF .\" ======================================================================== .\" .IX Title "PT-TABLE-USAGE 1p" .TH PT-TABLE-USAGE 1p "2020-08-30" "perl v5.30.3" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "NAME" pt\-table\-usage \- Analyze how queries use tables. .SH "SYNOPSIS" .IX Header "SYNOPSIS" Usage: pt-table-usage [\s-1OPTIONS\s0] [\s-1FILES\s0] .PP pt-table-usage reads queries from a log and analyzes how they use tables. If no \&\s-1FILE\s0 is specified, it reads \s-1STDIN.\s0 It prints a report for each query. .SH "RISKS" .IX Header "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: .IP "\(bu" 4 Read the tool's documentation .IP "\(bu" 4 Review the tool's known \*(L"\s-1BUGS\*(R"\s0 .IP "\(bu" 4 Test the tool on a non-production server .IP "\(bu" 4 Backup your production server and verify the backups .SH "DESCRIPTION" .IX Header "DESCRIPTION" 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. .PP 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 \*(L"\s-1OUTPUT\*(R"\s0 section lists the possible contexts and describes how to read a table usage report. .PP 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 \f(CW\*(C`EXPLAIN EXTENDED\*(C'\fR, followed by \&\f(CW\*(C`SHOW WARNINGS\*(C'\fR, to determine to which tables the columns belong. .PP If the tool does not know the query's default database, which can occur when the database is not printed in the log, then \f(CW\*(C`EXPLAIN EXTENDED\*(C'\fR can fail. In this case, you can specify a default database with \*(L"\-\-database\*(R". You can also use the \*(L"\-\-create\-table\-definitions\*(R" option to help resolve ambiguities. .SH "OUTPUT" .IX Header "OUTPUT" The tool prints a usage report for each table in every query, similar to the following: .PP .Vb 6 \& 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 .Ve .PP The first line contains the query \s-1ID,\s0 which by default is the same as those shown in pt-query-digest reports. It is an \s-1MD5\s0 checksum of the query's \&\*(L"fingerprint,\*(R" which is what remains after removing literals, collapsing white space, and a variety of other transformations. The query \s-1ID\s0 has two parts separated by a period: the query \s-1ID\s0 and the table number. If you wish to use a different value to identify the query, you can specify the \*(L"\-\-id\-attribute\*(R" option. .PP The previous example shows two paragraphs for a single query, not two queries. Note that the query \s-1ID\s0 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 \s-1UPDATE\s0 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 \s-1DELETE\s0 queries.) The example output above is from this query: .PP .Vb 3 \& UPDATE t1 AS a JOIN t2 AS b USING (id) \& SET a.foo="bar", b.foo="bat" \& WHERE a.id=1; .Ve .PP The \f(CW\*(C`SET\*(C'\fR clause indicates that the query updates two tables: \f(CW\*(C`a\*(C'\fR aliased as \&\f(CW\*(C`t1\*(C'\fR, and \f(CW\*(C`b\*(C'\fR aliased as \f(CW\*(C`t2\*(C'\fR. .PP After the first line, the tool prints a variable number of CONTEXT-TABLE lines. Possible contexts are as follows: .IP "\(bu" 4 \&\s-1SELECT\s0 .Sp \&\s-1SELECT\s0 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 \&\s-1SELECT\s0 queries return result sets, so the report always shows a \s-1SELECT\s0 context for \s-1SELECT\s0 queries. .Sp The second case is when data flows to another table as part of an \s-1INSERT\s0 or \&\s-1UPDATE.\s0 For example, the \s-1UPDATE\s0 query in the example above has the usage: .Sp .Vb 1 \& SELECT DUAL .Ve .Sp This refers to: .Sp .Vb 1 \& SET a.foo="bar", b.foo="bat" .Ve .Sp The tool uses \s-1DUAL\s0 for any values that do not originate in a table, in this case the literal values \*(L"bar\*(R" and \*(L"bat\*(R". If that \f(CW\*(C`SET\*(C'\fR clause were \f(CW\*(C`SET a.foo=b.foo\*(C'\fR instead, then the complete usage would be: .Sp .Vb 6 \& Query_id: 0x1CD27577D202A339.1 \& UPDATE t1 \& SELECT t2 \& JOIN t1 \& JOIN t2 \& WHERE t1 .Ve .Sp The presence of a \s-1SELECT\s0 context after another context, such as \s-1UPDATE\s0 or \&\s-1INSERT,\s0 indicates where the \s-1UPDATE\s0 or \s-1INSERT\s0 retrieves its data. The example immediately above reflects an \s-1UPDATE\s0 query that updates rows in table \f(CW\*(C`t1\*(C'\fR with data from table \f(CW\*(C`t2\*(C'\fR. .IP "\(bu" 4 Any other verb .Sp Any other verb, such as \s-1INSERT, UPDATE, DELETE,\s0 etc. may be a context. These verbs indicate that the query modifies data in some way. If a \s-1SELECT\s0 context follows one of these verbs, then the query reads data from the \s-1SELECT\s0 table and writes it to this table. This happens, for example, with \s-1INSERT..SELECT\s0 or \&\s-1UPDATE\s0 queries that use values from tables instead of constant values. .Sp These query types are not supported: \s-1SET, LOAD,\s0 and multi-table \s-1DELETE.\s0 .IP "\(bu" 4 \&\s-1JOIN\s0 .Sp The \s-1JOIN\s0 context lists tables that are joined, either with an explicit \s-1JOIN\s0 in the \s-1FROM\s0 clause, or implicitly in the \s-1WHERE\s0 clause, such as \f(CW\*(C`t1.id = t2.id\*(C'\fR. .IP "\(bu" 4 \&\s-1WHERE\s0 .Sp The \s-1WHERE\s0 context lists tables that are used in the \s-1WHERE\s0 clause to filter results. This does not include tables that are implicitly joined in the \&\s-1WHERE\s0 clause; those are listed as \s-1JOIN\s0 contexts. For example: .Sp .Vb 1 \& WHERE t1.id > 100 AND t1.id < 200 AND t2.foo IS NOT NULL .Ve .Sp Results in: .Sp .Vb 2 \& WHERE t1 \& WHERE t2 .Ve .Sp The tool lists only distinct tables; that is why table \f(CW\*(C`t1\*(C'\fR is listed only once. .IP "\(bu" 4 \&\s-1TLIST\s0 .Sp The \s-1TLIST\s0 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 \f(CW\*(C`SELECT * FROM t1, t2\*(C'\fR results in: .Sp .Vb 5 \& Query_id: 0xBDDEB6EDA41897A8.1 \& SELECT t1 \& SELECT t2 \& TLIST t1 \& TLIST t2 .Ve .Sp First of all, there are two \s-1SELECT\s0 contexts, because \f(CW\*(C`SELECT *\*(C'\fR selects rows from all tables; \f(CW\*(C`t1\*(C'\fR and \f(CW\*(C`t2\*(C'\fR 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 \s-1TLIST\s0 context for each. .SH "EXIT STATUS" .IX Header "EXIT STATUS" pt-table-usage exits 1 on any kind of error, or 0 if no errors. .SH "OPTIONS" .IX Header "OPTIONS" This tool accepts additional command-line arguments. Refer to the \&\*(L"\s-1SYNOPSIS\*(R"\s0 and usage information for details. .IP "\-\-ask\-pass" 4 .IX Item "--ask-pass" Prompt for a password when connecting to MySQL. .IP "\-\-charset" 4 .IX Item "--charset" short form: \-A; type: string .Sp Default character set. If the value is utf8, sets Perl's binmode on \&\s-1STDOUT\s0 to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs \s-1SET NAMES UTF8\s0 after connecting to MySQL. Any other value sets binmode on \s-1STDOUT\s0 without the utf8 layer, and runs \s-1SET NAMES\s0 after connecting to MySQL. .IP "\-\-config" 4 .IX Item "--config" type: Array .Sp Read this comma-separated list of config files; if specified, this must be the first option on the command line. .IP "\-\-constant\-data\-value" 4 .IX Item "--constant-data-value" type: string; default: \s-1DUAL\s0 .Sp 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 (\*(L"foo\*(R") and numbers (42), or functions such as \f(CW\*(C`NOW()\*(C'\fR. For example, in the query \f(CW\*(C`INSERT INTO t (c) VALUES (\*(Aqa\*(Aq)\*(C'\fR, the string 'a' is constant data, so the table usage report is: .Sp .Vb 2 \& INSERT t \& SELECT DUAL .Ve .Sp The first line indicates that the query inserts data into table \f(CW\*(C`t\*(C'\fR, and the second line indicates that the inserted data comes from some constant value. .IP "\-\-[no]continue\-on\-error" 4 .IX Item "--[no]continue-on-error" default: yes .Sp Continue to work even if there is an error. .IP "\-\-create\-table\-definitions" 4 .IX Item "--create-table-definitions" type: array .Sp Read \f(CW\*(C`CREATE TABLE\*(C'\fR definitions from this list of comma-separated files. If you cannot use \*(L"\-\-explain\-extended\*(R" to fully qualify table and column names, you can save the output of \f(CW\*(C`mysqldump \-\-no\-data\*(C'\fR to one or more files and specify those files with this option. The tool will parse all \&\f(CW\*(C`CREATE TABLE\*(C'\fR 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. .IP "\-\-daemonize" 4 .IX Item "--daemonize" Fork to the background and detach from the shell. \s-1POSIX\s0 operating systems only. .IP "\-\-database" 4 .IX Item "--database" short form: \-D; type: string .Sp Default database. .IP "\-\-defaults\-file" 4 .IX Item "--defaults-file" short form: \-F; type: string .Sp Only read mysql options from the given file. You must give an absolute pathname. .IP "\-\-explain\-extended" 4 .IX Item "--explain-extended" type: \s-1DSN\s0 .Sp A server to execute \s-1EXPLAIN EXTENDED\s0 queries. This may be necessary to resolve ambiguous (unqualified) column and table names. .IP "\-\-filter" 4 .IX Item "--filter" type: string .Sp Discard events for which this Perl code doesn't return true. .Sp This option is a string of Perl code or a file containing Perl code that is compiled into a subroutine with one argument: \f(CW$event\fR. If the given value is a readable file, then pt-table-usage reads the entire file and uses its contents as the code. .Sp Filters are implemented in the same fashion as in the pt-query-digest tool, so please refer to its documentation for more information. .IP "\-\-help" 4 .IX Item "--help" Show help and exit. .IP "\-\-host" 4 .IX Item "--host" short form: \-h; type: string .Sp Connect to host. .IP "\-\-id\-attribute" 4 .IX Item "--id-attribute" type: string .Sp Identify each event using this attribute. The default is to use a query \s-1ID,\s0 which is an \s-1MD5\s0 checksum of the query's fingerprint. .IP "\-\-log" 4 .IX Item "--log" type: string .Sp Print all output to this file when daemonized. .IP "\-\-password" 4 .IX Item "--password" short form: \-p; type: string .Sp Password to use when connecting. If password contains commas they must be escaped with a backslash: \*(L"exam\e,ple\*(R" .IP "\-\-pid" 4 .IX Item "--pid" type: string .Sp Create the given \s-1PID\s0 file. The tool won't start if the \s-1PID\s0 file already exists and the \s-1PID\s0 it contains is different than the current \s-1PID.\s0 However, if the \s-1PID\s0 file exists and the \s-1PID\s0 it contains is no longer running, the tool will overwrite the \s-1PID\s0 file with the current \s-1PID.\s0 The \s-1PID\s0 file is removed automatically when the tool exits. .IP "\-\-port" 4 .IX Item "--port" short form: \-P; type: int .Sp Port number to use for connection. .IP "\-\-progress" 4 .IX Item "--progress" type: array; default: time,30 .Sp Print progress reports to \s-1STDERR.\s0 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. .IP "\-\-query" 4 .IX Item "--query" type: string .Sp Analyze the specified query instead of reading a log file. .IP "\-\-read\-timeout" 4 .IX Item "--read-timeout" type: time; default: 0 .Sp Wait this long for an event from the input; 0 to wait forever. .Sp 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. .Sp This option requires the Perl \s-1POSIX\s0 module. .IP "\-\-run\-time" 4 .IX Item "--run-time" type: time .Sp How long to run before exiting. The default is to run forever (you can interrupt with CTRL-C). .IP "\-\-set\-vars" 4 .IX Item "--set-vars" type: Array .Sp Set the MySQL variables in this comma-separated list of \f(CW\*(C`variable=value\*(C'\fR pairs. .Sp By default, the tool sets: .Sp .Vb 1 \& wait_timeout=10000 .Ve .Sp Variables specified on the command line override these defaults. For example, specifying \f(CW\*(C`\-\-set\-vars wait_timeout=500\*(C'\fR overrides the defaultvalue of \f(CW10000\fR. .Sp The tool prints a warning and continues if a variable cannot be set. .IP "\-\-socket" 4 .IX Item "--socket" short form: \-S; type: string .Sp Socket file to use for connection. .IP "\-\-user" 4 .IX Item "--user" short form: \-u; type: string .Sp User for login if not current user. .IP "\-\-version" 4 .IX Item "--version" Show version and exit. .SH "DSN OPTIONS" .IX Header "DSN OPTIONS" These \s-1DSN\s0 options are used to create a \s-1DSN.\s0 Each option is given like \&\f(CW\*(C`option=value\*(C'\fR. The options are case-sensitive, so P and p are not the same option. There cannot be whitespace before or after the \f(CW\*(C`=\*(C'\fR and if the value contains whitespace it must be quoted. \s-1DSN\s0 options are comma-separated. See the percona-toolkit manpage for full details. .IP "\(bu" 4 A .Sp dsn: charset; copy: yes .Sp Default character set. .IP "\(bu" 4 D .Sp copy: no .Sp Default database. .IP "\(bu" 4 F .Sp dsn: mysql_read_default_file; copy: no .Sp Only read default options from the given file .IP "\(bu" 4 h .Sp dsn: host; copy: yes .Sp Connect to host. .IP "\(bu" 4 p .Sp dsn: password; copy: yes .Sp Password to use when connecting. If password contains commas they must be escaped with a backslash: \*(L"exam\e,ple\*(R" .IP "\(bu" 4 P .Sp dsn: port; copy: yes .Sp Port number to use for connection. .IP "\(bu" 4 S .Sp dsn: mysql_socket; copy: no .Sp Socket file to use for connection. .IP "\(bu" 4 u .Sp dsn: user; copy: yes .Sp User for login if not current user. .SH "ENVIRONMENT" .IX Header "ENVIRONMENT" The environment variable \f(CW\*(C`PTDEBUG\*(C'\fR enables verbose debugging output to \s-1STDERR.\s0 To enable debugging and capture all output to a file, run the tool like: .PP .Vb 1 \& PTDEBUG=1 pt\-table\-usage ... > FILE 2>&1 .Ve .PP Be careful: debugging output is voluminous and can generate several megabytes of output. .SH "SYSTEM REQUIREMENTS" .IX Header "SYSTEM REQUIREMENTS" You need Perl, \s-1DBI,\s0 DBD::mysql, and some core packages that ought to be installed in any reasonably new version of Perl. .SH "BUGS" .IX Header "BUGS" For a list of known bugs, see . .PP Please report bugs at . Include the following information in your bug report: .IP "\(bu" 4 Complete command-line used to run the tool .IP "\(bu" 4 Tool \*(L"\-\-version\*(R" .IP "\(bu" 4 MySQL version of all servers involved .IP "\(bu" 4 Output from the tool including \s-1STDERR\s0 .IP "\(bu" 4 Input files (log/dump/config files, etc.) .PP If possible, include debugging output by running the tool with \f(CW\*(C`PTDEBUG\*(C'\fR; see \*(L"\s-1ENVIRONMENT\*(R"\s0. .SH "DOWNLOADING" .IX Header "DOWNLOADING" Visit to download the latest release of Percona Toolkit. Or, get the latest release from the command line: .PP .Vb 1 \& wget percona.com/get/percona\-toolkit.tar.gz \& \& wget percona.com/get/percona\-toolkit.rpm \& \& wget percona.com/get/percona\-toolkit.deb .Ve .PP You can also get individual tools from the latest release: .PP .Vb 1 \& wget percona.com/get/TOOL .Ve .PP Replace \f(CW\*(C`TOOL\*(C'\fR with the name of any tool. .SH "AUTHORS" .IX Header "AUTHORS" Daniel Nichter .SH "ABOUT PERCONA TOOLKIT" .IX Header "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 to learn about other free, open-source software from Percona. .SH "COPYRIGHT, LICENSE, AND WARRANTY" .IX Header "COPYRIGHT, LICENSE, AND WARRANTY" This program is copyright 2012\-2018 Percona \s-1LLC\s0 and/or its affiliates. .PP \&\s-1THIS PROGRAM IS PROVIDED \*(L"AS IS\*(R" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.\s0 .PP This program is free software; you can redistribute it and/or modify it under the terms of the \s-1GNU\s0 General Public License as published by the Free Software Foundation, version 2; \s-1OR\s0 the Perl Artistic License. On \s-1UNIX\s0 and similar systems, you can issue `man perlgpl' or `man perlartistic' to read these licenses. .PP You should have received a copy of the \s-1GNU\s0 General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, \s-1MA\s0 02111\-1307 \s-1USA.\s0 .SH "VERSION" .IX Header "VERSION" pt-table-usage 3.2.1