Mysqlsla installation and slow query analysis

Installation

Mysqlsla is a feature that helps with parsing, filtering, and sorting of MySQL slow query logs, binary logs, and more. Overall, the functionality is very powerful. can make SQL query data report, analysis including execution frequency, data volume, query consumption, etc.

And the tool comes with similar SQL statement to the function, can be sorted according to the specified way (for example, when analyzing the slow query log, so that it according to the SQL statement execution Time counter-order, it can be very convenient to locate the problem)

-------------Install Mysqlsla Slow query log analysis tool-------------+

Copy the code code as follows:

Yum-y Install Perl-extutils-cbuilder Perl-extutils-makemaker

Yum-y Install PERL-DBI Perl-dbd-mysql

Yum-y Install Perl-cpan

PERL-MCPAN-E Shell

Enter the prompt line and yes

Enter CPAN

Copy the code code as follows:

Cpan > Install YAML

Cpan > Install Time::hires

# above the installation has the prompt thing to enter Yes

wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz

Tar Xvfz mysqlsla-2.03.tar.gz

CD mysqlsla-2.03

Perl makefile.pl

Make && make install

+-------------------Mysqlsla Tool Usage Introduction

Basic methods of Use:

Copy CodeThe code is as follows:

Mysqlsla-lt Slow-sort t_sum-top 1000/tmp/slow_query.log

The output is similar to

code as follows:

Report For slow Logs:slowquery.log
1.59k queries Total, a unique
Sorted by ' t_sum '
Grand totals:time 109 s, Lock 0 s , rows sent 142.02k, rows examined 21.26M
______________________________________________________________________ 001 ___
Count:26 (1.64%)
time:6.121513 s Total, 235.443 ms Avg, 202.917 ms to 311.527 Ms Max (5.63%)
  9 5% of time:5.538256 s total, 230.761 ms Avg, 202.917 ms to 271.056 Msmax
Lock Time (s): 2.407 Ms All, 93μs AVG, 8 4μs to 139μs Max (1.55%)
  95% of lock:2.152 Ms Total, 90μs AVG, 84μs to 99μs Max
Rows sent:0 avg, 0 t o 0 max (0.00%)
Rows examined:153.68k avg, 153.67k to 153.69k Max (18.79%)
Database:
Users:
 [EMAIL&N Bsp;protected] 127.0.0.1:100.00% (+) of query, 100.00% (1586) of allusers

Queryabstract:
SET timestamp=n; SELECT order_pid from Wfc_delivery WHERE (Order_pid in (S1)) and (status in (S3)) GROUP by Order_pid;

Querysample:
SET timestamp=1387964641;
SELECT ' order_pid ' from ' wfc_delivery ' WHERE (' Order_pid ' in (' 8831 ') ") and (' Status ' in (' 1 ', ' 4 ', '") ') GROUP by Order_ pid

______________________________________________________________________ 001 ___

count:19.16k (16.78%)

time:76711.82882 s Total, 4.003958 s AVG, 1.003058 s to 26.800847 s Max (19.75%)

95% of time:66412.55456 s total, 3.648841 s AVG, 1.003058 s to 9.204016 s Max

Lock time (s): 1.425805 s Total, 74 μs Avg, 0 to + Ms Max (4.36%)

95% of lock:0 Total, 0 avg, 0 to 0 max

Rows sent:0 avg, 0 to 0 max (0%)

Rows examined:1 AVG, 1 to 1 max (0%)

Database:

Users:

[Email protected] 172.31.43.29:100.00% (19159) of query, 97.62% (111483) of all users

Query Abstract:

SET timestamp=n; SELECT node_id as Nodeid, org_id as OrgID from t_org_info WHERE ets_project_id = NULL LIMIT N;

Query Sample:

SET timestamp=1504061325;

SELECT node_id as NodeId, org_id as orgId from t_org_info where ets_project_id = null limit 1;

______________________________________________________________________ 002 ___

Option Description:
Total queries (queries totals), number of SQL to go back (unique)
Sorting the contents of the output report (sorted by)
The most significant slow SQL statistics, including the average execution time, the waiting lock time, the total number of result rows, and the total number of rows scanned.
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 times.
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 belong to
Users, which user, IP, accounts for the percentage of SQL performed by all users
Query abstract, SQL statement after abstraction
Query sample, SQL statement

Mysqlsla Common parameter Description:

1)-log-type (-LT) type logs:
Using this parameter to develop the type of log, mainly slow, general, binary, MSL, UDL, analysis slow log by the enactment of the slow

2)-sort:
Define what parameters to use to sort the results of the analysis, and by default, sort by t_sum.
T_sum: Sort by total time
C_sum: Sort by total number of times
C_sum_p:sql the percentage of the total execution count for the execution of the statement.

3)-top:
Displays the number of SQL, by default, 10, which indicates the first number of bars 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, for example "+select,insert", does not appear by default--that is not included.

5)-db: The log of which library to process:

# For example, take only the SELECT statement from the Funsion database and sort the first 1000 data by the total time.
# saved to the Slow_query.pretty.log file in the current directory
Mysqlsla-lt slow-sort t_sum-sf "+select"-dbfunsion-top 1000/tmp/slow_query.log >/slow_query.pretty.log

+------------------------MySQL Slow Query Log open method-------------------------+

Modify the MY.CNF configuration and add the following in the [mysqld] column to turn on the slow query log

Copy Code The code is as follows:

[Mysqld]
Log_slow_queries = On
Log-slow-queries =/tmp/slow_query.log
Long_query_time = 1

2 , using Mysqlsla to analyze the MySQL slow query log

# query records up to 20 SQL statements, and write to Select.log

Mysqlsla-lt Slow--sort t_sum--top 20/data/mysql/127-slow.log >/tmp/select.log

# statistics slow Query SQL for all select of/data/mysql/127-slow.log, and shows the longest execution time of 100 SQL, and writes to Sql_select.log

Mysqlsla-lt slow-sf "+select"-top100/data/mysql/127-slow.log >/tmp/sql_select.log

# statistics slow query file for/data/mysql/127-slow.log database is mydata for all select and update slow query SQL, and queries up to 100 SQL, and writes to Sql_num.sql

Mysqlsla-lt slow-sf "+select,update"-top100-sort c_sum-db mydata/data/mysql/127-slow.log >/tmp/sql_num.log

This article is from the "Know the Ming" blog, please be sure to keep this source http://357712148.blog.51cto.com/6440370/1961193

Mysqlsla installation and slow query analysis