.\" Automatically generated by Pod::Man v1.37, Pod::Parser v1.32 .\" .\" Standard preamble: .\" ======================================================================== .de Sh \" Subsection heading .br .if t .Sp .ne 5 .PP \fB\\$1\fR .PP .. .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. | will give a .\" real vertical bar. \*(C+ will give a nicer C++. Capital omega is used to .\" do unbreakable dashes and therefore won't be available. \*(C` and \*(C' .\" expand to `' in nroff, nothing in troff, for use with C<>. .tr \(*W-|\(bv\*(Tr .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' 'br\} .\" .\" If the F register is turned on, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.Sh), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .if \nF \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . nr % 0 . rr F .\} .\" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .hy 0 .if n .na .\" .\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2). .\" Fear. Run. Save yourself. No user-serviceable parts. . \" fudge factors for nroff and troff .if n \{\ . ds #H 0 . ds #V .8m . ds #F .3m . ds #[ \f1 . ds #] \fP .\} .if t \{\ . ds #H ((1u-(\\\\n(.fu%2u))*.13m) . ds #V .6m . ds #F 0 . ds #[ \& . ds #] \& .\} . \" simple accents for nroff and troff .if n \{\ . ds ' \& . ds ` \& . ds ^ \& . ds , \& . ds ~ ~ . ds / .\} .if t \{\ . ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u" . ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u' . ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u' . ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u' . ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u' . ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u' .\} . \" troff and (daisy-wheel) nroff accents .ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V' .ds 8 \h'\*(#H'\(*b\h'-\*(#H' .ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#] .ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H' .ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u' .ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#] .ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#] .ds ae a\h'-(\w'a'u*4/10)'e .ds Ae A\h'-(\w'A'u*4/10)'E . \" corrections for vroff .if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u' .if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u' . \" for low resolution devices (crt and lpr) .if \n(.H>23 .if \n(.V>19 \ \{\ . ds : e . ds 8 ss . ds o a . ds d- d\h'-1'\(ga . ds D- D\h'-1'\(hy . ds th \o'bp' . ds Th \o'LP' . ds ae ae . ds Ae AE .\} .rm #[ #] #H #V #F C .\" ======================================================================== .\" .IX Title "MYSQL-QUERY-PROFILER 1" .TH MYSQL-QUERY-PROFILER 1 "2007-10-15" "perl v5.8.8" "User Contributed Perl Documentation" .SH "NAME" mysql\-query\-profiler \- Execute SQL statements and print statistics, or measure activity caused by other processes. .SH "SYNOPSIS" .IX Header "SYNOPSIS" mysql-query-profiler can profile the (semicolon\-terminated, blank-line separated) queries in a file: .PP .Vb 5 \& mysql-query-profiler queries.sql \& cat queries.sql | mysql-query-profiler \& mysql-query-profiler -vv queries.sql \& mysql-query-profiler -v --separate --only 2,5,6 queries.sql \& mysql-query-profiler --tab queries.sql > results.csv .Ve .PP It can also just observe what happens in the server: .PP .Vb 1 \& mysql-query-profiler --external .Ve .PP Or it can run shell commands from a file and measure the result: .PP .Vb 2 \& mysql-query-profiler --external commands.txt \& mysql-query-profiler --external - < commands.txt .Ve .PP Read \*(L"\s-1HOW\s0 \s-1TO\s0 \s-1INTERPRET\s0\*(R" to learn what it all means. .SH "OPTIONS" .IX Header "OPTIONS" Some options can be negated by prefixing \-\-no to the option name. .IP "\-\-allowcache" 4 .IX Item "--allowcache" Allows the MySQL query cache to cache the queries executed. By default this is disabled. When enabled, cache profiling information is added to the printout. See for more information about the query cache. .IP "\-\-askpass" 4 .IX Item "--askpass" Prompt for password for connections. .IP "\-\-calibrate" 4 .IX Item "--calibrate" Makes MySQL Query Profiler measure and compensate for the \*(L"cost of observation\*(R" caused by running \s-1SHOW\s0 \s-1STATUS\s0. Enabled by default. Only works reliably on a quiet server; on a busy server, other processes can cause the calibration to be wrong. .IP "\-\-database" 4 .IX Item "--database" Database to use for connection. .IP "\-\-defaults\-file" 4 .IX Item "--defaults-file" Only read default options from the given file. .IP "\-\-external" 4 .IX Item "--external" Makes MySQL Query Profiler measure the cost of observation, then pause, typically while you run an external program. When you press [enter] MySQL Query Profiler will stop sleeping and take another measurement, then print statistics as usual. .Sp When there is a filename on the command line, MySQL Query Profiler executes each line in the file as a shell command. If you give \- as the filename, MySQL Query Profiler reads from \s-1STDIN\s0. .Sp Output from shell commands is printed to \s-1STDOUT\s0 and terminated with _\|_BEGIN_\|_, after which MySQL Query Profiler prints its own output. .IP "\-\-flush" 4 .IX Item "--flush" Calls \s-1FLUSH\s0 \s-1TABLES\s0 before profiling. If you are executing queries from a batch file, specifying \-\-flush twice will cause MySQL Query Profiler to call \&\s-1FLUSH\s0 \s-1TABLES\s0 between every query, not just once at the beginning. Default is not to flush at all. See for more information. .IP "\-\-help" 4 .IX Item "--help" Displays a help message. .IP "\-\-host" 4 .IX Item "--host" Connect to host. .IP "\-\-innodb" 4 .IX Item "--innodb" Shows InnoDB statistics. Enabled by default. .IP "\-\-only" 4 .IX Item "--only" Only shows statistics for the specified queries or commands in a batch file. Specify as a comma-separated list of numbers beginning at 1. .IP "\-\-password" 4 .IX Item "--password" Password to use for connection. .IP "\-\-port" 4 .IX Item "--port" Port number to use for connection. .IP "\-\-separate" 4 .IX Item "--separate" Print statistics for each query or command in a batch file. The default is to show only the summary of the entire batch. See also \*(L"\-\-verbose\*(R". .IP "\-\-socket" 4 .IX Item "--socket" Socket file to use for connection. .IP "\-\-tab" 4 .IX Item "--tab" Print tab-separated values for easy reading with a spreadsheet, instead of whitespace-aligned columns for humans to read. .IP "\-\-user" 4 .IX Item "--user" User for login if not current user. .IP "\-\-verbose" 4 .IX Item "--verbose" Verbosity; specify multiple times for more detailed output. When \*(L"\-\-tab\*(R" is given, prints variables that don't change. Otherwise increasing the level of verbosity includes extra sections in the output. .IP "\-\-verify" 4 .IX Item "--verify" Verify nothing else is accessing the server. This is a weak verification; it simply calibrates twice (see \*(L"\-\-calibrate\*(R") and verifies that the cost of observation remains constant. .IP "\-\-version" 4 .IX Item "--version" Output version information and exit. .SH "DESCRIPTION" .IX Header "DESCRIPTION" MySQL Query Profiler reads a file containing one or more \s-1SQL\s0 statements or shell commands, executes them, and analyzes the output of \s-1SHOW\s0 \s-1STATUS\s0 afterwards. It then prints statistics about how the batch performed. For example, it can show how many table scans the batch caused, how many page reads, how many temporary tables, and so forth. .PP All command-line arguments are optional, but you must either specify a file containing the batch to profile as the last argument, or specify that you're profiling an external program with the \*(L"\-\-external\*(R" option, or provide input to \s-1STDIN\s0. .PP If the file contains multiple statements, they must be separated by blank lines. If you don't do that, MySQL Query Profiler won't be able to split the file into individual queries, and MySQL will complain about syntax errors. .PP If the MySQL server version is before 5.0.2, you should make sure the server is completely unused before trying to profile a batch. Prior to this version, \&\s-1SHOW\s0 \s-1STATUS\s0 showed only global status variables, so other queries will interfere and produce false results. MySQL Query Profiler will try to detect if anything did interfere, but there can be no guarantees. .PP Prior to MySQL 5.0.2, InnoDB status variables are not available, and prior to version 5.0.3, InnoDB row lock status variables are not available. MySQL Query Profiler will omit any output related to these variables if they're not available. .PP For more information about \s-1SHOW\s0 \s-1STATUS\s0, read the relevant section of the MySQL manual at .SH "HOW TO INTERPRET" .IX Header "HOW TO INTERPRET" .Sh "TAB-SEPARATED \s-1OUTPUT\s0" .IX Subsection "TAB-SEPARATED OUTPUT" If you specify \*(L"\-\-tab\*(R", you will get the raw output of \s-1SHOW\s0 \s-1STATUS\s0 in tab-separated format, convenient for opening with a spreadsheet. This is not the default output, but it's so much easier to describe that I'll cover it first. .IP "\(bu" 4 Most of the command-line options for controlling verbosity and such are ignored in \-\-tab mode. .IP "\(bu" 4 The variable names you see in MySQL, such as 'Com_select', are kept \*(-- there are no euphimisms, so you have to know your MySQL variables. .IP "\(bu" 4 The columns are Variable_name, Before, After1...AfterN, Calibration. The Variable_name column is just what it sounds like. Before is the result from the first run of \s-1SHOW\s0 \s-1STATUS\s0. After1, After2, etc are the results of running \s-1SHOW\s0 \s-1STATUS\s0 after each query in the batch. Finally, the last column is the result of running \s-1SHOW\s0 \s-1STATUS\s0 just after the last AfterN column, so you can see how much work \s-1SHOW\s0 \s-1STATUS\s0 itself causes. .IP "\(bu" 4 If you specify \*(L"\-\-verbose\*(R", output includes every variable MySQL Query Profiler measures. If not (default) it only includes variables where there was some difference from one column to the next. .Sh "\s-1NORMAL\s0 \s-1OUTPUT\s0" .IX Subsection "NORMAL OUTPUT" If you don't specify \-\-tab, you'll get a report formatted for human readability. This is the default output format. .PP MySQL Query Profiler can output a lot of information, as you've seen if you ran the examples in the \*(L"\s-1SYNOPSIS\s0\*(R". What does it all mean? .PP First, there are two basic groups of information you might see: per-query and summary. If your batch contains only one query, these will be the same and you'll only see the summary. You can recognize the difference by looking for centered, all\-caps, boxed-in section headers. Externally profiled commands will have \s-1EXTERNAL\s0, individually profiled queries will have \s-1QUERY\s0, and summary will say \s-1SUMMARY\s0. .PP Next, the information in each section is grouped into subsections, headed by an underlined title. Each of these sections has varying information in it. Which sections you see depends on command-line arguments and your MySQL version. I'll explain each section briefly. If you really want to know where the numbers come from, read . .PP You need to understand which numbers are insulated from other queries and which are not. This depends on your MySQL version. Version 5.0.2 introduced the concept of session status variables, so you can see information about only your own connection. However, many variables aren't session\-ized, so when you have MySQL 5.0.2 or greater, you will actually see a mix of session and global variables. That means other queries happening at the same time will pollute some of your results. If you have MySQL versions older than 5.0.2, you won't have \s-1ANY\s0 connection-specific stats, so your results will be polluted by other queries no matter what. Because of the mixture of session and global variables, by far the best way to profile is on a completely quiet server where nothing else is interfering with your results. .PP While explaining the results in the sections that follow, I'll refer to a value as \*(L"protected\*(R" if it comes from a session-specific variable and can be relied upon to be accurate even on a busy server. Just keep in mind, if you're not using MySQL 5.0.2 or newer, your results will be inaccurate unless you're running against a totally quiet server, even if I label it as \&\*(L"protected.\*(R" .Sh "Overall stats" .IX Subsection "Overall stats" This section shows the overall elapsed time for the query, as measured by Perl, and the optimizer cost as reported by MySQL. .PP If you're viewing separate query statistics, this is all you'll see. If you're looking at a summary, you'll also see a breakdown of the questions the queries asked the server. .PP The execution time is not totally reliable, as it includes network round-trip time, Perl's own execution time, and so on. However, on a low-latency network, this should be fairly negligible, giving you a reasonable measure of the query's time, especially for queries longer than a few tenths of a second. .PP The optimizer cost comes from the Last_query_cost variable, and is protected from other connections in MySQL 5.0.7 and greater. It is not available before 5.0.1. .PP The total number of questions is not protected, but the breakdown of individual question types is, because it comes from the Com_ status variables. .Sh "Table and index accesses" .IX Subsection "Table and index accesses" This section shows you information about the batch's table and index-level operations (as opposed to row-level operations, which will be in the next section). The \*(L"Table locks acquired\*(R" and \*(L"Temp files\*(R" values are unprotected, but everything else in this section is protected. .PP The \*(L"Potential filesorts\*(R" value is calculated as the number of times a query had both a scan sort (Sort_scan) and created a temporary table (Created_tmp_tables). There is no Sort_filesort or similar status value, so it's a best guess at whether a query did a filesort. It should be fairly accurate. .PP If you specified \*(L"\-\-allowcache\*(R", you'll see statistics on the query cache. These are unprotected. .Sh "Row operations" .IX Subsection "Row operations" These values are all about the row-level operations your batch caused. For example, how many rows were inserted, updated, or deleted. You'll also see row-level index access statistics, such as how many times the query sought and read the next entry in an index. .PP Depending on your MySQL version, you'll either see one or two columns of information in this section. The one headed \*(L"Handler\*(R" is all from the Handler_ variables, and those statistics are protected. If your MySQL version supports it, you'll also see a column headed \*(L"InnoDB,\*(R" which is unprotected. .Sh "I/O Operations" .IX Subsection "I/O Operations" This section gives information on I/O operations your batch caused, both in memory and on disk. Unless you have MySQL 5.0.2 or greater, you'll only see information on the key cache. Otherwise, you'll see a lot of information on InnoDB's I/O operations as well, such as how many times the query was able to satisfy a read from the buffer pool and how many times it had to go to the disk. .PP None of the information in this section is protected. .Sh "InnoDB Data Operations" .IX Subsection "InnoDB Data Operations" This section only appears when you're querying MySQL 5.0.2 or newer. None of the information is protected. You'll see statistics about how many pages were affected, how many operations took place, and how many bytes were affected. .SH "BUGS" .IX Header "BUGS" Please use the Sourceforge bug tracker, forums, and mailing lists to request support or report bugs: . .SH "SYSTEM REQUIREMENTS" .IX Header "SYSTEM REQUIREMENTS" You need Perl, \s-1DBI\s0, DBD::mysql, and some core modules. .SH "SEE ALSO" .IX Header "SEE ALSO" See also mysql-profile-compact. .SH "LICENSE" .IX Header "LICENSE" This program is copyright (c) 2007 Baron Schwartz. Feedback and improvements are welcome. .PP \&\s-1THIS\s0 \s-1PROGRAM\s0 \s-1IS\s0 \s-1PROVIDED\s0 \*(L"\s-1AS\s0 \s-1IS\s0\*(R" \s-1AND\s0 \s-1WITHOUT\s0 \s-1ANY\s0 \s-1EXPRESS\s0 \s-1OR\s0 \s-1IMPLIED\s0 \&\s-1WARRANTIES\s0, \s-1INCLUDING\s0, \s-1WITHOUT\s0 \s-1LIMITATION\s0, \s-1THE\s0 \s-1IMPLIED\s0 \s-1WARRANTIES\s0 \s-1OF\s0 \&\s-1MERCHANTIBILITY\s0 \s-1AND\s0 \s-1FITNESS\s0 \s-1FOR\s0 A \s-1PARTICULAR\s0 \s-1PURPOSE\s0. .PP This program is free software; you can redistribute it and/or modify it under the terms of the \s-1GNU\s0 General Public License as published by the Free Software Foundation, version 2; \s-1OR\s0 the Perl Artistic License. On \s-1UNIX\s0 and similar systems, you can issue `man perlgpl' or `man perlartistic' to read these licenses. .PP You should have received a copy of the \s-1GNU\s0 General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, \s-1MA\s0 02111\-1307 \s-1USA\s0. .SH "AUTHOR" .IX Header "AUTHOR" Baron Schwartz. .SH "ACKNOWLEDGEMENTS" .IX Header "ACKNOWLEDGEMENTS" I was inspired by the wonderful mysqlreport utility available at . .PP Other contributors: Bart van Bragt. .PP Thanks to all who have helped. .SH "VERSION" .IX Header "VERSION" This manual page documents Ver 1.1.5 Distrib 1053 \f(CW$Revision\fR $.