MySQL slow query and slow query log Analysis Sharp Weapon Mysqlsla installation use

1. Install Mysqlsla

The code is as follows	Copy Code
wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz Tar zvxf mysqlsla-2.03.tar.gz CD mysqlsla-2.03 Perl makefile.pl Make Make install

Perl makefile.pl will complain, according to the error content Baidu has relevant information, nothing but yum install some things

2. Using Mysqlsla syntax

The code is as follows	Copy Code
Slow log:mysqlsla-lt Slow Slow.log General LOG:MYSQLSLA-LT General General.log Binary Log:mysqlbinlog Bin.log | Mysqlsla-lt binary-

3. Analysis Log

The code is as follows	Copy Code
./bin/mysqlsla. /slowlog/10.4.1.107.log.1206

Analysis results

auto-detected logs as slow logs
For slow logs:.. /slowlog/10.4.1.107.log.1206
799 queries total, 4 unique
Sorted by ' T_sum '
Grand Totals:time 1.25k S, Lock 0 s, rows sent 917, rows examined 426.83M

______________________________________________________________________ 001 ___
count:372 (46.56%)
time:581.404365 s Total, 1.562915 s AVG, 1.001595 s to 4.129653 s Max (46.56%)
All time:516.842801 S, 1.464144 s AVG, 1.001595 s to 2.815608 s Max
Lock time (s): 40.935 Mobile Ms Total, s AVG, S-211 max (44.74%)
All lock:38.016 Ms Total, 108 s AVG, 135? s Max
Rows sent:1 AVG, 1 to 1 max (40.57%)
Rows examined:387.71k avg, 225.34k to 725.90k Max (33.79%)
Database:yy
Users:
yyr@ 10.4.1.83:33.87% (126) of query, 34.79% (278) All users
yyr@ 10.4.1.81:33.33% (124) of query, 32.79% (262) All users
yyr@ 10.4.1.82:32.80% (122) of query, 32.42% (259) All Users

Query Abstract:
SET timestamp=n; Select COUNT (N) as CNT from Qz_feed WHERE (qz_id= ') and (Feed_key in (S1)) and (Created>n) and feed_id in (SELECT) _id from Qz_notice WHERE type_detail= ' S ' and Member_id=n and isnew=n);

Query Sample:
SET timestamp=1354637384;
SELECT COUNT (1) as CNT from Qz_feed WHERE (qz_id= ' 377 ') and (Feed_key in (' Speech_add ')) and (created>0) and feed_id in (SELECT from_id from Qz_notice WHERE type_detail= ' Speech_add ' and member_id=38011 and isnew=1);

Common parameter Description:

1)-log-type (-LT) type logs:
This parameter is used to define the type of log, mainly with slow, general, binary, MSL, UDL, and slow log when analyzed by a slow.
2)-sort:
Define what parameters to use to sort the analysis results, by default, by T_sum.
T_sum: Sorted by total time
C_sum: Sorted by total number of times
The percentage of the total execution number of C_sum_p:sql statement executions.
3)-top:
Displays the number of SQL, which defaults to 10, indicating how many of the first items sorted by rule
4) –statement-filter (-SF) [+-][type]:
Filter the types of SQL statements, such as SELECT, Update, Drop.
[TYPE] has SELECT, CREATE, DROP, UPDATE, INSERT, such as "+select,insert", does not appear by default is-, that is not included.
5 db: Which library's log to process:

For example, select statements that take only the backup library, the first 2 records sorted by c_sum_p

The code is as follows	Copy Code
Mysqlsla-lt slow-sort c_sum_p-sf "+select"-db backup-top

The effect after analysis

______________________________________________________________________ 001 ___
Count:18 (75%)
time:15 s Total, 833.333 MS Avg, 0 to 8 S max (93.75%)
All Time:7 S, 411.765 MS Avg, 0 to 4 s max
Lock time (s): 0 Total, 0 AVG, 0 to 0 max (0%)
lock:0 Total, 0 avg, 0 to 0 max
Rows sent:0 avg, 0 to 0 max (0%)
Rows examined:116.51k AVG, 8 to 1.05M Max (99.99%)
Database:
Users:
root@localhost:100.00% (in) query, 100.00% () of all users

Query Abstract:
INSERT into T2 SELECT * from T2;

Query Sample:
INSERT INTO T2 select * from T2;
........
Option Description:

Total number of queries (queries totals), after the amount of SQL (unique)

Output report content sort (sorted by)
The most significant slow SQL statistics, including average execution time, wait lock time, total number of resulting rows, total number of rows scanned.
Count, the number of executions of SQL and the percentage of the total number of slow logs.
time, execution times, including total time, average time, minimum, maximum time, percentage of total slow SQL time.
95% of time to get rid of the fastest and slowest SQL, with coverage accounting for up to one of SQL's execution times.
Lock time, waiting for the lock.
95% of lock, slow SQL wait for lock time.
rows sent, the resulting row counts, including the average, minimum, and maximum number.
rows examined, the number of rows scanned.
databases, which database
users, which users, IP, account for all user SQL percentages executed
Query abstract, abstract SQL Statement
Query sample, SQL statement
MySQL SLAs can be sorted according to the most expensive query (total query time), displaying the maximum minimum average query time, which is a very functional