MySQL log analysis software mysqlsla installation and use tutorial _ MySQL

This article describes how to install and use MySQL log analysis software mysqlsla. the Linux system is used as an environment example. if you need it, refer MySQL log analysis software mysqlsla installation and use tutorial _ MySQL

1. download mysqlsla

[root@localhost tmp]# wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz

--19:45:45-- http://hackmysql.com/scripts/mysqlsla-2.03.tar.gzResolving hackmysql.com... 64.13.232.157Connecting to hackmysql.com|64.13.232.157|:80... connected.HTTP request sent, awaiting response... 200 OKLength: 33674 (33K) [application/x-tar]Saving to: `mysqlsla-2.03.tar.gz.2'100%[=============================================================================>] 33,674   50.2K/s  in 0.7s  19:45:47 (50.2 KB/s) - `mysqlsla-2.03.tar.gz.2' saved [33674/33674]

II. decompress

[root@localhost tmp]# tar -zxvf mysqlsla-2.03.tar.gz

mysqlsla-2.03/mysqlsla-2.03/Changesmysqlsla-2.03/INSTALLmysqlsla-2.03/READMEmysqlsla-2.03/Makefile.PLmysqlsla-2.03/bin/mysqlsla-2.03/bin/mysqlslamysqlsla-2.03/META.ymlmysqlsla-2.03/lib/mysqlsla-2.03/lib/mysqlsla.pmmysqlsla-2.03/MANIFEST

[root@localhost tmp]# cd mysqlsla-2.03[root@localhost mysqlsla-2.03]# ls

bin Changes INSTALL lib Makefile.PL MANIFEST META.yml README

III. run the perl script to check the package dependency

[root@localhost mysqlsla-2.03]# perl Makefile.PL

Checking if your kit is complete...Looks goodWriting Makefile for mysqlsla

IV. Installation

[root@localhost mysqlsla-2.03]# make && make install;

cp lib/mysqlsla.pm blib/lib/mysqlsla.pmcp bin/mysqlsla blib/script/mysqlsla/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysqlslaManifying blib/man3/mysqlsla.3pmInstalling /usr/lib/perl5/site_perl/5.8.8/mysqlsla.pmInstalling /usr/share/man/man3/mysqlsla.3pmInstalling /usr/bin/mysqlslaWriting /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/mysqlsla/.packlistAppending installation info to /usr/lib/perl5/5.8.8/i386-linux-thread-multi/perllocal.pod

5. Basic use
1. usage parameter description
(1). -- log-type (-lt) type logs:
This parameter is used to specify the log types, including slow, general, binary, msl, and udl. when analyzing slow logs, slow is used.
(2). -- sort:
Specify the parameters used to sort the analysis results. The default value is t_sum.
T_sum is sorted by the total time, and c_sum is sorted by the total number of times
(3). -- top:
Displays the number of SQL statements. the default value is 10, indicating the number of first SQL statements sorted by rules.
(4). -- statement-filter (-sf) [+-] [TYPE]:
Filter the types of SQL statements, such as select, update, and drop. [TYPE] SELECT, CREATE, DROP, UPDATE, INSERT, for example, "+ SELECT, INSERT". The default value is-, that is, not included.
(5). -- databases db:
Log of the database to be processed:
2. Statistical parameter description
(1). queries total: total number of queries
(2). unique: Number of SQL statements after deduplication
(3). sorted by: sort the most important slow SQL statement statistics in the output report, including average execution time, lock wait time, total number of result rows, and total number of scanned rows.
(4). Count: the number of SQL executions and percentage of the total number of slow logs.
(5). Time: the execution Time, including the total Time, average Time, minimum, maximum Time, and percentage of Time in the total slow SQL Time.
(6). 95% of Time: remove the fastest and slowest SQL statements, and the SQL execution Time with a coverage rate of 95%.
(7). Lock Time: The Lock wait Time.
(8). 95% of Lock: 95% of slow SQL wait Lock time.
(9). Rows sent: Number of result Rows, including average, minimum, and maximum.
(10). Rows examined: number of Rows scanned.
(11). Database: the Database to which it belongs.
(12). Users: Which user and IP address account for the percentage of SQL statements executed by all Users.
(13). Query abstract: abstract SQL statement.
(14). Query sample: SQL statement.
3. example
(1). count the slow query SQL of all select statements whose slow query file is dowload_server1-slow.log, and display the 10 SQL statements with the longest execution time, and write to SQL _time. SQL

mysqlsla -lt slow -sf "+select" -top 10 dowload_server1-slow.log >test_time.log

(2) statistics slow query files for the dowload_server1-slow.log of the database is ultraxsmutf8 all select and update slow query SQL, and the maximum number of queries 100 SQL, and write to SQL _num. SQL

mysqlsla -lt slow -sf "+select,update" -top 100 -sort c_sum -db ultraxsmutf8 dowload_server1-slow.log >num_time.log

The above is the installation and usage tutorial of MySQL log analysis software mysqlsla _ MySQL. For more information, see PHP Chinese website (www.php1.cn )!