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 mk-query-profiler can profile the (semicolon-terminated, blank-line
11 separated) queries in a file:
12 mk-query-profiler queries.sql
14 cat queries.sql | mk-query-profiler
15 mk-query-profiler -vv queries.sql
16 mk-query-profiler -v --separate --only 2,5,6 queries.sql
17 mk-query-profiler --tab queries.sql > results.csv
18 It can also just observe what happens in the server:
20 mk-query-profiler --external
22 Or it can run shell commands from a file and measure the result:
24 mk-query-profiler --external commands.txt
26 mk-query-profiler --external - < commands.txt
27 Read "HOW TO INTERPRET" to learn what it all means.
29
31 The following section is included to inform users about the potential
32 risks, whether known or unknown, of using this tool. The two main
33 categories of risks are those created by the nature of the tool (e.g.
34 read-only tools vs. read-write tools) and those created by bugs.
35 mk-query-profiler is generally read-only and very low risk. It will
37 execute FLUSH TABLES if you specify "--flush".
38 At the time of this release, we know of no bugs that could cause
40 serious harm to users.
41 The authoritative source for updated information is always the online
43 issue tracking system. Issues that affect this tool will be marked as
44 such. You can see a list of such issues at the following URL:
45 http://www.maatkit.org/bugs/mk-query-profiler
46 <http://www.maatkit.org/bugs/mk-query-profiler>.
47 See also "BUGS" for more information on filing bugs and getting help.
49
51 mk-query-profiler reads a file containing one or more SQL statements or
52 shell commands, executes them, and analyzes the output of SHOW STATUS
53 afterwards. It then prints statistics about how the batch performed.
54 For example, it can show how many table scans the batch caused, how
55 many page reads, how many temporary tables, and so forth.
56 All command-line arguments are optional, but you must either specify a
58 file containing the batch to profile as the last argument, or specify
59 that you're profiling an external program with the "--external" option,
60 or provide input to STDIN.
61 If the file contains multiple statements, they must be separated by
63 blank lines. If you don't do that, mk-query-profiler won't be able to
64 split the file into individual queries, and MySQL will complain about
65 syntax errors.
66 If the MySQL server version is before 5.0.2, you should make sure the
68 server is completely unused before trying to profile a batch. Prior to
69 this version, SHOW STATUS showed only global status variables, so other
70 queries will interfere and produce false results. mk-query-profiler
71 will try to detect if anything did interfere, but there can be no
72 guarantees.
73 Prior to MySQL 5.0.2, InnoDB status variables are not available, and
75 prior to version 5.0.3, InnoDB row lock status variables are not
76 available. mk-query-profiler will omit any output related to these
77 variables if they're not available.
78 For more information about SHOW STATUS, read the relevant section of
80 the MySQL manual at
81 http://dev.mysql.com/doc/en/server-status-variables.html
82 <http://dev.mysql.com/doc/en/server-status-variables.html>
83
85 TAB-SEPARATED OUTPUT
86 If you specify "--tab", you will get the raw output of SHOW STATUS in
87 tab-separated format, convenient for opening with a spreadsheet. This
88 is not the default output, but it's so much easier to describe that
89 I'll cover it first.
90 · Most of the command-line options for controlling verbosity and such
92 are ignored in --tab mode.
93 · The variable names you see in MySQL, such as 'Com_select', are kept
95 -- there are no euphimisms, so you have to know your MySQL
96 variables.
97 · The columns are Variable_name, Before, After1...AfterN,
99 Calibration. The Variable_name column is just what it sounds like.
100 Before is the result from the first run of SHOW STATUS. After1,
101 After2, etc are the results of running SHOW STATUS after each query
102 in the batch. Finally, the last column is the result of running
103 SHOW STATUS just after the last AfterN column, so you can see how
104 much work SHOW STATUS itself causes.
105 · If you specify "--verbose", output includes every variable mk-
107 query-profiler measures. If not (default) it only includes
108 variables where there was some difference from one column to the
109 next.
110 NORMAL OUTPUT
112 If you don't specify --tab, you'll get a report formatted for human
113 readability. This is the default output format.
114 mk-query-profiler can output a lot of information, as you've seen if
116 you ran the examples in the "SYNOPSIS". What does it all mean?
117 First, there are two basic groups of information you might see: per-
119 query and summary. If your batch contains only one query, these will
120 be the same and you'll only see the summary. You can recognize the
121 difference by looking for centered, all-caps, boxed-in section headers.
122 Externally profiled commands will have EXTERNAL, individually profiled
123 queries will have QUERY, and summary will say SUMMARY.
124 Next, the information in each section is grouped into subsections,
126 headed by an underlined title. Each of these sections has varying
127 information in it. Which sections you see depends on command-line
128 arguments and your MySQL version. I'll explain each section briefly.
129 If you really want to know where the numbers come from, read
130 http://dev.mysql.com/doc/en/server-status-variables.html
131 <http://dev.mysql.com/doc/en/server-status-variables.html>.
132 You need to understand which numbers are insulated from other queries
134 and which are not. This depends on your MySQL version. Version 5.0.2
135 introduced the concept of session status variables, so you can see
136 information about only your own connection. However, many variables
137 aren't session-ized, so when you have MySQL 5.0.2 or greater, you will
138 actually see a mix of session and global variables. That means other
139 queries happening at the same time will pollute some of your results.
140 If you have MySQL versions older than 5.0.2, you won't have ANY
141 connection-specific stats, so your results will be polluted by other
142 queries no matter what. Because of the mixture of session and global
143 variables, by far the best way to profile is on a completely quiet
144 server where nothing else is interfering with your results.
145 While explaining the results in the sections that follow, I'll refer to
147 a value as "protected" if it comes from a session-specific variable and
148 can be relied upon to be accurate even on a busy server. Just keep in
149 mind, if you're not using MySQL 5.0.2 or newer, your results will be
150 inaccurate unless you're running against a totally quiet server, even
151 if I label it as "protected."
152 Overall stats
154 This section shows the overall elapsed time for the query, as measured
155 by Perl, and the optimizer cost as reported by MySQL.
156 If you're viewing separate query statistics, this is all you'll see.
158 If you're looking at a summary, you'll also see a breakdown of the
159 questions the queries asked the server.
160 The execution time is not totally reliable, as it includes network
162 round-trip time, Perl's own execution time, and so on. However, on a
163 low-latency network, this should be fairly negligible, giving you a
164 reasonable measure of the query's time, especially for queries longer
165 than a few tenths of a second.
166 The optimizer cost comes from the Last_query_cost variable, and is
168 protected from other connections in MySQL 5.0.7 and greater. It is not
169 available before 5.0.1.
170 The total number of questions is not protected, but the breakdown of
172 individual question types is, because it comes from the Com_ status
173 variables.
174 Table and index accesses
176 This section shows you information about the batch's table and index-
177 level operations (as opposed to row-level operations, which will be in
178 the next section). The "Table locks acquired" and "Temp files" values
179 are unprotected, but everything else in this section is protected.
180 The "Potential filesorts" value is calculated as the number of times a
182 query had both a scan sort (Sort_scan) and created a temporary table
183 (Created_tmp_tables). There is no Sort_filesort or similar status
184 value, so it's a best guess at whether a query did a filesort. It
185 should be fairly accurate.
186 If you specified "--allow-cache", you'll see statistics on the query
188 cache. These are unprotected.
189 Row operations
191 These values are all about the row-level operations your batch caused.
192 For example, how many rows were inserted, updated, or deleted. You'll
193 also see row-level index access statistics, such as how many times the
194 query sought and read the next entry in an index.
195 Depending on your MySQL version, you'll either see one or two columns
197 of information in this section. The one headed "Handler" is all from
198 the Handler_ variables, and those statistics are protected. If your
199 MySQL version supports it, you'll also see a column headed "InnoDB,"
200 which is unprotected.
201 I/O Operations
203 This section gives information on I/O operations your batch caused,
204 both in memory and on disk. Unless you have MySQL 5.0.2 or greater,
205 you'll only see information on the key cache. Otherwise, you'll see a
206 lot of information on InnoDB's I/O operations as well, such as how many
207 times the query was able to satisfy a read from the buffer pool and how
208 many times it had to go to the disk.
209 None of the information in this section is protected.
211 InnoDB Data Operations
213 This section only appears when you're querying MySQL 5.0.2 or newer.
214 None of the information is protected. You'll see statistics about how
215 many pages were affected, how many operations took place, and how many
216 bytes were affected.
217
219 --allow-cache
220 Let MySQL query cache cache the queries executed.
221 By default this is disabled. When enabled, cache profiling
223 information is added to the printout. See
224 http://dev.mysql.com/doc/en/query-cache.html
225 <http://dev.mysql.com/doc/en/query-cache.html> for more information
226 about the query cache.
227 --ask-pass
229 Prompt for a password when connecting to MySQL.
230 --[no]calibrate
232 default: yes
233 Try to compensate for "SHOW STATUS".
235 Measure and compensate for the "cost of observation" caused by
237 running SHOW STATUS. Only works reliably on a quiet server; on a
238 busy server, other processes can cause the calibration to be wrong.
239 --charset
241 short form: -A; type: string
242 Default character set. If the value is utf8, sets Perl's binmode
244 on STDOUT to utf8, passes the mysql_enable_utf8 option to
245 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
246 other value sets binmode on STDOUT without the utf8 layer, and runs
247 SET NAMES after connecting to MySQL.
248 --config
250 type: Array
251 Read this comma-separated list of config files; if specified, this
253 must be the first option on the command line.
254 --database
256 short form: -D; type: string
257 Database to use for connection.
259 --defaults-file
261 short form: -F; type: string
262 Only read mysql options from the given file. You must give an
264 absolute pathname.
265 --external
267 Calibrate, then pause while an external program runs.
268 This is typically useful while you run an external program. When
270 you press [enter] mk-query-profiler will stop sleeping and take
271 another measurement, then print statistics as usual.
272 When there is a filename on the command line, mk-query-profiler
274 executes each line in the file as a shell command. If you give -
275 as the filename, mk-query-profiler reads from STDIN.
276 Output from shell commands is printed to STDOUT and terminated with
278 __BEGIN__, after which mk-query-profiler prints its own output.
279 --flush
281 cumulative: yes
282 Flush tables. Specify twice to do between every query.
284 Calls FLUSH TABLES before profiling. If you are executing queries
286 from a batch file, specifying --flush twice will cause mk-query-
287 profiler to call FLUSH TABLES between every query, not just once at
288 the beginning. Default is not to flush at all. See
289 <http://dev.mysql.com/doc/en/flush.html> for more information.
290 --help
292 Show help and exit.
293 --host
295 short form: -h; type: string
296 Connect to host.
298 --[no]innodb
300 default: yes
301 Show InnoDB statistics.
303 --only
305 type: hash
306 Only show statistics for this comma-separated list of queries or
308 commands.
309 --password
311 short form: -p; type: string
312 Password to use when connecting.
314 --pid
316 type: string
317 Create the given PID file. The file contains the process ID of the
319 script. The PID file is removed when the script exits. Before
320 starting, the script checks if the PID file already exists. If it
321 does not, then the script creates and writes its own PID to it. If
322 it does, then the script checks the following: if the file contains
323 a PID and a process is running with that PID, then the script dies;
324 or, if there is no process running with that PID, then the script
325 overwrites the file with its own PID and starts; else, if the file
326 contains no PID, then the script dies.
327 --port
329 short form: -P; type: int
330 Port number to use for connection.
332 --separate
334 Print stats separately for each query.
335 The default is to show only the summary of the entire batch. See
337 also "--verbose".
338 --[no]session
340 default: yes
341 Use session "SHOW STATUS" and "SHOW VARIABLES".
343 Disabled if the server version doesn't support it.
345 --set-vars
347 type: string; default: wait_timeout=10000
348 Set these MySQL variables. Immediately after connecting to MySQL,
350 this string will be appended to SET and executed.
351 --socket
353 short form: -S; type: string
354 Socket file to use for connection.
356 --tab
358 Print tab-separated values instead of whitespace-aligned columns.
359 --user
361 short form: -u; type: string
362 User for login if not current user.
364 --verbose
366 short form: -v; cumulative: yes; default: 0
367 Verbosity; specify multiple times for more detailed output.
369 When "--tab" is given, prints variables that don't change.
371 Otherwise increasing the level of verbosity includes extra sections
372 in the output.
373 --verify
375 Verify nothing else is accessing the server.
376 This is a weak verification; it simply calibrates twice (see
378 "--[no]calibrate") and verifies that the cost of observation
379 remains constant.
380 --version
382 Show version and exit.
383
385 These DSN options are used to create a DSN. Each option is given like
386 "option=value". The options are case-sensitive, so P and p are not the
387 same option. There cannot be whitespace before or after the "=" and if
388 the value contains whitespace it must be quoted. DSN options are
389 comma-separated. See the maatkit manpage for full details.
390 · A
392 dsn: charset; copy: yes
394 Default character set.
396 · D
398 dsn: database; copy: yes
400 Default database.
402 · F
404 dsn: mysql_read_default_file; copy: yes
406 Only read default options from the given file
408 · h
410 dsn: host; copy: yes
412 Connect to host.
414 · p
416 dsn: password; copy: yes
418 Password to use when connecting.
420 · P
422 dsn: port; copy: yes
424 Port number to use for connection.
426 · S
428 dsn: mysql_socket; copy: yes
430 Socket file to use for connection.
432 · u
434 dsn: user; copy: yes
436 User for login if not current user.
438
440 You can download Maatkit from Google Code at
441 <http://code.google.com/p/maatkit/>, or you can get any of the tools
442 easily with a command like the following:
443 wget http://www.maatkit.org/get/toolname
445 or
446 wget http://www.maatkit.org/trunk/toolname
447 Where "toolname" can be replaced with the name (or fragment of a name)
449 of any of the Maatkit tools. Once downloaded, they're ready to run; no
450 installation is needed. The first URL gets the latest released version
451 of the tool, and the second gets the latest trunk code from Subversion.
452
454 The environment variable "MKDEBUG" enables verbose debugging output in
455 all of the Maatkit tools:
456 MKDEBUG=1 mk-....
458
460 You need Perl, DBI, DBD::mysql, and some core modules.
461
463 For list of known bugs see
464 http://www.maatkit.org/bugs/mk-query-profiler
465 <http://www.maatkit.org/bugs/mk-query-profiler>.
466 Please use Google Code Issues and Groups to report bugs or request
468 support: <http://code.google.com/p/maatkit/>. You can also join
469 #maatkit on Freenode to discuss Maatkit.
470 Please include the complete command-line used to reproduce the problem
472 you are seeing, the version of all MySQL servers involved, the complete
473 output of the tool when run with "--version", and if possible,
474 debugging output produced by running with the "MKDEBUG=1" environment
475 variable.
476
478 This program is copyright 2007-2010 Baron Schwartz. Feedback and
479 improvements are welcome.
480 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
482 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
483 MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
484 This program is free software; you can redistribute it and/or modify it
486 under the terms of the GNU General Public License as published by the
487 Free Software Foundation, version 2; OR the Perl Artistic License. On
488 UNIX and similar systems, you can issue `man perlgpl' or `man
489 perlartistic' to read these licenses.
490 You should have received a copy of the GNU General Public License along
492 with this program; if not, write to the Free Software Foundation, Inc.,
493 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
494
496 See also mk-profile-compact.
497
499 Baron Schwartz
500
502 This tool is part of Maatkit, a toolkit for power users of MySQL.
503 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
504 primary code contributors. Both are employed by Percona. Financial
505 support for Maatkit development is primarily provided by Percona and
506 its clients.
507
509 I was inspired by the wonderful mysqlreport utility available at
510 <http://www.hackmysql.com/>.
511 Other contributors: Bart van Bragt.
513 Thanks to all who have helped.
515
517 This manual page documents Ver 1.1.22 Distrib 6839 $Revision: 6831 $.
518perl v5.12.1 2010-08-01 MK-QUERY-PROFILER(1)