Use mysqlsla to analyze MySQL logs

Mysqlsla is a MySQL Log Analysis Tool launched by hackmysql.com. It can analyze mysql slow query logs and analyze slow queries. It can analyze slow query language for databases.

Mysqlsla is a MySQL Log Analysis Tool launched by hackmysql.com. It can analyze mysql slow query logs and analyze slow queries. It can analyze slow query language for databases.

Mysqlsla is a MySQL Log Analysis Tool launched by hackmysql.com. It can be used to analyze mysql slow query logs and analyze slow queries ,, it can analyze the execution frequency, scanned data volume, and consumed time of slow query statements for databases, and also provides statement Examples after analysis, which is better than mysqldumpslow.

Next we will introduce in detail the use of mysqlsla:

I. Installation of mysqlsla

Wget

Tar zvxf mysqlsla-2.03.tar.gz

Cd mysqlsla-2.03

Perl Makefile. PL

Make

Make install

Ii. usage parameters

1. -- log-type (-lt) type logs:

This parameter is used to specify the log types, including slow, general, binary, msl, and udl. When analyzing slow logs, slow is used.

2. -- sort:

Specify the parameters used to sort the analysis results. The default value is t_sum.

T_sum is sorted by the total time, And c_sum is sorted by the total number of times

3. -- top:

Displays the number of SQL statements. The default value is 10, indicating the number of first SQL statements sorted by rules.

4. -- statement-filter (-sf) [+-] [TYPE]:

Filter the types of SQL statements, such as select, update, and drop. [TYPE] SELECT, CREATE, DROP, UPDATE, INSERT, for example, "+ SELECT, INSERT". The default value is-, that is, not included.

5. -- databases db:

Log of the database to be processed:

Iii. Statistical parameter description

1. queries total: total number of queries

2. unique: Number of SQL statements after deduplication

3. sorted by: sort the most important slow SQL statement statistics in the output report, including average execution time, lock wait time, total number of result rows, and total number of scanned rows.

4. Count: Number of SQL statements executed and percentage of the total number of slow logs.

5. Time: the execution Time, including the total Time, average Time, minimum, maximum Time, and Time percentage of the total slow SQL Time.

6. 95% of Time: remove the fastest and slowest SQL statements, and the SQL Execution Time with a coverage rate of 95%.

7. Lock Time: the Lock wait Time.

8.95% of Lock: 95% of slow SQL wait Lock time.

9. Rows sent: Number of result Rows, including average, minimum, and maximum.

10. Rows examined: number of Rows scanned.

11. Database: Which Database does the Database belong.

12. Users: which user and IP address account for the percentage of SQL statements executed by all Users.

13. Query abstract: abstract SQL statement.

14. Query sample: SQL statement.

Iv. Examples

1. Count the slow query SQL of all select statements whose slow Query file is the dowload_server1-slow.log, display the 10 SQL statements with the longest execution time, and write to SQL _time. SQL

Mysqlsla-lt slow-sf "+ select"-top 10 dowload_server1-slow.log> test_time.log

2. count all the select and update slow query SQL statements of the database whose slow Query file is dowload_server1-slow.log as ultraxsmutf8, and query the maximum number of 100 SQL statements, and write to SQL _num. SQL

Mysqlsla-lt slow-sf "+ select, update"-top 100-sort c_sum-db ultraxsmutf8 dowload_server1-slow.log> num_time.log

5. FAQs

1. Error:

Can't locate DBI. pm in @ INC (@ INC contains:/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread -.......

Solution: perl-MCPAN-e 'Install dbi' or cpan DBI

2. Error:

Use of uninitialized value in numeric comparison (<=>) at/usr/bin/mysqlsla line 2962.

Solution: this error occurs when-sort t_su is used. The default value is time sorting. Therefore, this error can be removed.