MySQL Slow query log analysis tool Mysqlsla

First, Introduction

Mysqlsla is a tool for analyzing the MySQL slow log , which analyzes the reasons for slow queries, including the number of times a SQL is executed and the percentage of slow log data, execution time, waiting time, and so on.

The company's database has a lot of slow query logs, resulting in a high system load, and MySQL slow query log file content format is not very good-looking, the recommended use of Mysqlsla, easy to use, easy to operate.

Second, installation Mysqlsla

System environment

CentOS Release 6.6 (Final) 2.6.32-504.el6.x86_64

The official website has been unable to download, the required documents have been shared in the Baidu cloud disk.

Baidu Cloud Disk File: Http://pan.baidu.com/s/1kVvyUYN

1. Download and install Mysqlsla

Mkdir-p/server/tools

From Baidu Cloud disk download mysqlsla-2.03.tar.gz to/server/tools directory

Cd/server/toolstar XF MYSQLSLA-2.03.TAR.GZCP Mysqlsla-2.03/bin/mysqlsla/usr/local/bin

Attention:

Note: You need to install PERL-DBI Perl-dbd-mysql if the following error occurs during execution.

Can ' t locate time/hires.pm in @INC (@INC contains:/usr/local/lib64/perl5/usr/local/share/perl5/usr/lib64/perl5/ Vendor_perl/usr/share/perl5/vendor_perl/usr/lib64/perl5/usr/share/perl5.) At./run Line 6.BEGIN failed--compilation aborted at./run Line 6.

Workaround:

Yum Install perl-dbi perl-dbd-mysql-y

Third, the use of Mysqlsla

Use the following columns:

mysqlsla -lt slow mysql-slow.log -sf  "+select"  -db dbname -top 10  -sort t_sum display results .... ______________________________________________________________________ 001  ___Count         : 1.30k   (83.88%) time           : 3924.256013 s total, 3.016338  s avg, 2.00058 s to 7.387539 s max   (80.80%)   95%  of time : 3518.198185 s total, 2.848743 s avg, 2.00058  s to 5.495059 s maxLock Time  (s)  : 233.705 ms total,  180 μs avg, 63 μs to 26.485 ms max   (26.48%)    95% of lock : 168.319 ms total, 136 μs avg, 63 μs  to 210 μs maxrows sent     : 67 avg, 0 to 323 max    (3.60%) rows examined : 853.77k avg, 852.04k to 855.00k max    (89.56%) database      :users          :        [email protected] 10.160.51.219 :  100.00%  (1301)  of query, 99.81%  (1548)  of all usersQuery  abstract:select a.*,b.real_name as user_name,b.order_operator_id,b.back_status from  approval_record a left join loan_order b on a.order_no=b.order_no  Where n=n and a.is_del = n and ifnull (a.message_content, ' S ')  <> ' S '  AND a.approval_user = N ORDER BY approval_record_id DESC; Query sample:select a.*,b.real_name as user_name,b.order_operator_id,b.back_status from approval_record a             left join loan_order b  on a.ORDER_NO=b.ORDER_NO                  where 1=1                and a.is_del = 0 and  ifnull (A.MESSAGE_CONTENT, ')   <> '                                                    and                       a.approval_user = 9184   

Command parameter explanation:

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 some rule, t_sum sorted by total time, c_sum by total number of times.

Result parameter Explanation:

Count:sql number of executions 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% SQL Execution time Lock time: Time to wait for a lock 95% of lock:95% slow SQL wait lock times rows Sent: Result row statistics, including average, minimum, maximum rows Examined: Number of rows scanned database: Which databases are users: which user, IP, accounts for all users SQL percentage executed by query Abstract: Abstract SQL statement Query Sample:sql statement

Reference blog:

Good tool for analyzing MySQL slow query log--mysqlsla

MySQL Slow log analysis tool Mysqlsla

This article is from "Barley tea" blog, please be sure to keep this source http://damaicha.blog.51cto.com/6046098/1867614

MySQL Slow query log analysis tool Mysqlsla