14
pt-query-profiler - Execute SQL statements and print statistics, or measure
15
activity caused by other processes.
23
Usage: pt-query-profiler [OPTION...] [FILE...]
25
pt-query-profiler reads and executes queries, and prints statistics about
26
MySQL server load. Connection options are read from MySQL option files.
27
If FILE is given, queries are read and executed from the file(s). With no
28
FILE, or when FILE is -, read standard input. If --external is specified,
29
lines in FILE are executed by the shell. You must specify - if no FILE and
30
you want --external to read and execute from standard input. Queries in
31
FILE must be terminated with a semicolon and separated by a blank line.
33
pt-query-profiler can profile the (semicolon-terminated, blank-line
34
separated) queries in a file:
39
pt-query-profiler queries.sql
40
cat queries.sql | pt-query-profiler
41
pt-query-profiler -vv queries.sql
42
pt-query-profiler -v --separate --only 2,5,6 queries.sql
43
pt-query-profiler --tab queries.sql > results.csv
46
It can also just observe what happens in the server:
51
pt-query-profiler --external
54
Or it can run shell commands from a file and measure the result:
59
pt-query-profiler --external commands.txt
60
pt-query-profiler --external - < commands.txt
63
Read "HOW TO INTERPRET" to learn what it all means.
71
The following section is included to inform users about the potential risks,
72
whether known or unknown, of using this tool. The two main categories of risks
73
are those created by the nature of the tool (e.g. read-only tools vs. read-write
74
tools) and those created by bugs.
76
pt-query-profiler is generally read-only and very low risk. It will execute FLUSH TABLES if you specify "--flush".
78
At the time of this release, we know of no bugs that could cause serious harm to
81
The authoritative source for updated information is always the online issue
82
tracking system. Issues that affect this tool will be marked as such. You can
83
see a list of such issues at the following URL:
84
`http://www.percona.com/bugs/pt-query-profiler <http://www.percona.com/bugs/pt-query-profiler>`_.
86
See also "BUGS" for more information on filing bugs and getting help.
94
pt-query-profiler reads a file containing one or more SQL statements or shell
95
commands, executes them, and analyzes the output of SHOW STATUS afterwards.
96
It then prints statistics about how the batch performed. For example, it can
97
show how many table scans the batch caused, how many page reads, how many
98
temporary tables, and so forth.
100
All command-line arguments are optional, but you must either specify a file
101
containing the batch to profile as the last argument, or specify that you're
102
profiling an external program with the "--external" option, or provide
105
If the file contains multiple statements, they must be separated by blank
106
lines. If you don't do that, pt-query-profiler won't be able to split the
107
file into individual queries, and MySQL will complain about syntax errors.
109
If the MySQL server version is before 5.0.2, you should make sure the server
110
is completely unused before trying to profile a batch. Prior to this version,
111
SHOW STATUS showed only global status variables, so other queries will
112
interfere and produce false results. pt-query-profiler will try to detect
113
if anything did interfere, but there can be no guarantees.
115
Prior to MySQL 5.0.2, InnoDB status variables are not available, and prior to
116
version 5.0.3, InnoDB row lock status variables are not available.
117
pt-query-profiler will omit any output related to these variables if they're not
120
For more information about SHOW STATUS, read the relevant section of the MySQL
122
`http://dev.mysql.com/doc/en/server-status-variables.html <http://dev.mysql.com/doc/en/server-status-variables.html>`_
134
If you specify "--tab", you will get the raw output of SHOW STATUS in
135
tab-separated format, convenient for opening with a spreadsheet. This is not
136
the default output, but it's so much easier to describe that I'll cover it
142
Most of the command-line options for controlling verbosity and such are
143
ignored in --tab mode.
149
The variable names you see in MySQL, such as 'Com_select', are kept --
150
there are no euphimisms, so you have to know your MySQL variables.
156
The columns are Variable_name, Before, After1...AfterN, Calibration.
157
The Variable_name column is just what it sounds like. Before is the result
158
from the first run of SHOW STATUS. After1, After2, etc are the results of
159
running SHOW STATUS after each query in the batch. Finally, the last column
160
is the result of running SHOW STATUS just after the last AfterN column, so you
161
can see how much work SHOW STATUS itself causes.
167
If you specify "--verbose", output includes every variable
168
pt-query-profiler measures. If not (default) it only includes variables where
169
there was some difference from one column to the next.
178
If you don't specify --tab, you'll get a report formatted for human
179
readability. This is the default output format.
181
pt-query-profiler can output a lot of information, as you've seen if you
182
ran the examples in the "SYNOPSIS". What does it all mean?
184
First, there are two basic groups of information you might see: per-query and
185
summary. If your batch contains only one query, these will be the same and
186
you'll only see the summary. You can recognize the difference by looking for
187
centered, all-caps, boxed-in section headers. Externally profiled commands will
188
have EXTERNAL, individually profiled queries will have QUERY, and summary will
191
Next, the information in each section is grouped into subsections, headed by
192
an underlined title. Each of these sections has varying information in it.
193
Which sections you see depends on command-line arguments and your MySQL
194
version. I'll explain each section briefly. If you really want to know where
195
the numbers come from, read
196
`http://dev.mysql.com/doc/en/server-status-variables.html <http://dev.mysql.com/doc/en/server-status-variables.html>`_.
198
You need to understand which numbers are insulated from other queries and
199
which are not. This depends on your MySQL version. Version 5.0.2 introduced
200
the concept of session status variables, so you can see information about only
201
your own connection. However, many variables aren't session-ized, so when you
202
have MySQL 5.0.2 or greater, you will actually see a mix of session and global
203
variables. That means other queries happening at the same time will pollute
204
some of your results. If you have MySQL versions older than 5.0.2, you won't
205
have ANY connection-specific stats, so your results will be polluted by other
206
queries no matter what. Because of the mixture of session and global
207
variables, by far the best way to profile is on a completely quiet server
208
where nothing else is interfering with your results.
210
While explaining the results in the sections that follow, I'll refer to a
211
value as "protected" if it comes from a session-specific variable and can be
212
relied upon to be accurate even on a busy server. Just keep in mind, if
213
you're not using MySQL 5.0.2 or newer, your results will be inaccurate unless
214
you're running against a totally quiet server, even if I label it as
222
This section shows the overall elapsed time for the query, as measured by
223
Perl, and the optimizer cost as reported by MySQL.
225
If you're viewing separate query statistics, this is all you'll see. If
226
you're looking at a summary, you'll also see a breakdown of the questions the
227
queries asked the server.
229
The execution time is not totally reliable, as it includes network round-trip
230
time, Perl's own execution time, and so on. However, on a low-latency
231
network, this should be fairly negligible, giving you a reasonable measure of
232
the query's time, especially for queries longer than a few tenths of a second.
234
The optimizer cost comes from the Last_query_cost variable, and is protected
235
from other connections in MySQL 5.0.7 and greater. It is not available before
238
The total number of questions is not protected, but the breakdown of
239
individual question types is, because it comes from the Com_ status variables.
242
Table and index accesses
243
========================
246
This section shows you information about the batch's table and index-level
247
operations (as opposed to row-level operations, which will be in the next
248
section). The "Table locks acquired" and "Temp files" values are unprotected,
249
but everything else in this section is protected.
251
The "Potential filesorts" value is calculated as the number of times a query had
252
both a scan sort (Sort_scan) and created a temporary table (Created_tmp_tables).
253
There is no Sort_filesort or similar status value, so it's a best guess at
254
whether a query did a filesort. It should be fairly accurate.
256
If you specified "--allow-cache", you'll see statistics on the query cache.
257
These are unprotected.
264
These values are all about the row-level operations your batch caused. For
265
example, how many rows were inserted, updated, or deleted. You'll also see
266
row-level index access statistics, such as how many times the query sought and
267
read the next entry in an index.
269
Depending on your MySQL version, you'll either see one or two columns of
270
information in this section. The one headed "Handler" is all from the
271
Handler_ variables, and those statistics are protected. If your MySQL version
272
supports it, you'll also see a column headed "InnoDB," which is unprotected.
279
This section gives information on I/O operations your batch caused, both in
280
memory and on disk. Unless you have MySQL 5.0.2 or greater, you'll only see
281
information on the key cache. Otherwise, you'll see a lot of information on
282
InnoDB's I/O operations as well, such as how many times the query was able to
283
satisfy a read from the buffer pool and how many times it had to go to the
286
None of the information in this section is protected.
289
InnoDB Data Operations
290
======================
293
This section only appears when you're querying MySQL 5.0.2 or newer. None of
294
the information is protected. You'll see statistics about how many pages were
295
affected, how many operations took place, and how many bytes were affected.
304
This tool accepts additional command-line arguments. Refer to the
305
"SYNOPSIS" and usage information for details.
310
Let MySQL query cache cache the queries executed.
312
By default this is disabled. When enabled, cache profiling information is added
313
to the printout. See `http://dev.mysql.com/doc/en/query-cache.html <http://dev.mysql.com/doc/en/query-cache.html>`_ for more
314
information about the query cache.
320
Prompt for a password when connecting to MySQL.
328
Try to compensate for \ ``SHOW STATUS``\ .
330
Measure and compensate for the "cost of observation" caused by running SHOW
331
STATUS. Only works reliably on a quiet server; on a busy server, other
332
processes can cause the calibration to be wrong.
338
short form: -A; type: string
340
Default character set. If the value is utf8, sets Perl's binmode on
341
STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and
342
runs SET NAMES UTF8 after connecting to MySQL. Any other value sets
343
binmode on STDOUT without the utf8 layer, and runs SET NAMES after
352
Read this comma-separated list of config files; if specified, this must be the
353
first option on the command line.
359
short form: -D; type: string
361
Database to use for connection.
367
short form: -F; type: string
369
Only read mysql options from the given file. You must give an absolute
376
Calibrate, then pause while an external program runs.
378
This is typically useful while you run an external program. When you press
379
[enter] pt-query-profiler will stop sleeping and take another measurement, then
380
print statistics as usual.
382
When there is a filename on the command line, pt-query-profiler executes
383
each line in the file as a shell command. If you give - as the filename,
384
pt-query-profiler reads from STDIN.
386
Output from shell commands is printed to STDOUT and terminated with __BEGIN__,
387
after which pt-query-profiler prints its own output.
395
Flush tables. Specify twice to do between every query.
397
Calls FLUSH TABLES before profiling. If you are executing queries from a
398
batch file, specifying --flush twice will cause pt-query-profiler to call
399
FLUSH TABLES between every query, not just once at the beginning. Default is
400
not to flush at all. See `http://dev.mysql.com/doc/en/flush.html <http://dev.mysql.com/doc/en/flush.html>`_ for more
413
short form: -h; type: string
423
Show InnoDB statistics.
431
Only show statistics for this comma-separated list of queries or commands.
437
short form: -p; type: string
439
Password to use when connecting.
447
Create the given PID file. The file contains the process ID of the script.
448
The PID file is removed when the script exits. Before starting, the script
449
checks if the PID file already exists. If it does not, then the script creates
450
and writes its own PID to it. If it does, then the script checks the following:
451
if the file contains a PID and a process is running with that PID, then
452
the script dies; or, if there is no process running with that PID, then the
453
script overwrites the file with its own PID and starts; else, if the file
454
contains no PID, then the script dies.
460
short form: -P; type: int
462
Port number to use for connection.
468
Print stats separately for each query.
470
The default is to show only the summary of the entire batch. See also
479
Use session \ ``SHOW STATUS``\ and \ ``SHOW VARIABLES``\ .
481
Disabled if the server version doesn't support it.
487
type: string; default: wait_timeout=10000
489
Set these MySQL variables. Immediately after connecting to MySQL, this string
490
will be appended to SET and executed.
496
short form: -S; type: string
498
Socket file to use for connection.
504
Print tab-separated values instead of whitespace-aligned columns.
510
short form: -u; type: string
512
User for login if not current user.
518
short form: -v; cumulative: yes; default: 0
520
Verbosity; specify multiple times for more detailed output.
522
When "--tab" is given, prints variables that don't change. Otherwise
523
increasing the level of verbosity includes extra sections in the output.
529
Verify nothing else is accessing the server.
531
This is a weak verification; it simply calibrates twice (see
532
"--[no]calibrate") and verifies that the cost of observation remains
539
Show version and exit.
549
These DSN options are used to create a DSN. Each option is given like
550
\ ``option=value``\ . The options are case-sensitive, so P and p are not the
551
same option. There cannot be whitespace before or after the \ ``=``\ and
552
if the value contains whitespace it must be quoted. DSN options are
553
comma-separated. See the percona-toolkit manpage for full details.
558
dsn: charset; copy: yes
560
Default character set.
566
dsn: database; copy: yes
574
dsn: mysql_read_default_file; copy: yes
576
Only read default options from the given file
590
dsn: password; copy: yes
592
Password to use when connecting.
600
Port number to use for connection.
606
dsn: mysql_socket; copy: yes
608
Socket file to use for connection.
616
User for login if not current user.
626
Visit `http://www.percona.com/software/ <http://www.percona.com/software/>`_ to download the latest release of
627
Percona Toolkit. Or, to get the latest release from the command line:
632
wget percona.com/latest/percona-toolkit/PKG
635
Replace \ ``PKG``\ with \ ``tar``\ , \ ``rpm``\ , or \ ``deb``\ to download the package in that
636
format. You can also get individual tools from the latest release:
641
wget percona.com/latest/percona-toolkit/TOOL
644
Replace \ ``TOOL``\ with the name of any tool.
652
The environment variable \ ``PTDEBUG``\ enables verbose debugging output to STDERR.
653
To enable debugging and capture all output to a file, run the tool like:
658
PTDEBUG=1 pt-query-profiler ... > FILE 2>&1
661
Be careful: debugging output is voluminous and can generate several megabytes
670
You need Perl, DBI, DBD::mysql, and some core packages that ought to be
671
installed in any reasonably new version of Perl.
679
For a list of known bugs, see `http://www.percona.com/bugs/pt-query-profiler <http://www.percona.com/bugs/pt-query-profiler>`_.
681
Please report bugs at `https://bugs.launchpad.net/percona-toolkit <https://bugs.launchpad.net/percona-toolkit>`_.
682
Include the following information in your bug report:
685
\* Complete command-line used to run the tool
693
\* MySQL version of all servers involved
697
\* Output from the tool including STDERR
701
\* Input files (log/dump/config files, etc.)
705
If possible, include debugging output by running the tool with \ ``PTDEBUG``\ ;
714
Baron Schwartz and Bart van Bragt
717
*********************
718
ABOUT PERCONA TOOLKIT
719
*********************
722
This tool is part of Percona Toolkit, a collection of advanced command-line
723
tools developed by Percona for MySQL support and consulting. Percona Toolkit
724
was forked from two projects in June, 2011: Maatkit and Aspersa. Those
725
projects were created by Baron Schwartz and developed primarily by him and
726
Daniel Nichter, both of whom are employed by Percona. Visit
727
`http://www.percona.com/software/ <http://www.percona.com/software/>`_ for more software developed by Percona.
730
********************************
731
COPYRIGHT, LICENSE, AND WARRANTY
732
********************************
735
This program is copyright 2007-2011 Baron Schwartz, 2011 Percona Inc.
736
Feedback and improvements are welcome.
738
THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
739
WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
740
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
742
This program is free software; you can redistribute it and/or modify it under
743
the terms of the GNU General Public License as published by the Free Software
744
Foundation, version 2; OR the Perl Artistic License. On UNIX and similar
745
systems, you can issue \`man perlgpl' or \`man perlartistic' to read these
748
You should have received a copy of the GNU General Public License along with
749
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
750
Place, Suite 330, Boston, MA 02111-1307 USA.
758
Percona Toolkit v1.0.0 released 2011-08-01
added
removed
docs/user/pt-query-profiler.rst
