A good tool for analyzing MySQL slow query logs-mysqlsla

The slow query log of mysql database is a very important auxiliary log for tuning. However, the log format recorded by mysql by default is not friendly enough to read. This is determined by the mysql Logging rules, capture one log. Although the recorded information is detailed enough, reading slow query logs generated by mysql may be inefficient.

In addition to operating system commands, mysql also provides a command line tool for reading slowlog: mysqldumpslow, which provides a certain analysis and summary function, multiple similar SQL statements can be abstracted and displayed as one, but the functions are still simple. In addition, there are many third-party tools that can be used to analyze mysql slow query logs. Among them, mysqlsla, easy to use.

Mysqlsla can be used not only to process slow query logs, but also to analyze other logs, such as binary logs and common query logs. It provides very practical Abstract Functions for SQL statements, and concise and easy-to-use parameter settings, easy to use.

The latest version of MySQL SLA is 2.03. You can download it from the official website:
Http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz

Mysqlsla is a script written in perl. Running mysqlsla requires support from the perl-DBI and per-DBD-Mysql modules, therefore, before running mysqlsla, you must first install the DBI module and the corresponding database DBD driver. By default, the two modules are not installed in linux. You must download and install them 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 steps for DBI compilation and installation are as follows:
# Tar xvfz DBI-1.608.tar.gz
# Cd DBI-1.608
# Perl Makefile. PL
# Make
# Make test
# Make install

The steps for compiling and installing 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 mysql_config is required when DBD-mysql is installed. This command is included in the MySQL-devel installation package. If the software is not installed in the current system, you must first install MySQL-devel, otherwise, DBD-mysql may encounter errors during compilation.

After the preparation is complete, you can install mysqlsla. The compilation and installation steps are as follows:
# Tar xvfz mysqlsla-2.03.tar.gz
# Cd mysqlsla-2.03
# Perl Makefile. PL
# Make
# Make install

By default, the mysqlsla command is saved in the/usr/bin path and can be directly executed in any path. To analyze slow query log files, the simplest method of calling is as follows:
# Mysqlsla-lt slow [SlowLogFilePath]> [ResultFilePath]

For example, there are a bunch of the following statements in the raw slow log:
# Time: 110417 0:00:09
# User @ Host: 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 ';
# User @ Host: 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 ';
# User @ Host: 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 ';
# User @ Host: 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 ';
# User @ Host: 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 reading directly is poor. After mysqlsla is used for processing, the results are shown 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 total, 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.53 k avg, 5.70 k to 17.60 k max (1.07%)
Database: jssdb
Users:
Junsansi @ 192.168.1.27: 86.96% (20) of query, 11.11% (30) of all users
Jss @ 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 preceding results, the statement execution information (execution times, object information, query record quantity, time overhead, and source statistics) is clear at a glance, which is easier for DBA to further analyze.