MySQL Database slow log analysis tool Mysqlsla use tutorial

Mysqlsla is hackmysql.com launched a MySQL log analysis tool, the function is very powerful. Data reports are very useful for analyzing the causes of slow queries, including execution frequency, data volume, query consumption, etc.

First, use

Mysqlsla-lt Slow Mysql-slow.log

Or

Mysqlsla-lt Slow MYSQL-SLOW.LOG-SF "+select"-db dbname-top, 10-sort t_sum

Parameter meaning

LT: Indicates log type, with slow, general, binary, MSL, UDL.
Sf:[+-][type],[type] has SELECT, CREATE, DROP, UPDATE, INSERT, such as "+select,insert", does not appear by default is-, that is not included.
DB: The log of which library to process.
Top: Indicates how many previous bars are sorted by rule.
Sort: Sorted by some rule, t_sum by total time, c_sum sorted by total number of times.

II. Results of analysis

Total number of queries (queries totals), after the amount of SQL (unique)

Sort the contents of the output report (sorted by)

The most significant slow SQL statistics, including the average execution time, the wait lock time, the total number of resulting rows, and the total number of rows scanned.

Count, the number of executions of SQL and the percentage of the total number of slow logs.
Time, execution times, including total time, average time, minimum, maximum time, and time as a percentage of total slow SQL time.
Of time, the fastest and slowest SQL is removed, with coverage accounting for 95% of SQL execution times.
Lock time, waiting for the lock.
of lock, 95% slow SQL wait lock time.
Rows sent, the result row statistics quantity, including average, minimum, maximum quantity.
Rows examined, the number of lines scanned.
database, which databases belong to
Users, which user, IP, percent of SQL executed by all users
Query abstract, abstract SQL statement
Query sample, SQL statement

Third, installation

Method One

Download Mysqlsla from Http://hackmysql.com/mysqlsla, currently the latest version is: 2.03

Tar zxvf mysqlsla-2.03.tar.gz
CP Mysqlsla-2.03/bin/mysqlsla/usr/local/bin
If you are prompted "Can ' t locate dbi.pm", execute the following command

Yum Install perl-dbi perl-dbd-mysql-y
Then you can use the Mysqlsla, as follows

Mysqlsla-lt Slow Slow.log

Method Two

Wgethttp://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
# tar XZVF mysqlsla-2.03.tar.gz
mysqlsla-2.03/
Mysqlsla-2.03/changes
Mysqlsla-2.03/install
Mysqlsla-2.03/readme
mysqlsla-2.03/makefile.pl
mysqlsla-2.03/bin/
Mysqlsla-2.03/bin/mysqlsla
Mysqlsla-2.03/meta.yml
mysqlsla-2.03/lib/
mysqlsla-2.03/lib/mysqlsla.pm
Mysqlsla-2.03/manifest
# CD mysqlsla-2.03
# LL
Total 68

Drwxr-xr-x 2 1000 1000 4096 2008-11-11 bin
-RW-R–R–1 1000 1000 5630 2008-11-11 Changes
-RW-R–R–1 1000 1000 394 2008-07-13 INSTALL
Drwxr-xr-x 2 1000 1000 4096 2008-11-11 Lib
-RW-R–R–1 1000 1000 389 2008-07-13 makefile.pl
-RW-R–R–1 1000 1000 152 2008-07-13 MANIFEST
-RW-R–R–1 1000 1000 303 2008-11-11 meta.yml
-RW-R–R–1 1000 1000 1969 2008-11-11 README
# less INSTALL

Installing Mysqlsla
===================

Perl makefile.pl
Make
Make install

Depending on your system, the Mysqlsla script would be copied to some common bin
Directory (/usr/local/bin/for example). Then you should is able to run it from
The command line like:mysqlsla-lt slow LOG

For quick help, Mans Mysqlsla

For all documentation and guides, visit Http://hackmysql.com/mysqlsla

# less makefile.pl
Use 5.008004;
Use Extutils::makemaker;

Writemakefile (
NAME => ' Mysqlsla ',
version_from => ' lib/mysqlsla.pm ',
prereq_pm => {},
($) &G t;= 5.005?
(abstract_from => ' lib/mysqlsla.pm ', # Retrieve ABSTRACT from module
AUTHOR => ' Daniel nichter (http://hack mysql.com/contact): ()),
Exe_files => [' Bin/mysqlsla '],
,
# perl makefile.pl
Checking If your kit is complete ...
Looks Good
Writing Makefile for Mysqlsla
# ll
Grand total
Drwxr-xr-x 2 1000 1000 4096 2008-11-11 bin
-R W-R–R–1 1000 1000 5630 2008-11-11 Changes
-rw-r–r–1 1000 1000 394 2008-07-13 INSTALL
Drwxr-xr-x 2 1000 1000 4096 2008-11-11 lib
-rw-r–r–1 root root 22722 09-08 18:52 Makefile
-rw-r–r–1 1000 1000 389 2008-07-13 makefile.pl
-rw-r–r–1 1000 1000 152 2008-07-13 MANIFEST
-rw-r–r–1 1000 1000 303 2008-11-11 meta.yml
-rw-r–r–1 1000 1000 1 969 2008-11-11 README
 

# make
CP lib/mysqlsla.pm BLIB/LIB/MYSQLSLA.PM
CP Bin/mysqlsla Blib/script/mysqlsla
/usr/bin/perl "-mextutils::my"-E "my->fixin (shift)" Blib/script/mysqlsla
Manifying blib/man3/mysqlsla.3pm
# make Install
installing/usr/lib/perl5/site_perl/5.8.8/mysqlsla.pm
installing/usr/share/man/man3/mysqlsla.3pm
Installing/usr/bin/mysqlsla
Writing/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/mysqlsla/.packlist
Appending installation Info To/usr/lib/perl5/5.8.8/i386-linux-thread-multi/perllocal.pod
By default, the command Mysqlsla is installed to the/usr/bin directory, and the DBI module is not installed when the mysqlsla–help is executed, and Mysqlsla is written in Perl.

The first time you install the Perl module through CPAN, you need to configure the configuration, most of the configuration using the default value, all the way to enter.

#perl-mcpan-e Shell

You can then change the configured options in each of the following queries, and then execute the following command to save the

Cpan>reload Index
Cpan>reload CPAN

Installing the DBI Module

Cpan>install DBI
If you are entering the CPAN system for the first time and want to modify the previous configuration, you can type the following instructions on the CPAN command line (that is, cpan>):

Cpan>o conf Init
Also execute the following command to save the

Cpan>reload Index
Cpan>reload CPAN

Man Mysqlsla can see the relevant options for Mysqlsla

# Mans Mysqlsla
Xxx
XXX warning:old character encoding and/or character set
Xxx
Mysqlsla (3) User contributed Perl documentation Mysqlsla (3)

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.
It also supports user-defined logs.

This Pod/man page are only a very brief outline the usage and command line options. For the full library of
Mysqlsla documentation Visithttp://hackmysql.com/mysqlsla.

# Mysqlsla/tmp/mysqlslow.log | Less
auto-detected logs as slow logs
Slow logs:/tmp/mysqlslow.log
6 queries total, 6 unique
Sorted by ' T_sum '
Grand Totals:time 0 S, Lock 0 s, rows sent, rows examined 13
______________________________________________________________________ 001 ___
Count:1 (16.67%)
time:2.321 Ms Total, 2.321 MS Avg, 2.321 ms to 2.321 Ms Max (47.2%)
Lock time (s): 629. s total, 629 s AVG, 629. S to 629? s Max (77.75%)
Rows Sent:8 AVG, 8 to 8 Max (61.54%)
Rows Examined:8 AVG, 8 to 8 Max (61.54%)
Database:
Users:
root@ 192.168.1.100:100.00% (1) of query, 100.00% (6) All users

Query Abstract:
SET timestamp=n; Show variables like ' S ';

Query Sample:
SET timestamp=1252395365;
Show variables like '%character% ';
......
The format description is as follows:

Total number of queries (queries totals), after the amount of SQL (unique)
Sort the contents of the output report (sorted by)
The most significant slow SQL statistics, including the average execution time, the wait lock time, the total number of resulting rows, and the total number of rows scanned.

Count, the number of executions of SQL and the percentage of the total number of slow logs.
Time, execution times, including total time, average time, minimum, maximum time, and time as a percentage of total slow SQL time.
Of time, the fastest and slowest SQL is removed, with coverage accounting for 95% of SQL execution times.
Lock time, waiting for the lock.
of lock, 95% slow SQL wait lock time.
Rows sent, the result row statistics quantity, including average, minimum, maximum quantity.
Rows examined, the number of lines scanned.
database, which databases belong to
Users, which user, IP, percent of SQL executed by all users
Query abstract, abstract SQL statement
Query sample, SQL statement
In addition to the above output, the official also provides a lot of customization parameters, is a rare good tool.