Shulou(Shulou.com)06/01 Report--

This article mainly introduces mysqlsla installation tutorials and slow query log analysis, the things involved, from the theoretical analysis, the whole can be divided into three layers to describe, there are many books and documents on the Internet for your reference, in terms of practical significance, accumulated years of practical experience can be shared with you.

Installation

Mysqlsla is a function that helps analyze, filter, and sort statements, and can handle MySQL slow query logs, binary logs, and so on. On the whole, the function is very powerful. Can make SQL query data report, analysis including execution frequency, data volume, query consumption, etc.

And the tool has the function of removing duplicates with similar SQL statements, which can be sorted in a specified way (for example, when analyzing slow query logs, let them reverse the order according to the execution time of SQL statements, and it will be very convenient to locate the problem)

-install mysqlslaslow query log analysis tool-+

The copy code is 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 enter yes

Enter CPAN

The copy code is as follows:

Cpan > install YAML

Cpan > install Time::HiRes

# enter yes for all the prompts for the above installation

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

Introduction to the use of +-mysqlsla tool

Basic usage:

The copy code is as follows:

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

The output is similar to

The copy code is as follows:

Report for slow logs: slowquery.log

1.59k queries total, 69 unique

Sorted by'tasking summation'

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%)

95% of Time: 5.538256 s total, 230.761 ms avg, 202.917 ms to 271.056 msmax

Lock Time (s): 2.407 ms total, 93 μ s avg, 84 μ 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 to 0 max (0.005%)

Rows examined: 153.68k avg, 153.67k to 153.69k max (18.79%)

Database:

Users:

Root@localhost 127.0.0.1: 100.00% (26) 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 (`status` IN ('8831')) AND (`status` IN) 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 avg, 0 to 78 ms max (4.36%)

95% of Lock: 0 total, 0 avg, 0 to 0 max

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

Rows examined: 1 avg, 1 to 1 max (0.005%)

Database:

Users:

Root@ 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 number of queries (queries total), number of sql removed (unique)

Sort the contents of the output report (sorted by)

The most significant slow sql statistics, including average execution time, waiting lock time, total number of resulting rows, total number of rows scanned.

The number of Count, sql execution and its percentage in the total slow log.

Time, execution time, including total time, average time, minimum, maximum time, time as a percentage of total slow sql time.

95% of Time, removal of the fastest and slowest sql, coverage accounted for 95% of the sql execution time.

Lock Time, waiting time for the lock.

95% of Lock, 95% slow sql waiting lock time.

Rows sent, the number of result rows, including average, minimum, and maximum.

Rows examined, the number of rows scanned.

Database, which database does it belong to

Users, which user, IP, accounts for the percentage of sql executed by all users

Query abstract, abstract sql statement

Query sample, sql statement

Description of mysqlsla common parameters:

1)-log-type (- lt) type logs:

Use this parameter to determine the type of log, mainly slow, general, binary, msl, udl, and slow when analyzing slow log.

2)-sort:

Determine what parameters are used to sort the analysis results, which is sorted by t_sum by default.

T_sum: sort by total time

C_sum: sort by total number

C_sum_p: the percentage of the total number of times the sql statement was executed.

3)-top:

Displays the number of sql. The default is 10, indicating how many items are sorted by rule.

4)-statement-filter (- sf) [+ -] [TYPE]:

Filter the types of sql statements, such as select, update, drop.

[TYPE] there are SELECT, CREATE, DROP, UPDATE, INSERT, such as "+ SELECT,INSERT". If it does not appear, the default is -, that is, not included.

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

# for example, take only the select statements of the funsion database and sort them by total time, taking the first 1000 pieces of data

# Save 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 opening method-+

Modify the my.cnf configuration, add the following to the [mysqld] column, and enable slow log

The copy code is as follows:

[mysqld]

Log_slow_queries = ON

Log-slow-queries = / tmp/slow_query.log

Long_query_time = 1

2. Use mysqlsla to analyze MySQL slow query log

# query the 20 most recorded sql statements and write them to select.log

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

# count the slow query sql of all select whose slow query file is / data/mysql/127-slow.log, and show the sql with the longest execution time, and write it to sql_select.log

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

# Statistics that the database with slow query file / data/mysql/127-slow.log is all select of mydata and slow query sql of update, and write to sql_num.sql the sql with the largest number of queries

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

Read the above introduction of mysqlsla installation tutorials and slow query log analysis, hoping to give you some help in practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.