Use of mysqlsla, a log tool for slow database query, which is more powerful than mysqldumpslow.

Use of mysqlsla, a log tool for slow database query, which is more powerful than mysqldumpslow.

Install mysqlsla
Source code path: https://github.com/daniel-nichter/hackmysql.com
Source code storage path:/usr/local/src
1. Get source code
If you do not have the git command, install git first.
Yum install git

Cd/usr/local/src

Git clone https://github.com/daniel-nichter/hackmysql.com.git

Cp-Rf hackmysql.com/opt/mysqlMonitor

Cd/opt/mysqlMonitor/mysqlsla

Perl Makefile. PL

Make & make install
Result:
/Usr/bin/perl-MExtUtils: MY-e 'my-> fixin (shift) '-- blib/script/mysqlsla
Manifying blib/man3/mysqlsla.3pm
Installing/usr/local/share/perl5/mysqlsla. pm
Installing/usr/local/share/man/man3/mysqlsla.3pm
Installing/usr/local/bin/mysqlsla
Appending installation info to/usr/lib64/perl5/perllocal. pod

2. install other packages
Yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-Time-HiRes perl-DBI

List the installation packages
Yum list perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-Time-HiRes perl-DBI

Lt: indicates the log type, including slow, general, binary, msl, and udl.
Sf: [+-] [TYPE], [TYPE] Has SELECT, CREATE, DROP, UPDATE, INSERT, for example, "+ SELECT, INSERT". The default value -, that is, it is not included.
Db: the log of the database to be processed.
Top: the number of first entries sorted by rules.
Sort: sort by certain rules, t_sum: sort by the total time; c_sum: sort by the total number of times; c_sum_p: percentage of the number of SQL statement executions to the total number of executions.

# Enable slow query log item Configuration

Mysql Performance Optimization Configuration my. cnf file: http://www.cnblogs.com/NiceTime/p/6848132.html

Slow_query_log_file =/opt/mysql/mysqllog/logfile/slow-query.log depends on the mysql slow query log path you have installed.

 

#20 SQL statements with the longest execution time (default time duration)
[Root @ localhost mysqllog] # mysqlsla-lt slow -- sort t_sum -- top 20/opt/mysql/mysqllog/logfile/slow-query.log

# Count all select slow query SQL statements in the slow Query file, and display the 20 SQL statements with the longest execution time
[Root @ localhost mysqllog] # mysqlsla-lt slow-sf "+ select"-top 20/opt/mysql/mysqllog/logfile/slow-query.log

# Count all select and update slow query SQL statements in the slow Query file where the database in the slow Query file is test, and the 20 SQL statements with the maximum number of queries are written to SQL _num.log
[Root @ localhost mysqllog] # mysqlsla-lt slow-sf "+ select, update "-top 20-sort c_sum-db test/opt/mysql/mysqllog/logfile/slow-query.log>/tmp/slowQuery. log

# Mysqlsla output format description:
Queries total: total number of queries, unique: Number of SQL statements after deduplication
Sorted by: sorts the output report content
Count: Number of SQL executions and percentage of SQL statements in the total slow log Count
Time: the execution Time, including the total Time, average Time, minimum, maximum Time, and Time as a percentage of the total slow SQL Time.
95% of Time: the fastest and slowest SQL statements are removed, and the SQL Execution Time with a coverage rate of 95%
Lock Time: Lock wait Time
95% of Lock: 95% of slow SQL wait Lock time
Rows sent: Number of result Rows, including average, minimum, and maximum
Rows examined: number of Rows scanned
Database: Which Database does the Database belong?
Users: User, IP address, percentage of SQL executed by all Users
Query abstract: abstract SQL statements
Query sample: SQL sample statement