Installation and Use of MySQL SLA

Author: skate
Time: 2012/02/17

Installation and Use of MySQL SLA

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.gz
Resolving hackmysql.com... 64.13.232.157
Connecting to hackmysql.com | 64.13.232.157 |: 80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 33674 (33 K) [application/x-tar]
Saving to: 'mysqlsla-2.03.tar.gz. 2'

100% [============================================== ========================================================== ==============>] 33,674 50.2 K/s in 0.7 s

19:45:47 (50.2 KB/s)-'mysqlsla-2.03.tar.gz. 2 'saved [33674/33674]

2. Extract
[Root @ localhost tmp] # tar-zxvf mysqlsla-2.03.tar.gz
Mysqlsla-2.03/
Mysqlsla-2.03/Changes
Mysqlsla-2.03/INSTALL
Mysqlsla-2.03/README
Mysqlsla-2.03/Makefile. PL
Mysqlsla-2.03/bin/
Mysqlsla-2.03/bin/mysqlsla
Mysqlsla-2.03/META. yml
Mysqlsla-2.03/lib/
Mysqlsla-2.03/lib/mysqlsla. pm
Mysqlsla-2.03/MANIFEST
[Root @ localhost tmp] # mysqlsla-2.03 cd
[Root @ localhost: mysqlsla-2.03] # ls
Bin Changes INSTALL lib Makefile. pl manifest meta. yml README

3. Run the perl script to check the package dependency.
[Root @ localhost mysqlsla-2.03] # perl Makefile. PL
Checking if your kit is complete...
Looks good
Writing Makefile for mysqlsla

4. Installation
[Root @ localhost mysqlsla-2.03] # make & make install;
Cp lib/mysqlsla. pm blib/lib/mysqlsla. pm
Cp bin/mysqlsla blib/script/mysqlsla
/Usr/bin/perl "-MExtUtils: MY"-e "MY-> fixin (shift)" blib/script/mysqlsla
Manifying blib/man3/mysqlsla.3pm
Installing/usr/lib/perl5/site_perl/5.8.8/mysqlsla. pm
Installing/usr/share/man/man3/mysqlsla.3pm
Installing/usr/bin/mysqlsla
Writing/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/mysqlsla/. packlist
Appending installation info to/usr/lib/perl5/5.8.8/i386-linux-thread-multi/perllocal. pod
[Root @ localhost mysqlsla-2.03] #

5. Easy to use

Syntax:
Slow log: mysqlsla-lt slow. log
General log: mysqlsla-lt general. log
Binary log: mysqlbinlog bin. log | mysqlsla-lt binary-

Here, the slow log is used as an example:

[Root @ localhost mysqlsla-2.03] # mysqlsla-lt slow/tmp/mongo_slow.log | more
Report for slow logs:/tmp/127_slow.log
24 queries total, 6 unique
Sorted by 't_ Sum'
Grand Totals: Time 16 s, Lock 1 s, Rows sent 18, Rows Examined 2.10 M


__________________________________________________________________________ 001 ___
Count: 18 (75.00%)
Time: 15 s total, 833.333 MS avg, 0 to 8 s max (93.75%)
95% of Time: 7 s total, 411.765 MS avg, 0 to 4 s max
Lock Time (s): 0 total, 0 avg, 0 to 0 max (0.00%)
95% of Lock: 0 total, 0 avg, 0 to 0 max
Rows sent: 0 avg, 0 to 0 max (0.00%)
Rows examined: 116.51 k avg, 8 to 1.05 M max (99.99%)
Database:
Users:
Root @ localhost: 100.00% (18) of query, 100.00% (24) of all users

Query abstract:
Insert into t2 SELECT * FROM t2;

Query sample:
Insert into t2 select * from t2;
........

Option description:
Total number of queries (queries total), number of SQL statements after deduplication (unique)
Sorted)
The most important slow SQL statement statistics, including average execution time, lock wait time, total number of result rows, and total number of scanned rows.
Count, the number of SQL executions and percentage of the total slow log Count.
Time, execution Time, including the total Time, average Time, minimum, maximum Time, and Time as a percentage of the total slow SQL Time.
95% of Time removes the fastest and slowest SQL statements, and the SQL Execution Time with a coverage rate of 95%.
Lock Time, the waiting Time.
95% of Lock, 95% of slow SQL wait Lock time.
Rows sent, the number of result Rows, including average, minimum, and maximum.
Rows examined: number of Rows scanned.
Database, which Database belongs
Users, which user and IP address account for the percentage of SQL statements executed by all Users
Query abstract: abstract SQL statement
Query sample, SQL statement


Common mysqlsla parameters:

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: sort by total time
C_sum: sort by the total number of times
C_sum_p: Percentage of SQL statement executions in total.

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) db: the log of the database to be processed:

For example, only the select statement of the backup database and the first two records sorted by c_sum_p are used.

[Root @ localhost mysqlsla-2.03] # mysqlsla-lt slow-sort c_sum_p-sf "+ select"-db backup-top 2/tmp/__slow.log
Report for slow logs:/tmp/127_slow.log
4 queries total, 3 unique
Sorted by 'C _ sum_p'
Grand Totals: Time 1 s, Lock 1 s, Rows sent 18, Rows Examined 195


__________________________________________________________________________ 001 ___
Count: 2 (50.00%)
Time: 0 total, 0 avg, 0 to 0 max (0.00%)
Lock Time (s): 0 total, 0 avg, 0 to 0 max (0.00%)
Rows sent: 1 avg, 1 to 1 max (11.11%)
Rows examined: 86 avg, 77 to 94 max (87.69%)
Database:
Users:
Root @ localhost: 100.00% (2) of query, 100.00% (4) of all users

Query abstract:
Select sum (format (duration, N) AS duration FROM information_schema.profiling WHERE query_id = N;

Query sample:
Select sum (format (duration, 6) as duration from information_schema.profiling where query_id = 7;

__________________________________________________________________________ 002 ___
Count: 1 (25.00%)
Time: 1 s total, 1 s avg, 1 s to 1 s max (100.00%)
Lock Time (s): 1 s total, 1 s avg, 1 s to 1 s max (100.00%)
Rows sent: 4 avg, 4 to 4 max (22.22%)
Rows examined: 12 avg, 12 to 12 max (6.15%)
Database:
Users:
Root @ localhost: 100.00% (1) of query, 100.00% (4) of all users

Query abstract:
SELECT * FROM tt WHERE a = N;

Query sample:
Select * from tt where a = 2;
[Root @ localhost mysqlsla-2.03] #

[Root @ localhost mysqlsla-2.03] #

 

 

 

 

-------- End -------