Comparison of mysql slow log analysis tools (1)

The slow log in mysql is a log tool used to record SQL statements that have been executed for a long time (beyond long_query_time seconds.

Enable slow log

Set in my. cnf

 
 

  
  
[mysqld] 
  
  
slow_query_log=on 
  
  
slow_query_log_file=mysql-slow 
 
 

Restart the MySQL service.

Five common tools

Mysqldumpslow

The log analysis tool for slow query officially provided by mysql. The output chart is as follows:

Main functions include calculating slow SQL statements

• Count)
• Average execution Time and total execution Time (Time)
• Lock)
• Total number of Rows sent to the client (Rows)
• Total number of Rows scanned (Rows)
• User and SQL statement itself (abstracted format, for example, limit 1, 20 is represented by limit N, N)

Mysqlsla

A log analysis tool launched by hackmysql.com (This website also maintains mysqlreport, mysq1_xchk, and other useful mysql tools ).

In general, the function is very powerful. The output data report is very helpful for analyzing the causes of slow queries, including the execution frequency, data volume, and query consumption.

The format is described as follows:

• Total number of queries (queries total), number of SQL statements after deduplication (unique)
• Sorted)
• The most important slow SQL statement statistics, including average execution time, lock wait time, total number of result rows, and total number of scanned rows.
• Count -- the number of SQL executions and percentage of the total slow log Count.
• Time-the execution Time, including the total Time, average Time, minimum, maximum Time, and Time, as a percentage of the total slow SQL Time.
• 95% of Time -- remove the fastest and slowest SQL statements, with a coverage rate of 95% of the SQL Execution Time.
• Lock Time -- the waiting Time.
• 95% of Lock -- 95% of slow SQL wait Lock time.
• Rows sent-Number of result Rows, including average, minimum, and maximum.
• Rows examined -- number of Rows scanned.
• Database -- Which Database belongs.
• Users-which user and IP address account for the percentage of SQL statements executed by all Users.
• Query abstract -- abstract SQL statement.
• Query sample -- SQL statement.

In addition to the above output, the official website also provides a lot of customized parameters, which is a rare and good tool.

Mysql-explain-slow-log

A perl script written by the Germans.

Http://www.willamowius.de/mysql-tools.html

Http://www.bt285.cn/content.php? Id = 1196863

Functionality flaws. We recommend that you not only print all slow logs to the screen, but also count the number of slow logs.