Use mysqlsla to analyze mysql logs

Using mysqlsla to analyze mysql logs 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. mysqlsla installation wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gztar zvxf mysqlsla-2.03.tar.gz www.2cto.com cd mysqlsla-2.03 perl Makefile. PL make install
2. Description of parameters 1. -- log-type (-lt) type logs: this parameter is used to specify log types, including slow, general, binary, msl, udl, when analyzing slow logs, you can define slot2. -- 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, which indicates the number of first SQL statements sorted by rules. -- 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 by www.2cto.com:
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: the number of SQL executions and percentage of the total slow log Count. 5. time: the execution Time, including the total Time, average Time, minimum, maximum Time, and Time as a percentage of the total slow SQL Time. 6. 95% of Time: removes the fastest and slowest SQL statements, and the SQL Execution Time with a coverage rate of 95%. 7. lock Time: the waiting 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: the Database to which it belongs. 12. users: user and IP address, which accounts for the percentage of SQL statements executed by all Users. 13. query abstract: abstract SQL statement. 14. query sample: SQL statement.
Iv. Example 1. count the slow query SQL for all the select statements in the dowload_server1-slow.log, and display the 10 SQL statements with the longest execution time, and write to SQL _time. SQL to mysqlsla-lt slow-sf "+ select"-top 10 dowload_server1-slow.log> test_time.log2. count the slow query SQL statements for all select and update statements of ultraxsmutf8 databases whose slow Query file is dowload_server1-slow.log, and query up to 100 SQL queries, and write to SQL _num. SQL to mysqlsla-lt slow-sf "+ select, update"-top 100-sort c_sum-db ultraxsmutf8 dowload_server1-slow.log> num_time.log www.2cto.com
Five, FAQ1. 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. By default, time sorting is adopted, so the error can be removed.