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
/usr/local/srcgit clone https://github.com/daniel-nichter/hackmysql.com.git- Rf hackmysql.com/opt//opt/mysqlmonitor/&& make install
Execution Result:
' my->fixin (Shift) ' --blib/script/mysqlslamanifying blib/man3//usr/local/share/perl5//usr/ local/share/man/man3//usr/local/bin//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 -/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 -/opt/mysql/mysqllog/logfile/slow-query.log# Statistics The database in slow query file is test for all select and update slow query SQL, and queries the most number of 20 SQL statements, and writes to Sql_num.log [[email protected] mysqllog]# Mysqlsla-lt SLOW-SF"+select,update"-top --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: Amount of SQL to go back sorted by: Output report content sort Count:sql executions and total slow Percent of log number time: execution times, including total time, average time, minimum, maximum time, percentage of total slow SQL time the% of time: removal of the fastest and slowest SQL, coverage accounted for 95%SQL Execution time lock time: Wait for the lock the% of Lock: the%Slow SQL Wait lock time rows Sent: Result row statistics, including average, minimum, maximum rows examined: Number of rows scanned database: Which databases are the users: which user, IP, accounts for the percentage of SQL performed by all users Qu eryAbstract: An abstract SQL statement query Sample:sql sample statement
Use of the Slow query log tool Mysqlsla