MySQL Slow query

Installing Mysqlsla
Source Path: https://github.com/daniel-nichter/hackmysql.com
Source Storage Path:/USR/LOCAL/SRC
1. Get the source code
If you don't have a git command, install git first

Yum Install Gitcd/usr/local/srcgit clone https://github.com/daniel-nichter/hackmysql.com.gitcp-Rf hackmysql.com/opt /mysqlmonitorcd/opt/mysqlmonitor/mysqlslaperl makefile.plmake && make install

Execution Result:

/usr/bin/perl-mextutils::my-e ' My->fixin (Shift) '--blib/script/mysqlslamanifying blib/man3/ mysqlsla.3pminstalling/usr/local/share/perl5/mysqlsla.pminstalling/usr/local/share/man/man3/ Mysqlsla.3pminstalling/usr/local/bin/mysqlslaappending Installation Info To/usr/lib64/perl5/perllocal.pod

2. Install other Packages:

Yum install Perl-extutils-cbuilder perl-extutils-makemaker perl-time-hires perl-dbi

To list this installation package:

Yum list Perl-extutils-cbuilder Perl-extutils-makemaker perl-time-hires perl-dbi

LT: Indicates the log type, with slow, general, binary, MSL, UDL.
Sf:[+-][type],[type] has SELECT, CREATE, DROP, UPDATE, INSERT, such as "+select,insert", does not appear by default--that is, not included.
DB: The log of which library to process.
Top: Indicates how many first bars are sorted by rule.
Sort: sorted by a rule, T_sum: sorted by total time, C_sum: sorted by total number of times, c_sum_p:sql statement execution count as a percentage of total executions.

#开启慢查询日志项配置

Reference files optimized configuration of MySQL performance optimization my.cnf file: http://www.cnblogs.com/NiceTime/p/6848132.html

Slow_query_log_file=/opt/mysql/mysqllog/logfile/slow-query.log should be based on the MySQL slow query log path you installed.

#执行时间最长的20条sql语句 (default time long) [[email protected] mysqllog]# mysqlsla-lt slow--sort t_sum--top 20/opt/mysql/mysqllog/logfile /slow-query.log# statistics slow Query SQL for all select in the query file and displays the 20 most executed SQL statements [[email protected] mysqllog]# mysqlsla-lt SLOW-SF "+ Select "-top 20/opt/mysql/mysqllog/logfile/slow-query.log# the database in the slow query file is a slow query SQL for all SELECT and update test and query the most frequently 20 SQL statements, and write to Sql_num.log [[email protected] mysqllog]# mysqlsla-lt slow-sf "+select,update"-top 20-sort c_sum- DB test/opt/mysql/mysqllog/logfile/slow-query.log >/tmp/slowquery.log#mysqlsla output format Description: Queries total: Number of queries, Unique: The number of SQL to be sorted by: Output report content sort Count:sql execution times and percentage of total slow log number time: Execution time, including total time, average time, minimum, maximum duration, percentage of total slow SQL time 95% of time: removal of the fastest and slowest SQL, coverage accounted for 95% of SQL execution times lock time: Time to wait for a lock 95% of lock:95% slow SQL wait lock times rows Sent: Result row statistic quantity, including average, minimum, maximum number Volume rows examined: Number of rows scanned database: Which databases users: which user, IP, accounts for all users SQL percentage query abstract: Abstract SQL statement Query Sample:sql sample statement

MySQL Slow query