NAME

mysqlsla - Parse, filter, analyze and sort MySQL slow, general and binary logs

SYNOPSIS

    # Basic operation: parse a MySQL slow or general log
    mysqlsla --log-type slow LOG
    mysqlsla --log-type general LOG

    # Parse output from mysqlbinlog
    # mysqlsla cannot directly parse binary logs
    mysqlbinlog LOG | mysqlsla --log-type binary -

    # Parse a microslow patched slow log
    mysqlsla --log-type msl LOG

    # Replay a replay file
    mysqlsla --replay FILE

    # Parse a user-defined log specify its format
    mysqlsla --log-type udl --udl-format FILE

    # Let mysqlsla automatically determine the log type
    mysqlsla LOG

DESCRIPTION

mysqlsla parses, filters, analyzes and sorts MySQL slow, general, binary and microslow patched slow logs. It also supports user-defined logs.

This POD/man page is only a very brief outline of usage and command line options. For the full library of mysqlsla documentation visit http://hackmysql.com/mysqlsla.

.mysqlsla CONFIG FILE

Reading "~/.mysqlsla" is the very first thing mysqlsla does when it starts. Command line options can be set in this file (one per line). Example:

atomic-statements

statement-filter=+UPDATE,INSERT

Notice: no leading dashes (- or --), no quotations marks ("), and the form "option=value" when "option" requires a value.

These options are overriden by those given on the real command line.

COMMAND LINE OPTIONS

"--log-type (-lt) TYPE LOGS"

Parse MySQL LOGS of TYPE. Default none. TYPE must be either slow, general, binary, msl or udl. LOGS is a space-separated list of MySQL log files.

As of mysqlsla v2.03 this option is optional. If given, mysqlsla will treat the logs as the given type. If not given, mysqlsla will automatically detect the log type by examining the first log file.

Binary logs are a special case. mysqlsla cannot read MySQL binary log directly. You must first "decode" the binary log using mysqlbinlog without the --short-form option. It is only the text output from mysqlbinlog that mysqlsla can read.

If you want to use the --short-form option with mysqlbinlog you must then use TYPE udl with mysqlsla.

LOGS can also be - to make mysqlsla read from STDIN. This can be used to pipe the output of another program into mysqlsla. However, if you use - to read from STDIN, you must explicitly give the log type; mysqlsla cannot automatically detect the log type from STDIN.

"--abstract-in (-Ai) N"

Abstract IN () clauses further by grouping in groups of N. Disabled by default.

"--abstract-values (-Av)"

Abstract VALUES () clauses further by removing the number of condensed value sets. Disabled by default.

"--atomic-statements"

Treat multi-statment groups atomically when filtering. Disabled by default.

"--avg (-n) N"

Average query timing over N executions. Default 1.

"--databases (-db) (-D) DATABASES"

Try EXPLAINing queries which have no known database using DATABASES. Default none. DATABASES is a comma-separated list of database names (without spaces). Only used when option explain is used too.

"--db-inheritance"

Allow queries to inherit the last database specified in the log. Disabled by default.

"--debug"

Enable a flood of debugging information from both mysqlsla and MySQL::Log::ParseFilter. Disabled by default. Use with caution.

"--dist"

Calculate distribution frequency of values. Disabled by default. Requires an appropriate standard report format line.

"--dist-min-percent (-dmin) N"

Do not display dist percents less than N. Default 5.

"--dist-top (-dtop) N"

Display at most N dist percentages. Default 10.

"--dont-save-meta-values"

Do not save meta-property values from log. Disabled by default (meta-property values are saved).

"--explain (-ex)"

EXPLAIN each query. Disabled by default. Requires an appropriate standard report format line.

"--extra (-x) TINFO"

Display extra table information. TINFO can be either tschema, tcount or both like tschema,tcount. Default none.

tschema will cause mysqlsla to print each tables structure (the output of SHOW CREATE TABLE).

tcount will cause mysqlsla to print the number of rows in each table using SELECT COUNT(*) FROM table.

"--flush-qc"

Flush query cache before query execution timing. Disabled by default.

"--grep PATTERN"

grep statements for PATTERN and keep only those which match. Default none.

"--help (-?)"

Tells you to see what you are reading right now.

"--host ADDRESS"

Connect to MySQL at host ADDRESS. Default localhost if no socket is available.

"--meta-filter (-mf) CONDTIONS"

Set meta-properties filter using CONDITIONS. Default none. CONDITIONS is a comma-separated list of meta-property conditions (without spaces) in the form: [meta][op][value].

[meta] refers to a meta-property name, the list of which is long: see mysqlsla v2 Filters.

[op] is either > < or =. [value] is the value, numeric or string, against which the value for [meta] from the log must be true according to [op].

For string-based [meta], like db or host, [op] can only be =.

"--microsecond-symbol (-us) STRING"

Use STRING to denote microsecond values. Default Xs.

"--no-mycnf"

Do not read ~/.my.cnf when initializing. Does not apply to Windows servers.

"--nthp-min-values (nthpm) N"

Do not calculate Nth percent values if there are less than N values. Default 10.

"--nth-percent (-nthp) N"

Calculate Nth percent values. Disabled by default or 95 if used but no N is given. Requires an appropriate standard report format line.

"--password PASS"

Use PASS as MySQL user password. If PASS is omitted, the password will be prompted for (on STDERR).

"--percent"

Display a basic percentage complete indictor while timing all queries for the time-all report. Disabled by default.

"--port PORT"

Connect to MySQL on PORT. Default none (relies on system default which will be 3306).

"--post-analyses-replay FILE"

Save a post-analyses replay as FILE.

"--post-parse-replay FILE"

Save a post-parse replay as FILE.

"--post-sort-replay FILE"

Save a post-sort replay as FILE.

"--replay FILE"

Load unique queries from replay FILE. Default none.

"--report-format (-rf) FILE"

Use FILE to format the standard report. Default internal report format.

"--reports (-R) REPORTS"

Print REPORTS. Default standard. REPORTS is a comma-separated list of report names (without spaces).

Available reports are: standard, time-all, print-unique, print-all, dump.

WARNING: A safety SQL statement filter of "+SELECT,USE" is automatically set when using time-each-query or the time-all report. Overriding the safety SQL statement filter by explicitly setting another with statement-filter can permit real changes to databases. Use with caution!

"--save-all-values"

Save extra "all values" for some meta-properties. Disabled by default.

"--silent"

Do not print any reports. Disabled by default. Debug messages will still be printed.

"--socket SOCKET"

Connect to MySQL through SOCKET. Default none (relies on system default which is compiled into the MySQL client library).

"--sort META"

Sort queries according to META. Default t_sum for slow and msl logs, c_sum for all others. META is any meta-property name.

"--statement-filter (-sf) CONDTIONS"

Set SQL statement filter using CONDITIONS. Default none. CONDITIONS is a comma-separated list of SQL statement types in the form: [+-][TYPE],[TYPE],etc.

The [+-] is given only once before the first [TYPE]. A + indicates a positive filter: keep only SQL statements of [TYPE]. A - indicates a negative filter: remove only SQL statements of [TYPE]. If neither is given, - is default.

[TYPE] is a SQL statement type: SELECT, CREATE, DROP, UPDATE, INSERT, etc.

"--time-each-query (-te)"

Time each query by actually executing it on the MySQL server. Disabled by default. Requires an appropriate standard report format line.

WARNING: A safety SQL statement filter of "+SELECT,USE" is automatically set when using time-each-query or the time-all report. Overriding the safety SQL statement filter by explicitly setting another with statement-filter can permit real changes to databases. Use with caution!

"--top N"

After sorting display only the top N queries. Default 10.

"--udl-format (-uf) FILE"

Use FILE to define the format of the user-defined log (udl) instead of the default. Default is ";\n" record separator and no headers.

"--user USER"

Connect to MySQL as USER. Default user of mysqlsla process.

CUSTOM REPORT FORMATS

The standard report is the human-readable report which shows all the numbers and values calculated from the log. If no other report is specified, it is the default report.

mysqlsla automatically formats the standard report according to a report format depending on the log type being parsed. Therefore, the standard report for general logs is different from slow logs and binary logs, etc. mysqlsla has, internally, basic report formats for every log type, but a custom report format can be explicitly set by using the "--report-format" option.

Read http://hackmysql.com/mysqlsla_reports for more information on creating and customizing report formats.

BUGS

I follow the zero known bugs release policy in releasing new versions of mysqlsla. Certainly, however, bugs still exist somewhere. So when you find one, contact me through the web site at http://hackmysql.com/contact.

AUTHOR

Daniel Nichter (http://hackmysql.com/)

COPYRIGHT AND LICENSE

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; either version 2 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

The GNU General Public License is available at: http://www.gnu.org/copyleft/gpl.html