1MK-QUERY-PROFILER(1)  User Contributed Perl Documentation MK-QUERY-PROFILER(1)
2
3
4

NAME

6       mk-query-profiler - Execute SQL statements and print statistics, or
7       measure activity caused by other processes.
8

SYNOPSIS

10       Usage: mk-query-profiler [OPTION...] [FILE...]
11
12       mk-query-profiler reads and executes queries, and prints statistics
13       about MySQL server load.  Connection options are read from MySQL option
14       files.  If FILE is given, queries are read and executed from the
15       file(s).  With no FILE, or when FILE is -, read standard input.  If
16       --external is specified, lines in FILE are executed by the shell.  You
17       must specify - if no FILE and you want --external to read and execute
18       from standard input.  Queries in FILE must be terminated with a
19       semicolon and separated by a blank line.
20
21       mk-query-profiler can profile the (semicolon-terminated, blank-line
22       separated) queries in a file:
23
24          mk-query-profiler queries.sql
25          cat queries.sql | mk-query-profiler
26          mk-query-profiler -vv queries.sql
27          mk-query-profiler -v --separate --only 2,5,6 queries.sql
28          mk-query-profiler --tab queries.sql > results.csv
29
30       It can also just observe what happens in the server:
31
32          mk-query-profiler --external
33
34       Or it can run shell commands from a file and measure the result:
35
36          mk-query-profiler --external commands.txt
37          mk-query-profiler --external - < commands.txt
38
39       Read "HOW TO INTERPRET" to learn what it all means.
40

RISKS

42       The following section is included to inform users about the potential
43       risks, whether known or unknown, of using this tool.  The two main
44       categories of risks are those created by the nature of the tool (e.g.
45       read-only tools vs. read-write tools) and those created by bugs.
46
47       mk-query-profiler is generally read-only and very low risk.  It will
48       execute FLUSH TABLES if you specify "--flush".
49
50       At the time of this release, we know of no bugs that could cause
51       serious harm to users.
52
53       The authoritative source for updated information is always the online
54       issue tracking system.  Issues that affect this tool will be marked as
55       such.  You can see a list of such issues at the following URL:
56       <http://www.maatkit.org/bugs/mk-query-profiler>.
57
58       See also "BUGS" for more information on filing bugs and getting help.
59

DESCRIPTION

61       mk-query-profiler reads a file containing one or more SQL statements or
62       shell commands, executes them, and analyzes the output of SHOW STATUS
63       afterwards.  It then prints statistics about how the batch performed.
64       For example, it can show how many table scans the batch caused, how
65       many page reads, how many temporary tables, and so forth.
66
67       All command-line arguments are optional, but you must either specify a
68       file containing the batch to profile as the last argument, or specify
69       that you're profiling an external program with the "--external" option,
70       or provide input to STDIN.
71
72       If the file contains multiple statements, they must be separated by
73       blank lines.  If you don't do that, mk-query-profiler won't be able to
74       split the file into individual queries, and MySQL will complain about
75       syntax errors.
76
77       If the MySQL server version is before 5.0.2, you should make sure the
78       server is completely unused before trying to profile a batch.  Prior to
79       this version, SHOW STATUS showed only global status variables, so other
80       queries will interfere and produce false results.  mk-query-profiler
81       will try to detect if anything did interfere, but there can be no
82       guarantees.
83
84       Prior to MySQL 5.0.2, InnoDB status variables are not available, and
85       prior to version 5.0.3, InnoDB row lock status variables are not
86       available.  mk-query-profiler will omit any output related to these
87       variables if they're not available.
88
89       For more information about SHOW STATUS, read the relevant section of
90       the MySQL manual at
91       <http://dev.mysql.com/doc/en/server-status-variables.html>
92

HOW TO INTERPRET

94   TAB-SEPARATED OUTPUT
95       If you specify "--tab", you will get the raw output of SHOW STATUS in
96       tab-separated format, convenient for opening with a spreadsheet.  This
97       is not the default output, but it's so much easier to describe that
98       I'll cover it first.
99
100       ·   Most of the command-line options for controlling verbosity and such
101           are ignored in --tab mode.
102
103       ·   The variable names you see in MySQL, such as 'Com_select', are kept
104           -- there are no euphimisms, so you have to know your MySQL
105           variables.
106
107       ·   The columns are Variable_name, Before, After1...AfterN,
108           Calibration.  The Variable_name column is just what it sounds like.
109           Before is the result from the first run of SHOW STATUS.  After1,
110           After2, etc are the results of running SHOW STATUS after each query
111           in the batch.  Finally, the last column is the result of running
112           SHOW STATUS just after the last AfterN column, so you can see how
113           much work SHOW STATUS itself causes.
114
115       ·   If you specify "--verbose", output includes every variable mk-
116           query-profiler measures.  If not (default) it only includes
117           variables where there was some difference from one column to the
118           next.
119
120   NORMAL OUTPUT
121       If you don't specify --tab, you'll get a report formatted for human
122       readability.  This is the default output format.
123
124       mk-query-profiler can output a lot of information, as you've seen if
125       you ran the examples in the "SYNOPSIS".  What does it all mean?
126
127       First, there are two basic groups of information you might see: per-
128       query and summary.  If your batch contains only one query, these will
129       be the same and you'll only see the summary.  You can recognize the
130       difference by looking for centered, all-caps, boxed-in section headers.
131       Externally profiled commands will have EXTERNAL, individually profiled
132       queries will have QUERY, and summary will say SUMMARY.
133
134       Next, the information in each section is grouped into subsections,
135       headed by an underlined title.  Each of these sections has varying
136       information in it.  Which sections you see depends on command-line
137       arguments and your MySQL version.  I'll explain each section briefly.
138       If you really want to know where the numbers come from, read
139       <http://dev.mysql.com/doc/en/server-status-variables.html>.
140
141       You need to understand which numbers are insulated from other queries
142       and which are not.  This depends on your MySQL version.  Version 5.0.2
143       introduced the concept of session status variables, so you can see
144       information about only your own connection.  However, many variables
145       aren't session-ized, so when you have MySQL 5.0.2 or greater, you will
146       actually see a mix of session and global variables.  That means other
147       queries happening at the same time will pollute some of your results.
148       If you have MySQL versions older than 5.0.2, you won't have ANY
149       connection-specific stats, so your results will be polluted by other
150       queries no matter what.  Because of the mixture of session and global
151       variables, by far the best way to profile is on a completely quiet
152       server where nothing else is interfering with your results.
153
154       While explaining the results in the sections that follow, I'll refer to
155       a value as "protected" if it comes from a session-specific variable and
156       can be relied upon to be accurate even on a busy server.  Just keep in
157       mind, if you're not using MySQL 5.0.2 or newer, your results will be
158       inaccurate unless you're running against a totally quiet server, even
159       if I label it as "protected."
160
161   Overall stats
162       This section shows the overall elapsed time for the query, as measured
163       by Perl, and the optimizer cost as reported by MySQL.
164
165       If you're viewing separate query statistics, this is all you'll see.
166       If you're looking at a summary, you'll also see a breakdown of the
167       questions the queries asked the server.
168
169       The execution time is not totally reliable, as it includes network
170       round-trip time, Perl's own execution time, and so on.  However, on a
171       low-latency network, this should be fairly negligible, giving you a
172       reasonable measure of the query's time, especially for queries longer
173       than a few tenths of a second.
174
175       The optimizer cost comes from the Last_query_cost variable, and is
176       protected from other connections in MySQL 5.0.7 and greater.  It is not
177       available before 5.0.1.
178
179       The total number of questions is not protected, but the breakdown of
180       individual question types is, because it comes from the Com_ status
181       variables.
182
183   Table and index accesses
184       This section shows you information about the batch's table and index-
185       level operations (as opposed to row-level operations, which will be in
186       the next section).  The "Table locks acquired" and "Temp files" values
187       are unprotected, but everything else in this section is protected.
188
189       The "Potential filesorts" value is calculated as the number of times a
190       query had both a scan sort (Sort_scan) and created a temporary table
191       (Created_tmp_tables).  There is no Sort_filesort or similar status
192       value, so it's a best guess at whether a query did a filesort.  It
193       should be fairly accurate.
194
195       If you specified "--allow-cache", you'll see statistics on the query
196       cache.  These are unprotected.
197
198   Row operations
199       These values are all about the row-level operations your batch caused.
200       For example, how many rows were inserted, updated, or deleted.  You'll
201       also see row-level index access statistics, such as how many times the
202       query sought and read the next entry in an index.
203
204       Depending on your MySQL version, you'll either see one or two columns
205       of information in this section.  The one headed "Handler" is all from
206       the Handler_ variables, and those statistics are protected.  If your
207       MySQL version supports it, you'll also see a column headed "InnoDB,"
208       which is unprotected.
209
210   I/O Operations
211       This section gives information on I/O operations your batch caused,
212       both in memory and on disk.  Unless you have MySQL 5.0.2 or greater,
213       you'll only see information on the key cache.  Otherwise, you'll see a
214       lot of information on InnoDB's I/O operations as well, such as how many
215       times the query was able to satisfy a read from the buffer pool and how
216       many times it had to go to the disk.
217
218       None of the information in this section is protected.
219
220   InnoDB Data Operations
221       This section only appears when you're querying MySQL 5.0.2 or newer.
222       None of the information is protected.  You'll see statistics about how
223       many pages were affected, how many operations took place, and how many
224       bytes were affected.
225

OPTIONS

227       This tool accepts additional command-line arguments.  Refer to the
228       "SYNOPSIS" and usage information for details.
229
230       --allow-cache
231           Let MySQL query cache cache the queries executed.
232
233           By default this is disabled.  When enabled, cache profiling
234           information is added to the printout.  See
235           <http://dev.mysql.com/doc/en/query-cache.html> for more information
236           about the query cache.
237
238       --ask-pass
239           Prompt for a password when connecting to MySQL.
240
241       --[no]calibrate
242           default: yes
243
244           Try to compensate for "SHOW STATUS".
245
246           Measure and compensate for the "cost of observation" caused by
247           running SHOW STATUS.  Only works reliably on a quiet server; on a
248           busy server, other processes can cause the calibration to be wrong.
249
250       --charset
251           short form: -A; type: string
252
253           Default character set.  If the value is utf8, sets Perl's binmode
254           on STDOUT to utf8, passes the mysql_enable_utf8 option to
255           DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL.  Any
256           other value sets binmode on STDOUT without the utf8 layer, and runs
257           SET NAMES after connecting to MySQL.
258
259       --config
260           type: Array
261
262           Read this comma-separated list of config files; if specified, this
263           must be the first option on the command line.
264
265       --database
266           short form: -D; type: string
267
268           Database to use for connection.
269
270       --defaults-file
271           short form: -F; type: string
272
273           Only read mysql options from the given file.  You must give an
274           absolute pathname.
275
276       --external
277           Calibrate, then pause while an external program runs.
278
279           This is typically useful while you run an external program.  When
280           you press [enter] mk-query-profiler will stop sleeping and take
281           another measurement, then print statistics as usual.
282
283           When there is a filename on the command line, mk-query-profiler
284           executes each line in the file as a shell command.  If you give -
285           as the filename, mk-query-profiler reads from STDIN.
286
287           Output from shell commands is printed to STDOUT and terminated with
288           __BEGIN__, after which mk-query-profiler prints its own output.
289
290       --flush
291           cumulative: yes
292
293           Flush tables.  Specify twice to do between every query.
294
295           Calls FLUSH TABLES before profiling.  If you are executing queries
296           from a batch file, specifying --flush twice will cause mk-query-
297           profiler to call FLUSH TABLES between every query, not just once at
298           the beginning.  Default is not to flush at all. See
299           <http://dev.mysql.com/doc/en/flush.html> for more information.
300
301       --help
302           Show help and exit.
303
304       --host
305           short form: -h; type: string
306
307           Connect to host.
308
309       --[no]innodb
310           default: yes
311
312           Show InnoDB statistics.
313
314       --only
315           type: hash
316
317           Only show statistics for this comma-separated list of queries or
318           commands.
319
320       --password
321           short form: -p; type: string
322
323           Password to use when connecting.
324
325       --pid
326           type: string
327
328           Create the given PID file.  The file contains the process ID of the
329           script.  The PID file is removed when the script exits.  Before
330           starting, the script checks if the PID file already exists.  If it
331           does not, then the script creates and writes its own PID to it.  If
332           it does, then the script checks the following: if the file contains
333           a PID and a process is running with that PID, then the script dies;
334           or, if there is no process running with that PID, then the script
335           overwrites the file with its own PID and starts; else, if the file
336           contains no PID, then the script dies.
337
338       --port
339           short form: -P; type: int
340
341           Port number to use for connection.
342
343       --separate
344           Print stats separately for each query.
345
346           The default is to show only the summary of the entire batch.  See
347           also "--verbose".
348
349       --[no]session
350           default: yes
351
352           Use session "SHOW STATUS" and "SHOW VARIABLES".
353
354           Disabled if the server version doesn't support it.
355
356       --set-vars
357           type: string; default: wait_timeout=10000
358
359           Set these MySQL variables.  Immediately after connecting to MySQL,
360           this string will be appended to SET and executed.
361
362       --socket
363           short form: -S; type: string
364
365           Socket file to use for connection.
366
367       --tab
368           Print tab-separated values instead of whitespace-aligned columns.
369
370       --user
371           short form: -u; type: string
372
373           User for login if not current user.
374
375       --verbose
376           short form: -v; cumulative: yes; default: 0
377
378           Verbosity; specify multiple times for more detailed output.
379
380           When "--tab" is given, prints variables that don't change.
381           Otherwise increasing the level of verbosity includes extra sections
382           in the output.
383
384       --verify
385           Verify nothing else is accessing the server.
386
387           This is a weak verification; it simply calibrates twice (see
388           "--[no]calibrate") and verifies that the cost of observation
389           remains constant.
390
391       --version
392           Show version and exit.
393

DSN OPTIONS

395       These DSN options are used to create a DSN.  Each option is given like
396       "option=value".  The options are case-sensitive, so P and p are not the
397       same option.  There cannot be whitespace before or after the "=" and if
398       the value contains whitespace it must be quoted.  DSN options are
399       comma-separated.  See the maatkit manpage for full details.
400
401       ·   A
402
403           dsn: charset; copy: yes
404
405           Default character set.
406
407       ·   D
408
409           dsn: database; copy: yes
410
411           Default database.
412
413       ·   F
414
415           dsn: mysql_read_default_file; copy: yes
416
417           Only read default options from the given file
418
419       ·   h
420
421           dsn: host; copy: yes
422
423           Connect to host.
424
425       ·   p
426
427           dsn: password; copy: yes
428
429           Password to use when connecting.
430
431       ·   P
432
433           dsn: port; copy: yes
434
435           Port number to use for connection.
436
437       ·   S
438
439           dsn: mysql_socket; copy: yes
440
441           Socket file to use for connection.
442
443       ·   u
444
445           dsn: user; copy: yes
446
447           User for login if not current user.
448

DOWNLOADING

450       You can download Maatkit from Google Code at
451       <http://code.google.com/p/maatkit/>, or you can get any of the tools
452       easily with a command like the following:
453
454          wget http://www.maatkit.org/get/toolname
455          or
456          wget http://www.maatkit.org/trunk/toolname
457
458       Where "toolname" can be replaced with the name (or fragment of a name)
459       of any of the Maatkit tools.  Once downloaded, they're ready to run; no
460       installation is needed.  The first URL gets the latest released version
461       of the tool, and the second gets the latest trunk code from Subversion.
462

ENVIRONMENT

464       The environment variable "MKDEBUG" enables verbose debugging output in
465       all of the Maatkit tools:
466
467          MKDEBUG=1 mk-....
468

SYSTEM REQUIREMENTS

470       You need Perl, DBI, DBD::mysql, and some core modules.
471

BUGS

473       For a list of known bugs see
474       <http://www.maatkit.org/bugs/mk-query-profiler>.
475
476       Please use Google Code Issues and Groups to report bugs or request
477       support: <http://code.google.com/p/maatkit/>.  You can also join
478       #maatkit on Freenode to discuss Maatkit.
479
480       Please include the complete command-line used to reproduce the problem
481       you are seeing, the version of all MySQL servers involved, the complete
482       output of the tool when run with "--version", and if possible,
483       debugging output produced by running with the "MKDEBUG=1" environment
484       variable.
485

COPYRIGHT, LICENSE AND WARRANTY

487       This program is copyright 2007-2011 Baron Schwartz.  Feedback and
488       improvements are welcome.
489
490       THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
491       WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
492       MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
493
494       This program is free software; you can redistribute it and/or modify it
495       under the terms of the GNU General Public License as published by the
496       Free Software Foundation, version 2; OR the Perl Artistic License.  On
497       UNIX and similar systems, you can issue `man perlgpl' or `man
498       perlartistic' to read these licenses.
499
500       You should have received a copy of the GNU General Public License along
501       with this program; if not, write to the Free Software Foundation, Inc.,
502       59 Temple Place, Suite 330, Boston, MA  02111-1307  USA.
503

SEE ALSO

505       See also mk-profile-compact.
506

AUTHOR

508       Baron Schwartz
509

ABOUT MAATKIT

511       This tool is part of Maatkit, a toolkit for power users of MySQL.
512       Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
513       primary code contributors.  Both are employed by Percona.  Financial
514       support for Maatkit development is primarily provided by Percona and
515       its clients.
516

ACKNOWLEDGEMENTS

518       I was inspired by the wonderful mysqlreport utility available at
519       <http://www.hackmysql.com/>.
520
521       Other contributors: Bart van Bragt.
522
523       Thanks to all who have helped.
524

VERSION

526       This manual page documents Ver 1.1.22 Distrib 7540 $Revision: 7477 $.
527
528
529
530perl v5.28.1                      2011-06-08              MK-QUERY-PROFILER(1)
Impressum