AD

[Reserved] mysqlsla log analysis tool

mysqlsla log analysis tool

mysqlsla is hackmysql** introduced a MySQL log analysis tool, very powerful. data report is very useful for analyzing the reasons for slow queries, including the execution frequency, the amount of data, query consumption.
First, the use of
mysqlsla-lt slow mysql-slow.log
Or
mysqlsla-lt slow mysql-slow.log-sf "+ SELECT"-db dbName-top 10-sort t_sum

Meaning of the parameters
lt: that log type, there are slow, general, binary, msl, udl.
sf :[+-][ TYPE], [TYPE] have SELECT, CREATE, DROP, UPDATE, INSERT, for example, "+ SELECT, INSERT", the default is not there - that does not include.
db: what libraries have to deal with the log.
top: means to take the first sort by how many rules.
sort: Sort by certain rules, t_sum sorted by total time, c_sum sorted by the total number of times.

Second, the results of the analysis the total number of queries (queries total), to the heavy volume after the sql (unique)

Sorting out the contents of the report (sorted by)

The most significant slow sql statistics, including the average execution time, waiting for the lock time, the total number of result rows, the total number of scan lines.

Count, sql execution times and the total percentage of the number of slow log.

Time, execution time, including total time, average, minimum, maximum time, time accounting for the total percentage of time slow sql.

95% of Time, removal of the fastest and slowest sql, sql coverage accounted for 95% of the execution time.

Lock Time, waiting for the lock time.

95% of Lock, 95% of the slow sql lock wait time.

Rows sent, the number of result rows statistics, including average, minimum, maximum number.

Rows examined, the number of scan lines.

Database, which belongs to the database

Users, which the user, IP, accounting for the percentage of all users execute the sql

Query abstract, abstraction, after the sql statement

Query sample, sql statement

Third, the installation

Method One to download from **hackmysql**/mysqlsla mysqlsla, the current latest version is: 2.03
tar zxvf mysqlsla-2.03.tar.gz
cp mysqlsla-2.03/bin/mysqlsla / usr / local / bin

If prompted to "Can't locate DBI.pm", execute the following command

yum install perl-DBI perl-DBD-MySQL-y

Then you can use mysqlsla, and use the following

mysqlsla-lt slow slow.log

Method Two #
wgethttp: / / hackmysql**/scripts/mysqlsla-2.03.tar.gz
-18:44:18 - Http://hackmysql**/scripts/mysqlsla-2.03.tar.gz
Resolving host hackmysql** ... 64.13.232.157
Connecting to hackmysql** | 64.13.232.157 |: 80 ... connected.
HTTP request sent, awaiting response ... 200 OK
Length: 33674 (33K) [application / x-tar]
Saving to: `mysqlsla-2.03.tar.gz '

100 %[=============================================== ======================================>] 33,674 45.9K / s in 0.7s

18:44:20 (45.9 KB / s) - `mysqlsla-2.03.tar.gz 'saved [33674/33674]

# 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 will be copied to some common bin
directory (/ usr / local / bin / for example). Then you should be able to run it from
the command line like: mysqlsla-lt slow LOG

For quick help, man mysqlsla

For all documentation and guides, visit **hackmysql**/mysqlsla

# Less Makefile.PL
use 5.008004;
use ExtUtils:: MakeMaker;

WriteMakefile (
NAME => 'mysqlsla',
VERSION_FROM => 'lib / mysqlsla.pm',
PREREQ_PM => {},
($]> = 5.005?
(ABSTRACT_FROM => 'lib / mysqlsla.pm', # retrieve abstract from module
AUTHOR => 'Daniel Nichter (**hackmysql**/contact)'): ()),
EXE_FILES => ['bin / mysqlsla'],
);
# Perl Makefile.PL
Checking if your kit is complete ...
Looks good
Writing Makefile for mysqlsla

# Ll
Total 96
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 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 1969 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

The default installation of the command mysqlsla to / usr / bin directory prompt implementation mysqlsla-help did not install the DBI module, mysqlsla is written in perl

The first installation of perl modules through CPAN, the need for the relevant configuration, most of the configuration using the default, you can enter all the way
# Perl-MCPAN-e shell
Then ask the following individual has been configured in the options can be changed and then execute the following command to save the
CPAN> reload index
CPAN> reload cpan
Install the DBI module
CPAN> install DBI

If CPAN is the first time since entering the system, want to change the previous configuration, you can CPAN command line (ie CPAN>), type the following command:
CPAN> o conf init
At the same time to execute the following command to save the
CPAN> reload index
CPAN> reload cpan

man mysqlsla can see the relevant options mysqlsla
# Man 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 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 visithttp: / / hackmysql** / mysqlsla.

# Mysqlsla / tmp / mysqlslow.log | less
Auto-detected logs as slow logs
Report for slow logs: / tmp / mysqlslow.log
6 queries total, 6 unique
Sorted by 't_sum'
Grand Totals: Time 0 s, Lock 0 s, Rows sent 13, 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.20%)
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:
[email protected]: 100.00% (1) of query, 100.00% (6) of all users

Query abstract:
SET timestamp = N; SHOW variables LIKE 'S';

Query sample:
SET timestamp = 1252395365;
show variables like '% character%';
... ...

Format as follows:
The total number of queries (queries total), after the sql to the number of heavy (unique)
Sorting out the contents of the report (sorted by)
The most significant slow sql statistics, including the average execution time, waiting for the lock time, the total number of result rows, the total number of scan lines.

Count, sql execution times and the total percentage of the number of slow log.
Time, execution time, including total time, average, minimum, maximum time, time accounting for the total percentage of time slow sql.
95% of Time, removal of the fastest and slowest sql, sql coverage accounted for 95% of the execution time.
Lock Time, waiting for the lock time.
95% of Lock, 95% of the slow sql lock wait time.
Rows sent, the number of result rows statistics, including average, minimum, maximum number.
Rows examined, the number of scan lines.
Database, which belongs to the database
Users, which the user, IP, accounting for the percentage of all users execute the sql

Query abstract, abstraction, after the sql statement
Query sample, sql statement

Apart from the above output, the official but also offers many customization parameters, is a rare good tool.

Attached to the official web** **hackmysql**/mysqlsla
标签: data query, lt, sql statement, sql query, abstraction, maximum number, time time, execution time, database users, time c, consumption, maximum time, result rows, time lock, log analysis tool, log time, scan lines, time execution, execution times, number rows
分类: Database
时间: 2011-06-07

相关文章

  1. A log analysis tool splunk

    Splunk is a Unix environment running on log analysis software. With Google Analytics this type of Web log anal ...
  2. AWStats log analysis tool

    The following tutorial is installed under the windows2003 server configuration, as by awstats perl program to ...
  3. Tomcat access log analysis tool

    http://www.blogjava.net/xmatthew/archive/2008/04/14/192450.html Normal web server logs of two parts: 1: is a r ...
  4. web server log statistics tool - awstats

    Awstats is a free, very simple but powerful statistical tools have personality. It statistics for your site th ...
  5. Oracle log analysis tools - LogMiner Detailed

    LogMiner ---- Oracle 8i provided after the self through the form of SQL commands to query and resolve redo (re ...
  6. [Switch] with awstats log analysis of some records Nginx

    Original Address: http://www.linuxbyte.org/yong-awstats-fen-xi-nginx-ri-zhi-de-yi-xie-ji-lu.html System enviro ...
  7. Talk about log analysis

    Log Analysis Overview: Log in the computer system is a very broad concept, there may be any program output log ...
  8. AWStats Description: Apache / Windows IIS log analysis tools to download, install, configure and use sample

    http://127.0.0.1:8600/awstats/awstats.pl?config=test You do not have the patience to read all the content: a b ...
  9. IIS log analysis methods and tools

    The importance of logging has been more and more attention to the programmer, IIS log is self-evident. Recomme ...
  10. Understand and use Oracle log analysis tools-LogMiner

    This article is reproduced, the first author is unknown, please contact me. Oracle LogMiner is Oracle 8i from ...
  11. Oracle archive log analysis - LogMiner (rpm)

    Log Analysis Technical Overview: As the Oracle DBA, we sometimes need to track the malicious user data acciden ...
  12. Flex Builder 3.x in the performance analysis tool

    flex profile collection This quote from http://blog.minidx.com/2008/12/21/1800.html # _Toc217397958 Using the ...
  13. Demonstrate a strong command line under linux - command-line log analysis buttoned

    Demonstrate a strong command line under linux - command-line log analysis buttoned Recently, a need to analyze ...
  14. Crash Analysis Tool using the Linux dump file

    Preface Linux kernel (the core) is a process not associated with a particular feature set, the kernel code is ...
  15. Cover: A Coverage Analysis Tool for Erlang

    == == Cover: A Coverage Analysis Tool for Erlang <br> Coverage analysis tool for Erlang Level: module | ...
  16. MySQL monitoring tools and log analysis tools to summarize

    Monitoring tools: innotop ( installation and description ), mysqlsniffer ( http://hackmysql.com/mysqlsniffer ) ...
  17. apache log analysis software Weg Log Explorer

    apache log analysis software Weg Log Explorer
  18. [Maintenance] mysql log analysis, summary

    mysql> show master logs; shows the number of binary logs mysql> show variables like 'log_bin'; confirm y ...
  19. mdxReplay SQL Server Analysis Tool

    mdxReplay SQL Server Analysis Tool 网站 : http://mdxreplay.codeplex.com/ mdxReplay SQL Server Analysis Tool 是一个命 ...