MK-QUERY-PROFILER(1)

📦
version 7540 · 3 versions
, in fedora 43

🎀 MK-QUERY-PROFILER(1) 🎀

okay so bestie, this is a User Contributed Perl Documentation page for mk-query-profiler, and I'm gonna walk you through it like we're texting at 2am. it's long, it's iconic, and by the end you'll be profiling like a whole main character. let's go 🦋

✨ NAME ✨

mk-query-profiler — execute SQL statements and print statistics, or measure activity caused by other processes.

💅 SYNOPSIS 💅

Usage: mk-query-profiler [OPTION...] [FILE...]

mk-query-profiler reads and executes queries, and prints statistics about MySQL server load. Connection options are read from MySQL option files. If FILE is given, queries are read and executed from the file(s). With no FILE, or when FILE is -, read standard input. If --external is specified, lines in FILE are executed by the shell. You must specify - if no FILE and you want --external to read and execute from standard input. Queries in FILE must be terminated with a semicolon and separated by a blank line.

so like. it can profile the (semicolon-terminated, blank-line separated) queries in a file, bestie:

mk-query-profiler queries.sql
cat queries.sql | mk-query-profiler
mk-query-profiler -vv queries.sql
mk-query-profiler -v --separate --only 2,5,6 queries.sql
mk-query-profiler --tab queries.sql > results.csv

and it can also just 👀 observe what happens in the server:

mk-query-profiler --external

or it can run shell commands from a file and measure the result:

mk-query-profiler --external commands.txt
mk-query-profiler --external - < commands.txt

read "HOW TO INTERPRET" to learn what it all means. fr fr, don't skip that part.

⚠️ RISKS ⚠️

this section is here to inform you about the potential risks, whether known or unknown, of using this tool. the two main categories of risks are those created by the nature of the tool (e.g. read-only tools vs. read-write tools) and those created by bugs.

mk-query-profiler is generally read-only and very low risk. it will execute FLUSH TABLES if you specify --flush. so like, heads up 🫶

at the time of this release, we know of no bugs that could cause serious harm to users. slay.

the authoritative source for updated information is always the online issue tracking system. issues that affect this tool will be marked as such. you can see a list of such issues at the following URL: http://www.maatkit.org/bugs/mk-query-profiler.

see also "BUGS" for more information on filing bugs and getting help.

🌸 DESCRIPTION 🌸

mk-query-profiler reads a file containing one or more SQL statements or shell commands, executes them, and analyzes the output of SHOW STATUS 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. literally so useful.

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 --external option, or provide input to STDIN.

if the file contains multiple statements, they must be separated by blank lines. if you don't do that, mk-query-profiler won't be able to split the file into individual queries, and MySQL will complain about syntax errors. 😭 so please, blank lines, bestie.

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, SHOW STATUS showed only global status variables, so other queries will interfere and produce false results. mk-query-profiler will try to detect if anything did interfere, but there can be no guarantees.

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. mk-query-profiler will omit any output related to these variables if they're not available.

for more information about SHOW STATUS, read the relevant section of the MySQL manual at http://dev.mysql.com/doc/en/server-status-variables.html.

🔍 HOW TO INTERPRET 🔍

📊 TAB-SEPARATED OUTPUT 📊

if you specify --tab, you will get the raw output of SHOW STATUS 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. 💅

  • most of the command-line options for controlling verbosity and such are ignored in --tab mode.
  • 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. iykyk.
  • 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 SHOW STATUS. After1, After2, etc are the results of running SHOW STATUS after each query in the batch. finally, the last column is the result of running SHOW STATUS just after the last AfterN column, so you can see how much work SHOW STATUS itself causes. yes, the measuring tape measures itself. iconic.
  • if you specify --verbose, output includes every variable mk-query-profiler measures. if not (default) it only includes variables where there was some difference from one column to the next.

📖 NORMAL OUTPUT 📖

if you don't specify --tab, you'll get a report formatted for human readability. this is the default output format.

mk-query-profiler can output a lot of information, as you've seen if you ran the examples in the "SYNOPSIS". what does it all mean? 🤔

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 EXTERNAL, individually profiled queries will have QUERY, and summary will say SUMMARY.

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 http://dev.mysql.com/doc/en/server-status-variables.html.

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 ANY 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. 🤫

while explaining the results in the sections that follow, I'll refer to a value as "protected" 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 "protected." 🫠

⏱️ Overall stats ⏱️

this section shows the overall elapsed time for the query, as measured by Perl, and the optimizer cost as reported by MySQL.

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.

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.

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.

the total number of questions is not protected, but the breakdown of individual question types is, because it comes from the Com_ status variables.

🗂️ 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 "Table locks acquired" and "Temp files" values are unprotected, but everything else in this section is protected.

the "Potential filesorts" 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.

if you specified --allow-cache, you'll see statistics on the query cache. these are unprotected.

🔢 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.

depending on your MySQL version, you'll either see one or two columns of information in this section. the one headed "Handler" is all from the Handler_ variables, and those statistics are protected. if your MySQL version supports it, you'll also see a column headed "InnoDB," which is unprotected.

💾 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.

none of the information in this section is protected.

🌈 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.

✨ OPTIONS ✨

okay bestie this tool takes extra command-line arguments on top of the basics. Peep the SYNOPSIS and the usage info for the deets 💖

  • --allow-cache — let the MySQL query cache, like, actually cache the queries you run. By default this is off. When you turn it on, cache profiling info gets added to the printout. More on the query cache here: <http://dev.mysql.com/doc/en/query-cache.html> 🎀
  • --ask-pass — prompts you for a password when connecting to MySQL. no typing secrets into your shell history, periodt 💅
  • --[no]calibrate — default: yes. Tries to compensate for SHOW STATUS. Basically it measures and compensates for the "cost of observation" that comes from running SHOW STATUS at all. Heads up: this only works reliably on a quiet server — on a busy server other processes can mess the calibration up 😭
  • --charset — short form: -A; type: string. The default character set. If the value is utf8, it sets Perl's binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL ✨
  • --config — type: Array. Reads this comma-separated list of config files. If you specify it, it must be the first option on the command line. no cutting the line bestie 🫶
  • --database — short form: -D; type: string. The database to use for the connection.
  • --defaults-file — short form: -F; type: string. Only read MySQL options from the given file. You must give an absolute pathname, fr fr.
  • --external — calibrate, then pause while an external program runs. This is typically useful while you run an external program. When you press [enter], mk-query-profiler stops sleeping and takes another measurement, then prints statistics as usual. When there's a filename on the command line, mk-query-profiler executes each line in the file as a shell command. If you give - as the filename, mk-query-profiler reads from STDIN. Output from shell commands is printed to STDOUT and terminated with __BEGIN__, after which mk-query-profiler prints its own output 🦋
  • --flush — cumulative: yes. Flush tables. Specify it twice to do it between every query. Calls FLUSH TABLES before profiling. If you're executing queries from a batch file, specifying --flush twice makes mk-query-profiler call FLUSH TABLES between every query, not just once at the beginning. Default is to not flush at all. More: <http://dev.mysql.com/doc/en/flush.html> 🌸
  • --help — show help and exit. the classic escape hatch 😌
  • --host — short form: -h; type: string. Connect to host.
  • --[no]innodb — default: yes. Show InnoDB statistics.
  • --only — type: hash. Only show statistics for this comma-separated list of queries or commands.
  • --password — short form: -p; type: string. Password to use when connecting.
  • --pid — type: string. Create the given PID file. The file contains the process ID of the script. The PID file is removed when the script exits. Before starting, the script checks if the PID file already exists. If it doesn't, the script creates it and writes its own PID to it. If it does exist, the script checks the following: if the file contains a PID and a process is running with that PID, then the script dies 💀; or, if there's no process running with that PID, then the script overwrites the file with its own PID and starts; else, if the file contains no PID, then the script dies. so much drama in one flag, we love to see it
  • --port — short form: -P; type: int. Port number to use for the connection.
  • --separate — print stats separately for each query. The default is to show only the summary of the whole batch. See also --verbose 👀
  • --[no]session — default: yes. Use session SHOW STATUS and SHOW VARIABLES. Disabled if the server version doesn't support it.
  • --set-vars — type: string; default: wait_timeout=10000. Set these MySQL variables. Immediately after connecting to MySQL, this string gets appended to SET and executed ✨
  • --socket — short form: -S; type: string. Socket file to use for the connection.
  • --tab — print tab-separated values instead of whitespace-aligned columns. for when you're piping into other things, iykyk
  • --user — short form: -u; type: string. User for login if not the current user.
  • --verbose — short form: -v; cumulative: yes; default: 0. Verbosity; specify it multiple times for more detailed output. When --tab is given, it prints variables that don't change. Otherwise, increasing the level of verbosity includes extra sections in the output 🔥
  • --verify — verify nothing else is accessing the server. This is a weak verification; it simply calibrates twice (see --[no]calibrate) and verifies that the cost of observation stays constant.
  • --version — show version and exit.

🌈 DSN OPTIONS 🌈

These DSN options get used to create a DSN. Each option is given like option=value. The options are case-sensitive, so P and p are not the same option. There cannot be whitespace before or after the =, and if the value contains whitespace it must be quoted. DSN options are comma-separated. See the maatkit manpage for full details 🎀

  • A — dsn: charset; copy: yes. Default character set.
  • D — dsn: database; copy: yes. Default database.
  • F — dsn: mysql_read_default_file; copy: yes. Only read default options from the given file.
  • h — dsn: host; copy: yes. Connect to host.
  • p — dsn: password; copy: yes. Password to use when connecting.
  • P — dsn: port; copy: yes. Port number to use for the connection.
  • S — dsn: mysql_socket; copy: yes. Socket file to use for the connection.
  • u — dsn: user; copy: yes. User for login if not the current user.

💾 DOWNLOADING 💾

You can download Maatkit from Google Code at <http://code.google.com/p/maatkit/>, or you can grab any of the tools easily with a command like this:

wget http://www.maatkit.org/get/toolname
or
wget http://www.maatkit.org/trunk/toolname

Where toolname can be swapped for the name (or a fragment of a name) of any of the Maatkit tools. Once downloaded, they're ready to run — no installation needed, which is lowkey iconic. The first URL gets the latest released version of the tool, and the second gets the latest trunk code from Subversion ✨

🌱 ENVIRONMENT 🌱

The environment variable MKDEBUG enables verbose debugging output in all of the Maatkit tools:

MKDEBUG=1 mk-....

🧚 SYSTEM REQUIREMENTS 🧚

You need Perl, DBI, DBD::mysql, and some core modules. that's it bestie, go forth 🫶

💀 BUGS 💀

For a list of known bugs see <http://www.maatkit.org/bugs/mk-query-profiler>.

Please use Google Code Issues and Groups to report bugs or request support: <http://code.google.com/p/maatkit/>. You can also join #maatkit on Freenode to discuss Maatkit 🙈

Please include the complete command-line used to reproduce the problem you're seeing, the version of all MySQL servers involved, the complete output of the tool when run with --version, and if possible, debugging output produced by running with the MKDEBUG=1 environment variable. help us help you, fr fr 💖

This program is copyright 2007-2011 Baron Schwartz. Feedback and improvements are welcome 🎀

THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, version 2; OR the Perl Artistic License. On UNIX and similar systems, you can issue man perlgpl or man perlartistic to read these licenses 📜

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.

🔍 SEE ALSO 🔍

See also mk-profile-compact. yes bestie, they're a matched set 💅

✍️ AUTHOR ✍️

Baron Schwartz

🌟 ABOUT MAATKIT 🌟

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

🎀 ACKNOWLEDGEMENTS 🎀

I was inspired by the wonderful mysqlreport utility available at <http://www.hackmysql.com/> 🌸

Other contributors: Bart van Bragt.

Thanks to all who have helped. 🫶

⭐ VERSION ⭐

This manual page documents Ver 1.1.22 Distrib 7540 $Revision: 7477 $.

and that's the whole page bestie!! this one's perl v5.42.0, dated 2025-07-24, and it lives at MK-QUERY-PROFILER(1) — go profile some queries and slay 💖✨🎀

📦 where this page comes from