PT-TABLE-USAGE(1)

📦
version 3.7.1_4 · 5 versions
, in arch rolling

✨ NAME ✨

pt-table-usage — analyze how queries use tables. 💅 That's the whole vibe, bestie: it reads queries and tells you how they use tables. This is the pt-table-usage(1) manual, and we're gonna get through it together. 🫶

🎀 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 ⚠️

Okay so real talk for a sec: 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 💾
Like, actually do these. No cap. 🙈

💖 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. 🐬

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 STATUS 🚪

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

✨ OPTIONS ✨

Okay bestie, this tool takes extra command-line arguments on top of everything else 💅 — peep the "SYNOPSIS" and the usage info for the full rundown.

  • --ask-pass — makes it ask you for a password when it connects to MySQL 🙈
  • --[no]buffer-stdout — default: yes ⭐

Turns STDOUT buffering on. Turn this off if you wanna watch live progress while piping stuff through output post-processing tools like tee or kubectl logs — otherwise you're just staring at nothing, and nobody has time for that 😭

  • --charset — short form: -A; type: string 🎀

Sets the default character set. If the value is utf8, it 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. Slay, encoding 💖

  • --config — type: Array 🌸

Reads this comma-separated list of config files. Lowkey important: if you specify it, it must be the first option on the command line. First or nothing, periodt 👑

  • --constant-data-value — type: string; default: DUAL 🦋

The table it prints as the source for constant data (literals). "Constant data" = literally any data that did not come from tables (or subqueries, because subqueries are simply not supported, okay?). So that's literal values like strings ("foo") and numbers (42), or functions like NOW(). Like, in the query INSERT INTO t (c) VALUES ('a'), the string 'a' is constant data, so the table usage report comes out as:

INSERT t
SELECT DUAL

First line = the query inserts data into table t. Second line = the inserted data came from some constant value. Easy 💅

  • --[no]continue-on-error — default: yes ✨

Keeps going even if there's an error. Resilient queen energy 🫶

  • --create-table-definitions — type: array 🎀

Reads CREATE TABLE definitions from this list of comma-separated files. So if you can't use --explain-extended to fully qualify table and column names, you can save the output of mysqldump --no-data into one or more files and hand those files over with this option. The tool parses all the CREATE TABLE definitions from the files and uses that info to qualify table and column names. Heads up though: if a column name shows up in multiple tables, or a table name shows up in multiple databases, those ambiguities cannot be resolved 💀

  • --daemonize — forks to the background and detaches from the shell. POSIX operating systems only, bestie 🧚
  • --database — short form: -D; type: string

Default database.

  • --defaults-file — short form: -F; type: string

Only reads mysql options from the given file. And you must give an absolute pathname — no shortcuts 🔥

  • --explain-extended — type: DSN 🌈

A server to execute EXPLAIN EXTENDED queries on. This may be necessary to resolve ambiguous (unqualified) column and table names. Basically the backup plan for when names are vague 💜

  • --filter — type: string 🦋

Discards 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 gets compiled into a subroutine with one argument: $event. If the value you give it is a readable file, then pt-table-usage reads the entire file and uses its contents as the code.

Filters work exactly the same way as in the pt-query-digest tool, so go read its documentation for more info, iykyk 📖

  • --help — shows help and exits 👋
  • --host — short form: -h; type: string

Connect to host.

  • --id-attribute — type: string ⭐

Identifies each event using this attribute. The default is to use a query ID, which is an MD5 checksum of the query's fingerprint. Main character identification 💅

  • --log — type: string

Prints all output to this file when daemonized.

  • --mysql_ssl — short form: -s; type: int 🔒

Creates an SSL MySQL connection.

  • --password — short form: -p; type: string 🙈

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

  • --pid — type: string 🎀

Creates the given PID file. The tool won't start if the PID file already exists and the PID inside it is different from the current PID. But if the PID file exists and the PID inside it is no longer running, the tool will overwrite the PID file with the current PID. The PID file gets removed automatically when the tool exits. Self-cleaning icon ✨

  • --port — short form: -P; type: int

Port number to use for connection.

  • --progress — type: array; default: time,30 🌸

Prints progress reports to STDERR. The value is a comma-separated list with two parts. First part can be percentage, time, or iterations; second part says how often an update should be printed — in percentage, seconds, or number of iterations.

  • --query — type: string 🔥

Analyzes the specified query instead of reading a log file. One query, all the attention 👑

  • --read-timeout — type: time; default: 0 ⏳

Waits this long for an event from the input; 0 means wait forever.

This option sets the max time to wait for an event from the input. If no event arrives 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. Default is to run forever — you can interrupt with Ctrl+C.

  • --set-vars — type: Array 💜

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

By default, the tool sets:

wait_timeout=10000

Variables you specify on the command line override those defaults. For example, specifying --set-vars wait_timeout=500 overrides the default value of 10000.

If a variable can't be set, the tool prints a warning and keeps going. No drama 🫶

  • --socket — short form: -S; type: string

Socket file to use for connection.

  • --user — short form: -u; type: string

User for login if not the current user.

  • --version — shows version and exits ⭐

🦋 DSN OPTIONS 🦋

These DSN options are how you build a DSN. Each option is given like option=value. Bestie, they are case-sensitive, so P and p are absolutely not the same option — do not play with me on this 😭. There can't be whitespace before or after the =, and if the value contains whitespace it has to be quoted. DSN options are comma-separated. See the percona-toolkit manpage for the 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. If the password contains commas they must be escaped with a backslash: "exam\,ple"

  • 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 the current user.

  • s — dsn: mysql_ssl; copy: yes 🔒

Create SSL connection.

🌈 ENVIRONMENT 🌈

The environment variable PTDEBUG enables verbose debugging output to STDERR. To enable debugging and capture all the output in a file, run the tool like this:

PTDEBUG=1 pt-table-usage ... > FILE 2>&1
Be careful bestie — debugging output is voluminous and can generate several megabytes of output. Like, megabytes. Do not say I didn't warn you 💀

💀 ATTENTION 💀

Using PTDEBUG might expose passwords. When debug is enabled, all command-line parameters get shown in the output. So maybe don't do this on a livestream 🫣

🧚 SYSTEM REQUIREMENTS 🧚

You need Perl, DBI, DBD::mysql, and some core packages that ought to be installed in any reasonably new version of Perl. That's the whole vibe check ✅

🐛 BUGS 🐛

For a list of known bugs, see <https://jira.percona.com/projects/PT/issues>.

Please report bugs at <https://jira.percona.com/projects/PT>. Include the following information in your bug report, bestie — this is the whole receipts situation:

  • 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" above 💖

🎀 DOWNLOADING 🎀

Visit <http://www.percona.com/software/percona-toolkit/> to download the latest release of Percona Toolkit. Or, get the latest release straight from the command line, no browser needed:

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. Mix and match, it's giving build-your-own-adventure 🌸

👑 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. Iconic lineage honestly 🫶

This program is copyright 2012-2026 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, version 2 along with this program; if not, see <https://www.gnu.org/licenses/>.

🍓 VERSION 🍓

pt-table-usage 3.7.1-4

And that's the whole page, bestie! This little masterpiece was brought to you by perl v5.42.2, dated 2026-09-01 — page PT-TABLE-USAGE(1), logged off and living her best life 💅✨🎀

📦 where this page comes from