MySQL Slow query log analysis tool Mysqlsla (GO)

The slow query log of the MySQL database is an important tuning auxiliary log, but the log format of the MySQL default record is not friendly enough to read, which is determined by the MySQL logging rule, capturing one record, although the recorded information is sufficiently detailed, But if browsing the slow query log as a daily routine, reading the slow query log generated by MySQL can be inefficient.

In addition to operating system commands to view Slowlog directly, MySQL also provides a command-line tool to read Slowlog: Mysqldumpslow, which provides an analysis summary function that abstracts multiple similar SQL statements into one, But the function is still somewhat humble, in addition, there are a lot of third-party tools, can be used to analyze the MySQL slow query log, which, think twice mysqlsla, feel simple and easy to use.

Mysqlsla not only can be used to handle the slow query log, but also to analyze other logs such as binary log, ordinary query log and so on, its abstract function for SQL statements is very practical, simple and easy to use parameters set, very good to get started.

The latest version of the current Mysqlsla is 2.03, can be pulled down to download the website, the address is as follows:
Http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz

Mysqlsla is a Perl-written script that runs Mysqlsla requires PERL-DBI and per-dbd-mysql two module support, so you need to install the DBI module and the corresponding database DBD driver before running Mysqlsla, By default, Linux does not install these two modules, you need to download the installation itself, as follows:
Http://www.cpan.org/modules/by-module/DBI/DBI-1.608.tar.gz
Http://www.cpan.org/modules/by-module/DBD/DBD-mysql-4.011.tar.gz

The compile and install steps for DBI are as follows:
# tar Xvfz dbi-1.608.tar.gz
# CD DBI-1.608
# Perl makefile.pl
# make
# Make Test
# make Install

The compile and install steps for the Dbd-mysql driver module are as follows:
# tar Xvfz dbd-mysql-4.011.tar.gz
# CD dbd-mysql-4.011
# Perl makefile.pl
# make
# make Install

Note that you need to use Mysql_config when installing Dbd-mysql, which is included in the Mysql-devel installation package, and if the software is not installed on the current system, you need to install Mysql-devel first. Otherwise dbd-mysql errors occur during the compilation process.

Ready to work completely, you can install Mysqlsla, compile and install the following steps:
# tar Xvfz mysqlsla-2.03.tar.gz
# CD mysqlsla-2.03
# Perl makefile.pl
# make
# make Install

The Mysqlsla command is saved to the/usr/bin path by default, and can usually be executed directly under any path. For the analysis of Slow query log files, the most simplified way to call is as follows:
# mysqlsla-lt Slow [Slowlogfilepath] > [Resultfilepath]

How to use:

Using Mysqlsla to analyze the MySQL slow query log

#查询记录最多的20个sql语句, and write to Select.log mysqlsla-lt slow--sort t_sum--top-/data/mysql/127-slow.log >/tmp/  select.log# statistics slow Query SQL for all select of/data/mysql/127-slow.log, and shows the longest execution time of 100 SQL, and writes to Sql_select.log MYSQLSLA-LT Slow  -sf "+select"-top  /data/mysql/127-slow.log >/tmp/sql_select.log# Statistics slow query file is/data/mysql/127- The Slow.log database is a slow query SQL for all select and update of MyData, and queries up to 100 SQL, and writes to Sql_num.sql mysqlsla-lt slow  -sf "+select, Update "-top 100-sort c_sum  -db mydata/data/mysql/127-slow.log >/tmp/sql_num.log

For example, the original slow log has a bunch of the following statements:
# time:110417 0:00:09
#[email protected]: Junsansi[junsansi] @ [192.168.1.27]
# query_time:3 lock_time:0 rows_sent:1 rows_examined:17600
Select min (doc_his_id) as doc_his_id from t_******** where doc_his_isteammate=1 and doc_his_editor_user_id_encrypt= ' Nfeacawqew1mican2 ';
#[email protected]: Junsansi[junsansi] @ [192.168.1.27]
# query_time:4 lock_time:0 rows_sent:1 rows_examined:17600
Select min (doc_his_id) as doc_his_id from t_******** where doc_his_isteammate=1 and doc_his_editor_user_id_encrypt= ' Nfeacawqew2mican2 ';
#[email protected]: Jss[junsansi] @ [192.168.1.26]
# query_time:4 lock_time:0 rows_sent:1 rows_examined:17600
Select min (doc_his_id) as doc_his_id from t_******** where doc_his_isteammate=1 and doc_his_editor_user_id_encrypt= ' Nfeacawqew3mican2 ';
#[email protected]: Junsansi[junsansi] @ [192.168.1.27]
# query_time:3 lock_time:0 rows_sent:1 rows_examined:17600
Select min (doc_his_id) as doc_his_id from t_******** where doc_his_isteammate=1 and doc_his_editor_user_id_encrypt= ' Nfeacawqew4mican2 ';
#[email protected]: Jss[junsansi] @ [192.168.1.26]
# query_time:5 lock_time:0 rows_sent:1 rows_examined:17600
Select min (doc_his_id) as doc_his_id from t_******** where doc_his_isteammate=1 and doc_his_editor_user_id_encrypt= ' Nfeacawqew5mican2 ';
....................
....................

The operation experience of direct reading is not good, after using Mysqlsla processing, the results are rendered as follows:
count        : 23  (8.52%)
time         : 102 S Total, 4.434783 s AVG, 3 S to 7 s max  (6.79%)
  95% of time:88 s total, 4.190476 s AVG, 3 S to 6 s Max
Lock Time (s): 0 all, 0 avg, 0 to 0 max  (0 .00%)
  95% of lock:0 total, 0 avg, 0 to 0 Max
Rows sent    : 1 avg, 1 to 1 max  (0 .02%)
Rows examined:11.53k avg, 5.70k to 17.60k max  (1.07%)
database     : jssdb< br>users        :  
        [ Email protected] 192.168.1.27:86.96% (+) of query, 11.11% (+) of all users
     &nbs p;  [email protected] 192.168.1.26:13.04% (3) of query, 2.96% (8) of all users

Query Abstract:
SELECT MIN (doc_his_id) as doc_his_id from t_******** WHERE doc_his_isteammate=n and doc_his_editor_user_id_encrypt= ' S ';

Query Sample:
Select min (doc_his_id) as doc_his_id from t_******** where doc_his_isteammate=1 and doc_his_editor_user_id_encrypt= ' Nfeacawqew2mican2 ';

In the above results, the execution of the statement (number of executions, object information, query record amount, time cost, source statistics) and other information at a glance, more convenient for the DBA to further analysis.

Original: http://blog.itpub.net/7607759/viewspace-692828/

MySQL Slow query log analysis tool Mysqlsla (GO)