1MK-QUERY-PROFILER(1) User Contributed Perl Documentation MK-QUERY-PROFILER(1)
2
6 mk-query-profiler - Execute SQL statements and print statistics, or
7 measure activity caused by other processes.
8
10 Usage: mk-query-profiler [OPTION...] [FILE...]
11 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 mk-query-profiler can profile the (semicolon-terminated, blank-line
22 separated) queries in a file:
23 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 It can also just observe what happens in the server:
31 mk-query-profiler --external
33 Or it can run shell commands from a file and measure the result:
35 mk-query-profiler --external commands.txt
37 mk-query-profiler --external - < commands.txt
38 Read "HOW TO INTERPRET" to learn what it all means.
40
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 mk-query-profiler is generally read-only and very low risk. It will
48 execute FLUSH TABLES if you specify "--flush".
49 At the time of this release, we know of no bugs that could cause
51 serious harm to users.
52 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 See also "BUGS" for more information on filing bugs and getting help.
59
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 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 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 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 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 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
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 · Most of the command-line options for controlling verbosity and such
101 are ignored in --tab mode.
102 · 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 · 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 · 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 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 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 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 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 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 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 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 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 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 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 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 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 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 If you specified "--allow-cache", you'll see statistics on the query
196 cache. These are unprotected.
197 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 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 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 None of the information in this section is protected.
219 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
227 This tool accepts additional command-line arguments. Refer to the
228 "SYNOPSIS" and usage information for details.
229 --allow-cache
231 Let MySQL query cache cache the queries executed.
232 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 --ask-pass
239 Prompt for a password when connecting to MySQL.
240 --[no]calibrate
242 default: yes
243 Try to compensate for "SHOW STATUS".
245 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 --charset
251 short form: -A; type: string
252 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 --config
260 type: Array
261 Read this comma-separated list of config files; if specified, this
263 must be the first option on the command line.
264 --database
266 short form: -D; type: string
267 Database to use for connection.
269 --defaults-file
271 short form: -F; type: string
272 Only read mysql options from the given file. You must give an
274 absolute pathname.
275 --external
277 Calibrate, then pause while an external program runs.
278 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 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 Output from shell commands is printed to STDOUT and terminated with
288 __BEGIN__, after which mk-query-profiler prints its own output.
289 --flush
291 cumulative: yes
292 Flush tables. Specify twice to do between every query.
294 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 --help
302 Show help and exit.
303 --host
305 short form: -h; type: string
306 Connect to host.
308 --[no]innodb
310 default: yes
311 Show InnoDB statistics.
313 --only
315 type: hash
316 Only show statistics for this comma-separated list of queries or
318 commands.
319 --password
321 short form: -p; type: string
322 Password to use when connecting.
324 --pid
326 type: string
327 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 --port
339 short form: -P; type: int
340 Port number to use for connection.
342 --separate
344 Print stats separately for each query.
345 The default is to show only the summary of the entire batch. See
347 also "--verbose".
348 --[no]session
350 default: yes
351 Use session "SHOW STATUS" and "SHOW VARIABLES".
353 Disabled if the server version doesn't support it.
355 --set-vars
357 type: string; default: wait_timeout=10000
358 Set these MySQL variables. Immediately after connecting to MySQL,
360 this string will be appended to SET and executed.
361 --socket
363 short form: -S; type: string
364 Socket file to use for connection.
366 --tab
368 Print tab-separated values instead of whitespace-aligned columns.
369 --user
371 short form: -u; type: string
372 User for login if not current user.
374 --verbose
376 short form: -v; cumulative: yes; default: 0
377 Verbosity; specify multiple times for more detailed output.
379 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 --verify
385 Verify nothing else is accessing the server.
386 This is a weak verification; it simply calibrates twice (see
388 "--[no]calibrate") and verifies that the cost of observation
389 remains constant.
390 --version
392 Show version and exit.
393
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 · A
402 dsn: charset; copy: yes
404 Default character set.
406 · D
408 dsn: database; copy: yes
410 Default database.
412 · F
414 dsn: mysql_read_default_file; copy: yes
416 Only read default options from the given file
418 · h
420 dsn: host; copy: yes
422 Connect to host.
424 · p
426 dsn: password; copy: yes
428 Password to use when connecting.
430 · P
432 dsn: port; copy: yes
434 Port number to use for connection.
436 · S
438 dsn: mysql_socket; copy: yes
440 Socket file to use for connection.
442 · u
444 dsn: user; copy: yes
446 User for login if not current user.
448
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 wget http://www.maatkit.org/get/toolname
455 or
456 wget http://www.maatkit.org/trunk/toolname
457 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
464 The environment variable "MKDEBUG" enables verbose debugging output in
465 all of the Maatkit tools:
466 MKDEBUG=1 mk-....
468
470 You need Perl, DBI, DBD::mysql, and some core modules.
471
473 For a list of known bugs see
474 <http://www.maatkit.org/bugs/mk-query-profiler>.
475 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 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
487 This program is copyright 2007-2011 Baron Schwartz. Feedback and
488 improvements are welcome.
489 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 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 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
505 See also mk-profile-compact.
506
508 Baron Schwartz
509
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
518 I was inspired by the wonderful mysqlreport utility available at
519 <http://www.hackmysql.com/>.
520 Other contributors: Bart van Bragt.
522 Thanks to all who have helped.
524
526 This manual page documents Ver 1.1.22 Distrib 7540 $Revision: 7477 $.
527perl v5.28.1 2011-06-08 MK-QUERY-PROFILER(1)