Five common MySQL slow log analysis tools comparative analysis _php Tutorial

Source: Internet
Author: User
Tags perl script

Enable slow Log

There are two ways to enable: 1, in MY.CNF through Log-slow-queries[=file_name] 2, when mysqld process starts, specify the--log-slow-queries[=file_name] option

Five common tools for comparison

Mysqldumpslow, Mysqlsla, Myprofi, Mysql-explain-slow-log, Mysqllogfilter
Mysqldumpslow, MySQL official provides the slow query log analysis tool. The output chart is as follows: The main function is to count the number of occurrences of different slow SQL (Count), the maximum amount of time to execute (time), the cumulative total duration (times), the time to wait for a lock, the total number of rows sent to the client (rows), the total number of rows scanned (rows), The user and the SQL statement itself (the format is abstracted, for example, limit 1, 20 is indicated by the limit n,n).
Mysqlsla, hackmysql.com launched a log analysis tool (the site also maintains the Mysqlreport, Mysqlidxchk and other more useful MySQL tools)
Overall, the functionality is very powerful. The data report is very helpful for analyzing the reasons of slow query, including the frequency of execution, the amount of data, and the consumption of queries.

The format description is as follows: Total number of queries (queries totals), the number of de-SQL (unique) Output report content sort (sorted by) The most significant slow SQL statistics, including average execution time, waiting lock time, total number of result rows, total number of scanned rows.
Count, the number of executions of SQL and the percentage of total slow log count. Time, execution times, including total time, average time, minimum, maximum time, and percentage of total slow SQL time. 95% of time, removing the fastest and slowest SQL, coverage accounted for 95% of SQL execution time. Lock time, waiting for the lock. 95% of lock, 95% slow SQL waits for lock time. Rows sent, resulting row statistics, including average, minimum, maximum number.
Rows examined, the number of lines scanned. database, which databases users, which user, IP, accounts for the percentage of SQL performed by all users
Query abstract, SQL statement, SQL statement
In addition to the above output, the official also provides a lot of custom parameters, is a rare good tool.
Mysql-explain-slow-log, a Perl script written by the Germans.
Http://www.willamowius.de/mysql-tools.html


A bit of a flaw in functionality, not only does it print all the slow logs to the screen, but also counts only the number. It is not recommended. Mysql-log-filter, an analysis tool found on Google code. Two executable scripts for Python and PHP are available.
http://code.google.com/p/mysql-log-filter/function than the official Mysqldumpslow, more query time statistics (average, maximum, cumulative), and other functions are similar to Mysqldumpslow.
Features in addition to statistical information, but also for the output content of the layout and format, to ensure the overall output of the concise. Like concise report of friends, recommended to use a bit. Myprofi, an open source analysis tool written in pure PHP. The project is on the sourceforge.
http://myprofi.sourceforge.net/

Functionally, it lists the total number of slow queries and types, the number of SQL statements to go back, the number of executions, and the percentage of total slow log count.
Judging from the overall output style, it is more concise than mysql-log-filter. A lot of unnecessary content is omitted. It is recommended for users who want to see only the SQL statements and the number of executions.

Summarize

Tools/Features General statistics Advanced statistics script Advantage
mysqldumpslow support does not support perl mysql official comes with
mysqlsla support support perl powerful, complete data reporting, customization ability.
mysql-explain-slow-log support does not support perl no
mysql-log-filter support partially supports python or php without losing functionality, keep the output simple
Myprofi Support Not supported Php Very streamlined

http://www.bkjia.com/PHPjc/323505.html www.bkjia.com true http://www.bkjia.com/PHPjc/323505.html techarticle There are two ways to enable slow log: 1, in MY.CNF through Log-slow-queries[=file_name] 2, when mysqld process is started, specify--log-slow-queries[=file_name] option Comparison of five commonly used ...

  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.