Mysqlsla Slow Query Analysis tool tutorial

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 and so on. 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-------------+Yum-Y Install Perl-Extutils-Cbuilder Perl-Extutils-Makemakeryum-Y Install Perl-DBI Perl-Dbd-Mysqlyum-Y Install Perl-Cpanperl-Mcpan-e shell into the cue line, enter Yes to enter Cpancpan>Install Yamlcpan>Install time::hires# above installs the prompt thing to enter Yes wget http://Hackmysql.com/Scripts/Mysqlsla-2.03. Tar.gztar XVFZ Mysqlsla-2.03. TAR.GZCD Mysqlsla-2.03Perl Makefile.plmake&&Make Install+ --Introduction to-----------------Mysqlsla tool usage-------------------------+basic How to use: Mysqlsla-Lt Slow-Sort T_sum-Top  +  /Tmp/Slow_query.Logthe output is similar to the report forSlow logs:slowquery.Log1.59k queries Total, the UniqueSorted by 'T_sum'Grand Totals:time109S, Lock0S, Rows sent142.02k, Rows examined +. 26m______________________________________________________________________001 ___Count: -(1.64%) Time:6.121513S total,235.443Msavg,202.917Ms to 311.527MsMax(5.63%)   the%  ofTime:5.538256S total,230.761Msavg,202.917Ms to 271.056MsMaxLock Time (s):2.407Ms Total, theΜsavg, -Μs to 139ΜsMax(1.55%)   the%  ofLock:2.152Ms Total, -Μsavg, -Μs to  AboutΜsMaxRows Sent:0 avg,0  to 0 Max(0.00%) Rows examined:153.68kavg,153.67k to 153.69kMax(18.79%)Database: Users:root@localhost 127.0.0.1:100.00%( -) ofQuery100.00%(1586) of  AllUsersquery Abstract:SET timestamp=NSELECTOrder_pid fromWfc_deliveryWHERE(Order_pidinch(S1)) and(Statusinch(S3))GROUP  byOrder_pid; Query Sample:SET timestamp=1387964641;SELECT' Order_pid ' from' Wfc_delivery 'WHERE(' Order_pid 'inch('8831') ) and(' Status 'inch('1','4',' -') )GROUP  byorder_pid; option Description: Total number of queries (queries), amount of SQL to go back (Unique) The contents of the output report are sorted (sorted byThe most significant slow SQL statistics, including the average execution time, the waiting lock time, the total number of result rows, 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. the%  ofTime, removal of the fastest and slowest SQL, coverage accounted for 95%the execution time of the SQL. Lock time, waiting for the lock. the%  ofLock, the%the slow SQL waits for the lock time. Rows sent, resulting row statistics, including average, minimum, maximum number. Rows examined, the number of lines scanned.Database, which database users, which user, IP, accounts for all user executed SQL percent query abstract, abstract SQL statement Query sample, SQL statement mysqlsla Common parameters Description:1)-Log-Type (-lt) Type logs: The type of log is established by this parameter, mainly slow, general,binary, MSL, UDL, slow log when analyzed by the enactment of the slow2)-Sort : Define what parameters to use to sort the results of the analysis, by default, by T_sum. T_sum: Sort by total time c_sum: Sort by total number of C_sum_p:sql statement executions as a percentage of the total execution count. 3)-Top: Shows the number of SQL, default is 10, which indicates the first number of bars sorted by rule4) –statement-Filter (-sf[+-][TYPE]: Filters The types of SQL statements, such as SELECT,Update、Drop.[TYPE]Have SELECT,CREATE,DROP,UPDATE,INSERTFor example+SELECT,INSERT", the default that does not appear is-, which is not included. 5)-DB: The log of which library to process: # For example, just take the SELECT statement for the Funsion database and sort by the total time, and fetch the first 1000 data # into the Slow_query.pretty.log file in the current directory Mysqlsla-Lt Slow-Sort T_sum-SF "+Select"-DB funsion-Top  +  /Tmp/Slow_query.Log >./Slow_query.pretty.Log+ ------------------------mysql Slow query log open method-------------------------+Modify the MY.CNF configuration,[mysqld]Add the following in one column to open the slow query log[mysqld]log_slow_queries=  onLog-Slow-Queries= /Tmp/Slow_query.LogLong_query_time= 1

Mysqlsla Slow Query Analysis tool tutorial