Mysqlsla analyzing MySQL Slow query log

Found that there is a tool mysqlsla, analysis query log than Mysqldumpslow analysis will be more clear!

Install Mysqlsla:

Download mysqlsla-2.03.tar.gz

[Email protected] export]# TAR-XVF mysqlsla-2.03.tar.gz

[email protected] export]# Yum install *dbi*

[Email protected] mysqlsla-2.03]# Perl makefile.pl
Can ' t locate time/hires.pm in @INC (@INC contains:/usr/local/lib64/perl5/usr/local/share/perl5/usr/lib64/perl5/ Vendor_perl/usr/share/perl5/vendor_perl/usr/lib64/perl5/usr/share/perl5.) At/usr/local/bin/mysqlsla Line 2095.
BEGIN failed--compilation aborted At/usr/local/bin/mysqlsla line 2095.

Prompt for an error to install:
[Email protected] mysqlsla-2.03]# yum-y install Perl-time-hires

1, total number of queries (queries) the amount of SQL to go back (unique)
2. Contents of output Report sort: Sorted by ' t_sum ' the most significant slow SQL statistics, including average execution time, waiting lock time, total number of result rows, total number of scanned rows
3. Number of executions of count:sql and percentage of total slow log number
4, Time: Execution time, including total time, average time, minimum, maximum time,% of total slow SQL time
5, 95% of time: removal of the fastest and slowest SQL, coverage of 95% of SQL execution times
6. Lock time: Wait for the lock
7, 95% of lock:95% slow SQL wait lock time.
8. Rows sent: Result row statistics, including average, minimum, maximum quantity
9. Rows examined: Number of rows scanned
10, Database: which databases belong to
11. Users: Which user, IP, accounts for the percentage of SQL performed by all users
12. Query Abstract: SQL statement after abstraction
13. Query Sample:sql Statement


Parameter description
-sort
What parameters are used to sort the results of the analysis, by default t_sum to sort
T_sum: Sort by total time
C_sum: Sort by total number of times
C_sum_p:sql statement execution count as a percentage of total executions


-top
Shows the number of SQL, default is 10, which means the first 10 sorted by rule

-db-database
The corresponding database

-statement-filter:
Filter the SQL statement type, such as SELECT, Update, Drop.


10 SQL with the longest execution time in the slow query log
Mysqlsla-lt slow-sf "+select"-top slow.log > Yoon.log

Slow query SQL for all select and update queries in the Slow.log database for Sakila, and query the most frequently 100 SQL
Mysqlsla-lt slow-sf "+select,update"-top 100-sort c_sum-db sakila slow.log > Yoon.log

Take the SELECT statement in the Database Sakila library, the first 2 lines sorted by c_sum_p
Mysqlsla-lt slow-sort c_sum_p-sf "+select"-db Sakila-top2/export/servers/mysql/log/slow.log

In the slow query log, take the 3 SQL statements that have taken the longest execution time
Mysqlsla-lt Slow--top 3 Slow.log

According to the total number of executions
Mysqlsla-lt Slow--top 3--sort c_sum Slow.log


To remove a slow query for the CREATE statement
Mysqlsla-lt slow-sf "+create"--top 3--sort c_sum slow.log > Yoon.log

Mysqlsla analyzing MySQL Slow query log